در انتهای قسمت قبل، نحوه‌ی ایجاد یک جدول جدید با فیلدی از نوع فایل استریم بررسی شد، حال اگر جدولی از پیش وجود داشت، نحوه‌ی افزودن فیلد ویژه مورد نظر به آن، به صورت زیر است:
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
