Lingua

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 usati anche nelle clausole ORDER BY, GROUP BY, DISTINCT di un'istruzione SELECT, nonché nelle aggregazioni, join e sottoquery.

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 può essere referenziato nella clausola FROM di una query come se fosse un nome di tabella. Può anche essere indicata come tabella di destinazione di un'istruzione INSERT, UPDATE o DELETE, in base 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 i contenuti delle colonne varchar(max), nvarchar(max) o varbinary(max). In questo modo è possibile eseguire aggiornamenti parziali dei dati. La UPDATE .WRITE sintassi è illustrata di seguito in formato abbreviato:

UPDATE

{ <object> }

SET

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

Il WRITE metodo 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

Usare gli oggetti SqlParameter con SqlCommand per inviare valori di grandi dimensioni. Recupera i risultati con SqlDataReader, oppure popola gli oggetti DataSet o DataTable con SqlDataAdapter. È possibile gestire tipi di valori di grandi dimensioni come i corrispondenti tipi di dati valore più piccoli.

Uso di GetSqlBytes per recuperare i dati

Per recuperare il contenuto di una varbinary(max) colonna come SqlBytes, chiamare GetSqlBytes. Il frammento seguente presuppone che cmd sia l'oggetto SqlCommand che seleziona i dati binari e che reader sia l'oggetto SqlDataReader che lo recupera.

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

Per recuperare il contenuto di una colonna varchar(max) o nvarchar(max), chiamare GetSqlChars. Il frammento seguente presuppone che cmd sia l'oggetto SqlCommand che seleziona i nvarchar(max) dati e reader sia l'oggetto SqlDataReader che li recupera.

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

Per recuperare il contenuto di una varbinary(max) colonna come SqlBinary valore, chiamare GetSqlBinary. Il frammento seguente presuppone che cmd sia il SqlCommand che seleziona i dati binari e che reader sia il SqlDataReader che li recupera.

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 legge i byte dall'offset di colonna specificato in una matrice di byte in corrispondenza dell'offset di matrice specificato. Il frammento seguente presuppone che reader sia l'oggetto SqlDataReader che recupera i byte. 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 GetValue metodo restituisce il valore della colonna come oggetto . Il frammento seguente presuppone che reader sia SqlDataReader. In C# la colonna binaria restituisce byte[] e la colonna di caratteri restituisce string. In Visual Basic i tipi corrispondenti sono Byte() e String.

While reader.Read()
    ' Read the data from varbinary(max) column
    Dim binaryData() As Byte = CType(reader.GetValue(0), Byte())

    ' 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 colonna varchar(max) o nvarchar(max) colonna con metodi di conversione di stringhe, ad esempio ToString. Il frammento seguente presuppone che reader sia l'oggetto SqlDataReader 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 il LargePhoto valore dalla ProductPhoto tabella nel AdventureWorks database e lo salva in un file. Questo esempio di conversione di immagini richiede Windows e il System.Drawing.Common pacchetto NuGet. Tale restrizione si applica alla conversione di immagini, non al recupero dei dati binari del provider. Il GetSqlBytes metodo restituisce SqlBytes, che espone la Stream proprietà . Il codice usa il flusso per creare l'oggetto Bitmap e salvarlo in formato GIF.

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

  • Ultimo aggiornamento: