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参数有效的值包括：



◆ 0 在该实例上禁用FILESTREAM（文件流），这是默认值。



◆ 1 为Transact-SQL访问启用FILESTREAM（文件流）



◆ 2 为Transact-SQL和Win32流访问启用FILESTREAM（文件流）



完成服务器和数据库实例配置后，接下来是创建存储数据的真实数据库，因为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存储数据  

privatevoid 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值。

因此，下面的代码调用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列中的内容。

 private void btnReadFile_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.Read,
FileOptions.SequentialScan, 0);                        
lstResults.Items.Clear();
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);
stream.Close();
}
}
transaction.Commit();
}
}        

 为了简单起见，我只讨论与前面不同的代码，当你创建一个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）中。

图2 示例项目-通过使用SqlFileStream类读取和写入FILESTREAM列中的内容

接下来的例子显示如何扩展现有数据库中的文件流。

使用FILESTREAM追加数据

增加一个命令按钮，命名为btnAppendFile，使用清单3中的代码作为它的click事件处理程序代码。

清单3 使用FILESTREAM追加数据

FILESTREAM.  
private void btnAppendFile_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.ReadWrite,
FileOptions.SequentialScan, 0);
stream.Seek(0, SeekOrigin.End);
string contents = txtInput.Text;
stream.Write((System.Text.Encoding.ASCII.GetBytes
(contents)), 0, contents.Length);                        
stream.Close();
}
}
transaction.Commit();
}
MessageBox.Show("File contents successfully appended");

} 

 

 这次当你实例化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提供了一个易于使用的事务编程模型。