Ändra Large-Value (max) data i ADO.NET

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 kan max datatyper hämtas av en DataReaderoch kan även 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.

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

För mer information, se Användning av Large-Value datatyper.

Large-Value Typbegränsningar

Följande begränsningar gäller för max datatyper 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 partitionering av nyckelkolumner.

Arbeta med typer av Large-Value 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 en frågas FROM-sats som om det vore ett tabellnamn. Det kan också refereras till som måltabellen för en INSERT, UPDATEeller DELETE -instruktion, med förbehåll för funktionerna i OLE DB-providern.

Funktionen OPENROWSET inkluderar BULK raduppsättningsleverantör, som gör det möjligt för dig att läsa data direkt från en fil utan att behöva läsa in dem i en måltabell. På så sätt kan du använda OPENROWSET i en enkel INSERT SELECT instruktion.

De OPENROWSET BULK alternativargumenten 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 enda kolumn av typen varbinary, varchareller nvarchar.

I följande exempel infogas ett foto i productphoto-tabellen i AdventureWorks-exempeldatabasen. När du använder BULK OPENROWSET-providern 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 OPENROWSET-instruktionen, som i det här fallet är ThumbnailPhoto. Detta korrelerar med kolumnen i den ProductPhoto tabell 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

Instruktionen Transact-SQL UPDATE 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. Syntaxen UPDATE .WRITE visas här i förkortat format:

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å uttrycket skrivs och argumentet @Length är längden på avsnittet i kolumnen.

Om	Då
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å värdet för column_name	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.

Anmärkning

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 satser före och efter UPDATE-satsen 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 Large-Value typer i ADO.NET

Använd SqlParameter objekt med SqlCommand för att skicka stora värden. Hämta resultat med SqlDataReader, eller fyll DataSet eller DataTable objekt med SqlDataAdapter. Du hanterar stora värdetyper på samma sätt som relaterade, mindre värdetyper.

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

Om du vill hämta innehållet i en varbinary(max) kolumn som SqlBytes, anropar du GetSqlBytes. Följande fragment förutsätter att cmd är den SqlCommand som väljer binärdata och reader är den SqlDataReader som hämtar dem.

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

Om du vill hämta innehållet i en varchar(max)- eller nvarchar(max)-kolumn anropar du GetSqlChars. Följande fragment förutsätter att cmd är SqlCommand som väljer nvarchar(max) data och reader är SqlDataReader som hämtar dem.

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

Om du vill hämta innehållet i en varbinary(max) kolumn som SqlBinary värde anropar du GetSqlBinary. Följande fragment förutsätter att cmd är SqlCommand som väljer binära data och reader är SqlDataReader som hämtar dem.

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 läser byte från den angivna kolumnförskjutningen till en bytematris vid den angivna matrisförskjutningen. Följande fragment förutsätter att reader är den SqlDataReader som hämtar bytena. Till skillnad från GetSqlBytes kräver GetBytes en storlek för arraybufferten.

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 returnerar kolumnvärdet som ett objekt. Följande fragment förutsätter att reader är SqlDataReader. I C# returnerar byte[] den binära kolumnen och teckenkolumnen returnerar string. I Visual Basic är de motsvarande typerna Byte() och 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);
}

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

Du kan konvertera innehållet i en nvarchar(max)- eller varchar(max)-kolumn med strängkonverteringsmetoder, till exempel ToString. Följande fragment förutsätter att reader är SqlDataReader 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 värdet från ProductPhoto tabellen i AdventureWorks databasen och sparar det i en fil. Det här bildkonverteringsexemplet kräver Windows och System.Drawing.Common NuGet-paketet. Den begränsningen gäller för bildkonvertering, inte för leverantörens hämtning av binärdata. Metoden GetSqlBytes returnerar SqlBytes, som exponerar Stream egenskapen. Koden använder strömmen för att skapa Bitmap objektet och sparar det i 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

Använda parametrar med stora värdetyper

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 parametern @DocumentID indata och visar resultatet som skickas tillbaka i parametern @DocumentSummary utdata 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

Se även

• SQL Server Binary och Large-Value Data
• SQL Server-datatypmappningar
• SQL Server-datahantering i ADO.NET
• översikt över ADO.NET