如何将二进制数据存储和提取到文件流列中
描述了如何将二进制数据存储到文件流列中，并与 varbinary 列进行比较。
引言
文件流在 SQL Server 2008 中被引入。它们提供了将二进制数据存储到数据库的能力，但存储位置在正常的数据库文件之外。在此之前，varbinary 类型的数据会存储在数据库文件中，这会带来许多副作用。由于 SQL Server 以块（block）为单位存储数据，这些块又由区（extent）组成，因此早期的 varbinary 列中的数据必须符合块结构（尽管与普通数据块略有不同）。
在 SQL Server 2008 中，如果一个 varbinary 列被定义为存储为文件流，那么二进制数据将存储在一个特殊的文件夹结构中，该结构由 SQL Server 引擎管理。留在数据库中的唯一内容是指向文件的指针，以及一个强制性的 GUID 列，以便从 Win32 客户端使用文件流。
可以使用传统的 SqlParameter 通过 .NET Framework 使用文件流数据，但还有一个专门的类叫做 SqlFileStream，可以与 .NET Framework 3.5 SP1 或更高版本一起使用。这个类提供了机制，例如，可以定位到数据中的特定位置。
将二进制数据存储到数据库的优点
一个常见的问题是：为什么要将二进制数据存储到数据库中？一个非常常见的解决方案是将实际数据存储在文件系统中，而只在数据库中定义一个指向实际文件的路径或 URL。然而，有几个问题需要考虑。
- 备份：当数据独立于数据库存储时，它不会被 SQL Server 备份。如果需要备份这些文件，必须创建单独的机制。这也意味着这两个备份“永远”不会同步。例如，当 SQL Server 备份运行时，文件可能已被删除，但实际文件尚未备份。当数据存储在数据库中时，备份是一致的。
- 事务性：当文件存储在数据库外部时，文件的创建、修改和删除不是数据库事务的一部分。这意味着提交或回滚都不能保证结果的一致性。当数据存储在数据库内部时，它是事务的一部分。因此，例如，回滚将包含所有传统的数据库操作以及二进制数据操作。这通常能使客户端解决方案更加健壮，代码更少。
设置数据库
本节介绍如何创建一个能够处理文件流的测试数据库。该数据库包含两个表，用于比较传统的 varbinary 列和文件流。这些脚本在示例项目中的 DatabaseCreationScript.txt 文件中提供。注意：在使用脚本之前，请修改数据文件路径以适应您的环境。
另外，请记住，必须在 SQL Server 设置中启用文件流才能使用此功能。
数据库创建
--------------------------------------------------------------------------
-- Create the database
--------------------------------------------------------------------------
CREATE DATABASE [SqlFileStream] ON PRIMARY
( NAME = N'SqlFileStream',
FILENAME = N'C:\DATA\SqlFileStream.mdf',
SIZE = 500MB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 100MB ),
FILEGROUP [FileStreamData] CONTAINS FILESTREAM DEFAULT
( NAME = N'SqlFileStream_Data',
FILENAME = N'C:\DATA\FileStreamData\SqlFileStream_Data' )
LOG ON
( NAME = N'SqlFileStream_log',
FILENAME = N'C:\DATA\SqlFileStream_log.ldf',
SIZE = 500MB ,
MAXSIZE = 2048GB ,
FILEGROWTH = 100MB)
GO
前面的脚本创建了一个名为 SqlFileStream 的新数据库。使用文件流存储时，必须指定存储二进制数据的文件夹。此文件夹在 SQL Server 中被表示为一个特殊的、定义为包含文件流的文件组。主文件和日志文件正常定义。
--------------------------------------------------------------------------
-- Create the tables
--------------------------------------------------------------------------
USE [SqlFileStream]
GO
CREATE TABLE [FileStreamTest] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (NEWID()) PRIMARY KEY,
[Name] nvarchar(100) NOT NULL,
[Data] varbinary(max) FILESTREAM NOT NULL
)
GO
CREATE TABLE [VarbinaryTest] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (NEWID()) PRIMARY KEY,
[Name] nvarchar(100) NOT NULL,
[Data] varbinary(max) NOT NULL
)
GO
该脚本创建了两个表。FileStreamTest 表将二进制数据存储到文件流中。为了从 Win32 客户端使用数据，表中必须包含一个 GUID 列。此 GUID 实际上用于标识文件流文件夹中存储的文件。第二个表将二进制数据存储在数据库文件中（在本例中为 SqlFileStream.mdf）。此表由客户端程序用于测试与文件流数据的存储时间。在这种情况下，GUID 列不是必需的，尽管客户端使用它来标识行。
客户端程序
客户端程序是一个简单的 WPF 应用程序。第一个选项卡用于上传数据，有三种不同的方式：
- 使用
SqlParameter将数据加载到传统的varbinary列 - 使用
SqlParameter将数据加载到存储为文件流的varbinary列 - 使用
SqlFileStream将数据加载到存储为文件流的varbinary列
在上传过程中，会测量单个操作，如果使用重复计数重复上传，则会根据单个上传时间计算平均值。第二个选项卡用于下载数据并将其保存回文件，其方式与上传时相同。
该程序由主窗口和一个静态的 DbOperations 类组成，该类负责所有与数据库的通信。
存储数据
DbOperations 类实现了一个名为 StoreFileUsingSqlParameter 的方法，该方法使用 SqlParameter 将数据存储到数据库。代码与使用普通 varbinary 或文件流时相同，因此此决定实际上对客户端没有影响。代码只是创建一个命令并设置参数。之后，命令会在定义的次数内循环执行。
command.CommandText = "INSERT INTO "
+ (tableType == TableType.Traditional ? "VarbinaryTest" : "FileStreamTest")
+ " ([Name], [Data]) VALUES (@Name, @Data)";
command.CommandType = System.Data.CommandType.Text;
parameter = new System.Data.SqlClient.SqlParameter("@Name",
System.Data.SqlDbType.NVarChar, 100);
parameter.Value = file.Substring(file.LastIndexOf('\\') + 1);
command.Parameters.Add(parameter);
parameter = new System.Data.SqlClient.SqlParameter("@Data",
System.Data.SqlDbType.VarBinary);
parameter.Value = System.IO.File.ReadAllBytes(file);
command.Parameters.Add(parameter);
StoreFileUsingSqlFileStream 方法执行相同操作，但这次使用 SqlFileStream。使用 SqlFileStream 时有几个需要注意的地方。
插入新行时，存储 SQL Server 数据的文件的创建应同时进行。然而，如果在 INSERT 语句中省略了包含文件流数据的列，则会被解释为 NULL。在这种情况下，文件不会创建，之后使用该行会更加困难。因此，INSERT 语句会向 varbinary 列添加 (0x)（空数据）。
insertCommand.CommandText =
"INSERT INTO FileStreamTest ([Id], [Name], [Data]) VALUES (@Id, @Name, (0x))";
insertCommand.CommandType = System.Data.CommandType.Text;
由于首先插入行，然后才将二进制数据更新到行中，因此程序必须启动事务并获取稍后在创建 SqlFileStream 实例时将使用的事务上下文。
insertCommand.Transaction = connection.BeginTransaction();
helperCommand.Transaction = insertCommand.Transaction;
helperCommand.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";
transactionContext = helperCommand.ExecuteScalar();
首先将行作为普通行插入，但之后，会从 SQL Server 获取文件的路径。此路径与事务上下文一起用于初始化 SqlFileStream。初始化后，数据以字节数组的形式写入 SqlFileStream。请注意，为了效率，程序中的代码顺序有所不同。
helperCommand.CommandText = "SELECT Data.PathName() FROM FileStreamTest WHERE [Id] = @Id";
parameter = new System.Data.SqlClient.SqlParameter("@Id",
System.Data.SqlDbType.UniqueIdentifier);
helperCommand.Parameters.Add(parameter);
helperCommand.Parameters["@Id"].Value = insertCommand.Parameters["@Id"].Value;
filePathInServer = (string)helperCommand.ExecuteScalar();
sqlFileStream = new System.Data.SqlTypes.SqlFileStream(filePathInServer,
(byte[])transactionContext,
System.IO.FileAccess.Write);
sqlFileStream.Write(fileData, 0, fileData.Length);
sqlFileStream.Close();
使用代码
要使用此代码，您需要安装一个 SQL Server 实例，并在其中创建数据库和表。之后，通过 app.config 配置 SQL Server 实例名称和数据库名称。它看起来会像这样：
...
<applicationSettings>
<TableValuedParameters.Properties.Settings>
<setting name="DataSource" serializeAs="String">
<value>SqlServerMachine\SqlServerInstanceName</value>
</setting>
<setting name="DatabaseName" serializeAs="String">
<value>SqlFileStream</value>
</setting>
</TableValuedParameters.Properties.Settings>
</applicationSettings>
...
下载
下载选项卡用于从用户选择的单个行中获取二进制数据。它使用与上传相同的技术。下载选项卡包含有两个原因：确保数据没有以任何方式被修改，以及研究使用 SqlFileStream 进行的获取操作。
好了，大概就是这样了。下载程序，并随意使用它来根据您的设置调查这些技术的效率，并探索文件流的用法。
历史
- 2009 年 1 月 3 日：创建。
- 2016 年 12 月 11 日：代码转换为 Visual Studio 2015。