Udostępnij przez

Modyfikowanie danych o dużej wartości (maksymalnej) w ADO.NET

Pobieranie 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 typów danych varchar, nvarchar i varbinary, który pozwala na przechowywanie wartości o maksymalnym rozmiarze 2^32 bajtów. Kolumny tabeli i zmienne Transact-SQL mogą określać varchar(max)typy danych , nvarchar(max)lub varbinary(max) . Na .NET typy danych max można pobierać za pomocą DataReader, a także określić jako wartości zarówno parametrów wejściowych, jak i wyjściowych bez potrzeby specjalnej obsługi. W przypadku dużych varchar typów danych dane można pobierać i aktualizować przyrostowo.

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

Zobacz Using Large-Value Data Types z książek SQL Server Books Online, aby uzyskać więcej informacji na temat typów danych dużej wartości.

Ograniczenia typu dużych wartości

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

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

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

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

Praca z typami dużych wartości w Transact-SQL

Funkcja Transact-SQL OPENROWSET jest jednorazową metodą łączenia i uzyskiwania dostępu do danych zdalnych. OPENROWSET można odwoływać się do klauzuli FROM zapytania, tak jakby była to nazwa tabeli. Można również wskazać ją jako tabelę docelową instrukcji INSERT, UPDATE lub DELETE.

Funkcja OPENROWSET zawiera dostawcę BULK zestawu wierszy, który umożliwia odczytywanie danych bezpośrednio z pliku bez ładowania danych do tabeli docelowej. Dzięki temu można użyć OPENROWSET w prostej instrukcji INSERT SELECT.

OPENROWSET BULK Argumenty opcji 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 zestaw wierszy z jedną kolumną typu varbinary, varcharlub nvarchar. Aby uzyskać pełną składnię i opcje, zobacz Sql Server Books Online.

Poniższy przykład wstawia zdjęcie do tabeli ProductPhoto w przykładowej AdventureWorks2025 bazie danych. W przypadku korzystania z dostawcy BULK OPENROWSET należy podać nazwaną listę kolumn, nawet jeśli nie zamierzasz wstawiać wartości do każdej z nich. 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 OPENROWSET instrukcji , która w tym przypadku to ThumbnailPhoto. Jest to skorelowane z kolumną w ProductPhoto tabeli, 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 UPDATE .WRITE

Instrukcja Transact-SQL UPDATE ma nową składnię WRITE do modyfikowania zawartości varchar(max)kolumn , 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:

Aktualizacja

{ <object> }

SET

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

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

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

Uwaga / Notatka

Ani nie @Offset@Length może być liczbą ujemną.

Example

Ten przykład Transact-SQL aktualizuje wartość częściową w kolumnie DocumentSummary, 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 nim, aby porównać wyniki.

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.

Praca z typami dużych wartości w ADO.NET

W ADO.NET można pracować z typami wartości o dużej objętości, określając je jako SqlParameter obiekty w SqlDataReader, aby zwrócić zestaw wyników, lub używając SqlDataAdapter do wypełnienia elementu DataSet/DataTable. Nie ma różnicy między sposobem pracy z dużym typem wartości a powiązanym, mniejszym typem danych wartości.

Pobieranie danych przy użyciu polecenia GetSqlBytes

Metoda GetSqlBytes klasy SqlDataReader może służyć do pobierania zawartości kolumny varbinary(max). Poniższy fragment kodu zakłada SqlCommand obiekt o nazwie cmd , który wybiera varbinary(max) dane z tabeli i SqlDataReader obiekt o nazwie reader , który pobiera dane jako SqlBytes.

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

Pobieranie danych przy użyciu polecenia GetSqlChars

Metoda GetSqlChars w SqlDataReader może służyć do pobierania zawartości kolumny varchar(max) lub nvarchar(max). Poniższy fragment kodu zakłada SqlCommand obiekt o nazwie cmd , który wybiera nvarchar(max) dane z tabeli i SqlDataReader obiekt o nazwie reader , który pobiera dane.

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);  
while (reader.Read())  
{  
    SqlChars buffer = reader.GetSqlChars(0);  
}

Pobieranie danych przy użyciu metody GetSqlBinary

Metoda GetSqlBinary elementu SqlDataReader może służyć do pobierania zawartości varbinary(max) kolumny. Poniższy fragment kodu zakłada SqlCommand obiekt o nazwie cmd , który wybiera varbinary(max) dane z tabeli i SqlDataReader obiekt o nazwie reader , który pobiera dane jako SqlBinary strumień.

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);  
while (reader.Read())  
    {  
        SqlBinary binaryStream = reader.GetSqlBinary(0);  
    }

Pobieranie danych przy użyciu funkcji GetBytes

GetBytes Metoda SqlDataReader odczytu strumienia bajtów z określonego przesunięcia kolumny do tablicy bajtów, zaczynając od określonego przesunięcia w tablicy. Poniższy fragment kodu zakłada SqlDataReader obiekt o nazwie reader , który pobiera bajty do tablicy bajtów. Należy pamiętać, że w przeciwieństwie do GetSqlBytes, GetBytes wymaga rozmiaru buforu tablicy.

while (reader.Read())  
{  
    byte[] buffer = new byte[4000];  
    long byteCount = reader.GetBytes(1, 0, buffer, 0, 4000);  
}

Pobieranie danych przy użyciu polecenia GetValue

GetValue metoda SqlDataReader odczytuje wartość ze wskazanego przesunięcia kolumny do tablicy. Poniższy fragment kodu zakłada obiekt o nazwie reader, który pobiera dane binarne z przesunięcia pierwszej kolumny, a następnie dane tekstowe z przesunięcia drugiej kolumny.

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

Zawartość kolumny varchar(max) lub nvarchar(max) można przekonwertować przy użyciu dowolnej z metod konwersji łańcuchów znaków, takich jak ToString. Poniższy fragment kodu zakłada SqlDataReader obiekt o nazwie reader , który pobiera dane.

while (reader.Read())  
{  
     string str = reader[0].ToString();  
     Console.WriteLine(str);  
}

Example

Poniższy kod pobiera nazwę i LargePhoto obiekt z ProductPhoto tabeli w AdventureWorks bazie danych i zapisuje go w pliku. Zestaw należy skompilować przy użyciu odwołania do System.Drawing przestrzeni nazw. Metoda GetSqlBytes z SqlDataReader zwraca obiekt SqlBytes, który udostępnia właściwość Stream. Kod używa go do utworzenia nowego Bitmap obiektu, a następnie zapisuje go w pliku 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";
    }
}

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

Duże typy wartości mogą być używane w obiektach SqlParameter tak samo, jak mniejsze typy wartości w obiektach SqlParameter. Możesz pobrać duże typy wartości jako SqlParameter wartości, jak pokazano w poniższym przykładzie. W kodzie przyjęto założenie, że w przykładowej AdventureWorks2025 bazie danych 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

Example

Kod ADO.NET tworzy obiekty SqlConnection i SqlCommand do wykonywania procedury składowanej GetDocumentSummary i pobierania podsumowania dokumentu, które jest przechowywane jako duży typ danych. Kod przekazuje wartość parametru wejściowego @DocumentID i wyświetla wyniki przekazane z powrotem w parametrze wyjściowym @DocumentSummary w oknie Konsola.

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

Dalsze kroki

  • Last updated on