Ändra data med stort värde (max) i ADO.NET

Artikel
03/02/2024

Datatyper för stora objekt (LOB) är de som överskrider den maximala radstorleken på 8 kilobyte (KB). SQL Server tillhandahåller en max specificerare för varchar, nvarcharoch varbinary datatyper för att tillåta lagring av värden så stora som 2^32 byte. Tabellkolumner och Transact-SQL-variabler kan ange varchar(max), nvarchar(max)eller varbinary(max) datatyper. I ADO.NET max kan datatyperna hämtas av en DataReader, och kan också anges som både indata- och utdataparametervärden utan någon särskild hantering. För stora varchar datatyper kan data hämtas och uppdateras stegvis.

Datatyperna max kan användas för jämförelser, som Transact-SQL-variabler och för sammanlänkning. De kan också användas i DISTINCT-, ORDER BY-, GROUP BY-satserna i en SELECT-instruktion samt i aggregeringar, kopplingar och underfrågor.

Mer information finns i Använda datatyper med stort värde.

Begränsningar för stora värden

Följande begränsningar gäller för max datatyperna, som inte finns för mindre datatyper:

En sql_variant får inte innehålla en stor varchar datatyp.
Stora varchar kolumner kan inte anges som en nyckelkolumn i ett index. De tillåts i en inkluderad kolumn i ett icke-grupperat index.
Stora varchar kolumner kan inte användas som partitioneringsnyckelkolumner.

Arbeta med stora värdetyper i Transact-SQL

Funktionen Transact-SQL OPENROWSET är en engångsmetod för att ansluta och komma åt fjärrdata. Den innehåller all anslutningsinformation som krävs för att komma åt fjärrdata från en OLE DB-datakälla. OPENROWSET kan refereras i FROM-satsen för en fråga som om det vore ett tabellnamn. Det kan också refereras till som måltabellen för en INSERT-, UPDATE- eller DELETE-instruktion, med förbehåll för funktionerna i OLE DB-providern.

Funktionen OPENROWSET innehåller raduppsättningsprovidern BULK , som gör att du kan läsa data direkt från en fil utan att läsa in data i en måltabell. På så sätt kan du använda OPENROWSET i en enkel INSERT SELECT-instruktion.

Alternativargumenten OPENROWSET BULK ger betydande kontroll över var du ska börja och sluta läsa data, hur du hanterar fel och hur data tolkas. Du kan till exempel ange att datafilen ska läsas som en radradsuppsättning med en kolumn av typen varbinary, varchareller nvarchar.

I följande exempel infogas ett foto i productphoto-tabellen i AdventureWorks-exempeldatabasen. När du använder providern BULK OPENROWSET måste du ange den namngivna listan med kolumner även om du inte infogar värden i varje kolumn. Primärnyckeln i det här fallet definieras som en identitetskolumn och kan utelämnas från kolumnlistan. Observera att du också måste ange ett korrelationsnamn i slutet av -instruktionen OPENROWSET , som i det här fallet är ThumbnailPhoto. Detta korrelerar med kolumnen i tabellen ProductPhoto som filen läses in i.

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

Uppdaterar data med UPDATE . SKRIVA

Transact-SQL UPDATE-instruktionen har ny WRITE-syntax för att ändra innehållet i varchar(max), nvarchar(max)eller varbinary(max) kolumner. På så sätt kan du utföra partiella uppdateringar av data. UPPDATERA . Skrivsyntax visas här i förkortad form:

UPDATE

{ <objekt> }

SET

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

Metoden WRITE anger att ett avsnitt av värdet för column_name kommer att ändras. Uttrycket är det värde som ska kopieras till column_name, @Offset är startpunkten där uttrycket skrivs och @Length argumentet är längden på avsnittet i kolumnen.

If	Gäller följande
Uttrycket är inställt på NULL	`@Length` ignoreras och värdet i column_name trunkeras vid den angivna `@Offset`.
`@Offset` är NULL	Uppdateringsåtgärden lägger till uttrycket i slutet av det befintliga column_name-värdet och `@Length` ignoreras.
`@Offset` är större än längden på det column_name värdet	SQL Server returnerar ett fel.
`@Length` är NULL	Uppdateringsåtgärden tar bort alla data från `@Offset` till slutet av `column_name` värdet.

Kommentar

Varken @Offset eller @Length kan vara ett negativt tal.

Exempel

Det här Transact-SQL-exemplet uppdaterar ett partiellt värde i DocumentSummary, en nvarchar(max) kolumn i dokumenttabellen i Databasen AdventureWorks. Ordet "komponenter" ersätts av ordet "funktioner" genom att ange ersättningsordet, början plats (offset) för ordet som ska ersättas i befintliga data och antalet tecken som ska ersättas (längd). Exemplet innehåller SELECT-instruktioner före och efter UPDATE-instruktionen för att jämföra resultat.

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.

Arbeta med stora värdetyper i ADO.NET

Du kan arbeta med stora värdetyper i ADO.NET genom att ange stora värdetyper som SqlParameter objekt i en SqlDataReader för att returnera en resultatuppsättning eller genom att använda en SqlDataAdapter för att fylla i en DataSet/DataTable. Det finns ingen skillnad mellan hur du arbetar med en stor värdetyp och dess relaterade, mindre värdedatatyp.

Använda GetSqlBytes för att hämta data

Metoden GetSqlBytes SqlDataReader för kan användas för att hämta innehållet i en varbinary(max) kolumn. Följande kodfragment förutsätter ett SqlCommand objekt med namnet cmd som väljer varbinary(max) data från en tabell och ett SqlDataReader objekt med namnet reader som hämtar data som SqlBytes.

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

Använda GetSqlChars för att hämta data

Metoden GetSqlChars SqlDataReader för kan användas för att hämta innehållet i en varchar(max) eller nvarchar(max) en kolumn. Följande kodfragment förutsätter ett SqlCommand objekt med namnet cmd som väljer nvarchar(max) data från en tabell och ett SqlDataReader objekt med namnet reader som hämtar data.

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

Använda GetSqlBinary för att hämta data

Metoden GetSqlBinary för en SqlDataReader kan användas för att hämta innehållet i en varbinary(max) kolumn. Följande kodfragment förutsätter ett SqlCommand objekt med namnet cmd som väljer varbinary(max) data från en tabell och ett SqlDataReader objekt med namnet reader som hämtar data som en SqlBinary dataström.

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

Använda GetBytes för att hämta data

Metoden GetBytes för en SqlDataReader läser en ström med byte från den angivna kolumnförskjutningen till en bytematris med början vid den angivna matrisförskjutningen. Följande kodfragment förutsätter ett SqlDataReader objekt med namnet reader som hämtar byte till en bytematris. Observera att till skillnad från GetSqlByteskräver GetBytes en storlek för matrisbufferten.

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

Använda GetValue för att hämta data

Metoden GetValue för en SqlDataReader läser värdet från den angivna kolumnförskjutningen till en matris. Följande kodfragment förutsätter ett SqlDataReader objekt med namnet reader som hämtar binära data från den första kolumnförskjutningen och sedan strängdata från den andra kolumnförskjutningen.

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

Konvertera från stora värdetyper till CLR-typer

Du kan konvertera innehållet i en kolumn eller nvarchar(max) med varchar(max) någon av strängkonverteringsmetoderna, till exempel ToString. Följande kodfragment förutsätter ett SqlDataReader objekt med namnet reader som hämtar data.

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

Exempel

Följande kod hämtar namnet och LargePhoto objektet från ProductPhoto tabellen i AdventureWorks databasen och sparar det i en fil. Sammansättningen måste kompileras med en referens till System.Drawing namnområdet. Metoden GetSqlBytes för SqlDataReader returnerar ett SqlBytes objekt som exponerar en Stream egenskap. Koden använder detta för att skapa ett nytt Bitmap objekt och sparar det sedan i 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

Använda parametrar för stor värdetyp

Stora värdetyper kan användas i SqlParameter objekt på samma sätt som du använder mindre värdetyper i SqlParameter objekt. Du kan hämta stora värdetyper som SqlParameter värden, som du ser i följande exempel. Koden förutsätter att följande lagrade GetDocumentSummary-procedur finns i AdventureWorks-exempeldatabasen. Den lagrade proceduren tar en indataparameter med namnet @DocumentID och returnerar innehållet i kolumnen DocumentSummary i @DocumentSummary utdataparametern.

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

Exempel

Den ADO.NET koden skapar SqlConnection och SqlCommand objekt för att köra den lagrade proceduren GetDocumentSummary och hämta dokumentsammanfattningen, som lagras som en stor värdetyp. Koden skickar ett värde för @DocumentID indataparametern och visar resultatet som skickas tillbaka i @DocumentSummary utdataparametern i konsolfönstret.

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

Dela via

Ändra data med stort värde (max) i ADO.NET

Begränsningar för stora värden

Arbeta med stora värdetyper i Transact-SQL

Uppdaterar data med UPDATE . SKRIVA

Exempel

Arbeta med stora värdetyper i ADO.NET

Använda GetSqlBytes för att hämta data

Använda GetSqlChars för att hämta data

Använda GetSqlBinary för att hämta data

Använda GetBytes för att hämta data

Använda GetValue för att hämta data

Konvertera från stora värdetyper till CLR-typer

Exempel

Använda parametrar för stor värdetyp

Exempel

Se även

Ytterligare resurser