Использование функции UPDATETEXT с двоичными данными (ADO.NET)
В версиях SQL Server до SQL Server 2005 возможности работы с большими двоичными объектами (BLOB) были ограниченными. Запись объектов BLOB в базу данных производится путем вставки или обновления поля со строковым значением или массивом байтов, в зависимости от его типа. Однако данные типа BLOB могут иметь весьма большую длину, что может привести к расходованию значительного объема системной памяти при их записи в виде единственного значения, следствием чего становится уменьшение производительности приложения.
Общепринятым способом уменьшения объема памяти, используемого при записи значения BLOB, является запись BLOB в базу данных в виде «фрагментов». Осуществляемый при этом процесс записи BLOB в базу данных зависит от возможностей конкретной базы данных.
Пример использования инструкции Transact-SQL UPDATETEXT
В следующем образце показано, как записать BLOB в виде фрагментов в базу данных SQL Server. В этом образце добавляется новая запись в таблицу Employees базы данных Northwind, включая изображение сотрудника, которое представлено в виде BLOB. В образце используется функция UPDATETEXT, предусмотренная в SQL Server, для записи изображения вновь добавленного сотрудника в поле Photo в виде фрагментов указанного размера.
Для функции UPDATETEXT требуется указатель на обновляемое поле BLOB. В этом образце при добавлении новой записи сотрудника вызывается функция TEXTPTR, предусмотренная в SQL Server, для возврата указателя на поле Photo новой записи. Возвращенное значение указателя передается обратно в виде выходного параметра. В коде в этом образце сохраняется значение указателя и передается в функцию UPDATETEXT при добавлении фрагментов данных.
Код Transact-SQL, используемый для вставки новой записи сотрудника и сохранения указателя на поле Photo, показан в следующем примере (в котором @Identity и @Pointer обозначены как выходные параметры для SqlCommand).
INSERT INTO Employees (LastName, FirstName, Title, HireDate, ReportsTo, Photo)
Values(@LastName, @FirstName, @Title, @HireDate, @ReportsTo, 0x0)
SELECT @Identity = SCOPE_IDENTITY()
SELECT @Pointer = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = @Identity
Следует отметить, что в поле Photo вставлено начальное значение 0x0 (значение NULL). Благодаря этому гарантируется получение значения указателя для поля Photo вновь вставленной записи. Но применение этого значения NULL не оказывает влияния на добавленные фрагменты данных.
Указатель на поле Photo сохраняется во вновь вставленной записи, поэтому в рассматриваемом образце кода обеспечивается возможность после этого добавлять фрагменты данных к полю BLOB с использованием функции UPDATETEXT, предусмотренной в SQL Server. Функция UPDATETEXT принимает в качестве входных параметров идентификатор поля (Employees.Photo), указатель на поле BLOB, величину смещения, которая представляет то местоположение в поле BLOB, где должен быть записан текущий фрагмент и добавляемый фрагмент данных. В следующем примере кода показан синтаксис функции UPDATETEXT (где @Pointer, @Offset, и @Bytes обозначены как входные параметры для SqlCommand).
UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes
Величина смещения определяется размером буфера в памяти, который устанавливается с учетом требований конкретного приложения. Чем больше размер буфера, тем быстрее происходит запись BLOB, но вместе с тем увеличивается потребление системной памяти. В этом образце используется буфер относительно небольшого размера, равный 128 байт. Величина смещения начинается с 0, для первого фрагмента данных, и увеличивается на размер буфера для каждого последовательного фрагмента.
Пример обновления ADO.NET
В данном примере кода выполняется извлечение фотографии сотрудника из файла с указанным путем в виде фрагментов. Каждый фрагмент считывается в байтовый массив, соответствующий заданному размеру буфера. Затем этот байтовый массив задается как значение входного параметра @Bytes функции SqlCommand. Значение параметра @Offset обновляется и вызывается на выполнение функция SqlCommand, который добавляет текущий байтовый фрагмент к полю Photo записи сотрудника.
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Public Class EmployeeData
Public Shared Sub Main()
Dim hireDate As DateTime = DateTime.Parse("4/27/98")
Dim newID As Integer = _
AddEmployee("Smith", "John", "Sales Representative", hireDate, 5, "smith.bmp")
Console.WriteLine("New Employee added. EmployeeID = " & newID)
End Sub
Public Shared Function AddEmployee(ByVal lastName As String, _
ByVal firstName As String, ByVal title As String, ByVal hireDate As DateTime, _
ByVal reportsTo As Integer, ByVal photoFilePath As String) As Integer
Using connection As SqlConnection = New SqlConnection( _
"Data Source=(local);Integrated Security=true;Initial Catalog=Northwind;")
Dim addEmp As SqlCommand = New SqlCommand( _
"INSERT INTO Employees (LastName, FirstName, Title, HireDate, ReportsTo, Photo) " & _
"Values(@LastName, @FirstName, @Title, @HireDate, @ReportsTo, 0x0);" & _
"SELECT @Identity = SCOPE_IDENTITY();" & _
"SELECT @Pointer = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = @Identity", _
connection)
addEmp.Parameters.Add("@LastName", SqlDbType.NVarChar, 20).Value = lastName
addEmp.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10).Value = firstName
addEmp.Parameters.Add("@Title", SqlDbType.NVarChar, 30).Value = title
addEmp.Parameters.Add("@HireDate", SqlDbType.DateTime).Value = hireDate
addEmp.Parameters.Add("@ReportsTo", SqlDbType.Int).Value = reportsTo
Dim idParm As SqlParameter = addEmp.Parameters.Add("@Identity", SqlDbType.Int)
idParm.Direction = ParameterDirection.Output
Dim ptrParm As SqlParameter = addEmp.Parameters.Add("@Pointer", SqlDbType.Binary, 16)
ptrParm.Direction = ParameterDirection.Output
connection.Open()
addEmp.ExecuteNonQuery()
Dim newEmpID As Integer = CType(idParm.Value, Integer)
StorePhoto(photoFilePath, ptrParm.Value, connection)
Return newEmpID
End Using
End Function
Public Shared Sub StorePhoto(ByVal fileName As String, _
ByVal pointer As Byte(), ByVal connection As SqlConnection)
' The size of the "chunks" of the image.
Dim bufferLen As Integer = 128
Dim appendToPhoto As SqlCommand = New SqlCommand( _
"UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes", _
connection)
Dim ptrParm As SqlParameter = appendToPhoto.Parameters.Add( _
"@Pointer", SqlDbType.Binary, 16)
ptrParm.Value = pointer
Dim photoParm As SqlParameter = appendToPhoto.Parameters.Add( _
"@Bytes", SqlDbType.Image, bufferLen)
Dim offsetParm As SqlParameter = appendToPhoto.Parameters.Add( _
"@Offset", SqlDbType.Int)
offsetParm.Value = 0
'' Read the image in and write it to the database 128 (bufferLen) bytes
'' at a time. Tune bufferLen for best performance. Larger values
'' write faster, but use more system resources.
Dim fs As FileStream = New FileStream(fileName, FileMode.Open, FileAccess.Read)
Dim br As BinaryReader = New BinaryReader(fs)
Dim buffer() As Byte = br.ReadBytes(bufferLen)
Dim offset_ctr As Integer = 0
Do While buffer.Length > 0
photoParm.Value = buffer
appendToPhoto.ExecuteNonQuery()
offset_ctr += bufferLen
offsetParm.Value = offset_ctr
buffer = br.ReadBytes(bufferLen)
Loop
br.Close()
fs.Close()
End Sub
End Class
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
public class EmployeeData
{
public static void Main()
{
DateTime hireDate = DateTime.Parse("4/27/98");
int newID = AddEmployee("Smith", "John", "Sales Representative",
hireDate, 5, "smith.bmp");
Console.WriteLine("New Employee added. EmployeeID = " + newID);
}
public static int AddEmployee(string lastName, string firstName,
string title, DateTime hireDate, int reportsTo, string photoFilePath)
{
using (SqlConnection connection = new SqlConnection(
"Data Source=(local);Integrated Security=true;Initial Catalog=Northwind;"))
{
SqlCommand addEmp = new SqlCommand(
"INSERT INTO Employees (LastName, FirstName, Title, HireDate, ReportsTo, Photo) " +
"Values(@LastName, @FirstName, @Title, @HireDate, @ReportsTo, 0x0);" +
"SELECT @Identity = SCOPE_IDENTITY();" +
"SELECT @Pointer = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = @Identity",
connection);
addEmp.Parameters.Add("@LastName", SqlDbType.NVarChar, 20).Value = lastName;
addEmp.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10).Value = firstName;
addEmp.Parameters.Add("@Title", SqlDbType.NVarChar, 30).Value = title;
addEmp.Parameters.Add("@HireDate", SqlDbType.DateTime).Value = hireDate;
addEmp.Parameters.Add("@ReportsTo", SqlDbType.Int).Value = reportsTo;
SqlParameter idParm = addEmp.Parameters.Add("@Identity", SqlDbType.Int);
idParm.Direction = ParameterDirection.Output;
SqlParameter ptrParm = addEmp.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
ptrParm.Direction = ParameterDirection.Output;
connection.Open();
addEmp.ExecuteNonQuery();
int newEmpID = (int)idParm.Value;
StorePhoto(photoFilePath, (byte[])ptrParm.Value, connection);
return newEmpID;
}
}
public static void StorePhoto(string fileName, byte[] pointer,
SqlConnection connection)
{
// The size of the "chunks" of the image.
int bufferLen = 128;
SqlCommand appendToPhoto = new SqlCommand(
"UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes",
connection);
SqlParameter ptrParm = appendToPhoto.Parameters.Add(
"@Pointer", SqlDbType.Binary, 16);
ptrParm.Value = pointer;
SqlParameter photoParm = appendToPhoto.Parameters.Add(
"@Bytes", SqlDbType.Image, bufferLen);
SqlParameter offsetParm = appendToPhoto.Parameters.Add(
"@Offset", SqlDbType.Int);
offsetParm.Value = 0;
// Read the image in and write it to the database 128 (bufferLen) bytes at a time.
// Tune bufferLen for best performance. Larger values write faster, but
// use more system resources.
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);
byte[] buffer = br.ReadBytes(bufferLen);
int offset_ctr = 0;
while (buffer.Length > 0)
{
photoParm.Value = buffer;
appendToPhoto.ExecuteNonQuery();
offset_ctr += bufferLen;
offsetParm.Value = offset_ctr;
buffer = br.ReadBytes(bufferLen);
}
br.Close();
fs.Close();
}
}
См. также
Основные понятия
Сопоставления типов данных SQL Server (ADO.NET)
Другие ресурсы
Двоичные данные и данные большого размера SQL Server (ADO.NET)