آشنايي با قابليت FileStream اس كيوال سرور 2008 - قسمت سوم

در انتهاي قسمت قبل، نحوه‌ي ايجاد يك جدول جديد با فيلدي از نوع فايل استريم بررسي شد، حال اگر جدولي از پيش وجود داشت، نحوه‌ي افزودن فيلد ويژه مورد نظر به آن، به صورت زير است:

alter table tbl_files set(filestream_on ='default')

go
alter table tbl_files
 add

   [systemfile] varbinary(max) filestream  null  ,
   FileId  uniqueidentifier  not null rowguidcol unique default (newid())
go 

در ادامه جدول tblFiles قسمت قبل را در نظر بگيريد:

CREATE TABLE [tblFiles](
 [FileId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
 [Title] [nvarchar](255) NOT NULL,
 [SystemFile] [varbinary](max) FILESTREAM  NULL,
UNIQUE NONCLUSTERED
(
 [FileId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [fsg1]

ALTER TABLE [dbo].[tblFiles] ADD  DEFAULT (newid()) FOR [FileId]
GO

نحوه‌ي افزودن ركوردي جديد به جدول tblFiles :

INSERT INTO [tblFiles]
 (
   [Title],
   [SystemFile]
 )
VALUES
 (
   'file-1',
   CAST('data data data' AS VARBINARY(MAX))
 )

در اينجا سعي كرده‌ايم يك رشته ساده را در فيلدي از نوع فايل استريم ذخيره كنيم كه روش كار به صورت فوق است. از آنجائيكه مقدار پيش فرض FileId را هنگام تعريف جدول به NEWID تنظيم كرده‌ايم، نيازي به ذكر آن نيست و به صورت خودكار محاسبه و ذخيره خواهد شد.
اگر كنجكاو باشيد كه اين فايل اكنون كجا ذخيره شده و نحوه‌ي مديريت آن توسط اس كيوال سرور به چه صورتي است، فقط كافي است به مسيري كه هنگام افزودن گروه فايل‌ها و فايل مربوطه در تنظيمات خواص ديتابيس در قسمت قبل مشخص كرديم، مراجعه كرد (شكل زير).

بديهي است افزودن يك رشته به اين صورت كاربرد عملي ندارد و صرفا جهت يك مثال ارائه شد. در ادامه، نحوه‌ي ثبت محتويات يك فايل را در فيلدي از نوع فايل استريم و سپس خواندن اطلاعات آن‌را از طريق برنامه نويسي بررسي خواهيم كرد:

using System;
using System.IO;
using System.Data.SqlClient;
using System.Data;

namespace FileStreamTest
{
   class CFS
   {
       /// <summary>
       /// افزودن ركورد به جدول حاوي ستوني از نوع فايل استريم
       /// </summary>
       /// <param name="filePath">مسير فايل</param>
       /// <param name="title">عنواني دلخواه</param>
       public static void AddNewRecord(string filePath, string title)
       {
           //آيا فايل وجود دارد؟
           if (!File.Exists(filePath))
               throw new FileNotFoundException(
                   "لطفا مسير فايل معتبري را مشخص نمائيد", filePath);

           //خواندن اطلاعات فايل در آرايه‌اي از بايت‌ها
           byte[] buffer = File.ReadAllBytes(filePath);

           using (SqlConnection objSqlCon = new SqlConnection())
           {
               //todo: كانكشن استرينگ بايد از يك فايل كانفيگ خوانده شود
               objSqlCon.ConnectionString =
                   "Data Source=(local);Initial Catalog=testdb2009;Integrated Security = true";
               objSqlCon.Open();

               //شروع يك تراكنش
               using (SqlTransaction objSqlTran = objSqlCon.BeginTransaction())
               {
                   //ساخت عبارت افزودن پارامتري
                   using (SqlCommand objSqlCmd = new SqlCommand(
                       "INSERT INTO [tblFiles]([Title],[SystemFile]) VALUES(@title , @file)",
                       objSqlCon, objSqlTran))
                   {
                       objSqlCmd.CommandType = CommandType.Text;

                       //تعريف وضعيت پارامترها و مقدار دهي آن‌ها
                       objSqlCmd.Parameters.AddWithValue("@title", title);
                       objSqlCmd.Parameters.AddWithValue("@file", buffer);

                       //اجراي فرامين
                       objSqlCmd.ExecuteNonQuery();
                   }

                   //پايان تراكنش
                   objSqlTran.Commit();
               }
           }
       }
      
       /// <summary>
       /// دريافت اطلاعات فايل ذخيره شده به صورت آرايه‌اي از بايت‌ها
       /// </summary>
       /// <param name="fileId">كليد مورد استفاده</param>
       /// <returns></returns>
       public static byte[] GetDataFromDb(string fileId)
       {
           byte[] data = null;

           using (SqlConnection objConn = new SqlConnection())
           {
               //كوئري اس كيوال پارامتري جهت دريافت محتويات فايل
               string cmdText = "SELECT SystemFile FROM tblFiles WHERE FileId=@id";
               using (SqlCommand objCmd = new SqlCommand(cmdText, objConn))
               {
                   //todo: كانكشن استرينگ بايد از يك فايل كانفيگ خوانده شود
                   objConn.ConnectionString =
                       "Data Source=(local);Initial Catalog=testdb2009;Integrated Security = true";
                   objConn.Open();

                   //تنظيم كردن وضعيت و مقدار پارامتر تعريف شده در كوئري
                   objCmd.Parameters.AddWithValue("@id", fileId);

                   //اجراي فرامين و دريافت فايل
                   using (SqlDataReader objread = objCmd.ExecuteReader())
                   {
                       if (objread != null)
                           if (objread.Read())
                           {
                               if (objread["SystemFile"] != DBNull.Value)
                                   data = (byte[])objread["SystemFile"];
                           }
                   }
               }
           }

           return data;
       }
   }
}

مثالي در مورد روش استفاده از كلاس فوق :

using System.IO;

namespace FileStreamTest
{
   class Program
   {
       static void Main(string[] args)
       {
           CFS.AddNewRecord(@"C:\filest05.PNG", "test1");

           //آي دي ركورد ذخيره شده در ديتابيس براي مثال
           byte[] data = CFS.GetDataFromDb("BB848D45-382C-4D95-BF4E-52C3509407D4");
           if (data != null)
           {
               File.WriteAllBytes(@"C:\tst.PNG", data);
           }
       }
   }
}

روش فوق با روش متداول افزودن يك فايل به ديتابيس اس كيوال سرور هيچ تفاوتي ندارد و اين‌جا هم بدون مشكل كار مي‌كند. اطلاعات نهايي به صورت فايل‌هايي بر روي سيستم كه توسط اس كيوال سرور مديريت خواهند شد و با جدول شما يكپارچه‌اند، ذخيره مي‌شوند.

در روش ديگري كه در اكثر مقالات مرتبط مورد استفاده است، از شيء SqlFileStream كمك گرفته شده و نحوه‌ي انجام آن نيز به صورت زير مي‌باشد.
در ابتدا دو رويه ذخيره شده زير را ايجاد مي‌كنيم:

CREATE PROCEDURE [AddFile](@Title NVARCHAR(255), @filepath VARCHAR(MAX) OUTPUT)
AS
BEGIN
   SET NOCOUNT ON;
  
   DECLARE @ID UNIQUEIDENTIFIER
   SET @ID = NEWID()   
  
   INSERT INTO [tblFiles]
     (
       [FileId],
       [title],
       [SystemFile]
     )
   VALUES
     (
       @ID,
       @Title,
       CAST('' AS VARBINARY(MAX))
     )
  
   SELECT @filepath = SystemFile.PathName()
   FROM   tblFiles
   WHERE  FileId = @ID
END
GO

CREATE PROCEDURE [GetFilePath](@Id VARCHAR(50))
AS
BEGIN
   SET NOCOUNT ON;
  
   SELECT SystemFile.PathName()
   FROM   tblFiles
   WHERE  FileId = @ID
END

در رويه ذخيره شده AddFile ، ابتدا ركوردي بر اساس عنوان دلخواه ورودي با يك فايل خالي ايجاد مي‌شود. سپس مسير سيستمي اين فايل را در آرگومان خروجي filepath قرار مي‌دهيم. SystemFile.PathName از اس كيوال سرور 2008 جهت فيلدهاي فايل استريم به اس كيوال سرور اضافه شده است. از اين مسير در برنامه خود جهت نوشتن بايت‌هاي فايل مورد نظر در آن توسط شيء SqlFileStream استفاده خواهيم كرد.
رويه ذخيره شده GetFilePath نيز تنها مسير سيستمي فايل استريم ذخيره شده را بر مي‌گرداند.
به اين ترتيب كدهاي برنامه به صورت زير تغيير خواهند كرد:

using System.Data.SqlClient;
using System.Data;
using System.Data.SqlTypes;
using System.IO;

namespace FileStreamTest
{
   class CFSqlFileStream
   {
       /// <summary>
       /// افزودن ركورد به جدول حاوي ستوني از نوع فايل استريم
       /// </summary>
       /// <param name="filePath">مسير فايل</param>
       /// <param name="title">عنواني دلخواه</param>
       public static void AddNewRecord(string filePath, string title)
       {
           //آيا فايل وجود دارد؟
           if (!File.Exists(filePath))
               throw new FileNotFoundException(
                   "لطفا مسير فايل معتبري را مشخص نمائيد", filePath);

           //خواندن اطلاعات فايل در آرايه‌اي از بايت‌ها
           byte[] buffer = File.ReadAllBytes(filePath);

           using (SqlConnection objSqlCon = new SqlConnection())
           {
               //todo: كانكشن استرينگ بايد از يك فايل كانفيگ خوانده شود
               objSqlCon.ConnectionString =
                   "Data Source=(local);Initial Catalog=testdb2009;Integrated Security = true";
               objSqlCon.Open();

               //شروع يك تراكنش
               using (SqlTransaction objSqlTran = objSqlCon.BeginTransaction())
               {
                   //استفاده از رويه ذخيره شده افزودن فايل
                   using (SqlCommand objSqlCmd = new SqlCommand(
                               "AddFile", objSqlCon, objSqlTran))
                   {
                       objSqlCmd.CommandType = CommandType.StoredProcedure;

                       //مشخص ساختن وضعيت و مقدار پارامتر عنوان
                       SqlParameter objSqlParam1 = new SqlParameter("@Title", SqlDbType.NVarChar, 255);
                       objSqlParam1.Value = title;

                       //مشخص ساختن پارامتر خروجي رويه ذخيره شده
                       SqlParameter objSqlParamOutput = new SqlParameter("@filepath", SqlDbType.VarChar, -1);
                       objSqlParamOutput.Direction = ParameterDirection.Output;

                       //افزودن پارامترها به شيء كامند
                       objSqlCmd.Parameters.Add(objSqlParam1);
                       objSqlCmd.Parameters.Add(objSqlParamOutput);

                       //اجراي رويه ذخيره شده
                       objSqlCmd.ExecuteNonQuery();

                       //و سپس دريافت خروجي آن
                       string Path = objSqlCmd.Parameters["@filepath"].Value.ToString();

                       //زمينه تراكنش فايل استريم موجود را دريافت كرده و از آن براي نوشتن محتويات فايل استفاده خواهيم كرد
                       //اين مورد نيز يكي از تازه‌هاي اس كيوال سرور 2008 است
                       using (SqlCommand objCmd = new SqlCommand(
                           "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", objSqlCon, objSqlTran))
                       {
                           byte[] objContext = (byte[])objCmd.ExecuteScalar();
                           using (SqlFileStream objSqlFileStream =
                               new SqlFileStream(Path, objContext, FileAccess.Write))
                           {
                               objSqlFileStream.Write(buffer, 0, buffer.Length);
                           }
                       }
                   }

                   objSqlTran.Commit();
               }
           }
       }

       /// <summary>
       /// دريافت اطلاعات فايل ذخيره شده به صورت آرايه‌اي از بايت‌ها
       /// </summary>
       /// <param name="fileId">كليد مورد استفاده</param>
       /// <returns></returns>
       public static byte[] GetDataFromDb(string fileId)
       {
           byte[] buffer = null;

           using (SqlConnection objSqlCon = new SqlConnection())
           {
               //todo: كانكشن استرينگ بايد از يك فايل كانفيگ خوانده شود
               objSqlCon.ConnectionString =
                   "Data Source=(local);Initial Catalog=testdb2009;Integrated Security = true";
               objSqlCon.Open();

               //شروع يك تراكنش
               using (SqlTransaction objSqlTran = objSqlCon.BeginTransaction())
               {
                   //استفاده از رويه ذخيره شده دريافت مسير فايل
                   using (SqlCommand objSqlCmd =
                       new SqlCommand("GetFilePath", objSqlCon, objSqlTran))
                   {
                       objSqlCmd.CommandType = CommandType.StoredProcedure;

                       //مشخص ساختن پارامتر ورودي رويه ذخيره شده و مقدار دهي آن                       
                       SqlParameter objSqlParam1 = new SqlParameter("@ID", SqlDbType.VarChar, 50);
                       objSqlParam1.Value = fileId;
                       objSqlCmd.Parameters.Add(objSqlParam1);

                       //اجراي رويه ذخيره شده و دريافت مسير سيستمي فايل استريم
                       string path = string.Empty;
                       using (SqlDataReader sdr = objSqlCmd.ExecuteReader())
                       {
                           sdr.Read();
                           path = sdr[0].ToString();
                       }
                      
                       //زمينه تراكنش فايل استريم موجود را دريافت كرده و از آن براي خواندن محتويات فايل استفاده خواهيم كرد
                       //اين مورد نيز يكي از تازه‌هاي اس كيوال سرور 2008 است
                       using (SqlCommand objCmd = new SqlCommand(
                           "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", objSqlCon, objSqlTran))
                       {
                           byte[] objContext = (byte[])objCmd.ExecuteScalar();

                           using (SqlFileStream objSqlFileStream =
                               new SqlFileStream(path, objContext, FileAccess.Read))
                           {
                               buffer = new byte[(int)objSqlFileStream.Length];
                               objSqlFileStream.Read(buffer, 0, buffer.Length);
                           }
                       }
                   }

                   objSqlTran.Commit();
               }
           }

           return buffer;
       }
   }
}

در پايان براي تكميل بحث مي‌توان به مقاله‌ي مرجع زير مراجعه كرد:
FILESTREAM Storage in SQL Server 2008