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

Статья
05/20/2011

В версиях SQL Server, выпущенных до SQL Server 2005, работа с типами данных объектов большого размера (LOB) требовала специальной обработки. Типы данных LOB — это данные, размер которых превышает максимальный размер строки в 8 килобайт (КБ). В SQL Server 2005 для типов данных varchar, nvarchar и varbinary появился описатель max, позволяющий хранить значения размером до 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.

SQL Server 2000	SQL Server 2005	SQL Server 2008
Типы данных	Использование типов данных большого размера	Использование типов данных большого размера

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

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

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

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

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

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

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

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

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

UPDATE

{ <объект> }

SET

{имя_столбца = { .WRITE ( выражение , @Offset , @Length ) }

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

If	Then
Выражение устанавливается в значение 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);
}

Пример

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

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

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

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

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

Пример

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

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

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

См. также

Основные понятия

Сопоставления типов данных SQL Server (ADO.NET)

Другие ресурсы

Двоичные данные и данные большого размера SQL Server (ADO.NET)

Операции с данными SQL Server в ADO.NET

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

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

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

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

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

Пример

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

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

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

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

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

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

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

Пример

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

Пример

См. также

Основные понятия

Другие ресурсы

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