Ändern von Daten mit umfangreichen Werten (max) in ADO.NET

In den SQL Server-Versionen vor SQL Server 2005 war beim Arbeiten mit LOB-Datentypen (Large Objects) ein besonderes Vorgehen erforderlich. Bei LOB-Datentypen übersteigt die Zeilengröße die maximal zulässige Zeilengröße von 8 KB. Mit SQL Server 2005 wurde ein max-Spezifizierer für die Datentypen varchar, nvarchar und varbinary eingeführt, 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 neuen max-Datentypen durch einen DataReader abgerufen werden. Außerdem können sie ohne spezielle Behandlung sowohl als Eingabe- als auch als Ausgabeparameterwerte angegeben werden. Bei großen 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. Auch der Einsatz in den Klauseln DISTINCT, ORDER BY und GROUP BY einer SELECT-Anweisung sowie in Aggregaten, Joins und Unterabfragen ist möglich.

Die folgende Tabelle enthält Links zur Dokumentation in der SQL Server-Onlinedokumentation.

SQL Server 2000	SQL Server 2005	SQL Server 2008
Datentypen	Verwenden von Datentypen mit umfangreichen Werten	Verwenden von Datentypen mit umfangreichen Werten

Einschränkungen für große Werttypen

Für die max-Datentypen gelten die folgenden Einschränkungen, die für kleinere Datentypen nicht vorhanden sind:

• Ein sql_variant kann keinen großen varchar-Datentyp enthalten.

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

• Große varchar-Spalten können nicht zum Partitionieren von Schlüsselspalten verwendet werden.

Arbeiten mit großen Werttypen in Transact-SQL

Die Transact-SQL-OPENROWSET-Funktion ist eine für den Einmalgebrauch bestimmte Methode zum Herstellen einer Verbindung mit Remotedaten und den Zugriff auf diese Daten. Sie enthält alle erforderlichen Verbindungsinformationen für den Zugriff auf Remotedaten von einer OLE DB-Datenquelle. Auf OPENROWSET kann in der FROM-Klausel einer Abfrage so verwiesen werden, als ob es ein Tabellenname wäre. Abhängig von den Funktionen des OLE DB-Anbieters kann OPENROWSET auch als Zieltabelle einer INSERT-, UPDATE- oder DELETE-Anweisung dienen.

Die OPENROWSET-Funktion wurde in SQL Server 2005 durch Hinzufügen des BULK-Rowsetanbieters erweitert, mit dem Sie Daten direkt aus einer Datei lesen können, ohne sie in eine Zieltabelle zu laden. Auf diese Weise können Sie OPENROWSET in einer einfachen INSERT SELECT-Anweisung verwenden.

Die BULK-Optionsargumente von OPENROWSET 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. So können Sie z. B. angeben, dass die Datendatei als Rowset mit einer einzelnen Zeile und einer einzelnen Spalte vom Typ varbinary, varchar oder nvarchar gelesen werden soll. Die vollständige Syntax und alle Optionen finden Sie in der SQL Server-Onlinedokumentation.

Im folgenden Beispiel wird ein Foto in die ProductPhoto-Tabelle der AdventureWorks-Beispieldatenbank eingefügt. Wenn Sie den OPENROWSET-Anbieter BULKverwenden, 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 Liste der Spalten weggelassen werden. Beachten Sie, dass Sie am Ende der OPENROWSET-Anweisung auch einen Korrelationsnamen angeben müssen. In diesem Fall lautet er "ThumbnailPhoto". Dies korreliert mit der Spalte in der ProductPhoto-Tabelle, 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-Anweisung UPDATE weist eine neue WRITE-Syntax zum Ändern des Inhalts in den Spalten varchar(max), nvarchar(max) oder varbinary(max) auf. Dies ermöglicht es Ihnen, teilweise Updates der Daten durchzuführen. Die UPDATE .WRITE-Syntax wird hier in gekürzter Form dargestellt:

UPDATE

{ <object> }

SET

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

Die WRITE-Methode gibt an, dass ein Abschnitt des Werts von Spaltenname geändert wird. 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.

Gegeben	Folge
Der Ausdruck ist auf NULL festgelegt.	@Length wird ignoriert, und der Wert in column_name wird am angegebenen @Offset abgekürzt.
@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 column_name-Werts	SQL Server gibt einen Fehler zurück.
@Length ist NULL	Beim Updatevorgang werden alle Daten ab @Offset bis zum Ende des column_name-Werts entfernt.

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

Beispiel

In diesem Transact-SQL-Beispiel wird ein Teil eines Werts in DocumentSummary aktualisiert. Dabei handelt es sich um eine nvarchar(max)-Spalte in der Document-Tabelle in der AdventureWorks-Datenbank. Das Wort "components" wird ersetzt durch "features". Dazu wird das Ersatzwort, die Anfangsposition (Offset) des zu ersetzenden Worts in den vorhandenen Daten und die Anzahl der zu ersetzenden Zeichen (Length) angegeben. Das Beispiel enthält SELECT-Anweisungen vor und nach der UPDATE-Anweisung, damit die Ergebnisse verglichen werden können.

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

Sie können in ADO.NET mit großen Werttypen arbeiten, indem Sie große Werttypen als SqlParameter -Objekte in einem SqlDataReader angeben, damit ein Resultset zurückgegeben wird, oder indem Sie mithilfe eines SqlDataAdapter ein DataSet oder eine DataTable füllen. Sie können mit großen Datentypen genauso arbeiten wie mit den entsprechenden kleineren Datentypen.

Abrufen von Daten mit "GetSqlBytes"

Mit der GetSqlBytes-Methode von SqlDataReader können Sie den Inhalt einer varbinary(max)-Spalte abrufen. Im folgenden Codefragment wird von einem SqlCommand-Objekt mit dem Namen cmd ausgegangen, das varbinary(max)-Daten aus einer Tabelle auswählt. Außerdem gibt es ein SqlDataReader-Objekt mit dem Namen reader, das die Daten als SqlBytes 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);
    }

Abrufen von Daten mit "GetSqlChars"

Mit der GetSqlChars-Methode des SqlDataReader-Objekts können Sie den Inhalt einer varchar(max)-Spalte oder einer nvarchar(max)-Spalte abrufen. Im folgenden Codefragment wird von einem SqlCommand-Objekt mit dem Namen cmd ausgegangen, das nvarchar(max)-Daten aus einer Tabelle auswählt. Außerdem gibt es ein SqlDataReader-Objekt mit dem Namen reader, das die Daten 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);
}

Abrufen von Daten mit "GetSqlBinary"

Mit der GetSqlBinary-Methode eines SqlDataReader können Sie den Inhalt einer varbinary(max)-Spalte abrufen. Im folgenden Codefragment wird von einem SqlCommand-Objekt mit dem Namen cmd ausgegangen, das varbinary(max)-Daten aus einer Tabelle auswählt. Außerdem gibt es ein SqlDataReader-Objekt mit dem Namen reader, das die Daten als SqlBinary-Stream 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);
    }

Abrufen von Daten mit "GetBytes"

Die GetBytes-Methode eines SqlDataReader liest einen Bytestream, beginnend am angegebenen Spaltenoffset, in ein Bytearray, beginnend am angegebenen Arrayoffset. Im folgenden Codefragment wird von einem SqlDataReader-Objekt mit dem Namen reader ausgegangen, das Bytes in ein Bytearray abruft. Beachten Sie, dass GetBytes im Unterschied zu GetSqlBytes eine Größe für den Arraypuffer benötigt.

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

Abrufen von Daten mit "GetValue"

Die GetValue-Methode eines SqlDataReader liest den Wert, beginnend am angegebenen Spaltenoffset, in ein Array. Im folgenden Codefragment wird von einem SqlDataReader-Objekt mit dem Namen reader ausgegangen, das zuerst binäre Daten abruft und damit am ersten Spaltenoffset beginnt. Anschließend werden Zeichenfolgendaten abgerufen, wobei am zweiten Spaltenoffset begonnen wird.

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

Konvertieren von großen Werttypen in CLR-Typen

Sie können den Inhalt einer varchar(max)-Spalte oder einer nvarchar(max)-Spalte mit einer beliebigen Methode zur Zeichenfolgenkonvertierung konvertieren, z. B. mit ToString. Im folgenden Codefragment wird von einem SqlDataReader-Objekt mit dem Namen reader ausgegangen, das 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 das LargePhoto-Objekt aus der ProductPhoto-Tabelle in der AdventureWorks-Datenbank ab und speichert das Objekt in einer Datei. Die Assembly muss mit einem Verweis auf den System.Drawing-Namespace kompiliert werden. Die GetSqlBytes-Methode des SqlDataReader gibt ein SqlBytes-Objekt zurück, das eine Stream-Eigenschaft verfügbar macht. Der Code verwendet diese, um ein neues Bitmap-Objekt zu erstellen, das dann im GIF-ImageFormat gespeichert wird.

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

Verwenden großer Werttypparameter

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. Im Code wird davon ausgegangen, dass in der AdventureWorks-Beispieldatenbank die folgende gespeicherte GetDocumentSummary-Prozedur vorhanden ist. Die gespeicherte Prozedur verwendet einen Eingabeparameter mit dem Namen @DocumentID und gibt den Inhalt der DocumentSummary-Spalte 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 ein SqlConnection-Objekt und ein SqlCommand-Objekt, um die gespeicherte GetDocumentSummary-Prozedur auszuführen und die Dokumentübersicht 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.

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

Siehe auch

Konzepte

SQL Server-Datentypmappings (ADO.NET)

Weitere Ressourcen

Binäre Daten und Daten mit umfangreichen Werten in SQL Server (ADO.NET)

SQL Server-Datenoperationen in ADO.NET