将 BLOB 值写入 SQL Server 时保留资源
通过插入或更新带有字符串值或字节数组(取决于数据库中的字段类型)的字段,可以将二进制大对象 (BLOB) 写入数据库(请参见将 BLOB 值写入数据库)。但是,BLOB 可能相当大,因此在作为单个值写入时可能要使用大量的系统内存,从而降低应用程序的性能。
为减少写入 BLOB 值时使用的内存量,通常是将 BLOB 以“块区”的形式写入数据库。以此方法将 BLOB 写入数据库的过程依赖于数据库的功能。
以下示例演示如何将 BLOB 以块区形式写入 SQL Server。该示例向 Northwind 数据库的 Employees 表添加了一个包含员工图像的新记录,该图像就是一个 BLOB。该示例使用 SQL Server 的 UPDATETEXT 函数将新添加的员工的图像以指定大小的块区写入 Photo 字段。
UPDATETEXT 函数要求一个指向所更新的 BLOB 字段的指针。在此示例中,在添加新员工的记录后,将调用 SQL Server TEXTPTR 函数以返回一个指向新记录的 Photo 字段的指针。返回的指针值将作为输出参数传递回去。示例中的代码保留此指针,并在追加数据块区时将其传递到 UPDATETEXT。
用于插入新员工记录和保留指向 Photo 字段的指针的 Transact-SQL 将在下例中显示(其中 @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
(空)。这确保可以检索到新插入记录的 Photo 字段的指针值。但是,空值不会影响追加的数据块区。
在保留指向新插入记录中的 Photo 字段的指针后,示例可以接着使用 SQL Server 的 UPDATETEXT 函数向 BLOB 字段追加数据块区。UPDATETEXT 函数接受以下对象作为输入:字段标识符 (Employees.Photo
)、指向 BLOB 字段的指针、表示 BLOB 中写入当前块区的位置的偏移量值,以及要追加的数据块区。以下代码示例显示 UPDATETEXT 函数的语法(其中 @Pointer
、@Offset
和 @Bytes
被标识为 SqlCommand 的输入参数)。
UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes
偏移量值由内存缓冲区的大小确定,而该大小取决于应用程序的需要。大的缓冲区写入 BLOB 的速度较快,但会使用更多的系统内存。此示例使用的缓冲区相当小,只有 128 字节。为第一个数据块区分配的偏移量值为 0,然后偏移量值按每个连续块区的缓冲区大小递增。
该示例按块区从提供的文件路径中检索员工相片。它根据指定的缓冲区大小,将每个块区读入一个字节数组。然后,将字节数组设置为 SqlCommand 的 @Bytes
输入参数的值。更新 @Offset
参数值并执行 SqlCommand 后,当前的字节块区追加到员工记录的 Photo 字段中。
[Visual Basic] 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(lastName As String, firstName As String, title As String, hireDate As DateTime, _ reportsTo As Integer, photoFilePath As String) As Integer Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;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", nwindConn) 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 nwindConn.Open() addEmp.ExecuteNonQuery() Dim newEmpID As Integer = CType(idParm.Value, Integer) StorePhoto(photoFilePath, ptrParm.Value, nwindConn) nwindConn.Close() Return newEmpID End Function Public Shared Sub StorePhoto(fileName As String, pointer As Byte(), nwindConn As SqlConnection) Dim bufferLen As Integer = 128 ' The size of the "chunks" of the image. Dim appendToPhoto As SqlCommand = New SqlCommand("UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes", nwindConn) 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 [C#] 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) { SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;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", nwindConn); 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; nwindConn.Open(); addEmp.ExecuteNonQuery(); int newEmpID = (int)idParm.Value; StorePhoto(photoFilePath, (byte[])ptrParm.Value, nwindConn); nwindConn.Close(); return newEmpID; } public static void StorePhoto(string fileName, byte[] pointer, SqlConnection nwindConn) { int bufferLen = 128; // The size of the "chunks" of the image. SqlCommand appendToPhoto = new SqlCommand("UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes", nwindConn); 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(); } }