Изменение данных больших объемов (max) в ADO.NET

Статья
04/08/2023

Типы данных LOB — это данные, размер которых превышает максимальный размер строки в 8 килобайт (КБ). SQL Server представляет описатель max для типов данных varchar, nvarchar и varbinary, позволяющий сохранять значения размером до 2^32 байт. В столбцах таблицы и переменных Transact-SQL может быть указан тип данных varchar(max), nvarchar(max) или varbinary(max). В ADO.NET типы данных max можно выбрать с помощью объекта DataReader, а также их можно задавать в качестве значений входных и выходных параметров без какой-либо специальной обработки. В случае типов больших значений varchar данные могут извлекаться и обновляться постепенно.

Типы данных max можно использовать для сравнения как переменные языка Transact-SQL, а также для объединения. Кроме того, их можно использовать в предложениях DISTINCT, ORDER BY и GROUP BY инструкции SELECT, а также в агрегатах, объединениях и вложенных запросах.

Дополнительные сведения см. в разделе "Использование типов данных больших значений".

Ограничения для типов данных большого размера

Приведенные ниже ограничения применяются к типам данных max, которые не существуют для типов данных меньших значений.

sql_variant не может содержать тип данных больших значений varchar.
Столбцы с данными больших значений varchar нельзя указать в качестве ключевого столбца в индексе. Они разрешены в столбце, включенном в некластеризованный индекс.
Столбцы с данными больших значений varchar нельзя использовать в качестве ключевых столбцов секционирования.

Работа с типами большого размера на языке Transact-SQL

Функция Transact-SQL OPENROWSET — это одноразовый метод подключения и получения доступа к удаленным данным. Включает все сведения о соединении, необходимые для доступа к удаленным данным источника данных OLE DB. Из предложения FROM запроса можно ссылаться на функцию OPENROWSET как на имя таблицы. Она также может быть использована в качестве целевой таблицы в инструкциях INSERT, UPDATE или DELETE. Это зависит от возможностей поставщика OLE DB.

Функция OPENROWSET содержит поставщик наборов строк BULK, который позволяет считывать данные напрямую из файла без загрузки в целевую таблицу. Это позволяет использовать функцию OPENROWSET в обычной инструкции INSERT SELECT.

С помощью аргументов параметра OPENROWSET BULK можно управлять началом и концом считывания данных, отладкой ошибок и способом представления полученных данных. Например, можно указать, что файл с данными будет считан как однострочный или как набор строк типа varbinary, varchar или nvarchar в один столбец.

Следующий пример вставляет фотографию в таблицу ProductPhoto в примере базы данных AdventureWorks. При использовании поставщика BULK OPENROWSET необходимо указывать именованный список столбцов, даже если значения не вставляются в каждый столбец. В этом случае первичный ключ определяется как столбец идентификаторов и может быть опущен в списке столбцов. Обратите внимание, что вам необходимо лишь указать имя корреляции (в данном случае ThumbnailPhoto) в конце инструкции OPENROWSET. Оно соотносится со столбцом в таблице ProductPhoto, в которую загружается файл.

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

Обновление данных с помощью инструкций UPDATE .WRITE

В инструкции Transact-SQL UPDATE имеется новый синтаксис WRITE, используемый для изменения содержимого столбцов varchar(max), nvarchar(max) или varbinary(max). Он позволяет выполнять частичные обновления данных. Синтаксис UPDATE .WRITE указан здесь в сокращенной форме.

UPDATE

{ <object> }

SET

{ column_name = { .WRITE ( выражение , @Offset , @Length ) }

Метод WRITE указывает, что часть значения column_name будет изменена. Выражение является значением, которое будет скопировано в поле column_name. Аргумент @Offset является начальной точкой записи выражения, а аргумент @Length — длиной изменяемой секции в столбце.

If	Следующее действие
Для выражения задано значение NULL.	Аргумент `@Length` не обрабатывается, а значение в поле column_name усекается в соответствии с указанным аргументом `@Offset`.
`@Offset` равно NULL	Операция обновления добавляет выражение в конец существующего значения column_name, и аргумент `@Length` не обрабатывается.
Значение аргумента `@Offset` больше, чем длина значения аргумента column_name.	SQL Server возвращает ошибку.
`@Length` равно NULL	Операция обновления удаляет все данные, начиная с позиции `@Offset` до конца значения `column_name`.

Примечание.

Ни @Offset, ни @Length не может быть отрицательным числом.

Пример

Этот пример Transact-SQL обновляет частичное значение в DocumentSummary, столбце nvarchar(max) таблицы Document в базе данных AdventureWorks. Слово components заменяется словом features, при этом указывается новое слово, начальное смещение слова, заменяемого в исходном тексте, и число заменяемых символов (длина). Этот пример содержит инструкцию SELECT перед и после инструкции UPDATE для сравнения результатов.

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.

Работа с типами данных большого размера в ADO.NET

В ADO.NET можно работать с типами больших значений, указав их в качестве параметров SqlParameter в SqlDataReader для возврата результирующего набора либо воспользовавшись объектом SqlDataAdapter для заполнения набора DataSet/DataTable. Обработка типов больших значений и связанных с ними типов данных меньших значений ничем не отличается.

Извлечение данных с помощью GetSqlBytes

Метод GetSqlBytes класса SqlDataReader можно использовать для извлечения содержимого столбца varbinary(max). В приведенном ниже фрагменте кода предполагается наличие объекта SqlCommand с именем cmd, который выбирает данные varbinary(max) из таблицы, и объекта SqlDataReader с именем reader, который извлекает данные в качестве класса SqlBytes.

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

Использование метода GetSqlChars для получения данных

Метод GetSqlChars класса SqlDataReader можно использовать для извлечения содержимого столбца varchar(max) или nvarchar(max). В приведенном ниже фрагменте кода предполагается наличие объекта SqlCommand с именем cmd, который выбирает данные nvarchar(max) из таблицы, и объекта SqlDataReader с именем reader, который извлекает данные.

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

Использование метода GetSqlBinary для получения данных

Метод GetSqlBinary класса SqlDataReader можно использовать для извлечения содержимого столбца varbinary(max). В приведенном ниже фрагменте кода предполагается наличие объекта SqlCommand с именем cmd, который выбирает данные varbinary(max) из таблицы, и объекта SqlDataReader с именем reader, который извлекает данные в качестве потока SqlBinary.

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 для получения данных

Метод GetBytes класса SqlDataReader считывает поток байтов с указанного смещения столбца в массив байтов, начиная с указанного смещения массива. В приведенном ниже фрагменте кода предполагается наличие объекта SqlDataReader с именем reader, который извлекает байты в массив байтов. Обратите внимание, что в отличие от GetSqlBytes для метода GetBytes требуется размер для буфера массива.

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

Использование метода GetValue для получения данных

Метод GetValue класса SqlDataReader считывает значение из указанного смещения столбца в массив. В приведенном ниже фрагменте кода предполагается наличие объекта SqlDataReader с именем reader, который извлекает двоичные данные из первого смещения столбца, а затем данные строки из второго смещения столбца.

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

Преобразование типов больших значений в типы CLR

Вы можете преобразовать содержимое столбца varchar(max) или nvarchar(max) с использованием любого метода преобразования строк, например ToString. В приведенном ниже фрагменте кода предполагается наличие объекта SqlDataReader с именем reader, который извлекает данные.

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

Пример

Приведенный ниже код извлекает имя и объект LargePhoto из таблицы ProductPhoto в базе данных AdventureWorks и сохраняет его в файле. Сборку необходимо скомпилировать со ссылкой на пространство имен System.Drawing. Метод GetSqlBytes класса SqlDataReader возвращает объект SqlBytes, который предоставляет свойство Stream. Код использует его для создания нового объекта Bitmap, а затем сохраняет его как изображение ImageFormat в формате 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("{0} is unavailable.", photoName);
                    }
                    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 {0}.", 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 Bitmap = _
                          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

Использование параметров типа больших значений

Типы больших значений можно использовать в объектах SqlParameter точно так же, как и типы меньших значений в объектах SqlParameter. Типы больших значений можно извлекать в виде значений SqlParameter, как показано в следующем примере. В коде предполагается существование в примере базы данных AdventureWorks приведенной ниже хранимой процедуры GetDocumentSummary. Хранимая процедура принимает входной параметр @DocumentID и возвращает содержимое столбца DocumentSummary в выходной параметр @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

Пример

Код ADO.NET создает объекты SqlConnection и SqlCommand для выполнения хранимой процедуры GetDocumentSummary и извлечения сводки документа, которая сохраняется как тип больших значений. Код передает значение входному параметру @DocumentID и отображает результаты, переданные обратно в выходной параметр @DocumentSummary, в окне консоли.

static string? GetDocumentSummary(int documentID)
{
    //Assumes GetConnectionString returns a valid connection string.
    using (SqlConnection connection =
               new(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("@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

Share via

Изменение данных больших объемов (max) в ADO.NET

Ограничения для типов данных большого размера

Работа с типами большого размера на языке Transact-SQL

Обновление данных с помощью инструкций UPDATE .WRITE

Пример

Работа с типами данных большого размера в ADO.NET

Извлечение данных с помощью GetSqlBytes

Использование метода GetSqlChars для получения данных

Использование метода GetSqlBinary для получения данных

Использование метода GetBytes для получения данных

Использование метода GetValue для получения данных

Преобразование типов больших значений в типы CLR

Пример

Использование параметров типа больших значений

Пример

См. также

Дополнительные ресурсы