如果要使用 ADO.NET 存取 SQL Server 上的 image 欄位，其實只要使用 byte[] 陣列即可:
// 將檔案儲存到資料庫 image 欄位中
string filename = "c:\demo.jpg";
FileStream fs = new FileStream(filename, FileMode.Open);
// 用來儲存檔案的 byte 陣列，檔案有多大，陣列就有多大
byte[] buffer = new byte[fs.Length];
fs.Read(buffer, 0, buffer.Length);
fs.Close();
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "";
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "INSERT Table1(Body) VALUES(@Body)";
cmd.Parameters.Add("@Body", SqlDbType.Image);
cmd.Parameters["@Body"].Value = buffer;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();##ReadMore##
// 從資料表 image 欄位取得資料存檔
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "";
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT Body FROM Table1 WHERE FileID=1";
conn.Open();
object obj = cmd.ExecuteScalar();
conn.Close();
if (obj != null && obj != DBNull.Value)
{
// 用來儲存檔案的 byte 陣列，檔案有多大，陣列就有多大
byte[] buffer = (byte[])obj;
FileStream fs = new FileStream("c:\new.jpg");
fs.Write(buffer, 0, buffer.Length);
fs.Close();
}
可是大家有沒有發現一個問題，那就是，如果檔案非常大，例如 1.5GB，那麼 buffer 陣列就需要 1.5GB ?! 如果是 Web 應用程式，不用幾個使用者連線上來，伺服器大概就掛了吧 ^_^
所以最好將檔案分批次寫入或讀取，例如一次存取 1KB，這樣不管檔案有多大，記憶體的使用量都是固定的。
好了，如果要做到這個效果，我們可以使用 READTEXT 和 WRITETEXT 這兩個 T-SQL 指令，
-- 讀取 Employees 資料表中 Photo 欄位的第 16 個 byte 開始，
-- 共 1024 個 bytes
DECLARE @ptr varbinary(16)
SELECT @ptr = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = 1
READTEXT Employees.Photo @ptr 15 1024
-- 刪除 Employees 資料表中 Photo 欄位的第 16 個 byte 開始，
-- 共 50 個bytes
-- 並且在同一個位置插入二進位資料 0x112233445566778899AABBCCDDEEFF
-- (16 進位字串表示)
DECLARE @ptr varbinary(16)
SELECT @ptr = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = 1
WRITETEXT Employees.Photo @ptr 15 50 0x112233445566778899AABBCCDDEEFF
聖哥寫了兩個副程式，可以直接用來存取 image 欄位:
// 將 byte 陣列轉換為 16 進位字串，例如: 0x112233445566778899AABBCC
public string ByteArrayToHexString(byte[] bytearray, int length)
{
StringBuilder sb = new StringBuilder("0x");
for (int i = 0; i < length; i++)
{
sb.Append(string.Format("{0:x2}", bytearray[i]));
}
return sb.ToString();
}
// 將檔案寫入 image 欄位
public void SaveFileToSqlImage(SqlConnection connection,
string table_name, string column_name,
string filename, string where_condition,
int buffer_size)
{
SqlCommand cmdBody = new SqlCommand();
cmdBody.Connection = connection;
string sql = "DECLARE @ptrval varbinary(16) " +
" SELECT @ptrval = TEXTPTR(" + column_name +
") FROM " + table_name + " WHERE " + where_condition +
" UPDATETEXT " + table_name + "." + column_name +
" @ptrval {0} 0 {1}";
FileStream fs = new FileStream(filename, FileMode.Open);
byte[] buffer = new byte[buffer_size];
int count = fs.Read(buffer, 0, buffer.Length);
int loc = 0;
while (count > 0)
{
cmdBody.CommandText = string.Format(sql, loc,
ByteArrayToHexString(buffer, count));
connection.Open();
cmdBody.ExecuteNonQuery();
connection.Close();
loc += count;
count = fs.Read(buffer, 0, buffer.Length);
}
fs.Close();
}
// 從 image 欄位讀取資料並存檔
public void SaveFileFromSqlImage(SqlConnection connection,
string table_name, string column_name,
string filename, string where_condition,
int buffer_size)
{
SqlCommand cmdGetSize = new SqlCommand(
"SELECT DATALENGTH(" + column_name +
") FROM " + table_name + " WHERE " +
where_condition, connection);
connection.Open();
object obj = cmdGetSize.ExecuteScalar();
connection.Close();
if (obj != null && obj != DBNull.Value)
{
int filesize = (int)obj;
string sql = "DECLARE @ptrval varbinary(16) " +
"SELECT @ptrval = TEXTPTR(" + column_name +
") FROM " + table_name + " WHERE " + where_condition +
" READTEXT " + table_name + "." + column_name +
" @ptrval {0} {1}";
SqlCommand cmdBody = new SqlCommand();
cmdBody.Connection = connection;
int loc = 0;
FileStream fs = new FileStream(filename, FileMode.Create);
while (loc < filesize)
{
int count = (filesize - loc >= buffer_size)
? buffer_size : filesize - loc;
cmdBody.CommandText = string.Format(sql, loc, count);
connection.Open();
byte[] buffer = (byte[])cmdBody.ExecuteScalar();
connection.Close();
fs.Write(buffer, 0, buffer.Length);
loc += count;
}
fs.Close();
}
}
使用方法:
SaveFileToSqlImage(conn, "MyTable", "MyImageColumn",
"c:\filename.ext", "FileID=1", 1024);
SaveFileFromSqlImage(conn, "MyTable", "MyImageColumn",
"c:\filename.ext", "FileID=1", 1024);
ps. 附上 VB.NET 版本:
Function ByteArrayToHexString(ByVal bytearray() As Byte, _
ByVal Length As Integer) As String
Dim sb As New StringBuilder("0x")
For I As Integer = 0 To Length - 1
sb.Append(String.Format("{0:x2}", bytearray(I)))
Next
Return sb.ToString()
End Function
' 將檔案寫入 image 欄位
Sub SaveFileToSqlImage(ByVal Connection As SqlConnection, _
ByVal TableName As String, _
ByVal ColumnName As String, ByVal FileName As String, _
ByVal WhereCondition As String, _
ByVal BufferSize As Integer)
Dim cmdBody As New SqlCommand()
cmdBody.Connection = Connection
Dim sql As String = "DECLARE @ptrval varbinary(16) " +
"SELECT @ptrval = TEXTPTR(" + _
ColumnName + ") FROM " + TableName + " WHERE " + _
WhereCondition + " UPDATETEXT " + TableName + "." + _
ColumnName + " @ptrval {0} 0 {1}"
Dim fs As New FileStream(FileName, FileMode.Open)
Dim buffer(BufferSize - 1) As Byte
Dim count As Integer = fs.Read(buffer, 0, buffer.Length)
Dim loc As Integer = 0
Do While count > 0
cmdBody.CommandText = String.Format(sql, loc, _
ByteArrayToHexString(buffer, count))
Connection.Open()
cmdBody.ExecuteNonQuery()
Connection.Close()
loc += count
count = fs.Read(buffer, 0, buffer.Length)
Loop
fs.Close()
End Sub
' 從 image 欄位讀取資料並存檔
Sub SaveFileFromSqlImage(ByVal Connection As SqlConnection, _
ByVal TableName As String, _
ByVal ColumnName As String, ByVal FileName As String, _
ByVal WhereCondition As String, _
ByVal BufferSize As Integer)
Dim cmdGetSize As New SqlCommand("SELECT DATALENGTH(" + _
ColumnName + ") FROM " + _
TableName + " WHERE " + WhereCondition, Connection)
Connection.Open()
Dim obj As Object = cmdGetSize.ExecuteScalar()
Connection.Close()
If obj IsNot Nothing AndAlso obj IsNot DBNull.Value Then
Dim filesize As Integer = obj
Dim sql As String = "DECLARE @ptrval varbinary(16) " +
"SELECT @ptrval = TEXTPTR(" + _
ColumnName + ") FROM " + TableName + " WHERE " + _
WhereCondition + " READTEXT " + TableName + "." + _
ColumnName + " @ptrval {0} {1}"
Dim cmdBody As New SqlCommand()
cmdBody.Connection = Connection
Dim loc As Integer = 0
Dim fs As New FileStream(FileName, FileMode.Create)
Do While loc < filesize
Dim count As Integer = _
IIf(filesize - loc >= BufferSize, BufferSize, filesize - loc)
cmdBody.CommandText = String.Format(sql, loc, count)
Connection.Open()
Dim buffer() As Byte = cmdBody.ExecuteScalar()
Connection.Close()
fs.Write(buffer, 0, buffer.Length)
loc += count
Loop
fs.Close()
End If
End Sub
##ReadMore##
