Изменение данных больших значений (max) в 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 в AdventureWorks2022 примере базы данных. При использовании поставщика 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 указан здесь в сокращенной форме.
ОБНОВИТЬ
{ <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, как показано в следующем примере. В коде предполагается, что в примере базы данных существует AdventureWorks2022 следующая хранимая процедура 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";
}
}
Следующие шаги
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по