SQL Server 2008中使用FileStream存取大文件的相关知识是本文我们主要要介绍的内容，我们知道，SQL Server 2008中引入了Filestream，使用它可以将非机构化大型数据（如文本文档、图像和视频）等以varbinary(max)的形式存储在文件系统中。使用数据库的备份还原功能可以将这些数据一起备份还原。本文将简单总结如何创建可以使用FileStream的数据库以及如何使用c#访问存取数据。
1. 创建数据库
创建数据时可以执行以下T-SQL语句，
CREATE DATABASE TestDB ON PRIMARY ( NAME = TestDB, FILENAME = 'c:dataTestDB.mdf'), FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3, FILENAME = 'c:datafilestream1') LOG ON ( NAME = TestDBlog, FILENAME = 'c:dataTestDBlog.ldf') GO
假如是在已经创建好的数据库上启用FileStream，可以
a. Right click the “TestDB” database and select “Properties”. b. First create a FileGroup, click “Filegroups” menu and create one under “Stream” section, named “FileStreamGroup1”. And then click “Files” menu, and add a file named “filestream1” and Set it’s FileGroup to “FileStreamGroup1”, and then specify a folder to hold data, like c:Data. Click ok, and you can check the folder “c:data” to see what is added.
2. 创建表
CREATE TABLE [dbo].[TestTable2]( [ID] [uniqueidentifier] ROWGUIDCOL NOT NULL, [ContentType] [nvarchar](50) NOT NULL, [FileName] [nvarchar](50) NOT NULL, [FileContent] [varbinary](max) FILESTREAM NULL, [FileSize] [int] NULL, CONSTRAINT [PK_TestTable2] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] FILESTREAM_ON [FileStreamGroup1] ) ON [PRIMARY] FILESTREAM_ON [FileStreamGroup1] GO SET ANSI_PADDING OFF GO
假如表已经创建，可以执行以下TSQL来设置：
ALTER TABLE [dbo].[TestTable] alter column ID [uniqueidentifier] not null ALTER TABLE [dbo].[TestTable] alter column ID add ROWGUIDCOL ALTER TABLE [dbo].[TestTable] add FileContent varbinary(MAX) FILESTREAM;
3. 使用C#读写
读：
SqlConnection conn = null;conn = new SqlConnection(connect); conn.Open(); tx = conn.BeginTransaction();var qry = "SELECT FileName, FileContent.PathName() as FilePath, ContentType, GET_FILESTREAM_TRANSACTION_CONTEXT() as TranContext FROM TestTable2 WHERE ID = @ID"; var cmd = new SqlCommand(qry, conn, tx); cmd.Parameters.AddWithValue("@ID", id);using (rdr = cmd.ExecuteReader()) { if (rdr.HasRows) { rdr.Read(); fileModel = new FileModel(); fileModel.FileName = Convert.ToString(rdr["FileName"]); fileModel.FilePath = Convert.ToString(rdr["FilePath"]); byte[] tranContext = (byte[])rdr["TranContext"]; fileModel.ContentType = Convert.ToString(rdr["ContentType"]); ; fileModel.FileStream = new SqlFileStream(fileModel.FilePath, tranContext, FileAccess.Read)； } }
得到文件的Stream，就可以对该文件进行读写操作了。
写：
public static void PostFileToDB(HttpPostedFileBase file) { string fileName = Path.GetFileName(file.FileName); string contentType = file.ContentType; int filefileSize = file.ContentLength / 1024; using (SqlConnection conn = new SqlConnection(connect)) { conn.Open(); using (SqlTransaction trn = conn.BeginTransaction ()) { SqlCommand cmdInsert = new SqlCommand( @"insert into TestTable2 (FileName, FileContent, ContentType, FileSize) output INSERTED.FileContent.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT () values (@FileName, 0x, @ContentType, @FileSize)", conn, trn); cmdInsert.Parameters.Add("@FileName", SqlDbType.VarChar, 256); cmdInsert.Parameters["@FileName"].Value = fileName; cmdInsert.Parameters.Add("@ContentType", SqlDbType.VarChar, 256); cmdInsert.Parameters["@ContentType"].Value = contentType; cmdInsert.Parameters.Add("@FileSize", SqlDbType.Int); cmdInsert.Parameters["@FileSize"].Value = fileSize; string path = null; byte[] context = null; // cmdInsert is an INSERT command that uses the OUTPUT clause // Thus we use the ExecuteReader to get the // result set from the output columns // using (SqlDataReader rdr = cmdInsert.ExecuteReader()) { rdr.Read(); path = rdr.GetString(0); context = rdr.GetSqlBytes(1).Buffer; } using (SqlFileStream sfs = new SqlFileStream( path, context, FileAccess.Write)) { file.InputStream.CopyTo(sfs); } trn.Commit (); } } }
关于SQL Server 2008中使用FileStream存取大文件实例就介绍到这里了，希望本次的介绍能够对您有所收获！