如何使用 T-SQL 與 ADO.Net 操作 SQL Server 2008 - Filestream的功能(內附原始碼)

        由於上次推出 [啟用與設定SQL Server 2008 新功能 - FileStream] 的介紹後，本篇我再來說明一下如何利用，T-SQL與ADO.NET來使用FileStream的新功能。同樣的我先把上一期介紹過的Table Schema貼上來給大家參考，然後再利用T-SQL的方式來進行介紹。

Table Schema:

CREATE TABLE [dbo].[cary_files](
[sn] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
[systemid] [int] NOT NULL,
[filedata] [varbinary](max) FILESTREAM NULL
) ON [PRIMARY]
GO;


我先利用Insert的指令寫入資料表中，先嘗試利用空值(Null)寫入FileStream的欄位中。

INSERT INTO [cary_db].[dbo].[cary_files]
VALUES (newid (), 1, NULL);

再將空字串('')寫入FileStream的欄位中。

INSERT INTO [cary_db].[dbo].[cary_files]
VALUES (newid (), 2,
CAST ('' as varbinary(max)));

最後再將文字內容('test data')整個寫入到FileStream的欄位中。

INSERT INTO [cary_db].[dbo].[cary_files]
VALUES (newid (), 3,
CAST ('test data' as varbinary(max)));


查詢結果：


SELECT [sn] ,[systemid] ,[filedata]
FROM [cary_db].[dbo].[cary_files]


寫入的資料如果只是純文字的話，可以透過下列的語法進行轉型，即可得知欄位的內容。


SELECT [sn] ,[systemid] ,cast([filedata] as varchar(100)) ac
FROM [cary_db].[dbo].[cary_files]


如果想要針對FileStream的欄位進行修改時，同樣地可以使用Update來進行更新，此步驟主要將原本的 'test data' 更改為 'Cary test data'。

UPDATE [cary_db].[dbo].[cary_files]
SET [filedata] = CAST('Cary test data' as varbinary(max))
WHERE [SerialNumber] = 3;


刪除的話，使用Delete即可。

DELETE [cary_db].[dbo].[cary_files]
WHERE SerialNumber = 1;


 以上的介紹主要透過T-SQL的語法來操作FIleStream，當然你可以包含在一個交易中，確保文件與資料的同步化，因為在以往如果只有文件的路徑存在資料庫，異動分成兩個程序時，如果有一邊失敗，容易造成資訊與文件不同步的情況，所以存放在FileStream算是一個不錯的選擇。

接下來我再透過ADO.Net的方式來操作剛剛建立的FIleStream，另外我再介紹如何將一個圖檔寫入FileStream後，再讀取出來。

範例：

在介紹前先說明一下程式中主要會使用到兩個Function，我先列出在下面進行說明，然後程式會從FileStream的欄位中，讀取出資料，並載入右邊空白的Picture的物件中，而寫入FileStream的程式碼也是大致相同，差別在 FileAccess.Read中要改用FileAccess.Write，然後在寫入資料庫中即可，另外我也有介紹如何利用程式將FileStream中的文字轉出，詳細的部份再請參考程式碼。

• PathName：會將路徑當做 Token 傳給 BLOB。應用程式會使用此 Token 來取得 Win32 控制代碼，並在 BLOB 資料上運作。
• GET_FILESTREAM_TRANSACTION_CONTEXT()：會傳回代表工作階段之目前交易的 Token。應用程式會使用此 Token 將 FILESTREAM 檔案系統資料流作業繫結至此交易。

FileStream to Image：


using (SqlConnection connection = new SqlConnection(ConnectionString))
{
    connection.Open();
    SqlCommand command = new SqlCommand("", connection);

    SqlTransaction tran = connection.BeginTransaction(
       System.Data.IsolationLevel.ReadCommitted);
    command.Transaction = tran;

    command.CommandText =
          " SELECT [filedata].PathName(), "
        + " GET_FILESTREAM_TRANSACTION_CONTEXT () from [cary_db].[dbo].[cary_files]"
        + " WHERE systemid = 2";
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // Get the pointer for the file
            string path = reader.GetString(0);
            byte[] transactionContext = reader.GetSqlBytes(1).Buffer;

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

            pic_target.Image = Image.FromStream(fileStream);

            // Read the contents as bytes and write them to the console                      
            fileStream.Close();
        }
    }
    tran.Commit();
}


FileStream to String：


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

byte[] contents = new byte[fileStream.Length];
fileStream.Read(contents, 0, (int)fileStream.Length);
string results = System.Text.Encoding.ASCII.GetString(contents);


原始下載：
http://blogger-files.googlecode.com/files/TestFormApplication.rar

參考網址：
http://technet.microsoft.com/zh-tw/library/cc645962.aspx
http://msdn.microsoft.com/zh-tw/library/cc716724.aspx#Y700
http://msdn.microsoft.com/zh-tw/library/bb933877(v=SQL.100).aspx