Modyfikowanie danych Large-Value (maks.) w ADO.NET

Typy danych dużych obiektów (LOB) to te, które przekraczają maksymalny rozmiar wiersza wynoszący 8 kilobajtów (KB). Program SQL Server udostępnia specyfikator max dla varchar, nvarchari varbinary typów danych, aby umożliwić przechowywanie wartości o rozmiarze 2^32 bajtów. Kolumny tabeli i zmienne Transact-SQL mogą określać typy danych varchar(max), nvarchar(max)lub varbinary(max). W ADO.NET typy danych max można pobrać za pomocą DataReader, a także można je określić jako wartości parametrów wejściowych i wyjściowych bez specjalnej obsługi. W przypadku dużych varchar typów danych można pobierać i aktualizować dane przyrostowo.

Typy danych max mogą służyć do porównań, jako zmiennych Transact-SQL i łączenia. Mogą być również używane w klauzulach DISTINCT, ORDER BY i GROUP BY instrukcji SELECT, a także w agregacjach, złączeniach i podzapytaniach.

Aby uzyskać więcej informacji, zobacz Używanie typów danych Large-Value.

Ograniczenia typu Large-Value

Następujące ograniczenia dotyczą typów danych max, które nie istnieją w przypadku mniejszych typów danych:

• sql_variant nie może zawierać dużego typu danych varchar.

• Nie można określić dużych kolumn varchar jako kolumny klucza w indeksie. Są one dozwolone w dołączonej kolumnie w indeksie nieklasowanym.

• Nie można używać dużych kolumn varchar jako kolumn kluczy partycjonowania.

Praca z typami Large-Value w usłudze Transact-SQL

Funkcja Transact-SQL OPENROWSET jest jednorazową metodą łączenia i uzyskiwania dostępu do danych zdalnych. Zawiera wszystkie informacje o połączeniu niezbędne do uzyskania dostępu do danych zdalnych ze źródła danych OLE DB. do elementu OPENROWSET można odwoływać się w klauzuli FROM zapytania tak, jakby była to nazwa tabeli. Można go również przywoływać jako tabelę docelową instrukcji INSERT, UPDATE lub DELETE, z zastrzeżeniem możliwości dostawcy OLE DB.

Funkcja OPENROWSET zawiera dostawcę zestawu wierszy BULK, który umożliwia odczytywanie danych bezpośrednio z pliku bez ładowania danych do tabeli docelowej. Umożliwia to użycie OPENROWSET w prostym wyrażeniu INSERT SELECT.

Argumenty opcji OPENROWSET BULK zapewniają znaczącą kontrolę nad tym, gdzie rozpocząć i zakończyć odczytywanie danych, jak radzić sobie z błędami i jak dane są interpretowane. Można na przykład określić, że plik danych będzie odczytywany jako jednoporzędowy zestaw wierszy typu varbinary, varcharlub nvarchar.

Poniższy przykład wstawia zdjęcie do tabeli ProductPhoto w przykładowej bazie danych AdventureWorks. W przypadku korzystania z dostawcy BULK OPENROWSET należy podać nazwaną listę kolumn, nawet jeśli nie wstawiasz wartości do każdej kolumny. Klucz podstawowy w tym przypadku jest definiowany jako kolumna tożsamości i może zostać pominięty z listy kolumn. Należy również podać nazwę korelacji na końcu instrukcji OPENROWSET, która w tym przypadku to ThumbnailPhoto. Jest to skorelowane z kolumną w tabeli ProductPhoto, do której jest ładowany plik.

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

Aktualizowanie danych przy użyciu komendy UPDATE .WRITE

Instrukcja Transact-SQL UPDATE ma nową WRITE składnię do modyfikowania zawartości kolumn varchar(max), nvarchar(max) lub varbinary(max). Umożliwia to wykonywanie częściowych aktualizacji danych. Składnia UPDATE .WRITE jest wyświetlana tutaj w postaci skróconej:

UPDATE

{ <obiekt> }

SET

{ column_name = { .WRITE ( wyrażenie , @Offset , @Length ) }

Metoda WRITE określa, że fragment wartości kolumny column_name zostanie zmodyfikowany. Wyrażenie to wartość, która zostanie skopiowana do column_name, @Offset jest punktem początkowym, w którym zostanie zapisane wyrażenie, a argument @Length jest długością sekcji w kolumnie.

Jeśli	Następnie
Wyrażenie jest ustawione na wartość NULL	@Length jest ignorowana, a wartość w column_name jest obcięta w określonym punkcie @Offset.
@Offset jest NULL	Operacja aktualizacji dołącza wyrażenie na końcu istniejącej wartości column_name i ignoruje @Length.
@Offset jest większe niż długość wartości „column_name”	Program SQL Server zwraca błąd.
@Length jest NULL	Operacja aktualizacji usuwa wszystkie dane z @Offset do końca wartości column_name.

Uwaga

Ani @Offset, ani @Length nie mogą być liczbą ujemną.

Przykład

Ten Transact-SQL przykład aktualizuje wartość częściową w dokumencie DocumentSummary, kolumnę nvarchar(max) w tabeli Document w bazie danych AdventureWorks. Wyraz "components" jest zastępowany słowem "features" przez określenie wyrazu zastępczego, lokalizację początkową (przesunięcie) wyrazu, który ma zostać zastąpiony w istniejących danych, oraz liczbę znaków do zastąpienia (długość). Przykład zawiera instrukcje SELECT przed instrukcją UPDATE i po niej w celu porównania wyników.

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.

Praca z typami Large-Value w usłudze ADO.NET

Użyj SqlParameter obiektów z , SqlCommand aby wysyłać duże wartości. Pobierz wyniki za pomocą SqlDataReader, lub wypełnij obiekty DataSet lub DataTable za pomocą SqlDataAdapter. Obsługujesz duże typy wartości, podobnie jak odpowiadające im mniejsze typy danych wartości.

Pobieranie danych przy użyciu polecenia GetSqlBytes

Aby pobrać zawartość kolumny SqlBytes jako varbinary(max), wywołaj GetSqlBytes. Poniższy fragment zakłada, że cmd to SqlCommand element, który wybiera dane binarne, a reader to SqlDataReader, który je pobiera.

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

Pobieranie danych przy użyciu polecenia GetSqlChars

Aby pobrać zawartość kolumny nvarchar(max) lub varchar(max), wywołaj GetSqlChars. Poniższy fragment zakłada, że cmd jest SqlCommand elementem, który wybiera nvarchar(max) dane, a reader to SqlDataReader, który je pobiera.

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

Pobieranie danych przy użyciu metody GetSqlBinary

Aby pobrać zawartość kolumny varbinary(max) jako wartość SqlBinary, wywołaj metodę GetSqlBinary. Poniższy fragment zakłada, że cmd to SqlCommand element, który wybiera dane binarne, a reader to SqlDataReader, który je pobiera.

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

Pobieranie danych przy użyciu funkcji GetBytes

Metoda GetBytes odczytuje bajty z określonego przesunięcia kolumny do tablicy bajtów przy określonym przesunięciu tablicy. Poniższy fragment zakłada, że reader jest to obiekt SqlDataReader, który pobiera bajty. W przeciwieństwie do GetSqlBytes, GetBytes wymaga rozmiaru buforu tablicy.

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

Pobieranie danych przy użyciu polecenia GetValue

Metoda GetValue zwraca wartość kolumny jako obiekt. Poniższy fragment zakłada, że reader jest to SqlDataReader. W języku C# kolumna binarna zwraca wartość byte[] , a kolumna znaku zwraca wartość string. W Visual Basic odpowiednie typy to Byte() i 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);
}

Konwertowanie z dużych typów wartości na typy CLR

Można przekonwertować zawartość kolumny nvarchar(max) lub varchar(max) przy użyciu metod konwersji ciągów, takich jak ToString. Poniższy fragment zakłada, że reader jest tym SqlDataReader, który pobiera dane.

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

Przykład

Poniższy kod pobiera nazwę i LargePhoto wartość z ProductPhoto tabeli w AdventureWorks bazie danych i zapisuje ją w pliku. Ten przykład konwersji obrazów wymaga Windows i System.Drawing.Common pakietu NuGet. To ograniczenie dotyczy konwersji obrazów, a nie pobierania danych binarnych od dostawcy. Metoda GetSqlBytes zwraca SqlBytes, która uwidacznia właściwość Stream. Kod używa strumienia do utworzenia Bitmap obiektu i zapisuje go w formacie GIF.

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

Używanie parametrów typu dużej wartości

Duże typy wartości mogą być używane w obiektach SqlParameter w taki sam sposób, jak w przypadku mniejszych typów wartości w obiektach SqlParameter. Możesz pobrać typy o dużej wartości jako wartości SqlParameter, jak pokazano w poniższym przykładzie. W kodzie przyjęto założenie, że w przykładowej bazie danych AdventureWorks istnieje następująca procedura składowana GetDocumentSummary. Procedura składowana przyjmuje parametr wejściowy o nazwie @DocumentID i zwraca zawartość kolumny DocumentSummary w parametrze wyjściowym @DocumentSummary.

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

Przykład

Kod ADO.NET tworzy obiekty SqlConnection i SqlCommand w celu wykonania procedury składowanej GetDocumentSummary i pobrania podsumowania dokumentu przechowywanego jako duży typ wartości. Kod przekazuje wartość parametru wejściowego @DocumentID i wyświetla wyniki przekazane z powrotem w parametrze wyjściowym @DocumentSummary w oknie Konsola.

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

Zobacz też

• danych binarnych i danych Large-Value programu SQL Server
• Mapowania typów danych SQL Server
• Operacje danych w SQL Server z wykorzystaniem ADO.NET
• ADO.NET — omówienie