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

Download-DownArrow-CircledСкачать ADO.NETDownload-DownArrow-CircledDownload ADO.NET

Типы данных LOB — это данные, размер которых превышает максимальный размер строки в 8 килобайт (КБ).Large object (LOB) data types are those that exceed the maximum row size of 8 kilobytes (KB). SQL Server представляет описатель max для типов данных varchar, nvarchar и varbinary, позволяющий сохранять значения размером до 2^32 байт.SQL Server provides a max specifier for varchar, nvarchar, and varbinary data types to allow storage of values as large as 2^32 bytes. В столбцах таблицы и переменных Transact-SQL может быть указан тип данных varchar(max), nvarchar(max) или varbinary(max).Table columns and Transact-SQL variables may specify varchar(max), nvarchar(max), or varbinary(max) data types. В ADO.NET новые типы данных max можно выбрать с помощью объекта DataReader, а также их можно задавать в качестве значений входных и выходных параметров без какой-либо специальной обработки.In .NET, the max data types can be fetched by a DataReader, and can also be specified as both input and output parameter values without any special handling. В случае типов больших значений varchar данные могут извлекаться и обновляться постепенно.For large varchar data types, data can be retrieved and updated incrementally.

Типы данных max можно использовать для сравнения как переменные языка Transact-SQL, а также для объединения.The max data types can be used for comparisons, as Transact-SQL variables, and for concatenation. Кроме того, их можно использовать в предложениях DISTINCT, ORDER BY и GROUP BY инструкции SELECT, а также в агрегатах, объединениях и вложенных запросах.They can also be used in the DISTINCT, ORDER BY, GROUP BY clauses of a SELECT statement as well as in aggregates, joins, and subqueries.

Дополнительные сведения о типах данных больших значений см. в этой статье в электронной документации на SQL Server.See Using Large-Value Data Types from SQL Server Books Online more details on large-value data types.

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

Приведенные ниже ограничения применяются к типам данных max, которые не существуют для типов данных меньших значений.The following restrictions apply to the max data types, which do not exist for smaller data types:

  • sql_variant не может содержать тип данных больших значений varchar.A sql_variant cannot contain a large varchar data type.

  • Столбцы с данными больших значений varchar нельзя указать в качестве ключевого столбца в индексе.Large varchar columns cannot be specified as a key column in an index. Они разрешены в столбце, включенном в некластеризованный индекс.They are allowed in an included column in a non-clustered index.

  • Столбцы с данными больших значений varchar нельзя использовать в качестве ключевых столбцов секционирования.Large varchar columns cannot be used as partitioning key columns.

Работа с типами больших значений в Transact-SQLWorking with large-value types in transact-SQL

Функция Transact-SQL OPENROWSET — это одноразовый метод подключения и получения доступа к удаленным данным.The Transact-SQL OPENROWSET function is a one-time method of connecting and accessing remote data. Из предложения FROM запроса можно ссылаться на функцию OPENROWSET как на имя таблицы.OPENROWSET can be referenced in the FROM clause of a query as though it were a table name. На нее можно также ссылаться как на целевую таблицу в инструкции INSERT, UPDATE или DELETE.It can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement.

Функция OPENROWSET содержит поставщик наборов строк BULK, который позволяет считывать данные напрямую из файла без загрузки в целевую таблицу.The OPENROWSET function includes the BULK rowset provider, which allows you to read data directly from a file without loading the data into a target table. Это позволяет использовать функцию OPENROWSET в обычной инструкции INSERT SELECT.This enables you to use OPENROWSET in a simple INSERT SELECT statement.

С помощью аргументов параметра OPENROWSET BULK можно управлять началом и концом считывания данных, отладкой ошибок и способом представления полученных данных.The OPENROWSET BULK option arguments provide significant control over where to begin and end reading data, how to deal with errors, and how data is interpreted. Например, можно указать, что файл с данными будет считан как однострочный или как набор строк типа varbinary, varchar или nvarchar в один столбец.For example, you can specify that the data file be read as a single-row, single-column rowset of type varbinary, varchar, or nvarchar. Полное описание синтаксиса и параметров см. в электронной документации на SQL Server.For the complete syntax and options, see SQL Server Books Online.

Следующий пример вставляет фотографию в таблицу ProductPhoto в примере базы данных AdventureWorks.The following example inserts a photo into the ProductPhoto table in the AdventureWorks sample database. При использовании поставщика BULK OPENROWSET необходимо указывать именованный список столбцов, даже если значения не вставляются в каждый столбец.When using the BULK OPENROWSET provider, you must supply the named list of columns even if you aren't inserting values into every column. В этом случае первичный ключ определяется как столбец идентификаторов и может быть опущен в списке столбцов.The primary key in this case is defined as an identity column, and may be omitted from the column list. Обратите внимание, что вам необходимо лишь указать имя корреляции (в данном случае ThumbnailPhoto) в конце инструкции OPENROWSET.Note that you must also supply a correlation name at the end of the OPENROWSET statement, which in this case is ThumbnailPhoto. Оно соотносится со столбцом в таблице ProductPhoto, в которую загружается файл.This correlates with the column in the ProductPhoto table into which the file is being loaded.

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 .WRITEUpdating data using UPDATE .WRITE

В инструкции Transact-SQL UPDATE имеется новый синтаксис WRITE, используемый для изменения содержимого столбцов varchar(max), nvarchar(max) или varbinary(max).The Transact-SQL UPDATE statement has new WRITE syntax for modifying the contents of varchar(max), nvarchar(max), or varbinary(max) columns. Он позволяет выполнять частичные обновления данных.This allows you to perform partial updates of the data. Синтаксис UPDATE .WRITE указан здесь в сокращенной форме.The UPDATE .WRITE syntax is shown here in abbreviated form:

UPDATEUPDATE

{ <object> }{ <object> }

SETSET

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

Метод WRITE указывает, что часть значения column_name будет изменена.The WRITE method specifies that a section of the value of the column_name will be modified. Выражение является значением, которое будет скопировано в поле column_name. Аргумент @Offset является начальной точкой записи выражения, а аргумент @Length — длиной изменяемой секции в столбце.The expression is the value that will be copied to the column_name, the @Offset is the beginning point at which the expression will be written, and the @Length argument is the length of the section in the column.

ЕслиIf ТоThen
Для выражения задано значение NULL.The expression is set to NULL Аргумент @Length не обрабатывается, а значение в поле column_name усекается в соответствии с указанным аргументом @Offset.@Length is ignored and the value in column_name is truncated at the specified @Offset.
@Offset равно NULL@Offset is NULL Операция обновления добавляет выражение в конец существующего значения column_name, и аргумент @Length не обрабатывается.The update operation appends the expression at the end of the existing column_name value and @Length is ignored.
Значение аргумента @Offset больше, чем длина значения аргумента column_name.@Offset is greater than the length of the column_name value SQL Server возвращает ошибку.SQL Server returns an error.
@Length равно NULL@Length is NULL Операция обновления удаляет все данные, начиная с позиции @Offset до конца значения column_name.The update operation removes all data from @Offset to the end of the column_name value.

Примечание

Ни @Offset, ни @Length не может быть отрицательным числом.Neither @Offset nor @Length can be a negative number.

ПримерExample

Этот пример Transact-SQL обновляет частичное значение в DocumentSummary, столбце nvarchar(max) таблицы Document в базе данных AdventureWorks.This Transact-SQL example updates a partial value in DocumentSummary, an nvarchar(max) column in the Document table in the AdventureWorks database. Слово components заменяется словом features, при этом указывается новое слово, начальное смещение слова, заменяемого в исходном тексте, и число заменяемых символов (длина).The word 'components' is replaced by the word 'features' by specifying the replacement word, the beginning location (offset) of the word to be replaced in the existing data, and the number of characters to be replaced (length). Этот пример содержит инструкцию SELECT перед и после инструкции UPDATE для сравнения результатов.The example includes SELECT statements before and after the UPDATE statement to compare results.

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.NETWorking with large-value types in ADO.NET

В ADO.NET можно работать с типами больших значений, указав их в качестве параметров SqlParameter в SqlDataReader для возврата результирующего набора либо воспользовавшись объектом SqlDataAdapter для заполнения набора DataSet/DataTable.You can work with large value types in ADO.NET by specifying large value types as SqlParameter objects in a SqlDataReader to return a result set, or by using a SqlDataAdapter to fill a DataSet/DataTable. Обработка типов больших значений и связанных с ними типов данных меньших значений ничем не отличается.There is no difference between the way you work with a large value type and its related, smaller value data type.

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

Метод GetSqlBytes класса SqlDataReader можно использовать для извлечения содержимого столбца varbinary(max).The GetSqlBytes method of the SqlDataReader can be used to retrieve the contents of a varbinary(max) column. В приведенном ниже фрагменте кода предполагается наличие объекта SqlCommand с именем cmd, который выбирает данные varbinary(max) из таблицы, и объекта SqlDataReader с именем reader, который извлекает данные в качестве класса SqlBytes.The following code fragment assumes a SqlCommand object named cmd that selects varbinary(max) data from a table and a SqlDataReader object named reader that retrieves the data as SqlBytes.

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

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

Метод GetSqlChars класса SqlDataReader можно использовать для извлечения содержимого столбца varchar(max) или nvarchar(max).The GetSqlChars method of the SqlDataReader can be used to retrieve the contents of a varchar(max) or nvarchar(max) column. В приведенном ниже фрагменте кода предполагается наличие объекта SqlCommand с именем cmd, который выбирает данные nvarchar(max) из таблицы, и объекта SqlDataReader с именем reader, который извлекает данные.The following code fragment assumes a SqlCommand object named cmd that selects nvarchar(max) data from a table and a SqlDataReader object named reader that retrieves the data.

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

Извлечение данных GetSqlBinaryUsing GetSqlBinary to retrieve data

Метод GetSqlBinary класса SqlDataReader можно использовать для извлечения содержимого столбца varbinary(max).The GetSqlBinary method of a SqlDataReader can be used to retrieve the contents of a varbinary(max) column. В приведенном ниже фрагменте кода предполагается наличие объекта SqlCommand с именем cmd, который выбирает данные varbinary(max) из таблицы, и объекта SqlDataReader с именем reader, который извлекает данные в качестве потока SqlBinary.The following code fragment assumes a SqlCommand object named cmd that selects varbinary(max) data from a table and a SqlDataReader object named reader that retrieves the data as a SqlBinary stream.

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

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

Метод GetBytes класса SqlDataReader считывает поток байтов с указанного смещения столбца в массив байтов, начиная с указанного смещения массива.The GetBytes method of a SqlDataReader reads a stream of bytes from the specified column offset into a byte array starting at the specified array offset. В приведенном ниже фрагменте кода предполагается наличие объекта SqlDataReader с именем reader, который извлекает байты в массив байтов.The following code fragment assumes a SqlDataReader object named reader that retrieves bytes into a byte array. Обратите внимание, что в отличие от GetSqlBytes для метода GetBytes требуется размер для буфера массива.Note that, unlike GetSqlBytes, GetBytes requires a size for the array buffer.

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

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

Метод GetValue класса SqlDataReader считывает значение из указанного смещения столбца в массив.The GetValue method of a SqlDataReader reads the value from the specified column offset into an array. В приведенном ниже фрагменте кода предполагается наличие объекта SqlDataReader с именем reader, который извлекает двоичные данные из первого смещения столбца, а затем данные строки из второго смещения столбца.The following code fragment assumes a SqlDataReader object named reader that retrieves binary data from the first column offset, and then string data from the second column offset.

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

Преобразование типов больших значений в типы CLRConverting from large value types to CLR types

Вы можете преобразовать содержимое столбца varchar(max) или nvarchar(max) с использованием любого метода преобразования строк, например ToString.You can convert the contents of a varchar(max) or nvarchar(max) column using any of the string conversion methods, such as ToString. В приведенном ниже фрагменте кода предполагается наличие объекта SqlDataReader с именем reader, который извлекает данные.The following code fragment assumes a SqlDataReader object named reader that retrieves the data.

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

ПримерExample

Приведенный ниже код извлекает имя и объект LargePhoto из таблицы ProductPhoto в базе данных AdventureWorks и сохраняет его в файле.The following code retrieves the name and the LargePhoto object from the ProductPhoto table in the AdventureWorks database and saves it to a file. Сборку необходимо скомпилировать со ссылкой на пространство имен System.Drawing.The assembly needs to be compiled with a reference to the System.Drawing namespace. Метод GetSqlBytes класса SqlDataReader возвращает объект SqlBytes, который предоставляет свойство Stream.The GetSqlBytes method of the SqlDataReader returns a SqlBytes object that exposes a Stream property. Код использует его для создания нового объекта Bitmap, а затем сохраняет его как изображение ImageFormat в формате Gif.The code uses this to create a new Bitmap object, and then saves it in the 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";
    }
}

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

Типы больших значений можно использовать в объектах SqlParameter точно так же, как и типы меньших значений в объектах SqlParameter.Large value types can be used in SqlParameter objects the same way you use smaller value types in SqlParameter objects. Типы больших значений можно извлекать в виде значений SqlParameter, как показано в следующем примере.You can retrieve large value types as SqlParameter values, as shown in the following example. В коде предполагается существование в примере базы данных AdventureWorks приведенной ниже хранимой процедуры GetDocumentSummary.The code assumes that the following GetDocumentSummary stored procedure exists in the AdventureWorks sample database. Хранимая процедура принимает входной параметр @DocumentID и возвращает содержимое столбца DocumentSummary в выходной параметр @DocumentSummary.The stored procedure takes an input parameter named @DocumentID and returns the contents of the DocumentSummary column in the @DocumentSummary output parameter.

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

Код ADO.NET создает объекты SqlConnection и SqlCommand для выполнения хранимой процедуры GetDocumentSummary и извлечения сводки документа, которая сохраняется как тип больших значений.The ADO.NET code creates SqlConnection and SqlCommand objects to execute the GetDocumentSummary stored procedure and retrieve the document summary, which is stored as a large value type. Код передает значение входному параметру @DocumentID и отображает результаты, переданные обратно в выходной параметр @DocumentSummary, в окне консоли.The code passes a value for the @DocumentID input parameter, and displays the results passed back in the @DocumentSummary output parameter in the Console window.

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

Дальнейшие действияNext steps