在將 BLOB 值寫入 SQL Server 時節約資源
根據資料庫中之欄位型別的不同，您可以利用字串值或位元組陣列插入或更新欄位，將二進位大型物件 (BLOB) 寫入至資料庫 (請參閱將 BLOB 值寫入資料來源)。然而，BLOB 可能相當大，因而在做為單一值寫入時可能會耗用許多系統記憶體，使得應用程式效能降低。
在寫入 BLOB 值時，為了減少記憶體的使用量，一般的做法是以「區塊」將 BLOB 寫入資料庫。以這種方式將 BLOB 寫入資料庫的過程取決於資料庫的容量。
下列範例示範如何以區塊將 BLOB 寫入 SQL Server。該範例會將包含員工影像的新資料錄 (亦即 BLOB) 加入 Northwind 資料庫的 Employees 資料表中。該範例也會使用 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 (null) 的初始值。這樣就一定能擷取到新插入資料錄 Photo 欄位的指標值。然而，Null 值卻不會影響附加的資料區塊。
在保留了新插入資料錄 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 欄位。
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 connection 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", 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)
connection.Close()
Return newEmpID
End Function
Public Shared Sub StorePhoto(fileName As String, pointer As Byte(), connection 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", 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)
{
SqlConnection connection = 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", 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);
connection.Close();
return newEmpID;
}
public static void StorePhoto(string fileName, byte[] pointer, SqlConnection connection)
{
int bufferLen = 128; // The size of the "chunks" of the image.
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();
}
}