Sql Server 2008 FileSteam全攻略(三)---使用Win32管理FileStream数据

砂拉豆

可以使用 Win32 在 FILESTREAM BLOB 中读取和写入数据。您需要执行以下步骤：
· 读取 FILESTREAM 文件路径。
· 读取当前事务上下文。
· 获取 Win32 句柄，并使用该句柄在 FILESTREAM BLOB 中读取和写入数据。
为了使用测试代码可用，请清空FileStreamTestDb数据，关于如何创建FileStreamTestDb，请见上一章节:
http://blog.csdn.net/tjvictor/archive/2009/04/30/4139440.aspx
代码：
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SqlClient;using System.Data.SqlTypes;using System.IO;namespace FileStreamTest{class Program{static void Main(string[] args){SqlConnection sqlConnection = new SqlConnection("Integrated Security=true;server=(local)");SqlCommand sqlCommand = new SqlCommand();sqlCommand.Connection = sqlConnection;try{sqlConnection.Open();//The first task is to retrieve the file path//of the SQL FILESTREAM BLOB that we want to//access in the application.sqlCommand.CommandText ="SELECT BlobData.PathName()"+ " FROM FileStreamTestDb.dbo.FileStreamTestTable"+ " where FileName  = 'TestFile'";String filePath = null;Object pathObj = sqlCommand.ExecuteScalar();if (DBNull.Value != pathObj)filePath = (string)pathObj;else{throw new System.Exception("BlobData.PathName() failed"+ " to read the path name "+ " for the Chart column.");}//The next task is to obtain a transaction//context. All FILESTREAM BLOB operations//occur within a transaction context to//maintain data consistency.//All SQL FILESTREAM BLOB access must occur in //a transaction. MARS-enabled connections//have specific rules for batch scoped transactions,//which the Transact-SQL BEGIN TRANSACTION statement//violates. To avoid this issue, client applications //should use appropriate API facilities for transaction management, //management, such as the SqlTransaction class.SqlTransaction transaction = sqlConnection.BeginTransaction("mainTranaction");sqlCommand.Transaction = transaction;sqlCommand.CommandText ="SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";Object obj = sqlCommand.ExecuteScalar();byte[] txContext = (byte[])obj;//The next step is to obtain a handle that//can be passed to the Win32 FILE APIs.SqlFileStream sqlFileStream = new SqlFileStream(filePath, txContext, FileAccess.ReadWrite);byte[] buffer = new byte[512];int numBytes = 0;//Write the string, "EKG data." to the FILESTREAM BLOB.//In your application this string would be replaced with//the binary data that you want to write.string someData = "EKG data.";Encoding unicode = Encoding.GetEncoding(0);sqlFileStream.Write(unicode.GetBytes(someData.ToCharArray()),0,someData.Length);//Read the data from the FILESTREAM//BLOB.sqlFileStream.Seek(0L, SeekOrigin.Begin);numBytes = sqlFileStream.Read(buffer, 0, buffer.Length);string readData = unicode.GetString(buffer);if (numBytes != 0)Console.WriteLine(readData);//Because reading and writing are finished, FILESTREAM //must be closed. This closes the c# FileStream class, //but does not necessarily close the the underlying //FILESTREAM handle. sqlFileStream.Close();//The final step is to commit or roll back the read and write//operations that were performed on the FILESTREAM BLOB.sqlCommand.Transaction.Commit();}catch (System.Exception ex){Console.WriteLine(ex.ToString());}finally{sqlConnection.Close();}return;}}}