用 SQL Server 的 FileStream 实现新方式数据库存储文件、保存图片

作者：vkvi 来源：ITPOW（原创）日期：2016-10-15

附件，有两种存储方式：一是以二进制存储在数据库中，二是存储在文件夹中。

法一优点是好管理，比如便于权限判断，没权限的不能读取附件，缺点是性能低下；法二是性能好，但不方便权限判断（虽然能够做到，但麻烦些）。

有没有结合二者优点的第三种方法呢？有，这在 SQL Server 2008 开始就已经实现了。

第一步、启用 FileStream

在 SQL Server 配置管理器/Configuration Manager 中，在 SQL Server 服务项中，在 SQL Server 上右键，单击属性，切换到 FILESTREAM 标签，勾上“针对 Transact-SQL 访问启用 FILESTREAM”。如下图：

启用 FileStream

第二步、启用文件流访问级别

打开 SQL Server Management Studio，在连接上右键，单击属性，切换到高级标签，文件流访问级别中选择“已启用 Transact-SQL 访问”。如下图：

第二步、启用文件流访问级别

第三步、重启 SQL Server

回到 SQL Server 配置管理器/Configuration Manager 重启 SQL Server 吧。

第四步、创建文件组

ALTER DATABASE [TestDB]
ADD FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM

如果不用代码，也可在数据库上右键，单击属性，切换到文件组标签，可以看到有“行”、“文件流”两个框，在“文件流”那里添加。

第五步、创建文件

ALTER DATABASE [TestDB]
ADD FILE (NAME = N'FileStreamFile', FILENAME = N'D:\Cftea\FileStreamFile')
TO FILEGROUP [FileStreamGroup]

FILESTREAM 数据文件不能指定 SIZE、MAXSIZE 或 FILEGROWTH，所以我们只指定了 NAME、FILENAME 两个 FILE 参数。

在数据库上右键，单击属性，切换到文件标签，可以看到新建的文件的路径一列是：“D:\Cftea”，而不是：“D:\Cftea\FileStreamFile”，这是正常的，因为 FileStreamFile 是 SQL Server 管理的。

第六步、创建表

CREATE TABLE [dbo].[Files]
(
    FileId UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL PRIMARY KEY,
    FileContent VARBINARY(MAX) FILESTREAM NULL
) FILESTREAM_ON [FileStreamGroup]
ALTER TABLE [dbo].[Files] ADD  CONSTRAINT [DF_Files_FileId]  DEFAULT (newid()) FOR [FileId]

我们只指定了两个必须字段，实际运用时肯定不止这两个字段。这两个字段，一个是 UNIQUEIDENTIFIER ROWGUIDCOL，另一个是 VARBINARY(MAX) FILESTREAM。

另外，为了我们给 FileId 指定了默认值 newid()，这样就不用我们在代码中为这个列指定值了，由数据库自动生成。

第七步、用 SQL 保存一个文件到数据库中

using (SqlConnection conn = new SqlConnection("Data Source=.; Initial Catalog=TestDb; Integrated Security=SSPI;"))
{
    conn.Open();

    // 添加文件
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = conn;

        cmd.CommandText = "INSERT INTO Files(FileContent) VALUES(@FileContent)";
        SqlParameter parameter = new SqlParameter("@FileContent", SqlDbType.VarBinary);
        parameter.Value = File.ReadAllBytes("D:\\CfteaExample.jpg");
        cmd.Parameters.Add(parameter);

        cmd.ExecuteNonQuery();
    }
    
    conn.Close();
}

要注意的是，必须用 Windows 身份认证，不能用 SQL Server 密码，所以连接字符串中用的是：Integrated Security=SSPI;。

保存后，我们可以发现数据库多了一条记录，但真正的文件是存储在 D:\Cftea\FileStreamFile 内部中，我们可以找到一个和 CfteaExample.jpg 一样大小的文件，如果我们把这个文件复制出来，加上扩展名，就可以用图片管理器打开了。

第八步、读取并输出这个图片到网页

using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=TestDb;Integrated Security=SSPI;"))
{
    conn.Open();

    // 读取并输出文件（以 JPG 文件输出到网页为例）
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = conn;

        cmd.CommandText = "SELECT * FROM Files";
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            reader.Read();
            byte[] bytes = new byte[3140134]; // 由于我们是测试，所以我们知道文件的实际大小，实际过程中，我们应该把这个大小记录在字段中
            reader.GetBytes(1, 0, bytes, 0, bytes.Length); // 1 代表第 2 个字段，在我们这里就是 FileContent
            Response.ContentType = "image/jpeg";
            Response.BinaryWrite(bytes);
            reader.Close();
        }
    }
    
    conn.Close();
}

第九步、删除文件

using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=TestDb;Integrated Security=SSPI;"))
{
    conn.Open();

    // 清空附件（为了方便这里不是删除某一附件，而是清空所有附件）。
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = conn;

        cmd.CommandText = "TRUNCATE TABLE Files;CHECKPOINT;"; // TRUNCATE TABLE Files 清空表，CHECKPOINT 检查文件夹中文件如果在表中不存在，则删除文件）

        cmd.ExecuteNonQuery();
    }
    
    conn.Close();
}

CHECKPOINT 很重要，如果漏掉了，就只会删除记录，不会删除文件夹中的文件。

最后

怎么样，要做权限判断（有权限的人能够访问，没权限的人不能访问）就轻松多了吧，而且性能还不错。