2011年4月19日 星期二

如何使用 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

4 則留言:

  1. 您好 請問一下要在SQL Management Studio下使用image的資料型態的欄位值要放甚麼?是否要設定成 allow null?

    回覆刪除
  2. 你好,請參考我建立表格的語法中有註明,第三個欄位(filesdata)就是,所以欄位形態為varbinary,也要勾選allow null即可。

    [filedata] [varbinary](max) FILESTREAM NULL

    回覆刪除
  3. 你好, 我試著要讀取或寫入時會跳出'存取被拒'的錯誤, 請問該如何解決呢

    回覆刪除
    回覆
    1. 你可以參考下列的文章,嘗試透過 execute as 的方式進行,或是嘗試透過local admin的帳號進行測試。

      GET_FILESTREAM_TRANSACTION_CONTEXT (Transact-SQL)
      http://msdn.microsoft.com/en-us/library/bb934014(v=sql.100).aspx

      刪除