Dela via

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

Ladda ned 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 .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.

Se Användning av datatyper med stora värden i SQL Server Books Online för mer information om datatyper med stora värden.

Begränsningar för stora värdetyper

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. 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 i ett INSERT-, UPDATE- eller DELETE-kommando.

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. Fullständig syntax och alternativ finns i SQL Server Books Online.

I följande exempel infogas ett foto i tabellen ProductPhoto i exempeldatabasen AdventureWorks2025 . 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

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. UPPDATERA.WRITE-syntax används här i förkortad form:

UPPDATERING

{ <objekt> }

SET

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

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

If 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.

Example

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 AdventureWorks2022;
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 av 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 SqlBytes.

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);  
while (reader.Read())  
    {  
        SqlBytes bytes = reader.GetSqlBytes(0);  
    }

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

Metoden GetSqlChars av SqlDataReader kan användas för att hämta innehållet i en varchar(max)- eller nvarchar(max)-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())  
{  
    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())  
    {  
        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())  
{  
    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 kolumnens offset 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  
    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 varchar(max) eller nvarchar(max)-kolumn med 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())  
{  
     string str = reader[0].ToString();  
     Console.WriteLine(str);  
}

Example

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.

using Microsoft.Data.SqlClient;
using System.Data.SqlTypes;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;

class Program
{
    static void Main()
    {
        // Supply any valid DocumentID value and file path.
        // The value 3 is supplied for DocumentID, and a literal
        // string for the file path where the image will be saved. 1, 60
        TestGetSqlBytes(7, @"c:\temp\");
        Console.ReadLine();
    }
    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();
            }
        }
    }

    static private string GetConnectionString()
    {
        // To avoid storing the connectionection string in your code, 
        // you can retrieve it from a configuration file, using the 
        // System.Configuration.ConfigurationSettings.AppSettings property 
        return "Data Source=(local);Initial Catalog=AdventureWorks;" +
            "Integrated Security=SSPI";
    }
}

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 exempeldatabasen AdventureWorks2025 . 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

Example

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.

using Microsoft.Data.SqlClient;
class Program
{
    static void Main()
    {
        // Supply any valid Document ID value.
        // The value 7 is supplied for demonstration purposes.
        string summaryString = GetDocumentSummary(7);
        Console.ReadLine();
    }
    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;
            }
        }
    }
    static private string GetConnectionString()
    {
        // To avoid storing the connectionection string in your code, 
        // you can retrieve it from a configuration file, using the 
        // System.Configuration.ConfigurationSettings.AppSettings property
        return "Data Source=(local);Initial Catalog=AdventureWorks;" +
            "Integrated Security=SSPI";
    }
}

Nästa steg

  • Last updated on