Large-Value (maximális) adatok módosítása az ADO.NET-ben

A nagy méretű objektum (LOB) adattípusok azok, amelyek túllépik a 8 kilobájtos (KB) maximális sorméretet. Az SQL Server biztosít egy max jelölőt a varchar, nvarcharés varbinary adattípusokhoz, amelyek lehetővé teszik az akár 2^32 bájtos értékek tárolását. A táblaoszlopok és Transact-SQL változók megadhatnak varchar(max), nvarchar(max)vagy varbinary(max) adattípusokat. Az ADO.NET-ben a max adattípusok beolvashatók egy DataReaderáltal, és különleges kezelés nélkül is meghatározhatók bemeneti és kimeneti paraméterértékként. Nagy varchar adattípusok esetén az adatok növekményesen kérhetők le és frissíthetők.

A max adattípusok használhatók összehasonlításokhoz, Transact-SQL változókhoz és összefűzéshez. A SELECT utasítás DISTINCT, ORDER BY, GROUP BY záradékaiban, valamint összesítésekben, kapcsolásokban és allekérdezésekben is használhatók.

További információ: Large-Value adattípusok használata.

Large-Value típuskorlátozások

A következő korlátozások vonatkoznak a max adattípusokra, amelyek kisebb adattípusok esetén nem léteznek:

• Egy sql_variant nem tartalmazhat nagy varchar adattípust.

• A nagyméretű varchar oszlopok nem adhatók meg kulcsoszlopként egy indexben. Engedélyezettek a nem fürtözött index egyesített oszlopaiban.

• A nagyméretű varchar oszlopok nem használhatók particionálási kulcsoszlopokként.

Large-Value-típusok használata a Transact-SQL

A Transact-SQL OPENROWSET függvény egy egyszeri módszer a távoli adatok csatlakoztatására és elérésére. Tartalmazza az OLE DB-adatforrásból származó távoli adatok eléréséhez szükséges összes kapcsolati információt. OPENROWSET egy lekérdezés FROM záradékában úgy hivatkozhat rá, mintha táblanév lenne. Az INSERT, UPDATE vagy DELETE utasítás céltáblájaként is hivatkozhat rá, az OLE DB-szolgáltató képességeinek függvényében.

A OPENROWSET függvény tartalmazza a BULK sorkészlet-szolgáltatót, amely lehetővé teszi az adatok közvetlen beolvasását egy fájlból anélkül, hogy betöltenie az adatokat egy céltáblába. Ez lehetővé teszi a OPENROWSET egyszerű INSERT SELECT utasításban való használatát.

A OPENROWSET BULK beállításargumentumok jelentős mértékben befolyásolják az olvasási adatok kezdő és befejező helyét, a hibák kezelését és az adatok értelmezését. Megadhatja például, hogy az adatfájl egysoros, egyoszlopos sorhalmazként legyen olvasható, varbinary, varcharvagy nvarchar.

Az alábbi példa beszúr egy fényképet az AdventureWorks mintaadatbázis ProductPhoto táblázatába. A BULK OPENROWSET szolgáltató használatakor akkor is meg kell adnia az oszlopok nevesített listáját, ha nem szúr be értékeket minden oszlopba. Ebben az esetben az elsődleges kulcs identitásoszlopként van definiálva, és kihagyható az oszloplistából. Vegye figyelembe, hogy a OPENROWSET utasítás végén meg kell adnia egy korrelációs nevet is, amely ebben az esetben a ThumbnailPhoto. Ez korrelál a ProductPhoto tábla azon oszlopával, amelybe a fájl betöltődik.

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  

Adatok frissítése az UPDATE .WRITE használatával.

Az Transact-SQL UPDATE utasítás új ÍRÁSi szintaxissal rendelkezik a varchar(max), nvarchar(max)vagy varbinary(max) oszlopok tartalmának módosításához. Ez lehetővé teszi az adatok részleges frissítését. Az UPDATE . A WRITE szintaxis rövidített formában jelenik meg:

Frissít

{ <objektum> }

BEÁLLÍTÁS

{ column_name = { . WRITE ( kifejezés , @Offset , @Length ) }

A WRITE metódus azt határozza meg, hogy a column_name értékének egy szakasza módosuljon. A kifejezés a column_namemásolandó érték, a @Offset a kifejezés írásának kezdőpontja, a @Length argumentum pedig az oszlopban lévő szakasz hossza.

Ha	Akkor
A kifejezés null értékűre van állítva	A @Length figyelmen kívül van hagyva, és a megadott -nál a @Offset értéke le van csonkolva.
@Offset null értékű	A frissítési művelet hozzáfűzi a kifejezést a meglévő column_name érték végéhez, és a @Length figyelmen kívül hagyja.
@Offset nagyobb, mint a column_name értékének hosszúsága	Az SQL Server hibát ad vissza.
@Length null értékű	A frissítési művelet eltávolítja az összes adatot a @Offset-tól az column_name érték végéig.

Megjegyzés

Sem @Offset, sem @Length nem lehet negatív szám.

Példa

Ez a Transact-SQL példa egy részleges értéket frissít a DocumentSummary fájlban, amely egy nvarchar(max) oszlop az AdventureWorks-adatbázis Dokumentum táblájában. Az "összetevők" szót a "features" szó váltja fel a helyettesítő szó, a lecserélendő szó kezdő helye (eltolása) megadásával a meglévő adatokban, valamint a lecserélendő karakterek számának (hosszúságának) megadásával. A példa az UPDATE utasítás előtti és utáni SELECT utasításokat is tartalmazza az eredmények összehasonlításához.

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.  

A ADO.NET Large-Value típusainak használata

A ADO.NET nagy értéktípusokkal is dolgozhat, ha nagy értéktípusokat ad meg SqlParameter objektumként egy SqlDataReader eredményhalmaz visszaadásához, vagy egy SqlDataAdapter használatával egy DataSet/DataTablekitöltéséhez. Nincs különbség a nagy értéktípus és a kapcsolódó, kisebb érték adattípus között.

GetSqlBytes használata adatok lekéréséhez

A GetSqlBytes metódus a(z) SqlDataReader-hez használható egy varbinary(max) oszlop tartalmának lekéréséhez. A következő kódrészlet feltételez egy SqlCommand típusú objektumot cmd néven, amely varbinary(max) adatokat választ ki egy táblázatból, valamint egy SqlDataReader típusú objektumot reader néven, amely az adatokat SqlBytesformájában lekéri.

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

Adatok lekérése a GetSqlChars használatával

A GetSqlCharsSqlDataReader metódusa használható egy varchar(max) vagy nvarchar(max) oszlop tartalmának lekérésére. A következő kódrészlet feltételez egy SqlCommandcmd nevű objektumot, amely nvarchar(max) adatokat választ ki egy táblából és egy SqlDataReaderreader nevű objektumból, amely lekéri az adatokat.

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

Adatok lekérése a GetSqlBinary használatával

A GetSqlBinarySqlDataReader metódusa használható egy varbinary(max) oszlop tartalmának lekérésére. Az alábbi kódtöredék feltételez egy SqlCommandcmd nevű objektumot, amely varbinary(max) adatokat választ ki egy táblából, és egy SqlDataReaderreader nevű objektumot, amely SqlBinary streamként kéri le az adatokat.

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 használata adatok lekéréséhez

A GetBytes metódusa a SqlDataReader oszlop megadott eltolásából olvas be egy bájtokból álló adatfolyamot egy bájttömbbe, a megadott tömbeltolástól kezdve. Az alábbi kódtöredék egy SqlDataReader nevű reader objektumot feltételez, amely bájtokat kér le egy bájttömbbe. Vegye figyelembe, hogy a GetSqlBytesellentétben a GetBytes a tömbpuffer méretét igényli.

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

Adatok lekérése a GetValue használatával

Egy GetValueSqlDataReader metódusa beolvassa a megadott oszlopeltolás értékét egy tömbbe. A következő kódtöredék feltételez egy SqlDataReaderreader nevű objektumot, amely lekéri a bináris adatokat az első oszlop eltolásából, majd a karakterlánc adatokat a második oszlop eltolásából.

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

Konvertálás nagy értéktípusokból CLR-típusokká

Egy varchar(max) vagy nvarchar(max) oszlop tartalmát bármely sztringkonvertálási módszerrel konvertálhatja, például ToString. Az alábbi kódrészlet feltételezi, hogy létezik egy SqlDataReader típusú, reader nevű objektum, amely lekéri az adatokat.

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

Példa

Az alábbi kód lekéri a nevet és a LargePhoto objektumot a ProductPhoto adatbázis AdventureWorks táblájából, és menti egy fájlba. A szerelvényt a System.Drawing névtérre mutató hivatkozással kell lefordítani. A GetSqlBytesSqlDataReader metódusa egy SqlBytes objektumot ad vissza, amely egy Stream tulajdonságot tesz elérhetővé. A kód ezzel létrehoz egy új Bitmap objektumot, majd menti azt a 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($"{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

Nagy értékű típusparaméterek használata

A nagyméretű értéktípusok ugyanúgy használhatók SqlParameter objektumokban, mint SqlParameter objektumokban. A nagy értéktípusokat SqlParameter értékként is lekérheti, ahogy az alábbi példában is látható. A kód feltételezi, hogy a következő GetDocumentSummary tárolt eljárás létezik az AdventureWorks mintaadatbázisban. A tárolt eljárás egy @DocumentID nevű bemeneti paramétert vesz fel, és visszaadja a DocumentSummary oszlop tartalmát a @DocumentSummary kimeneti paraméterben.

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  

Példa

A ADO.NET kód SqlConnection és SqlCommand objektumokat hoz létre a GetDocumentSummary tárolt eljárás végrehajtásához és a nagy értéktípusként tárolt dokumentumösszegzés lekéréséhez. A kód átadja a @DocumentID bemeneti paraméter értékét, és megjeleníti a konzolablak @DocumentSummary kimeneti paraméterében visszaadott eredményeket.

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

Lásd még

• SQL Server bináris és Large-Value adat
• SQL Server adattípus-leképezések
• SQL Server-adatműveletek a ADO.NET
• ADO.NET áttekintés