SQL Server2008新特性Filesteam的使用

在“开始”菜单中，依次指向“所有程序”、“Microsoft SQL Server 2008”和“配置工具”，然后单击“SQL Server 配置管理器”。

在 SQL Server Management Studio 中，单击“新建查询”以显示查询编辑器。

USE master; GO CREATE DATABASE FileStreamTestDb ON PRIMARY (NAME = FileStreamTestDb, FILENAME = 'c:\tmp\FileStreamTestDb.mdf'), FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM (NAME = FileStreamGroup1, FILENAME = 'c:\tmp\filestream1.ndf') LOG ON (NAME = FileStreamTestDblog, FILENAME = 'c:\tmp\FileStreamTestDb_log.ldf') GO -- 注意： 对于此脚本，C:\tmp目录必须存在。

use FileStreamTestDb go CREATE TABLE FileStreamTestTable ( [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, [FileName] NVARCHAR(64) , [BlobData] VARBINARY(MAX) FILESTREAM NULL ) GO

try { OpenFileDialog dialog = new OpenFileDialog(); dialog.Filter = "文件|*.*"; if (dialog.ShowDialog() == DialogResult.OK) { using (SqlConnection conn = new SqlConnection(CONNECTIONSTRING)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = "INSERT INTO FileStreamTestTable(FileName, BlobData) VALUES(@fileName, @file)"; SqlParameter param = new SqlParameter("@fileName", SqlDbType.NVarChar, 64); param.Value = dialog.FileName; cmd.Parameters.Add(param); FileStream fs = new FileStream(dialog.FileName, FileMode.Open); byte[] bytes = new byte[fs.Length]; fs.Read(bytes, 0, bytes.Length); SqlParameter param = new SqlParameter("@file", SqlDbType.VarBinary, 1000000); param.Value = bytes; cmd.Parameters.Add(param); MessageBox.Show(cmd.ExecuteNonQuery() == 1 ? "成功保存文件" : "保存文件失败"); fs.Close(); } conn.Close(); } } else { MessageBox.Show("用户取消了操作"); } catch (Exception ex) { MessageBox.Show(ex.Message); } }

try { using (SqlConnection conn = new SqlConnection(CONNECTIONSTRING)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = "select top 1 BinaryContents from BinaryTable order by ID desc"; SqlDataReader reader = cmd.ExecuteReader(); SaveFileDialog dialog = new SaveFileDialog(); dialog.Filter = "文件(*.*)|*.*"; if (dialog.ShowDialog() == DialogResult.OK) { reader.Read(); System.Data.SqlTypes.SqlBinary result = reader.GetSqlBinary(0);//值得注意的是这里并没有什么GetSqlImage的方法 FileStream fs = new FileStream(dialog.FileName, FileMode.Create); fs.Write(result.Value, 0, result.Length); fs.Close(); reader.Close(); } else { MessageBox.Show("用户取消操作"); } } conn.Close(); } } catch (Exception ex) { MessageBox.Show(ex.Message); }