使用 UPDATETEXT 搭配二進位資料 (ADO.NET)
更新： November 2007
在 SQL Server 2005 之前的 SQL Server 版本中，使用二進位大型物件 (BLOB) 的選項有所限制。根據資料庫中之欄位型別的不同，您可以利用字串值或位元組陣列插入或更新欄位，將 BLOB 寫入資料庫。不過，BLOB 可能相當大，因此在做為單一值寫入時可能會耗用許多系統記憶體，使得應用程式效能降低。
在寫入 BLOB 值時，為了減少記憶體的使用量，一般的做法是以「區塊」將 BLOB 寫入資料庫。以這種方式將 BLOB 寫入資料庫的過程取決於資料庫的容量。
Transact-SQL UPDATETEXT 範例
下列範例示範如何以區塊將 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 開始，之後以每個連續區塊的緩衝區大小來遞增。
ADO.NET 更新範例
這個範例會從提供的路徑上以區塊擷取員工相片。每個區塊會依照指定的緩衝區大小讀入位元組陣列中。之後，系統會將這個位元組陣列設定為 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(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();
}
}