Изменение данных Large-Value (максимум) в ADO.NET

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

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

Дополнительные сведения см. в разделе "Использование типов данных Large-Value".

Ограничения типов Large-Value

Следующие ограничения применяются к типам max данных, которые не существуют для небольших типов данных:

• sql_variant не может содержать крупный тип данных varchar.

• Большие varchar столбцы нельзя указать в качестве ключевого столбца в индексе. Они разрешены в включенном столбце в некластеризованном индексе.

• Большие varchar столбцы нельзя использовать в качестве секционирования ключевых столбцов.

Работа с типами Large-Value в Transact-SQL

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

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

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

В следующем примере фотография вставляется в таблицу ProductPhoto в примере базы данных AdventureWorks. При использовании BULK OPENROWSET поставщика необходимо указать именованный список столбцов, даже если вы не вставляете значения в каждый столбец. Первичный ключ в этом случае определяется как столбец идентификатора и может быть опущен из списка столбцов. Обратите внимание, что необходимо также указать имя корреляции в конце инструкции OPENROWSET , которая в данном случае — ThumbnailPhoto. Это коррелирует с столбцом в 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 показан здесь в сокращенной форме:

Обновление

{ <object> }

УСТАНОВКА

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

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

Если	Затем
Выражение имеет значение NULL	@Length игнорируется, а значение в column_name усечено по указанному параметру @Offset.
@Offset имеет значение NULL	Операция обновления добавляет выражение в конец существующего значения в имя_столбца, при этом @Length игнорируется.
@Offset больше, чем длина значения столбца column_name	SQL Server возвращает ошибку.
@Length имеет значение NULL	Операция обновления удаляет все данные с @Offset до конца значения column_name.

Замечание

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

Пример

Этот пример Transact-SQL показывает, как обновить частичное значение в DocumentSummary, nvarchar(max) столбце таблицы Document в базе данных AdventureWorks. Слово "компоненты" заменяется словом "функции" путем указания слова замены, начального расположения (смещения) слова, заменяемого в существующих данных, и количества символов, которые необходимо заменить (длина). В примере содержатся инструкции 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.  

Работа с типами Large-Value в ADO.NET

Вы можете работать с большими типами значений в ADO.NET, указав большие типы значений в качестве SqlParameter объектов в SqlDataReader результирующем SqlDataAdapter наборе, или используя SqlDataAdapter, чтобы заполнить /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 для извлечения данных

Метод GetSqlCharsSqlDataReader можно использовать для извлечения содержимого столбца 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 для извлечения данных

Метод GetBytesSqlDataReader считывает поток байтов из указанного отступа столбца в байтовый массив, начиная с указанного отступа массива. В следующем фрагменте кода предполагается объект 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 для извлечения данных

Метод GetValueSqlDataReader считывает значение из указанного столбца и помещает его в массив. Следующий фрагмент кода предполагает объект с именем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);  
}  

Преобразование из типов Large Value в типы CLR

Можно преобразовать содержимое столбца varchar(max) с помощью любого из методов преобразования строк, таких как nvarchar(max). В следующем фрагменте кода предполагается объект 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 объекта, а затем сохраняет его в 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

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

Крупные типы значений можно использовать в 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
        {
            // 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

См. также

• Двоичные данные и данные больших значений SQL Server
• Сопоставления типов данных SQL Server
• Операции с данными SQL Server в ADO.NET
• Общие сведения об ADO.NET