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

Скачать ADO.NET

Типы данных 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, а также в агрегатах, объединениях и вложенных запросах.

Дополнительные сведения о типах данных больших значений см. в этой статье в электронной документации на SQL Server.

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

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

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

Работа с типами больших значений в Transact-SQL

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

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

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

В следующем примере фотография вставляется в таблицу ProductPhoto в AdventureWorks2025 примере базы данных. При использовании поставщика 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 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.

Работа с типами больших значений в 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())  
    {  
        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())  
{  
    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())  
    {  
        SqlBinary binaryStream = reader.GetSqlBinary(0);  
    }

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

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

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  
    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())  
{  
     string str = reader[0].ToString();  
     Console.WriteLine(str);  
}

Пример

Приведенный ниже код извлекает имя и объект LargePhoto из таблицы ProductPhoto в базе данных AdventureWorks и сохраняет его в файле. Сборку необходимо скомпилировать со ссылкой на пространство имен System.Drawing. Метод GetSqlBytes класса SqlDataReader возвращает объект SqlBytes, который предоставляет свойство Stream. Код использует его для создания нового объекта Bitmap, а затем сохраняет его как изображение ImageFormat в формате Gif.

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

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

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

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

Следующие шаги

Обратная связь

Была ли эта страница полезна?

Last updated on 2025-01-02

Поделиться через

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

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

Работа с типами больших значений в Transact-SQL

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

Пример

Работа с типами больших значений в ADO.NET

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

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

Извлечение данных GetSqlBinary

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

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

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

Пример

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

Пример

Следующие шаги

Обратная связь

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