章立民研究室

如何讀取與寫入資料庫欄位中的二進位影像資料是大家非常喜歡討論的一項課題，為了順應廣大讀者的要求，我們特別撰寫了能夠讀取與寫入二進位影像資料的CLR預存程序，以下我們將討論相關的設計與使用技巧。 

存取二進位影像的 CLR 預存程序位於 ReadWriteBLOB 類別庫專案中，最主要的公用靜態方法 ReadBlobFromDbColumn 與 WriteBlobIntoDbColumn 分別代表讀取與寫入二進位資料的CLR預存程序，其用途說明如下： 

q  CLR 預存程序 ReadBlobFromDbColumn 負責將欄位中的二進位影像資料寫出成一個 JPEG 圖檔。程序會根據第一個參數所指定的身份證字號搜尋「章立民研究室」資料表之資料記錄，然後將該筆資料記錄之「玉照」欄位中的二進位影像資料寫出成一個與身份證字號同名的圖檔（副檔名為 .jpg），並且儲存至第二個參數所指定的目錄中，程式碼如下所列：

[Microsoft.SqlServer.Server.SqlProcedure()]
public static void ReadBlobFromDbColumn(string Id, string currentDirectory)
{
 using (SqlConnection connection =
   new SqlConnection("context connection = true"))
 {
  SqlCommand command = connection.CreateCommand();
 
  command.CommandText = "SELECT 玉照 FROM 章立民研究室 "
    + "WHERE 身份證字號 = @Id";
  command.Parameters.Add("@Id", SqlDbType.NVarChar);
  command.Parameters[0].Value = Id;
 
  // BLOB 緩衝區的大小。
  int bufferSize = 100;
 
  // BLOB byte() 緩衝區會使用 GetBytes 來填入。
  byte[] outByte = new byte[bufferSize];
 
  // GetBytes 所傳回的位元組。
  long retval;
 
  // 位於 BLOB 輸出中的起始位置。
  long startIndex = 0;
 
  // 開啟資料庫連接並將資料讀入資料讀取器中。
  connection.Open();
  using (SqlDataReader reader =
    command.ExecuteReader(CommandBehavior.SequentialAccess))
  {
   if (reader.Read())
   {
    // 此 FileStream 用來將 BLOB 寫至一個檔案（.jpg）。
    using (FileStream stream = new FileStream(
      currentDirectory + Id + ".jpg", FileMode.OpenOrCreate, FileAccess.Write))
    {
     using (BinaryWriter writer = new BinaryWriter(stream))
     {
      // 替一個新的 BLOB 重新設定起始位元組。
      startIndex = 0;
     
      // 將位元組讀入 outByte() 並保有傳回的位元組數目。
      retval = reader.GetBytes(0, startIndex, outByte, 0, bufferSize);
     
      // 只有在緩衝區的大小之後還有位元組就持續迴圈。
      while (retval == bufferSize)
      {
       writer.Write(outByte);
       writer.Flush();
      
       // 將啟始索引的位置設定成最後一次緩衝區的尾端並填入緩衝區。
       startIndex += bufferSize;
       retval = reader.GetBytes(0, startIndex, outByte, 0, bufferSize);
      }
     
      // 寫入剩餘的位元組。
      writer.Write(outByte, 0, Convert.ToInt32(retval));
      writer.Flush();
     }
    }
   }
  }
 }
}

q  CLR 預存程序 WriteBlobIntoDbColumn 負責將圖檔中的二進位影像資料寫回至資料庫欄位中。程序會根據第一個參數所指定的身份證字號搜尋「章立民研究室」資料表之資料記錄，接著利用第二與第三個參數來構建出圖檔的完整路徑以便讀取該圖檔的二進位影像資料，並將其寫回「玉照」欄位中，茲將原始程式碼列示如下：

[Microsoft.SqlServer.Server.SqlProcedure()]
public static void WriteBlobIntoDbColumn(
  string Id, string currentDirectory, string fileName)
{
 string fullFileName = currentDirectory + fileName;
 byte[] bytes = ReadFile(fullFileName);
 
 try
 {
  SqlConnection conn = new SqlConnection("context connection = true");
  conn.Open();
 
  SqlCommand sprocCommand = conn.CreateCommand();
  sprocCommand.CommandText = "dbo.usp_UpdateImage";
  sprocCommand.CommandType = CommandType.StoredProcedure;
 
  sprocCommand.Parameters.Add(new SqlParameter("@Id", SqlDbType.NVarChar));
 
  sprocCommand.Parameters[0].Value = Id;
  sprocCommand.Parameters.Add(
    new SqlParameter("@ThumbNailPhoto", SqlDbType.VarBinary));
  sprocCommand.Parameters[1].Value = bytes;
  sprocCommand.ExecuteNonQuery();
 }
 catch (SqlException e)
 {
  …
 }
}

q  顯然我們用於讀取與寫入二進位影像資料的CLR預存程序涉及到檔案的存取作業，但是請注意，要讓組件的程式碼能夠存取檔案、網路、環境變數、以及登錄（Registry）等特定的外部系統資源，必須將 PERMISSION_SET 設定成 EXTERNAL_ACCESS。然而大家必須瞭解，擁有 UNSAFE 或 EXTERNAL_ACCESS 權限的組件要能夠在SQL Server中登錄並正確運作，您必須已經設定了 TRUSTWORTHY 資料庫屬性或是組件已經使用非對稱金鑰加以簽署，而且對應的登入必須具有 EXTERNAL ACCESS ASSEMBLY 權限。 

在此我們是採用非對稱金鑰的作法，而其整體的安全性設定必須分別在類別庫專案與SQL Server 2005中完成，您可以參考「SQL Server 2005完全實戰」一書的說明，於此不再贅述。 

q  至此，用於讀取與寫入二進位影像資料的 CLR 預存程序還不能使用，因為我們還必須在 SQL Server 中登錄其組件並建立預存程序來參考該組件中的 CLR 預存程序。請您繼續執行程式碼如下所列的SQL指令碼來完成相關作業：

USE 北風貿易;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] =
  N'usp_UpdateImage')
DROP PROCEDURE [dbo].[usp_UpdateImage];
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] =
  N'ReadBlobFromDbColumn')
DROP PROCEDURE [dbo].[ReadBlobFromDbColumn];
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] =
  N'WriteBlobIntoDbColumn')
DROP PROCEDURE [dbo].[WriteBlobIntoDbColumn];
GO

IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] =
  N'ReadWriteBlob')
DROP ASSEMBLY ReadWriteBlob;
GO

-- 登錄組件 ReadWriteBlob.dll 。
CREATE ASSEMBLY ReadWriteBlob
  FROM 'C:SQL2005DemoCH13ReadWriteBLOB
  ReadWriteBLOBbinReadWriteBlob.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

-- 建立一個會參考所登錄之組件ReadWriteBlob.dll中
-- 之ReadBlobFromDbColumn的預存程序。
CREATE PROCEDURE [dbo].[ReadBlobFromDbColumn]
(
 @Id nvarchar(10),
 @CurrentDirectory nvarchar(1024)
)
AS EXTERNAL NAME
  ReadWriteBLOB.[ReadWriteBLOB.ReadWriteBlobClass].
  ReadBlobFromDbColumn;
GO

-- 建立一個會參考所登錄之組件ReadWriteBlob.dll中
-- 之WriteBlobIntoDbColumn的預存程序。
CREATE PROCEDURE [dbo].[WriteBlobIntoDbColumn]
(
 @Id nvarchar(10),
 @CurrentDirectory nvarchar(1024),
 @FileName nvarchar(1024)  
)
AS EXTERNAL NAME
  ReadWriteBLOB.[ReadWriteBLOB.ReadWriteBlobClass].
  WriteBlobIntoDbColumn;
GO

-- 此Transact-SQL預存程序會負責實際的更新作業。
CREATE PROCEDURE [dbo].[usp_UpdateImage]
  @Id nvarchar(10),
  @ThumbNailPhoto AS VARBINARY(max)
AS
BEGIN
  SET NOCOUNT ON;
 
  BEGIN TRY
    UPDATE 章立民研究室
      SET 玉照 = @ThumbNailPhoto
      WHERE 身份證字號 = @Id;
    IF(@@ROWCOUNT < 1)
      RAISERROR ('更新失敗。', 16, 1);
  END TRY
  BEGIN CATCH
    SELECT
      ERROR_NUMBER() AS ErrorNumber,
           ERROR_SEVERITY() AS ErrorSeverity,
           ERROR_STATE() as ErrorState,
           ERROR_PROCEDURE() as ErrorProcedure,
           ERROR_LINE() as ErrorLine,
           ERROR_MESSAGE() as ErrorMessage;
      RAISERROR ('更新失敗。', 16, 1);
  END CATCH;
END;
GO

q  我們現在終於完成了所有的前置準備工作，可以去執行 CLR 預存程序 ReadBlobFromDbColumn 與 WriteBlobIntoDbColumn 來讀取與寫入二進位影像資料。如下所列之 SQL 指令碼示範如何使用這兩個 CLR 預存程序，預存程序執行結果如圖表 1 所示：

USE 北風貿易;
GO

-- 將身份證字號為 'A156401174' 之資料記錄的「玉照」欄位
-- 內容寫出成 C:TempA156401174.jpg
EXEC ReadBlobFromDbColumn 'A156401174', 'C:Temp';
GO

-- 將身份證字號為 'O156405929' 之資料記錄的「玉照」欄位
-- 內容寫出成 C:TempO156405929.jpg
EXEC ReadBlobFromDbColumn 'O156405929', 'C:Temp';
GO

-- 將 C:TempA156401174.jpg 的圖檔內容寫回至身份證字號
-- 為 'A156401174' 之資料記錄的「玉照」欄位
EXEC WriteBlobIntoDbColumn 'A156401174', 'C:Temp', 'A156401174.jpg';
GO

-- 將 C:TempO156405929.jpg 的圖檔內容寫回至身份證字號
-- 為 'O156405929' 之資料記錄的「玉照」欄位
EXEC WriteBlobIntoDbColumn 'O156405929', 'C:Temp', 'O156405929.jpg';
GO

 

 

圖表 1

 

本文節錄並修改自「SQL Server 2005 完全實戰」一書，特此聲明之。

參考書籍：「Visual C# 2005 檔案 IO 與資料存取秘訣」

章立民研究室 2007/1/18