如何创建可以使用FileStream的数据库

　　1.创建数据库

　　创建数据时可以执行以下T-SQL语句，

　　CREATEDATABASETestDBONPRIMARY(NAME=TestDB,FILENAME='c:\data\TestDB.mdf'),FILEGROUPFileStreamGroup1CONTAINSFILESTREAM(NAME=Arch4,FILENAME='c:\data\filestream1')LOGON(NAME=TestDBlog,FILENAME='c:\data\TestDBlog.ldf')GO

　　假如是在已经创建好的数据库上启用FileStream，可以

　　a.Rightclickthe“TestDB”databaseandselect“Properties”.b.FirstcreateaFileGroup,click“Filegroups”menuandcreateoneunder“Stream”section,named“FileStreamGroup1”.Andthenclick“Files”menu,andaddafilenamed“filestream1”andSetit’sFileGroupto“FileStreamGroup1”,andthenspecifyafoldertoholddata,likec:\Data.Clickok,andyoucancheckthefolder“c:\data”toseewhatisadded.

　　2.创建表

　　CREATETABLE[dbo].[TestTable2]([ID][uniqueidentifier]ROWGUIDCOLNOTNULL,[ContentType][nvarchar](50)NOTNULL,[FileName][nvarchar](50)NOTNULL,[FileContent][varbinary](max)FILESTREAMNULL,[FileSize][int]NULL,CONSTRAINT[PK_TestTable2]PRIMARYKEYCLUSTERED([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]GOSETANSI_PADDINGOFFGO

　　假如表已经创建，可以执行以下TSQL来设置：

　　ALTERTABLE[dbo].[TestTable]altercolumnID[uniqueidentifier]notnullALTERTABLE[dbo].[TestTable]altercolumnIDaddROWGUIDCOLALTERTABLE[dbo].[TestTable]addFileContentvarbinary(MAX)FILESTREAM;

　　如何使用c#访问存取SQL数据

　　读：

　　SqlConnectionconn=null;conn=newSqlConnection(connect);conn.Open();tx=conn.BeginTransaction();varqry="SELECTFileName,FileContent.PathName()asFilePath,ContentType,GET_FILESTREAM_TRANSACTION_CONTEXT()asTranContextFROMTestTable2WHEREID=@ID";varcmd=newSqlCommand(qry,conn,tx);cmd.Parameters.AddWithValue("@ID",id);using(rdr=cmd.ExecuteReader()){if(rdr.HasRows){rdr.Read();fileModel=newFileModel();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=newSqlFileStream(fileModel.FilePath,tranContext,FileAccess.Read);}}

　　得到文件的Stream，就可以对该文件进行读写操作了。

　　写：

　　publicstaticvoidPostFileToDB(HttpPostedFileBasefile){stringfileName=Path.GetFileName(file.FileName);stringcontentType=file.ContentType;intfilefileSize=file.ContentLength/1024;using(SqlConnectionconn=newSqlConnection(connect)){conn.Open();using(SqlTransactiontrn=conn.BeginTransaction()){SqlCommandcmdInsert=newSqlCommand(@"insertintoTestTable2(FileName,FileContent,ContentType,FileSize)outputINSERTED.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;stringpath=null;byte[]context=null;//cmdInsertisanINSERTcommandthatusestheOUTPUTclause//ThusweusetheExecuteReadertogetthe//resultsetfromtheoutputcolumns//using(SqlDataReaderrdr=cmdInsert.ExecuteReader()){rdr.Read();path=rdr.GetString(0);context=rdr.GetSqlBytes(1).Buffer;}using(SqlFileStreamsfs=newSqlFileStream(path,context,FileAccess.Write)){file.InputStream.CopyTo(sfs);}trn.Commit();}}}