Gegevens met een grote waarde (max) wijzigen in ADO.NET

Artikel
03/05/2024

Grote objectgegevenstypen (LOB) zijn gegevenstypen die groter zijn dan de maximale rijgrootte van 8 kB (kB). SQL Server biedt een max aanduiding voor varchar, nvarcharen varbinary gegevenstypen om de opslag van waarden zo groot als 2^32 bytes mogelijk te maken. Tabelkolommen en Transact-SQL-variabelen kunnen gegevenstypen of varbinary(max) nvarchar(max)gegevenstypen opgevenvarchar(max). In ADO.NET kunnen de max gegevenstypen worden opgehaald door een DataReader, en kunnen ook worden opgegeven als invoer- en uitvoerparameterwaarden zonder speciale verwerking. Voor grote varchar gegevenstypen kunnen gegevens incrementeel worden opgehaald en bijgewerkt.

De max gegevenstypen kunnen worden gebruikt voor vergelijkingen, als Transact-SQL-variabelen en voor samenvoeging. Ze kunnen ook worden gebruikt in de DISTINCT-, ORDER BY-, GROUP BY-componenten van een SELECT-instructie en in aggregaties, joins en subquery's.

Zie Gegevenstypen met een grote waarde gebruiken voor meer informatie.

Beperkingen voor grootwaardetype

De volgende beperkingen gelden voor de max gegevenstypen, die niet bestaan voor kleinere gegevenstypen:

Een sql_variant kan geen groot varchar gegevenstype bevatten.
Grote varchar kolommen kunnen niet worden opgegeven als een sleutelkolom in een index. Ze zijn toegestaan in een opgenomen kolom in een niet-geclusterde index.
Grote varchar kolommen kunnen niet worden gebruikt als partitioneringssleutelkolommen.

Werken met grote-waardetypen in Transact-SQL

De Transact-SQL-functie OPENROWSET is een eenmalige methode voor het verbinden en openen van externe gegevens. Deze bevat alle verbindingsgegevens die nodig zijn voor toegang tot externe gegevens vanuit een OLE DB-gegevensbron. OPENROWSET kan worden verwezen in de FROM-component van een query alsof het een tabelnaam is. Er kan ook naar worden verwezen als de doeltabel van een INSERT-, UPDATE- of DELETE-instructie, afhankelijk van de mogelijkheden van de OLE DB-provider.

De OPENROWSET functie bevat de BULK rijensetprovider, waarmee u gegevens rechtstreeks vanuit een bestand kunt lezen zonder de gegevens in een doeltabel te laden. Hiermee kunt u een eenvoudige INSERT SELECT-instructie gebruiken OPENROWSET .

De OPENROWSET BULK optieargumenten bieden een aanzienlijke controle over waar gegevens moeten worden gestart en beëindigd, hoe fouten moeten worden behandeld en hoe gegevens worden geïnterpreteerd. U kunt bijvoorbeeld opgeven dat het gegevensbestand wordt gelezen als een rij met één rij, een rijset met één kolom van het type varbinary, varcharof nvarchar.

In het volgende voorbeeld wordt een foto ingevoegd in de tabel ProductPhoto in de voorbeelddatabase AdventureWorks. Wanneer u de BULK OPENROWSET provider gebruikt, moet u de benoemde lijst met kolommen opgeven, zelfs als u geen waarden in elke kolom invoegt. De primaire sleutel in dit geval wordt gedefinieerd als een identiteitskolom en kan worden weggelaten uit de kolomlijst. Houd er rekening mee dat u ook een correlatienaam moet opgeven aan het einde van de OPENROWSET instructie, in dit geval ThumbnailPhoto. Dit correleert met de kolom in de ProductPhoto tabel waarin het bestand wordt geladen.

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

Gegevens bijwerken met UPDATE. SCHRIJVEN

De Transact-SQL UPDATE-instructie heeft een nieuwe WRITE-syntaxis voor het wijzigen van de inhoud van varchar(max), nvarchar(max)of varbinary(max) kolommen. Hiermee kunt u gedeeltelijke updates van de gegevens uitvoeren. De UPDATE. Schrijfsyntaxis wordt hier weergegeven in verkorte vorm:

UPDATE

{ <object> }

SET

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

De WRITE-methode geeft aan dat een sectie van de waarde van de column_name wordt gewijzigd. De expressie is de waarde die wordt gekopieerd naar het column_name, het is het @Offset beginpunt waarop de expressie wordt geschreven en het @Length argument is de lengte van de sectie in de kolom.

If	Dan
De expressie is ingesteld op NULL	`@Length` wordt genegeerd en de waarde in column_name wordt afgekapt op de opgegeven `@Offset`.
`@Offset` is NULL	De updatebewerking voegt de expressie toe aan het einde van de bestaande column_name waarde en `@Length` wordt genegeerd.
`@Offset` groter is dan de lengte van de column_name waarde	SQL Server retourneert een fout.
`@Length` is NULL	Met de updatebewerking worden alle gegevens van `@Offset` het einde van de `column_name` waarde verwijderd.

Notitie

@Length Noch @Offset kan een negatief getal zijn.

Opmerking

In dit Transact-SQL-voorbeeld wordt een gedeeltelijke waarde bijgewerkt in DocumentSummary, een nvarchar(max) kolom in de tabel Document in de AdventureWorks-database. Het woord 'onderdelen' wordt vervangen door het woord 'functies' door het vervangende woord, de beginlocatie (verschuiving) van het woord dat moet worden vervangen in de bestaande gegevens en het aantal tekens dat moet worden vervangen (lengte). Het voorbeeld bevat SELECT-instructies voor en na de UPDATE-instructie om resultaten te vergelijken.

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.

Werken met typen grote waarden in ADO.NET

U kunt met grote waardetypen in ADO.NET werken door grote waardetypen op te geven als SqlParameter objecten in een SqlDataReader om een resultatenset te retourneren, of door een om een SqlDataAdapter DataSet/DataTablewaarde op te vullen. Er is geen verschil tussen de manier waarop u met een groot waardetype en het bijbehorende, kleinere waardegegevenstype werkt.

GetSqlBytes gebruiken om gegevens op te halen

De GetSqlBytes methode van de SqlDataReader methode kan worden gebruikt om de inhoud van een varbinary(max) kolom op te halen. In het volgende codefragment wordt ervan uitgegaan dat een object met de naam gegevens uit varbinary(max) een tabel en een SqlDataReader object dat reader de gegevens ophaalt als SqlBytes.cmd SqlCommand

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

GetSqlChars gebruiken om gegevens op te halen

De GetSqlChars methode van de SqlDataReader methode kan worden gebruikt om de inhoud van een varchar(max) of nvarchar(max) kolom op te halen. In het volgende codefragment wordt ervan uitgegaan dat een object met de SqlCommand naam cmd gegevens uit een tabel selecteert nvarchar(max) en een SqlDataReader object met de naam reader waarmee de gegevens worden opgehaald.

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

GetSqlBinary gebruiken om gegevens op te halen

De GetSqlBinary methode van een SqlDataReader kan worden gebruikt om de inhoud van een varbinary(max) kolom op te halen. In het volgende codefragment wordt ervan uitgegaan dat een object met de SqlCommand naam cmd gegevens uit een tabel selecteert varbinary(max) en een SqlDataReader object met de naam reader waarmee de gegevens als een SqlBinary stroom worden opgehaald.

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

GetBytes gebruiken om gegevens op te halen

De GetBytes methode van een SqlDataReader gelezen stroom van bytes van de opgegeven kolom offset naar een bytematrix beginnend bij de opgegeven matrix offset. In het volgende codefragment wordt ervan uitgegaan dat een object met de SqlDataReader naam reader bytes in een bytematrix ophaalt. Houd er rekening mee dat anders GetBytes dan GetSqlBytesvoor de matrixbuffer een grootte is vereist.

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

GetValue gebruiken om gegevens op te halen

De GetValue methode van een SqlDataReader waarde leest de waarde van de opgegeven kolomverschil in een matrix. In het volgende codefragment wordt ervan uitgegaan dat een object met de SqlDataReader naam reader binaire gegevens ophaalt uit de eerste kolom offset en vervolgens tekenreeksgegevens uit de tweede kolom offset.

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

Converteren van grote waardetypen naar CLR-typen

U kunt de inhoud van een varchar(max) of nvarchar(max) meer kolommen converteren met behulp van een van de methoden voor het converteren van tekenreeksen, zoals ToString. In het volgende codefragment wordt ervan uitgegaan dat een SqlDataReader object met de naam reader waarmee de gegevens worden opgehaald.

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

Opmerking

Met de volgende code worden de naam en het LargePhoto object opgehaald uit de ProductPhoto tabel in de AdventureWorks database en opgeslagen in een bestand. De assembly moet worden gecompileerd met een verwijzing naar de System.Drawing naamruimte. De GetSqlBytes methode van de SqlDataReader functie retourneert een SqlBytes object dat een Stream eigenschap beschikbaar maakt. De code gebruikt dit om een nieuw Bitmap object te maken en slaat het vervolgens op in gif ImageFormat.

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("{0} is unavailable.", photoName);
                    }
                    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 {0}.", 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

Parameters voor groot waardetype gebruiken

Grote waardetypen kunnen op dezelfde manier worden gebruikt in SqlParameter objecten als u kleinere waardetypen in SqlParameter objecten gebruikt. U kunt grote waardetypen ophalen als SqlParameter waarden, zoals wordt weergegeven in het volgende voorbeeld. In de code wordt ervan uitgegaan dat de volgende opgeslagen procedure GetDocumentSummary bestaat in de voorbeelddatabase AdventureWorks. De opgeslagen procedure gebruikt een invoerparameter met de naam @DocumentID en retourneert de inhoud van de kolom DocumentSummary in de @DocumentSummary uitvoerparameter.

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

Opmerking

De ADO.NET code maakt SqlConnection en SqlCommand objecten om de opgeslagen procedure GetDocumentSummary uit te voeren en haalt de documentsamenvatting op, die is opgeslagen als een groot waardetype. De code geeft een waarde door voor de @DocumentID invoerparameter en geeft de resultaten weer die zijn doorgestuurd in de @DocumentSummary uitvoerparameter in het consolevenster.

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

Delen via

Gegevens met een grote waarde (max) wijzigen in ADO.NET

Beperkingen voor grootwaardetype

Werken met grote-waardetypen in Transact-SQL

Gegevens bijwerken met UPDATE. SCHRIJVEN

Opmerking

Werken met typen grote waarden in ADO.NET

GetSqlBytes gebruiken om gegevens op te halen

GetSqlChars gebruiken om gegevens op te halen

GetSqlBinary gebruiken om gegevens op te halen

GetBytes gebruiken om gegevens op te halen

GetValue gebruiken om gegevens op te halen

Converteren van grote waardetypen naar CLR-typen

Opmerking

Parameters voor groot waardetype gebruiken

Opmerking

Zie ook

Aanvullende resources