Ändern von Large-Value (max)-Daten in ADO.NET

Bei Large Object-Datentypen (LOB) übersteigt die Zeilengröße die maximal zulässige Zeilengröße von 8 KB. SQL Server  bietet einen max-Spezifizierer für die Datentypen varchar, nvarchar und varbinary, der das Speichern von 2^32 Bytes großen Werten ermöglicht. Tabellenspalten und Transact-SQL-Variablen können die Datentypen varchar(max), nvarchar(max) oder varbinary(max) angeben. In ADO.NET können die max Datentypen von einem DataReader abgerufen werden und auch als Eingabe- und Ausgabeparameterwerte ohne spezielle Behandlung angegeben werden. Für große varchar-Datentypen können Daten inkrementell abgerufen und aktualisiert werden.

Die max-Datentypen können für Vergleiche, als Transact-SQL-Variablen und zum Verketten verwendet werden. Sie können auch in den DISTINCT, ORDER BY, GROUP BY-Klauseln einer SELECT Anweisung sowie in Aggregaten, Verknüpfungen und Unterabfragen verwendet werden.

Weitere Informationen finden Sie unter Verwenden von Large-Value Datentypen.

Large-Value Typbeschränkungen

Die folgenden Einschränkungen gelten für die max-Datentypen, die es für kleinere Datentypen nicht gibt:

• Ein sql_variant kann nicht den großen Datentyp varchar enthalten.

• Große varchar-Spalten können nicht als Schlüsselspalte in einem Index angegeben werden. Sie sind in einem nicht gruppierten Index in einer eingeschlossenen Spalte zulässig.

• Große varchar-Spalten können nicht als Partitionierungsschlüsselspalten verwendet werden.

Arbeiten mit großen Werttypen in Transact-SQL

Die Transact-SQL-Funktion OPENROWSET bietet eine Einmalmethode zum Herstellen einer Verbindung mit und Zugreifen auf Remotedaten. Sie enthält alle Verbindungsinformationen, die für den Zugriff auf Remotedaten aus einer OLE DB-Datenquelle erforderlich sind. Auf OPENROWSET kann in der FROM-Klausel einer Abfrage verwiesen werden, als wäre es ein Tabellenname. Sie kann auch als Zieltabelle einer INSERT-, UPDATE- oder DELETE-Anweisung verwiesen werden, je nach den Funktionen des OLE DB-Anbieters.

Die OPENROWSET-Funktion bietet den Rowsetanbieter BULK, mit dem Sie Daten direkt aus einer Datei lesen können, ohne sie in eine Zieltabelle zu laden. Auf diese Weise können Sie INSERT SELECT in einer einfachen OPENROWSET-Anweisung verwenden.

Die Optionsargumente von OPENROWSET BULK ermöglichen die flexible Festlegung, wo mit dem Lesen von Daten angefangen und aufgehört werden soll, wie mit Fehlern umzugehen ist und wie Daten interpretiert werden. Beispielsweise können Sie angeben, dass die Datendatei als einzeiliges, einspaltiges Rowset vom Datentyp varbinary, varchar oder nvarchar gelesen wird.

Im folgenden Beispiel wird ein Foto in die ProductPhoto-Tabelle in der AdventureWorks-Beispieldatenbank eingefügt. Wenn Sie den BULK OPENROWSET-Anbieter verwenden, müssen Sie die benannte Liste der Spalten auch dann angeben, wenn Sie nicht in jeder Spalte Werte einfügen. Der Primärschlüssel ist in diesem Fall als Identitätsspalte definiert und kann in der Spaltenliste weggelassen werden. Beachten Sie, dass Sie auch einen Korrelationsnamen am Ende der OPENROWSET-Anweisung angeben müssen, der in diesem Fall ThumbnailPhoto lautet. Dies korreliert mit der Spalte in der Tabelle ProductPhoto, in die die Datei geladen wird.

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

Aktualisieren von Daten mithilfe von UPDATE.WRITE

Die Transact-SQL UPDATE-Anweisung hat eine neue WRITE-Syntax zum Ändern des Inhalts der Spalten varchar(max), nvarchar(max) oder varbinary(max) . Dies ermöglicht Ihnen, Teilaktualisierungen an den Daten vorzunehmen. Die UPDATE .WRITE Syntax wird hier in abgekürzter Form gezeigt:

UPDATE

{ <Objekt> }

SET

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

Die WRITE Methode gibt an, dass ein Abschnitt des Werts des column_name geändert werden soll. Der Ausdruck ist der Wert, der in column_name kopiert wird, @Offset ist der Anfangspunkt, ab dem der Ausdruck geschrieben wird, und das @Length-Argument gibt die Länge des Abschnitts in der Spalte an.

If	Dann
Der Ausdruck ist auf NULL festgelegt.	@Length wird ignoriert, und der Wert in column_name wird am angegebenen @Offset abgeschnitten.
@Offset ist NULL	Beim Updatevorgang wird der Ausdruck am Ende des vorhandenen Werts column_name angefügt, und @Length wird ignoriert.
@Offset ist größer als die Länge des Werts von column_name.	SQL Server gibt einen Fehler zurück.
@Length ist NULL	Der Updatevorgang entfernt alle Daten aus @Offset bis zum Ende des column_name-Werts.

Hinweis

Weder @Offset noch @Length darf eine negative Zahl sein.

Beispiel

Dieses Transact-SQL-Beispiel aktualisiert einen Teilwert in DocumentSummary, einer nvarchar(max)-Spalte in der Tabelle „Document“ in der Datenbank AdventureWorks. Der Begriff „components“ wird durch den Begriff „features“ ersetzt, indem der Ersatzbegriff, die Anfangsposition (offset) des zu ersetzenden Begriffs in den vorhandenen Daten und die Anzahl der zu ersetzenden Zeichen (length) angegeben werden. Das Beispiel enthält SELECT Anweisungen vor und nach der UPDATE Anweisung zum Vergleichen von Ergebnissen.

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.

Arbeiten mit großen Werttypen in ADO.NET

Verwenden Sie SqlParameter Objekte mit SqlCommand, um große Werte zu senden. Ergebnisse mit SqlDataReader abrufen oder DataSet- bzw. DataTable-Objekte mit SqlDataAdapter füllen. Sie behandeln große Werttypen genauso wie die entsprechenden kleineren Wertdatentypen.

Verwenden von GetSqlBytes zum Abrufen von Daten

Um den Inhalt einer varbinary(max)-Spalte als SqlBytes abzurufen, rufen Sie GetSqlBytes auf. Im folgenden Fragment wird davon ausgegangen, dass cmd die SqlCommand ist, die die Binärdaten auswählt, und reader die SqlDataReader ist, die sie abruft.

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

Verwenden von GetSqlChars zum Abrufen von Daten

Um den Inhalt einer GetSqlChars- oder nvarchar(max)-Spalte abzurufen, rufen Sie varchar(max) auf. Im folgenden Fragment wird davon ausgegangen, dass cmd die SqlCommand ist, die die nvarchar(max)-Daten auswählt, und reader die SqlDataReader ist, die sie abruft.

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

Verwenden von GetSqlBinary zum Abrufen von Daten

Rufen Sie SqlBinary auf, um den Inhalt einer GetSqlBinary Spalte als den varbinary(max) Wert abzurufen. Im folgenden Fragment wird davon ausgegangen, dass cmd die SqlCommand ist, die die Binärdaten auswählt, und reader die SqlDataReader ist, die sie abruft.

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

Verwenden von GetBytes zum Abrufen von Daten

Die GetBytes Methode liest Bytes aus dem angegebenen Spaltenoffset in ein Bytearray beim angegebenen Arrayoffset. Im folgenden Fragment wird davon ausgegangen, dass reader die SqlDataReader ist, die die Bytes abruft. GetBytes Im Gegensatz dazu GetSqlBytesist für den Arraypuffer eine Größe erforderlich.

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

Verwenden von GetValue zum Abrufen von Daten

Die GetValue Methode gibt den Spaltenwert als Objekt zurück. Im folgenden Fragment wird davon ausgegangen, dass reader die SqlDataReader ist. In C# gibt die binäre Spalte zurück byte[] , und die Zeichenspalte wird zurückgegeben string. In Visual Basic sind Byte() die entsprechenden Typen und 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);
}

Konvertieren von großen Werttypen in CLR-Typen

Sie können den Inhalt einer varchar(max)- oder nvarchar(max)-Spalte mit Zeichenfolgenkonvertierungsmethoden konvertieren, wie ToString. Im folgenden Fragment wird davon ausgegangen, dass reader die SqlDataReader ist, die die Daten abruft.

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

Beispiel

Der folgende Code ruft den Namen und den LargePhoto-Wert aus der ProductPhoto-Tabelle in der AdventureWorks-Datenbank ab und speichert ihn in einer Datei. Dieses Beispiel für die Bildkonvertierung erfordert Windows und das System.Drawing.Common NuGet-Paket. Diese Einschränkung gilt für die Bildkonvertierung, nicht für den Binärdatenabruf des Anbieters. Die GetSqlBytes Methode gibt zurück SqlBytes, wodurch die Stream Eigenschaft verfügbar gemacht wird. Der Code verwendet den Datenstrom zum Erstellen des Bitmap Objekts und speichert es im GIF-Format.

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

Verwenden von Parametern vom Typ "Großer Wert"

Große Werttypen können in SqlParameter-Objekten auf dieselbe Weise verwendet werden wie kleinere Werttypen in SqlParameter-Objekten. Sie können große Werttypen als SqlParameter-Werte abrufen, wie im folgenden Beispiel dargestellt. Der Code geht davon aus, dass die folgende gespeicherte GetDocumentSummary-Prozedur in der AdventureWorks-Beispieldatenbank vorhanden ist. Die gespeicherte Prozedur verwendet einen Eingabeparameter mit dem Namen „@DocumentID“ und gibt den Inhalt der Spalte „DocumentSummary“ im @DocumentSummary-Ausgabeparameter zurück.

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

Beispiel

Der ADO.NET-Code erstellt SqlConnection- und SqlCommand-Objekte, um die gespeicherte Prozedur GetDocumentSummary auszuführen und die Dokumentzusammenfassung abzurufen, die als großer Werttyp gespeichert ist. Der Code übergibt einen Wert für den @DocumentID-Eingabeparameter, und die im @DocumentSummary-Ausgabeparameter zurückgegebenen Ergebnisse werden im Konsolenfenster angezeigt.

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

Siehe auch

• SQL Server Binärdaten und Large-Value-Daten
• SQL Server-Datentypzuordnungen
• SQL Server-Datenvorgänge in ADO.NET
• Übersicht über ADO.NET