SQL Server 2008的FILESTREAM特性使用之檔案管理

　　SQL Server的FILESTREAM（檔案流）特性簡化了基於檔案的資料（如影像）和關係資料同步的過程。

　　幾乎所有的應用程式都需要某種型別的資料集，至少在檢索某些資料和在使用者介面中顯示時要用到，通常，應用程式會使用到結構化資料和非結構化資料，這樣就引入了極大的挑戰，你不得不在一個事務中建立、更新、刪除和讀取這些完全不同的資料型別，當結構化資料駐留在關聯式資料庫中而非結構化資料卻儲存在檔案系統中時，這個問題尤為嚴重。SQL Server 2008新的FILESTREAM（檔案流）特性可以幫助解決這個問題，它讓你可以將非結構化資料儲存在檔案系統中，但仍然保持了事務的完整性，本文探討FILESTREAM（檔案流）的特性和優點，以及如何運用它幫助你對非結構化資料進行更好地控制。

　　非結構化資料選項

　　在SQL Server 2005中，構建一個既依賴於結構化（關係）資料有依賴於非結構化（無關係）資料時，你有兩個選擇：

　　在資料庫中儲存結構化資料，在一個專用的儲存中儲存非結構化資料，如檔案系統和檔案伺服器，雖然這種方法成本合算，但它引入了額外的複雜度，因為你需要跨關係和非關係系統管理事務的完整性。

　　將結構化資料和非結構化資料都儲存在資料庫中，多年以來，資料庫一直都支援儲存非關係資料，如二進位制大物件，或BLOB，SQL Server稱之為varbinary資料型別，雖然在資料庫中儲存這種資料是很方便的，但成本費用會更高，所需的磁碟空間更多，儲存和檢索時間更長，對應用程式的整體效能也會有負面影響。

　　在SQL Server 2008中，新的FILESTREAM（檔案流）特性和varbinary列配合，你可以在伺服器的檔案系統上儲存真實的資料，但可以在資料庫上下文內管理和訪問，這個特性讓SQL Server不僅可以維護好資料庫內記錄的完整性，也能夠維護好資料庫記錄和外部檔案之間的完整性。因為這個特性是在現有的varbinary(max)資料型別之上實現的，開發人員可以輕易地用上這個特性，不用對應用程式的架構進行改動。

　　什麼時候使用FILESTREAM（檔案流）

　　在下列任一情景下你都可以考慮使用FILESTREAM（檔案流）：

• 當你儲存平均大小不低於1MB的BLOB資料型別時。
• 當你需要更快、只讀訪問來自應用程式的資料時。
• 當你想直接從應用程式的中間層程式碼訪問BLOB時。
• 當你需要為單個資料庫事務在資料庫中儲存非關係資料和關係資料時。

　　啟用FILESTREAM（檔案流）

　　預設情況下，FILESTREAM（檔案流）特性是被禁用了的，因此在使用之前，你必須按照下面的步驟配置伺服器和資料庫例項：

　　1、要啟用伺服器例項上的FILESTREAM（檔案流），開啟SQL Server配置管理器，在SQL Server服務上點選右鍵，然後點選開啟，你會看到一串伺服器，在你想要啟用FILESTREAM（檔案流）的SQL Server例項上點選右鍵，從右鍵選單中選擇“屬性”，切換到FILESTREAM（檔案流）標籤，檢查“為Transact-SQL訪問啟用FILESTREAM（檔案流）”選項，參考圖1 ，你也可以在這個標籤頁為檔案I/O流訪問啟用FILESTREAM（檔案流）。

　　圖1 啟用FILESTREAM（檔案流）-在為資料庫例項配置使用FILESTREAM（檔案流）訪問之前必須為想要的SQL Server例項啟用FILESTREAM（檔案流）

　　2、要為資料庫例項啟用FILESTREAM（檔案流），執行系統儲存過程sp_configure，並設定filestream_access_level引數的值為2，如下：

EXEC sp_configure filestream_access_level, 2     GO     RECONFIGURE     GO

　　filestream_access_level引數有效的值包括：

• 在該例項上禁用FILESTREAM（檔案流），這是預設值。
• 為Transact-SQL訪問啟用FILESTREAM（檔案流）
• 為Transact-SQL和Win32流訪問啟用FILESTREAM（檔案流）[SPAN]

　　完成伺服器和資料庫例項配置後，接下來是建立儲存資料的真實資料庫，因為FILESTREAM（檔案流）是專門為儲存在檔案系統上的二進位制資料建立的，使用CREATE DATABASE語句時，專門建立一個FILEGROUP標記為流：

CREATE DATABASE FILESTREAMExample
ON
PRIMARY ( 
NAME = FILESTREAMExample_Primary,
FILENAME = 
'c:\Projects\DevX\Data\FILESTREAMExample.mdf'),
FILEGROUP FILESTREAMGroup CONTAINS  FILESTREAM ( 
NAME = FILESTREAMExample_FileGroup,
FILENAME = 'c:\Projects\DevX\Data\FILESTREAMExample')
LOG ON ( NAME = FILESTREAMExample_Log,
FILENAME =  
'c:\Projects\DevX\Data\FILESTREAMExample.ldf')
GO

　　接下來，建立一個表，將它的一個列指派為VARBINARY(MAX) FILESTREAM資料型別：

CREATE TABLE Product
(
ProductID INT  NOT NULL  PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Picture VARBINARY(MAX) FILESTREAM  NULL,
RowGuid UNIQUEIDENTIFIER  NOT NULL  ROWGUIDCOL
UNIQUE DEFAULT NEWID()
)
GO

　　前面的表定義指定Picture列為varbinary(max)型別，並啟用了FILESTREAM（檔案流）屬性，注意：凡是有FILESTREAM（檔案流）列的表必須要包括一個非空唯一性ROWGUID列。

　　所有儲存在Picture列中的二進位制資料都不能通過檔案系統訪問，訪問這個二進位制資料的唯一方法是通過標準的CRUD (INSERT，UPDATE和 DELETE)SQL語句，下面的例子是向Product表中插入一行資料：

INSERT INTO Product VALUES(1, 'Bicycle', 0x00, default)
GO

　　插入的新行ProductID等於1，Name包括Bicycle，Picture列為NULL，RowGuid列包括一些預設的GUID值，現在你可以在.NET程式中檢索這一行，當然也可以覆蓋和擴充套件它的內容。

　　使用FILESTREAM（檔案流）寫入資料

　　在這個例子中，假設使用者產生了一些輸入，要將這些輸入內容轉換成位元組陣列，並將其儲存在Product表的Picture列中，接下來建立一個Visual C#視窗應用程式，命名為FileStreamExample，在新專案的預設表單上，新增一個按鈕，命名為btnWriteFile，一個名叫txtInput的文字輸入框（TextBox），一個命名為lstResults的列表框（ListBox），然後，在按鈕上雙擊建立一個click事件處理器，包括清單1中的程式碼。

　　清單1 使用FILESTREAM儲存資料

　　private void btnWriteFile_Click(object sender, EventArgs e)
  {
   string connectionString = 
      ConfigurationManager.ConnectionStrings
    ["fileStreamDB"].ConnectionString;
   using (SqlConnection connection = new
     SqlConnection(connectionString))
   {
    connection.Open();
    SqlCommand command = new SqlCommand();
    command.Connection = connection;
    //Get the PathName of the File from the database
    command.CommandText = "SELECT Picture.PathName(), "   +
        "GET_FILESTREAM_TRANSACTION_CONTEXT() FROM Product " +
     "WHERE ProductID = 1";
    SqlTransaction transaction = connection.BeginTransaction
     (IsolationLevel.ReadCommitted);
    command.Transaction = transaction;
    using (SqlDataReader reader = command.ExecuteReader())
    {
     while (reader.Read())
     {
      string path = reader.GetString(0);
      SqlFileStream stream = new SqlFileStream(path,
       (byte[])reader.GetValue(1), FileAccess.Write,
       FileOptions.SequentialScan, 0);
      string contents = txtInput.Text;
      stream.Write((System.Text.Encoding.ASCII.GetBytes(contents)),
        0, contents.Length);
      stream.Close();
     }
    }
    transaction.Commit();
   }
   MessageBox.Show("File contents successfully written");
  }

　　它從app.config使用ConfigurationManager.ConnectionStrings屬性檢索連線字串：

string connectionString =  ConfigurationManager.ConnectionStrings
["fileStreamDB"].ConnectionString;

　　連線字串儲存在app.config中，如下：

＜?xml version="1.0" encoding="utf-8" ?＞
＜configuration＞
＜connectionStrings＞
＜add name="fileStreamDB"   
connectionString="server=localhost\SqlServer2008;
database=FILESTREAMExample;integrated security=SSPI;
persist security info=False;"/＞
＜/connectionStrings＞
＜/configuration＞

　　接下來它開啟一個到資料庫的連線，為SqlCommand物件分配屬性值，然後以ProductID=1為條件檢索Products表：

command.Connection = connection;
//從資料庫獲取檔案的路徑
command.CommandText = "SELECT Picture.PathName(), "
+ "GET_FILESTREAM_TRANSACTION_CONTEXT() FROM Product "
+ "WHERE ProductID = 1";

　　這個SQL語句使用了新的函式GET_FILESTREAM_TRANSACTION_CONTEXT ()檢索當前執行的會話事務，你可以繫結FILESTREAM（檔案流）檔案系統操作到該事務上，但這個事務必須是已經啟動了的，並且也不能被異常終止或提交，當沒有明確啟動的事務可用的，它返回NULL值。

[SPAN]

　　因此，下面的程式碼呼叫SqlConnection.BeginTransaction()方法建立一個新的SqlTransaction物件，並將其分配給SqlCommand物件：

SqlTransaction transaction = 
connection.BeginTransaction(IsolationLevel.ReadCommitted);
command.Transaction = transaction;

　　至此，清單1啟動ExecuteReader()方法執行SQL語句，執行完查詢後，返回檔案流的路徑，並向它分配一個本地變數：

string path = reader.GetString(0);

　　你需要一個流寫入到檔案中，因此，接下來要建立一個SqlFileStream類的例項，提供路徑、事務上下文、檔案訪問目錄、檔案選項一覽表和分配大小：

SqlFileStream stream = new SqlFileStream(path,
(byte[])reader.GetValue(1), FileAccess.Write,
FileOptions.SequentialScan, 0);

　　SqlFileStream類是一個新類（SQL Server 2008中才引入的），它提供了以位元組序列方式訪問FILESTREAM（檔案流）列的方法，表1對SqlFileStream類暴露在外的屬性做了一個簡單的描述。

　　表1 SqlFileStream類屬性

　　接下來清單1開始檢索使用者的輸入，轉換成位元組陣列，然後寫入到檔案流中：

string contents = txtInput.Text;             
stream.Write((System.Text.Encoding.ASCII.GetBytes(contents)), 0, 
contents.Length);
stream.Close();

　　最後，清單1呼叫SqlTransaction.Commit()方法提交事務：

transaction.Commit();

　　以上就是往由資料庫管理的啟用了FILESTREAM（檔案流）特性的檔案的基本過程，既然已經知道如何寫入FILESTREAM列，那從FILESTREAM列讀取就簡單了。

　　使用FILESTREAM讀取資料

　　在C#專案的預設表單上，新增一個按鈕，命名為btnReadFile，在click事件中插入清單2中的程式碼。

　　清單2 使用FILESTREAM讀取資料。這個click事件處理程式從資料庫讀取FILESTREAM列中的內容。

　　為了簡單起見，我只討論與前面不同的程式碼，當你建立一個SqlFileStream時，你需要指出你開啟的檔案流：

SqlFileStream stream = new SqlFileStream(path,
(byte[])reader.GetValue(1),FileAccess.Read,
FileOptions.SequentialScan, 0);

　　接下來，讀取檔案流的內容，轉換成位元組陣列，再轉換成字串，最後在列表框（ListBox）中顯示出來：

int length = (int) stream.Length;
byte[] contents = new byte[length];
stream.Read(contents,0,length);                     
string results = System.Text.Encoding.ASCII.GetString
(contents);
lstResults.Items.Add(results);

　　當你執行這個應用程式時，你會看到一個類似於圖2的螢幕，當你點選“寫入檔案”按鈕時，應用程式把文字框（TextBox）中的內容寫入到檔案流中；當你點選“讀取檔案”按鈕時，應用程式從檔案流讀取內容，將其顯示在列表框（ListBox）中。[SPAN]

　　圖2 示例專案-通過使用SqlFileStream類讀取和寫入FILESTREAM列中的內容

　　接下來的例子顯示如何擴充套件現有資料庫中的檔案流。

　　使用FILESTREAM追加資料

　　增加一個命令按鈕，命名為btnAppendFile，使用清單3中的程式碼作為它的click事件處理程式程式碼。

　　清單3 使用FILESTREAM追加資料

這次當你例項化SqlFileStream物件時，將檔案訪問權設為ReadWrite，因此你可以讀取和寫入檔案流。

SqlFileStream stream = new SqlFileStream(path,
(byte[])reader.GetValue(1), FileAccess.ReadWrite,
FileOptions.SequentialScan, 0);

　　然後移動檔案流的指標到檔案末尾，這樣你就可以追加資料了：

stream.Seek(0, SeekOrigin.End);

　　接下來使用SqlFileStream.Write()方法將使用者輸入的內容寫入到檔案流中：

stream.Write((System.Text.Encoding.ASCII.GetBytes
(contents)), 0, contents.Length);   

　　最後呼叫SqlTransaction.Commit()方法提交事務。

　　FILESTREAM的優點

　　這就是全部過程，現在你可以讀取、寫入和追加資料到資料庫管理的檔案中了，雖然這個過程可能比使用檔案或在BLOB中儲存資料更復雜一些，你會發現使用資料庫來管理檔案由許多好處。

　　◆ 使用單個資料儲存就可以同時訪問非關係和關係資料。

　　◆ 在資料庫備份和還原期間SQL Server自動包括非關係資料（BLOB）。

　　◆ 沒有檔案大小限制，varbinary(max)資料型別最大不能超過2GB，唯一受限的就是NTFS檔案系統上的可用空間。

　　◆ 你可以在單個事務中同時插入、更新和刪除關係資料和非關係資料。

　　◆ 使用FILESTREAM效率更高，因為SQL Server不再使用緩衝區記憶體操作非關係資料（BLOB）。

　　◆ 你可以使用ADO.NET在中間層程式碼直接訪問非關係資料，不用再求值於獨立的API了。

　　◆ 依賴於檔案大小，NTFS檔案系統可以比SQL Server更快地儲存和檢索大型BLOB。

　　本文向你展示瞭如何實現新的FILESTREAM特性，正如你所看到的，當你想在一個事務中同時儲存關係資料和非關係資料時，FILESTREAM提供了一個易於使用的事務程式設計模型。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-622804/，如需轉載，請註明出處，否則將追究法律責任。