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

Scaricare ADO.NET

I tipi di dati LOB (Large Object) sono quelli che superano la dimensione massima di 8 kilobyte (KB) per le righe. SQL Server include l'identificatore max per i tipi di dati varchar, nvarchar e varbinary per consentire l'archiviazione di valori di dimensioni fino a 2^32 byte. Le colonne della tabella e le variabili Transact-SQL possono specificare tipi di dati varchar(max), nvarchar(max)o varbinary(max). In .NET i tipi di dati max possono essere recuperati da un DataReader e possono anche essere specificati come parametri di input e di output senza richiedere una gestione speciale. Per i tipi di dati varchar di grandi dimensioni, è possibile recuperare e aggiornare i dati in modo incrementale.

I tipi di dati max possono essere usati per eseguire confronti, ad esempio per le variabili Transact-SQL, e per le concatenazioni. Si possono usare anche nelle clausole DISTINCT, ORDER BY, GROUP BY di un'istruzione SELECT, nonché in aggregazioni, join e sottoquery.

Vedere Utilizzo di tipi di dati per valori di grandi dimensioni nella documentazione online di SQL Server per maggiori dettagli sui tipi di dati con valori di grandi dimensioni.

Restrizioni dei tipi di valore di grandi dimensioni

Per i tipi di dati max vengono applicate le restrizioni seguenti, che non esistono per i tipi di dati più piccoli:

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

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

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

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

La funzione OPENROWSET di Transact-SQL è un metodo monouso per la connessione e l'accesso ai dati remoti. È possibile fare riferimento a OPENROWSET nella clausola FROM di una query come se fosse un nome di tabella. È anche possibile fare riferimento a questa funzione come tabella di destinazione di un'istruzione INSERT, UPDATE o DELETE,

La funzione OPENROWSET include il 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. Ciò consente di usare OPENROWSET in una semplice istruzione INSERT SELECT.

Gli argomenti dell'opzione OPENROWSET BULK 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 riga singola, set di righe a colonna singola di tipo varbinary, varchar o nvarchar. Per la sintassi e le opzioni complete, vedere documentazione online di SQL Server.

Nell'esempio seguente viene inserita una foto nella tabella ProductPhoto del database di esempio AdventureWorks. Se si usa il provider BULK OPENROWSET, è necessario fornire l'elenco di colonne denominato 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. Questo è 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 dei dati tramite UPDATE .WRITE

L'istruzione UPDATE di Transact-SQL usa una nuova sintassi di WRITE per la modifica del contenuto delle colonne varchar(max), nvarchar(max) o varbinary(max). In questo modo è possibile eseguire aggiornamenti parziali dei dati. La sintassi di UPDATE.WRITE è illustrata di seguito in forma abbreviata:

UPDATE

{ <object> }

SET

{ column_name = { . WRITE ( expression , @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.

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

Nota

@Offset@Length possono essere un numero negativo.

Esempio

Questo esempio di Transact-SQL aggiorna un valore parziale in DocumentSummary, una colonna nvarchar(max) nella tabella Document del database AdventureWorks. La parola 'components' viene sostituta con la parola 'features' specificando la parola sostitutiva, la posizione iniziale (offset) della parola da sostituire nei dati esistenti e il numero di caratteri da sostituire (lunghezza). Nell'esempio sono incluse 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 di tipi di valore di grandi dimensioni in ADO.NET

È possibile usare tipi di valore di grandi dimensioni in ADO.NET specificandoli come oggetti SqlParameter in un oggetto SqlDataReader per restituire un set di risultati oppure usando un oggetto SqlDataAdapter per compilare un oggetto DataSet/DataTable. Non esiste alcuna differenza tra l'uso di un tipo di dati per valori di grandi dimensioni e l'uso del tipo di dati correlato per valori più piccoli.

Uso di GetSqlBytes per recuperare i dati

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

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

Uso di GetSqlChars per recuperare i dati

È possibile usare 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 seleziona dati nvarchar(max) da una tabella e un oggetto SqlDataReader denominato reader che recupera i dati.

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

Uso di GetSqlBinary per recuperare i dati

È possibile usare 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 seleziona dati varbinary(max) da una tabella e un oggetto SqlDataReader denominato reader che recupera i dati come flusso SqlBinary.

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

Uso di GetBytes per recuperare i dati

Il metodo GetBytes di un oggetto SqlDataReader legge un flusso di byte dall'offset di colonna specificato in una matrice di byte, a partire dall'offset di matrice specificato. Il frammento di codice seguente presuppone l'uso di un oggetto SqlDataReader 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 della matrice.

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 un oggetto SqlDataReader legge il valore dall'offset di colonna specificato in una matrice. Il frammento di codice seguente presuppone l'uso di un oggetto SqlDataReader denominato reader che recupera i dati binari dall'offset della prima colonna, quindi i dati di tipo stringa dall'offset della seconda colonna.

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 per valori di grandi dimensioni a tipi CLR

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

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

Esempio

Il codice seguente recupera il nome e l'oggetto LargePhoto dalla tabella ProductPhoto nel database AdventureWorks e li salva in un file. L'assembly deve essere compilato con un riferimento allo spazio dei nomi System.Drawing. Il metodo GetSqlBytes di SqlDataReader restituisce un oggetto SqlBytes che espone una proprietà Stream. Questa viene usata nel codice per creare un nuovo oggetto Bitmap, che verrà quindi salvato come ImageFormat GIF.

using Microsoft.Data.SqlClient;
using System.Data.SqlTypes;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;

class Program
{
    static void Main()
    {
        // Supply any valid DocumentID value and file path.
        // The value 3 is supplied for DocumentID, and a literal
        // string for the file path where the image will be saved. 1, 60
        TestGetSqlBytes(7, @"c:\temp\");
        Console.ReadLine();
    }
    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();
            }
        }
    }

    static private string GetConnectionString()
    {
        // To avoid storing the connectionection string in your code, 
        // you can retrieve it from a configuration file, using the 
        // System.Configuration.ConfigurationSettings.AppSettings property 
        return "Data Source=(local);Initial Catalog=AdventureWorks;" +
            "Integrated Security=SSPI";
    }
}

Uso dei parametri con tipo di valore di grandi dimensioni

I tipi di valore di grandi dimensioni possono essere usati negli oggetti SqlParameter nello stesso modo in cui si usano i tipi di valore più piccoli negli oggetti SqlParameter. È possibile recuperare tipi di valore di grandi dimensioni come valori SqlParameter, come illustrato nell'esempio seguente. Il codice presuppone che la seguente stored procedure 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 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 ADO.NET crea oggetti SqlConnection e SqlCommand per eseguire la stored procedure GetDocumentSummary e recuperare il riepilogo del documento, archiviato 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.

using Microsoft.Data.SqlClient;
class Program
{
    static void Main()
    {
        // Supply any valid Document ID value.
        // The value 7 is supplied for demonstration purposes.
        string summaryString = GetDocumentSummary(7);
        Console.ReadLine();
    }
    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;
            }
        }
    }
    static private string GetConnectionString()
    {
        // To avoid storing the connectionection string in your code, 
        // you can retrieve it from a configuration file, using the 
        // System.Configuration.ConfigurationSettings.AppSettings property
        return "Data Source=(local);Initial Catalog=AdventureWorks;" +
            "Integrated Security=SSPI";
    }
}

Passaggi successivi