Modifica di dati con valori di grandi dimensioni (max) in ADO.NET

Nelle versioni di SQL Server precedenti a SQL Server 2005 i tipi di dati di grandi dimensioni (LOB) richiedevano una speciale gestione. I tipi di dati LOB sono quelli che superano la dimensione massima di 8 kilobyte (KB) per le righe. In SQL Server 2005 è stato introdotto un identificatore max per i tipi di dati varchar, nvarchar e varbinary per consentire l'archiviazione di valori di dimensioni pari a 2^32 byte. Nelle colonne di tabelle e nelle variabili Transact-SQL possono essere specificati tipi di dati varchar(max), nvarchar(max) o varbinary(max). In ADO.NET i nuovi tipi di dati max possono essere recuperati da un DataReader e possono inoltre essere specificati come parametri di input e di output senza richiedere una gestione speciale. Per tipi di dati varchar di grandi dimensioni, è possibile recuperare e aggiornare i dati in modo incrementale.

I tipi di dati max possono essere utilizzati per eseguire confronti, come le variabili Transact-SQL, e per eseguire concatenazioni. È inoltre utilizzarli nelle clausole DISTINCT, ORDER BY e GROUP BY di un'istruzione SELECT, nonché nelle aggregazioni, nelle unioni e nelle sottoquery.

Nella tabella seguente vengono forniti collegamenti alla documentazione online di SQL Server.

Restrizioni per i tipi di valori di grandi dimensioni

Le seguenti restrizioni si applicano ai tipi di dati max e non ai tipi di dati di dimensioni minori:

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

• Le colonne varchar di grandi dimensioni non possono essere specificate come colonne di chiave primaria in un indice. Sono consentite in una colonna inclusa in un indice non cluster.

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

Utilizzo di tipi di valori di grandi dimensioni in Transact-SQL

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

In SQL Server 2005 la funzione OPENROWSET è stata migliorata con l'aggiunta del provider di set di righe BULK, che consente di leggere i dati direttamente da un file senza caricare i dati in una tabella di destinazione. Questo consente l'utilizzo di OPENROWSET in una semplice istruzione INSERT SELECT.

Gli argomenti dell'opzione OPENROWSETBULK forniscono un controllo notevole sul punto in cui iniziare e terminare la lettura dei dati, sulla gestione degli errori e sull'interpretazione dei dati. È ad esempio possibile specificare che il file di dati venga letto come una singola riga o come un set di righe di una singola colonna di tipo varbinary, varchar o nvarchar. Per la sintassi e le opzioni complete, vedere la documentazione online di SQL Server.

Nell'esempio seguente viene inserita una foto nella tabella ProductPhoto del database di esempio AdventureWorks. Se si utilizza il provider BULKOPENROWSET, è necessario fornire l'elenco di colonne denominato anche se non si inseriscono valori in ogni colonna. In questo caso, la chiave primaria è definita come colonna Identity e può essere omessa dall'elenco di colonne. Notare che è necessario fornire anche un nome di correlazione alla fine dell'istruzione OPENROWSET, che in questo caso è ThumbnailPhoto. Tale nome è correlato alla colonna della tabella ProductPhoto 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 di dati tramite UPDATE .WRITE

L'istruzione Transact-SQL UPDATE include una nuova sintassi WRITE per modificare il contenuto delle colonne varchar(max), nvarchar(max) o varbinary(max). In tal modo è possibile eseguire aggiornamenti parziali dei dati. La sintassi UPDATE .WRITE viene illustrata di seguito in formato abbreviato:

UPDATE

{ <oggetto> }

SET

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

Il metodo WRITE specifica che una sezione del valore di column_name verrà modificato. L'espressione corrisponde al valore che verrà copiato in column_name, l'argomento @Offset al punto di inizio in cui verrà scritta l'espressione e l'argomento @Length alla lunghezza della sezione nella colonna.

Nota
Il valore di @Offset o di @Length non può essere un numero negativo.

Esempio

In questo esempio di Transact-SQL viene aggiornato un valore parziale di DocumentSummary, una colonna nvarchar(max) della tabella Document nel database AdventureWorks. La parola "components" viene sostituita con la 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). Per confrontare i risultati, nell'esempio sono incluse le istruzioni SELECT precedenti e successive all'istruzione UPDATE.

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 di tipi di valori di grandi dimensioni in ADO.NET

È possibile utilizzare tipi di valore di grandi dimensioni in ADO.NET specificandoli come oggetti SqlParameter in un oggetto SqlDataReader per restituire un set di risultati oppure utilizzando un oggetto SqlDataAdapter per compilare un oggetto DataSet o DataTable. Non vi è differenza tra il modo di utilizzare un tipo di valore di grandi dimensioni e il relativo tipo di dati del valore di dimensioni minori.

Utilizzo di GetSqlBytes per il recupero di dati

È possibile utilizzare il metodo GetSqlBytes del tipo SqlDataReader per recuperare il contenuto di una colonna varbinary(max). Il seguente frammento di codice presuppone un oggetto SqlCommand denominato cmd che consente di selezionare dati varbinary(max) da una tabella e un oggetto SqlDataReader denominato reader che consente di recuperare i dati come oggetto 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);
    }

Utilizzo di GetSqlChars per il recupero di dati

È possibile utilizzare il metodo GetSqlChars dell'oggetto SqlDataReader per recuperare il contenuto di una colonna varchar(max) o nvarchar(max). Il seguente frammento di codice presuppone un oggetto SqlCommand denominato cmd che consente di selezionare dati nvarchar(max) da una tabella e un oggetto SqlDataReader denominato reader che consente di recuperare 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);
}

Utilizzo di GetSqlBinary per il recupero di dati

È possibile utilizzare il metodo GetSqlBinary di un oggetto SqlDataReader per recuperare il contenuto di una colonna varbinary(max). Il seguente frammento di codice presuppone un oggetto SqlCommand denominato cmd che consente di selezionare dati varbinary(max) da una tabella e un oggetto SqlDataReader denominato reader che consente di recuperare i dati come flusso SqlBinary.

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);
    }

Utilizzo di GetBytes per il recupero di dati

Il metodo GetBytes di un oggetto SqlDataReader consente di leggere un flusso di byte dall'offset della colonna specificata nella matrice di byte a partire dall'offset della matrice specificata. Il seguente frammento di codice presuppone un oggetto SqlDataReader denominato reader che consente di recuperare byte in una matrice di byte. Notare che, a differenza di GetSqlBytes, con GetBytes è necessario specificare una dimensione per il buffer della matrice.

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);
}

Utilizzo di GetValue per il recupero di dati

Il metodo GetValue di un oggetto SqlDataReader consente di leggere il valore dall'offset della colonna specificata in una matrice. Il seguente frammento di codice presuppone un oggetto SqlDataReader denominato reader che consente di recuperare dati binari dall'offset della prima colonna e dati di tipo 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 di valore di grandi dimensioni a tipi CLR

È possibile convertire il contenuto di una colonna varchar(max) o nvarchar(max) utilizzando uno dei metodi disponibili per la conversione di stringhe, ad esempio ToString. Il seguente frammento di codice presuppone un oggetto SqlDataReader denominato reader che consente di recuperare 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 consente di recuperare il nome e l'oggetto LargePhoto dalla tabella ProductPhoto del database AdventureWorks e di salvarlo in un file. È necessario compilare l'assembly con un riferimento allo spazio dei nomi System.Drawing. Il metodo GetSqlBytes del tipo SqlDataReader restituisce un oggetto SqlBytes che espone una proprietà Stream. Questa viene utilizzata nel codice per creare un nuovo oggetto Bitmap, che verrà quindi salvato nel formato ImageFormat GIF.

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 Bitmap = _
                          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

static private void TestGetSqlBytes(int documentID, string filePath)
{
    // Assumes GetConnectionString returns a valid connection string.
    using (SqlConnection connection =
               new SqlConnection(GetConnectionString()))
    {
        SqlCommand command = connection.CreateCommand();
        SqlDataReader reader = null;
        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 SqlParameter("@ProductPhotoID", SqlDbType.Int);
            paramID.Value = documentID;
            command.Parameters.Add(paramID);
            connection.Open();

            string photoName = null;

            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("{0} is unavailable.", photoName);
                    }
                    else
                    {
                        SqlBytes bytes = reader.GetSqlBytes(1);
                        using (Bitmap productImage = new Bitmap(bytes.Stream))
                        {
                            String fileName = filePath + photoName;

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

Utilizzo dei parametri di tipi di valore di grandi dimensioni

I tipi di valore di grandi dimensioni possono essere utilizzati negli oggetti SqlParameter nello stesso modo in cui si utilizzano tipi di valore di dimensioni minori. È possibile recuperare tipi di valore di grandi dimensioni come valori SqlParameter, come illustrato nell'esempio seguente. Il codice presuppone l'esistenza della seguente stored procedure GetDocumentSummary nel database di esempio AdventureWorks. La stored procedure accetta un parametro di input denominato @DocumentID e restituisce il contenuto della colonna DocumentSummary nel parametro di output @DocumentSummary.

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 di ADO.NET crea oggetti SqlConnection e SqlCommand per eseguire la stored procedure GetDocumentSummary e recuperare le informazioni di riepilogo del documento archiviate come tipo di valore di grandi dimensioni. Il codice passa un valore per il parametro di input @DocumentID e i risultati restituiti nel parametro di output @DocumentSummary vengono visualizzati nella finestra della console.

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

static private string GetDocumentSummary(int documentID)
{
    //Assumes GetConnectionString returns a valid connection string.
    using (SqlConnection connection =
               new SqlConnection(GetConnectionString()))
    {
        connection.Open();
        SqlCommand command = connection.CreateCommand();
        try
        {
            // Setup 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 SqlParameter("@DocumentID", SqlDbType.Int);
            paramID.Value = documentID;
            command.Parameters.Add(paramID);

            // Set up the output parameter to retrieve the summary.
            SqlParameter paramSummary =
                new SqlParameter("@DocumentSummary",
                SqlDbType.NVarChar, -1);
            paramSummary.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;
        }
    }
}

Vedere anche

Concetti

Mapping dei tipi di dati SQL Server (ADO.NET)

Altre risorse

Dati binari e con valori di grandi dimensioni SQL Server (ADO.NET)

Operazioni sui dati SQL Server in ADO.NET