ADO.NET 二進位資料存取 (SQL Server image 型態)

如果要使用 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