Condividi tramite

Modifica dei dati Large-Value (max) in ADO.NET

I tipi di dati LOB (Large Object) sono quelli che superano le dimensioni massime della riga di 8 kilobyte (KB). SQL Server fornisce un max identificatore per varchari tipi di dati , nvarchare varbinary per consentire l'archiviazione di valori di dimensioni pari a 2^32 byte. Le colonne di tabella e le variabili Transact-SQL possono specificare varchar(max)tipi di dati , nvarchar(max)o varbinary(max) . In ADO.NET, i max tipi di dati possono essere recuperati da un DataReader oggetto e possono essere specificati anche come valori dei parametri di input e output senza alcun trattamento speciale. Per i tipi di dati di grandi dimensioni varchar , i dati possono essere recuperati e aggiornati in modo incrementale.

I max tipi di dati possono essere usati per i confronti, come variabili Transact-SQL e per la concatenazione. Possono essere utilizzati nelle clausole DISTINCT, ORDER BY, GROUP BY di un'istruzione SELECT, così come nelle aggregazioni, accoppiamenti e sottoselezioni.

Per altre informazioni, vedere Uso dei tipi di dati Large-Value.

restrizioni di tipo Large-Value

Le restrizioni seguenti si applicano ai max tipi di dati, che non esistono per i tipi di dati più piccoli:

  • Un sql_variant oggetto non può contenere un tipo di dati di grandi dimensioni varchar .

  • Non è possibile specificare colonne di grandi dimensioni varchar come colonna chiave in un indice. Sono consentiti in una colonna inclusa in un indice non clusterizzato.

  • Le colonne di grandi dimensioni varchar non possono essere usate come colonne chiave di partizionamento.

Utilizzo dei tipi di Large-Value in Transact-SQL

La funzione Transact-SQL OPENROWSET è un metodo monouso per la connessione e l'accesso ai dati remoti. Include tutte le informazioni di connessione necessarie per accedere ai dati remoti da un'origine dati OLE DB. OPENROWSET è possibile fare riferimento nella clausola FROM di una query come se fosse un nome di tabella. Può anche essere fatto riferimento come tabella di destinazione di un'istruzione INSERT, UPDATE o DELETE, soggetta alle funzionalità del provider OLE DB.

La funzione OPENROWSET include il provider di set di righe BULK, che permette di leggere direttamente i dati da un file senza doverli caricare in una tabella di destinazione. In questo modo è possibile usare OPENROWSET in una semplice istruzione INSERT SELECT.

Gli OPENROWSET BULK argomenti di opzione forniscono un controllo significativo sulla posizione in cui iniziare e terminare i dati di lettura, su come gestire gli errori e sul modo in cui vengono interpretati i dati. Ad esempio, è possibile specificare che il file di dati venga letto come set di righe a riga singola, a colonna singola di tipo varbinary, varcharo nvarchar.

Nell'esempio seguente viene inserita una foto nella tabella ProductPhoto nel database di esempio AdventureWorks. Quando si usa il BULK OPENROWSET provider, è necessario specificare l'elenco denominato di colonne anche se non si inseriscono valori in ogni colonna. La chiave primaria in questo caso è definita come colonna Identity e può essere omessa dall'elenco di colonne. Si noti che è necessario specificare anche un nome di correlazione alla fine dell'istruzione OPENROWSET , che in questo caso è ThumbnailPhoto. Ciò è correlato alla colonna nella ProductPhoto tabella in cui viene caricato il file.

INSERT Production.ProductPhoto (  
    ThumbnailPhoto,
    ThumbnailPhotoFilePath,
    LargePhoto,
    LargePhotoFilePath)  
SELECT ThumbnailPhoto.*, null, null, N'tricycle_pink.gif'  
FROM OPENROWSET
    (BULK 'c:\images\tricycle.jpg', SINGLE_BLOB) ThumbnailPhoto

Aggiornamento dei dati tramite UPDATE.WRITE

L'istruzione Transact-SQL UPDATE ha una nuova sintassi WRITE per modificare il contenuto delle colonne varchar(max), nvarchar(max) o varbinary(max). In questo modo è possibile eseguire aggiornamenti parziali dei dati. La sintassi UPDATE .WRITE è mostrata qui in forma abbreviata:

AGGIORNAMENTO

{ <object> }

IMPOSTA

{ column_name = { . WRITE ( expression , @Offset , @Length ) }

Il metodo WRITE specifica che verrà modificata una sezione del valore del column_name . L'espressione è il valore che verrà copiato nella column_name, @Offset è il punto iniziale in cui verrà scritta l'espressione e l'argomento @Length è la lunghezza della sezione nella colonna.

Se Poi
L'espressione è impostata su NULL @Length viene ignorato e il valore in column_name viene troncato al punto specificato @Offset.
@Offset è NULL L'operazione di aggiornamento aggiunge l'espressione alla fine del valore column_name esistente e @Length viene ignorata.
@Offset è maggiore della lunghezza del valore di "column_name" SQL Server restituisce un errore.
@Length è NULL L'operazione di aggiornamento rimuove tutti i dati da @Offset fino alla fine del valore column_name.

Nota

Né @Offset né @Length può essere un numero negativo.

Esempio

Questo esempio Transact-SQL aggiorna un valore parziale in DocumentSummary, una nvarchar(max) colonna della tabella Document nel database AdventureWorks. La parola 'components' viene sostituita dalla parola 'features' specificando la parola di sostituzione, la posizione iniziale (offset) della parola da sostituire nei dati esistenti e il numero di caratteri da sostituire (lunghezza). L'esempio include istruzioni SELECT prima e dopo l'istruzione UPDATE per confrontare i risultati.

USE AdventureWorks;  
GO  
--View the existing value.  
SELECT DocumentSummary  
FROM Production.Document  
WHERE DocumentID = 3;  
GO  
-- The first sentence of the results will be:  
-- Reflectors are vital safety components of your bicycle.  
  
--Modify a single word in the DocumentSummary column  
UPDATE Production.Document  
SET DocumentSummary .WRITE (N'features',28,10)  
WHERE DocumentID = 3 ;  
GO
--View the modified value.  
SELECT DocumentSummary  
FROM Production.Document  
WHERE DocumentID = 3;  
GO  
-- The first sentence of the results will be:  
-- Reflectors are vital safety features of your bicycle.

Utilizzo dei tipi di Large-Value in ADO.NET

È possibile utilizzare tipi di valore di grandi dimensioni in ADO.NET specificando tipi di valore di grandi dimensioni come SqlParameter oggetti in un SqlDataReader per restituire un set di risultati o usando un SqlDataAdapter oggetto per riempire un oggetto DataSet/DataTable. Non esiste alcuna differenza tra il modo in cui si lavora con un tipo valore di grandi dimensioni e il relativo tipo di dati relativo a valori più piccoli.

Uso di GetSqlBytes per recuperare i dati

Il GetSqlBytes metodo di SqlDataReader può essere utilizzato per recuperare il contenuto di una varbinary(max) colonna. Il frammento di codice seguente presuppone un oggetto denominato SqlCommand che seleziona i cmd dati da una tabella e un varbinary(max) oggetto denominato SqlDataReader che recupera i dati come reader.SqlBytes

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)  
While reader.Read()  
    Dim bytes As SqlBytes = reader.GetSqlBytes(0)  
End While  

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);  
while (reader.Read())  
    {  
        SqlBytes bytes = reader.GetSqlBytes(0);  
    }

Uso di GetSqlChars per recuperare i dati

Il GetSqlChars metodo di SqlDataReader può essere utilizzato per recuperare il contenuto di una varchar(max) colonna o nvarchar(max) . Il frammento di codice seguente presuppone un SqlCommand oggetto denominato cmd che seleziona i nvarchar(max) dati da una tabella e un SqlDataReader oggetto denominato reader che recupera i dati.

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)  
While reader.Read()  
    Dim buffer As SqlChars = reader.GetSqlChars(0)  
End While  

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);  
while (reader.Read())  
{  
    SqlChars buffer = reader.GetSqlChars(0);  
}

Uso di GetSqlBinary per recuperare i dati

Il GetSqlBinary metodo di un SqlDataReader oggetto può essere utilizzato per recuperare i contenuti di una varbinary(max) colonna. Il frammento di codice seguente presuppone un SqlCommand oggetto denominato cmd che seleziona i varbinary(max) dati da una tabella e un SqlDataReader oggetto denominato reader che recupera i dati come SqlBinary flusso.

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)  
While reader.Read()  
    Dim binaryStream As SqlBinary = reader.GetSqlBinary(0)  
End While  

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);  
while (reader.Read())  
    {  
        SqlBinary binaryStream = reader.GetSqlBinary(0);  
    }

Uso di GetBytes per recuperare i dati

Il GetBytes metodo di SqlDataReader legge un flusso di byte dall'offset della colonna specificato in un array di byte iniziando dall'offset dell'array specificato. Il frammento di codice seguente presuppone un SqlDataReader oggetto denominato reader che recupera i byte in una matrice di byte. Si noti che, a differenza di GetSqlBytes, GetBytes richiede una dimensione per il buffer dell'array.

While reader.Read()  
    Dim buffer(4000) As Byte  
    Dim byteCount As Integer = _  
    CInt(reader.GetBytes(1, 0, buffer, 0, 4000))  
End While  

while (reader.Read())  
{  
    byte[] buffer = new byte[4000];  
    long byteCount = reader.GetBytes(1, 0, buffer, 0, 4000);  
}

Uso di GetValue per recuperare i dati

Il metodo GetValue di SqlDataReader legge il valore dall'offset di colonna specificato in un array. Il frammento di codice seguente presuppone un SqlDataReader oggetto denominato reader che recupera i dati binari dal primo offset di colonna e quindi i dati stringa dall'offset della seconda colonna.

While reader.Read()  
    ' Read the data from varbinary(max) column  
    Dim binaryData() As Byte = CByte(reader.GetValue(0))  
  
    ' Read the data from varchar(max) or nvarchar(max) column  
    Dim stringData() As String = Cstr((reader.GetValue(1))  
End While  

while (reader.Read())  
{  
    // Read the data from varbinary(max) column  
    byte[] binaryData = (byte[])reader.GetValue(0);  
  
    // Read the data from varchar(max) or nvarchar(max) column  
    String stringData = (String)reader.GetValue(1);  
}

Conversione da tipi valore di grandi dimensioni a tipi CLR

È possibile convertire il contenuto di una varchar(max) colonna o nvarchar(max) usando uno dei metodi di conversione di stringhe, ad esempio ToString. Il frammento di codice seguente presuppone un SqlDataReader oggetto denominato reader che recupera i dati.

While reader.Read()  
    Dim str as String = reader(0).ToString()  
    Console.WriteLine(str)  
End While  

while (reader.Read())  
{  
     string str = reader[0].ToString();  
     Console.WriteLine(str);  
}

Esempio

Il codice seguente recupera il nome e l'oggetto LargePhoto dalla ProductPhoto tabella nel AdventureWorks database e lo salva in un file. L'assembly deve essere compilato con un riferimento allo spazio dei System.Drawing nomi . Il GetSqlBytes metodo di SqlDataReader restituisce un SqlBytes oggetto che espone una Stream proprietà . Il codice usa questo oggetto per creare un nuovo Bitmap oggetto e quindi lo salva nella gif ImageFormat.

static void TestGetSqlBytes(int documentID, string filePath)
{
    // Assumes GetConnectionString returns a valid connection string.
    using (SqlConnection connection =
               new(GetConnectionString()))
    {
        SqlCommand command = connection.CreateCommand();
        SqlDataReader reader = default!;
        try
        {
            // Setup the command
            command.CommandText =
                "SELECT LargePhotoFileName, LargePhoto "
                + "FROM Production.ProductPhoto "
                + "WHERE ProductPhotoID=@ProductPhotoID";
            command.CommandType = CommandType.Text;

            // Declare the parameter
            SqlParameter paramID =
                new("@ProductPhotoID", SqlDbType.Int)
                {
                    Value = documentID
                };
            command.Parameters.Add(paramID);
            connection.Open();

            string photoName = default!;

            reader = command.ExecuteReader(CommandBehavior.CloseConnection);

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    // Get the name of the file.
                    photoName = reader.GetString(0);

                    // Ensure that the column isn't null
                    if (reader.IsDBNull(1))
                    {
                        Console.WriteLine($"{photoName} is unavailable.");
                    }
                    else
                    {
                        SqlBytes bytes = reader.GetSqlBytes(1);
                        using (Bitmap productImage = new(bytes.Stream))
                        {
                            var fileName = filePath + photoName;

                            // Save in gif format.
                            productImage.Save(fileName, ImageFormat.Gif);
                            Console.WriteLine($"Successfully created {fileName}.");
                        }
                    }
                }
            }
            else
            {
                Console.WriteLine("No records returned.");
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            reader?.Dispose();
        }
    }
}

Private Sub GetPhoto(
  ByVal documentID As Integer, ByVal filePath As String)
    ' Assumes GetConnectionString returns a valid connection string.
    Using connection As New SqlConnection(GetConnectionString())
        Dim command As SqlCommand = connection.CreateCommand()
        Dim reader As SqlDataReader
        Try
            ' Setup the command
            command.CommandText =
              "SELECT LargePhotoFileName, LargePhoto FROM" _
                & " Production.ProductPhoto" _
                & " WHERE ProductPhotoID=@ProductPhotoID"
            command.CommandType = CommandType.Text

            ' Declare the parameter
            Dim paramID As SqlParameter =
                New SqlParameter("@ProductPhotoID", SqlDbType.Int)
            paramID.Value = documentID
            command.Parameters.Add(paramID)
            connection.Open()

            Dim photoName As String

            reader =
             command.ExecuteReader(CommandBehavior.CloseConnection)

            If reader.HasRows Then
                While reader.Read()
                    ' Get the name of the file
                    photoName = reader.GetString(0)

                    ' Ensure that the column isn't null
                    If (reader.IsDBNull(1)) Then
                        Console.WriteLine("{0} is unavailable.", photoName)
                    Else
                        Dim bytes As SqlBytes = reader.GetSqlBytes(1)
                        Using productImage As New Bitmap(bytes.Stream)
                            Dim fileName As String = filePath & photoName

                            ' Save in gif format.
                            productImage.Save(
                              fileName, ImageFormat.Gif)
                            Console.WriteLine("Successfully created {0}.", fileName)
                        End Using
                    End If
                End While
            Else
                Console.WriteLine("No records returned.")
            End If
        Catch ex As Exception
            Console.WriteLine("Exception: {0}", ex.Message)
        End Try
    End Using
End Sub

Uso di parametri di tipo valore di grandi dimensioni

I tipi valore di grandi dimensioni possono essere usati negli SqlParameter oggetti nello stesso modo in cui si usano tipi valore più piccoli negli SqlParameter oggetti . È possibile recuperare tipi di valore di grandi dimensioni come valori SqlParameter, come illustrato nell'esempio seguente. Il codice presuppone che la seguente procedura memorizzata GetDocumentSummary esista nel database di esempio AdventureWorks. La stored procedure accetta un parametro di input denominato @DocumentID e restituisce il contenuto della colonna DocumentSummary nel @DocumentSummary parametro di output.

CREATE PROCEDURE GetDocumentSummary
(  
    @DocumentID int,  
    @DocumentSummary nvarchar(MAX) OUTPUT  
)  
AS  
SET NOCOUNT ON  
SELECT  @DocumentSummary=Convert(nvarchar(MAX), DocumentSummary)  
FROM    Production.Document  
WHERE   DocumentID=@DocumentID

Esempio

Il codice ADO.NET crea oggetti SqlConnection e SqlCommand per eseguire la stored procedure GetDocumentSummary e recuperare il riepilogo del documento, che è archiviato come un tipo di valore grande. Il codice passa un valore per il @DocumentID parametro di input e visualizza i risultati passati di nuovo nel @DocumentSummary parametro di output nella finestra console.

static string? GetDocumentSummary(int documentID)
{
    // Assumes GetConnectionString returns a valid connection string.
    using (SqlConnection connection =
               new(GetConnectionString()))
    {
        connection.Open();
        SqlCommand command = connection.CreateCommand();
        try
        {
            // Set up the command to execute the stored procedure.
            command.CommandText = "GetDocumentSummary";
            command.CommandType = CommandType.StoredProcedure;

            // Set up the input parameter for the DocumentID.
            SqlParameter paramID =
                new("@DocumentID", SqlDbType.Int)
                {
                    Value = documentID
                };
            command.Parameters.Add(paramID);

            // Set up the output parameter to retrieve the summary.
            SqlParameter paramSummary =
                new("@DocumentSummary",
                SqlDbType.NVarChar, -1)
                {
                    Direction = ParameterDirection.Output
                };
            command.Parameters.Add(paramSummary);

            // Execute the stored procedure.
            command.ExecuteNonQuery();
            Console.WriteLine((string)paramSummary.Value);
            return (string)paramSummary.Value;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            return null;
        }
    }
}

Private Function GetDocumentSummary( _
  ByVal documentID As Integer) As String

    ' Assumes GetConnectionString returns a valid connection string.
    Using connection As New SqlConnection(GetConnectionString())
        connection.Open()
        Dim command As SqlCommand = connection.CreateCommand()

        ' Setup the command to execute the stored procedure.
        command.CommandText = "GetDocumentSummary"
        command.CommandType = CommandType.StoredProcedure

        ' Set up the input parameter for the DocumentID.
        Dim paramID As SqlParameter = _
            New SqlParameter("@DocumentID", SqlDbType.Int)
        paramID.Value = documentID
        command.Parameters.Add(paramID)

        ' Set up the output parameter to retrieve the summary.
        Dim paramSummary As SqlParameter = _
            New SqlParameter("@DocumentSummary", _
               SqlDbType.NVarChar, -1)
        paramSummary.Direction = ParameterDirection.Output
        command.Parameters.Add(paramSummary)

        ' Execute the stored procedure.
        command.ExecuteNonQuery()
        Console.WriteLine(paramSummary.Value)
        Return paramSummary.Value.ToString
    End Using
End Function

Vedere anche

  • Last updated on