为 FILESTREAM 数据创建客户端应用程序
可以使用 Win32 在 FILESTREAM BLOB 中读取和写入数据。 您需要执行以下步骤：
读取 FILESTREAM 文件路径。
读取当前事务上下文。
获取 Win32 句柄，并使用该句柄在 FILESTREAM BLOB 中读取和写入数据。
注意
本主题中的示例需要在 创建启用 FILESTREAM 的数据库 和 创建表以存储 FILESTREAM 数据中创建的启用了 FILESTREAM 的数据库和表。
用于使用 FILESTREAM 数据的函数
使用 FILESTREAM 来存储二进制大型对象 (BLOB) 数据时，可使用 Win32 API 来处理文件。 为了支持在 Win32 应用程序中处理 FILESTREAM BLOB 数据， SQL Server 提供了以下函数和 API：
PathName 可将路径作为标记返回给 BLOB。 应用程序可使用此标记来获取 Win32 句柄并对 BLOB 数据进行操作。
在包含 FILESTREAM 数据的数据库属于某一 AlwaysOn 可用性组时，PathName 函数返回虚拟网络名称 (VNN)，而非计算机名称。
GET_FILESTREAM_TRANSACTION_CONTEXT() 可返回表示会话当前事务的标记。 应用程序使用此标记可将 FILESTREAM 文件系统流式处理操作绑定到该事务。
OpenSqlFilestream API 可获取 Win32 文件句柄。 应用程序使用句柄来流式传输 FILESTREAM 数据，然后可以将句柄传递给以下 Win32 API：ReadFile、WriteFile、TransmitFile、SetFilePointer、SetEndOfFile 或 FlushFileBuffers。 如果应用程序使用此句柄来调用任何其他 API，则会返回 ERROR_ACCESS_DENIED 错误。 应用程序应使用 CloseHandle来关闭此句柄。
所有 FILESTREAM 数据容器访问都是在 SQL Server 事务中执行的。 可以在同一事务中执行 Transact-SQL 语句，以保持 SQL 数据和 FILESTREAM 数据之间的一致性。
访问 FILESTREAM 数据的步骤
读取 FILESTREAM 文件路径
FILESTREAM 表中的每个单元都具有关联的文件路径。 若要读取路径，请在 PathName Transact-SQL 语句中使用列的 varbinary(max) 属性。 下面的示例说明了如何读取 varbinary(max) 列的文件路径。
DECLARE @filePath varchar(max)
SELECT @filePath = Chart.PathName()
FROM Archive.dbo.Records
WHERE SerialNumber = 3
PRINT @filepath
读取事务上下文
若要获取当前事务上下文，请使用 Transact-SQL GET_FILESTREAM_TRANSACTION_CONTEXT () 函数。 下面的示例说明了如何开始执行事务并读取当前事务上下文。
DECLARE @txContext varbinary(max)
BEGIN TRANSACTION
SELECT @txContext = GET_FILESTREAM_TRANSACTION_CONTEXT()
PRINT @txContext
COMMIT
获取 Win32 文件句柄
若要获取 Win32 文件句柄，请调用 OpenSqlFilestream API。 此 API 是从 sqlncli.dll 文件中导出的。 返回的句柄可以传递给以下任何 Win32 API：ReadFile、WriteFile、TransmitFile、SetFilePointer、SetEndOfFile 或 FlushFileBuffers。 下面的示例说明了如何获取 Win32 文件句柄并使用它在 FILESTREAM BLOB 中读取和写入数据。
using System.IO;
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
namespace FILESTREAM
{
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 Chart.PathName()"
+ " FROM Archive.dbo.Records"
+ " WHERE SerialNumber = 3";
String filePath = null;
Object pathObj = sqlCommand.ExecuteScalar();
if (DBNull.Value != pathObj)
filePath = (string)pathObj;
else
{
throw new System.Exception(
"Chart.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 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;
}
}
}
Imports System.IO
Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Module Module1
Public Sub Main(ByVal args As String())
' Dim sqlConnection As New SqlConnection("Integrated Security=true;server=(local)")
Dim sqlConnection As New SqlConnection("Integrated Security=true;server=kellyreyue\MSSQL1")
Dim sqlCommand As 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 Chart.PathName()" + " FROM Archive.dbo.Records" + " WHERE SerialNumber = 3"
Dim filePath As String = Nothing
Dim pathObj As Object = sqlCommand.ExecuteScalar()
If Not pathObj.Equals(DBNull.Value) Then
filePath = DirectCast(pathObj, String)
Else
Throw New System.Exception("Chart.PathName() failed" + " to read the path name " + " for the Chart column.")
End If
'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.
Dim transaction As SqlTransaction = sqlConnection.BeginTransaction("mainTranaction")
sqlCommand.Transaction = transaction
sqlCommand.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()"
Dim obj As Object = sqlCommand.ExecuteScalar()
Dim txContext As Byte() = Nothing
Dim contextLength As UInteger
If Not obj.Equals(DBNull.Value) Then
txContext = DirectCast(obj, Byte())
contextLength = txContext.Length()
Else
Dim message As String = "GET_FILESTREAM_TRANSACTION_CONTEXT() failed"
Throw New System.Exception(message)
End If
'The next step is to obtain a handle that
'can be passed to the Win32 FILE APIs.
Dim sqlFileStream As New SqlFileStream(filePath, txContext, FileAccess.ReadWrite)
Dim buffer As Byte() = New Byte(511) {}
Dim numBytes As Integer = 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.
Dim someData As String = "EKG data."
Dim unicode As Encoding = Encoding.GetEncoding(0)
sqlFileStream.Write(unicode.GetBytes(someData.ToCharArray()), 0, someData.Length)
'Read the data from the FILESTREAM
'BLOB.
sqlFileStream.Seek(0, SeekOrigin.Begin)
numBytes = sqlFileStream.Read(buffer, 0, buffer.Length)
Dim readData As String = unicode.GetString(buffer)
If numBytes <> 0 Then
Console.WriteLine(readData)
End If
'Because reading and writing are finished, FILESTREAM
'must be closed. This closes the c# FileStream class,
'but does not necessarily close 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 ex As System.Exception
Console.WriteLine(ex.ToString())
Finally
sqlConnection.Close()
End Try
Return
End Sub
End Module
#include <windows.h>
#include <sql.h>
#include<sqltypes.h>
#include<sqlext.h>
#include <stdio.h>
#include <sqlncli.h>
#define COPYBUFFERSIZE 4096
/// <summary>
///This class iterates though the ODBC error queue and prints all of the
///accumulated error messages to the console.
/// </summary>
class ODBCErrors
{
private:
int m_iLine; //Source code line on which the error occurred
SQLSMALLINT m_type; //Type of handle on which the error occurred
SQLHANDLE m_handle; //ODBC handle on which the error occurred
public:
/// <summary>
///Default constructor for the ODBCErrors class
///</summary>
ODBCErrors()
{
m_iLine = -1;
m_type = 0;
m_handle = SQL_NULL_HANDLE;
}
/// <summary>
///Constructor for the ODBCErrors class
/// </summary>
/// <param name="iLine">
/// This parameter is the source code line
/// at which the error occurred.
///</param>
/// <param name="type">
/// This parameter is the type of ODBC handle passed in
/// the next parameter.
///</param>
/// <param name="handle">
/// This parameter is the handle on which the error occurred.
///</param>
ODBCErrors(int iLine, SQLSMALLINT type, SQLHANDLE handle)
{
m_iLine = iLine;
m_type = type;
m_handle = handle;
}
///<summary>
/// This method iterates though the error stack for the handle passed
/// into the constructor and displays those errors on the console.
///</summary>
void Print()
{
SQLSMALLINT i = 0, len = 0;
SQLINTEGER native;
SQLTCHAR state[9], text[256];
SQLRETURN sqlReturn = SQL_SUCCESS;
if ( m_handle == SQL_NULL_HANDLE )
{
wprintf_s(TEXT("The error handle is not a valid handle.\n"), m_iLine);
return;
}
wprintf_s(TEXT("Error Line(%d)\n"), m_iLine);
while( sqlReturn == SQL_SUCCESS )
{
len = 0;
sqlReturn = SQLGetDiagRec(
m_type,
m_handle,
++i,
state,
&native,
text,
sizeof(text)/sizeof(SQLTCHAR),
&len);
if ( SQL_SUCCEEDED(sqlReturn) )
wprintf_s(TEXT("Error(%d, %ld, %s) : %s\n"), i, native, state, text);
}
}
};
BOOL CopyFileToSQL(LPTSTR srcFilePath, LPTSTR dstFilePath, LPBYTE transactionToken, SQLINTEGER cbTransactionToken)
{
BOOL bRetCode = FALSE;
HANDLE srcHandle = INVALID_HANDLE_VALUE;
HANDLE dstHandle = INVALID_HANDLE_VALUE;
BYTE buffer[COPYBUFFERSIZE] = { 0 };
TCHAR *szErrMsgSrc = TEXT("Error opening source file.");
TCHAR *szErrMsgDst = TEXT("Error opening destFile file.");
TCHAR *szErrMsgRead = TEXT("Error reading source file.");
TCHAR *szErrMsgWrite = TEXT("Error writing SQL file.");
try
{
if ( (srcHandle = CreateFile(
srcFilePath,
GENERIC_READ,
FILE_SHARE_READ,
NULL,
OPEN_EXISTING,
FILE_FLAG_SEQUENTIAL_SCAN,
NULL)) == INVALID_HANDLE_VALUE )
throw szErrMsgSrc;
if ( (dstHandle = OpenSqlFilestream(
dstFilePath,
Write,
0,
transactionToken,
cbTransactionToken,
0)) == INVALID_HANDLE_VALUE)
throw szErrMsgDst;
DWORD bytesRead = 0;
DWORD bytesWritten = 0;
do
{
if ( ReadFile(srcHandle, buffer, COPYBUFFERSIZE, &bytesRead, NULL) == 0 )
throw szErrMsgRead;
if (bytesRead > 0)
{
if ( WriteFile(dstHandle, buffer, bytesRead, &bytesWritten, NULL) == 0 )
throw szErrMsgWrite;
}
} while (bytesRead > 0);
bRetCode = TRUE;
}
catch( TCHAR *szErrMsg )
{
wprintf_s(szErrMsg);
bRetCode = FALSE;
}
if ( srcHandle != INVALID_HANDLE_VALUE )
CloseHandle(srcHandle);
if ( dstHandle != INVALID_HANDLE_VALUE )
CloseHandle(dstHandle);
return bRetCode;
}
void main()
{
TCHAR *sqlDBQuery =
TEXT("INSERT INTO Archive.dbo.Records(Id, SerialNumber, Chart)")
TEXT(" OUTPUT GET_FILESTREAM_TRANSACTION_CONTEXT(), inserted.Chart.PathName()")
TEXT("VALUES (newid (), 5, CONVERT(VARBINARY, '**Temp**'))");
SQLCHAR transactionToken[32];
SQLHANDLE henv = SQL_NULL_HANDLE;
SQLHANDLE hdbc = SQL_NULL_HANDLE;
SQLHANDLE hstmt = SQL_NULL_HANDLE;
try
{
//These statements Initialize ODBC for the client application and
//connect to the database.
if ( SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv) != SQL_SUCCESS )
throw new ODBCErrors(__LINE__, SQL_HANDLE_ENV, henv);
if ( SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3, NULL) != SQL_SUCCESS )
throw new ODBCErrors(__LINE__, SQL_HANDLE_ENV, henv);
if ( SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc) != SQL_SUCCESS )
throw new ODBCErrors(__LINE__, SQL_HANDLE_ENV, henv);
//This code assumes that the dataset name "Sql Server FILESTREAM"
//has been previously created on the client computer system. An
//ODBC DSN is created with the ODBC Data Source item in
//the Windows Control Panel.
if ( SQLConnect(hdbc, TEXT("Sql Server FILESTREAM"),
SQL_NTS, NULL, 0, NULL, 0) <= 0 )
throw new ODBCErrors(__LINE__, SQL_HANDLE_DBC, hdbc);
//FILESTREAM requires that all read and write operations occur
//within a transaction.
if ( SQLSetConnectAttr(hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF,
SQL_IS_UINTEGER) != SQL_SUCCESS )
throw new ODBCErrors(__LINE__, SQL_HANDLE_DBC, hdbc);
if ( SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt) != SQL_SUCCESS )
throw new ODBCErrors(__LINE__, SQL_HANDLE_DBC, hdbc);
if ( SQLExecDirect(hstmt, sqlDBQuery, SQL_NTS) != SQL_SUCCESS )
throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt);
//Retrieve the transaction token.
if ( SQLFetch(hstmt) != SQL_SUCCESS )
throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt);
SQLINTEGER cbTransactionToken = sizeof(transactionToken);
if ( SQLGetData(hstmt, 1,
SQL_C_BINARY,
transactionToken,
sizeof(transactionToken),
&cbTransactionToken) != SQL_SUCCESS )
throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt);
//Retrieve the file path for the inserted record.
TCHAR dstFilePath[1024];
SQLINTEGER cbDstFilePath;
if ( SQLGetData(hstmt, 2, SQL_C_TCHAR, dstFilePath, sizeof(dstFilePath), &cbDstFilePath) != SQL_SUCCESS )
throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt);
if ( SQLCloseCursor(hstmt) != SQL_SUCCESS )
throw new ODBCErrors(__LINE__, SQL_HANDLE_STMT, hstmt);
SQLUSMALLINT mode = SQL_ROLLBACK;
if ( CopyFileToSQL(
TEXT("C:\\Users\\Data\\chart1.jpg"),
dstFilePath,
transactionToken,
cbTransactionToken) == TRUE )
mode = SQL_COMMIT;
SQLTransact(henv, hdbc, mode);
}
catch(ODBCErrors *pErrors)
{
pErrors->Print();
delete pErrors;
}
if ( hstmt != SQL_NULL_HANDLE )
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
if ( hdbc != SQL_NULL_HANDLE )
SQLDisconnect(hdbc);
if ( hdbc != SQL_NULL_HANDLE )
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
if ( henv != SQL_NULL_HANDLE )
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
应用程序设计和实现的最佳实践
设计和实现使用 FILESTREAM 的应用程序时，应考虑下列准则：
使用 NULL 代替 0x 来表示未初始化的 FILESTREAM 列。 0x 值会导致创建文件，而 NULL 不会。
避免在包含非空 FILESTREAM 列的表中执行插入和删除操作。 插入和删除操作会修改用于垃圾收集的 FILESTREAM 表。 这可能导致应用程序的性能随着时间的推移而下降。
在使用复制的应用程序中，使用 NEWSEQUENTIALID() 代替 NEWID()。 在这些应用程序中生成 GUID 时，NEWSEQUENTIALID() 比 NEWID() 的性能更好。
FILESTREAM API 是专门为了以 Win32 流方式访问数据而设计的。 避免使用 Transact-SQL 读取或写入大于 2 MB 的 BLOB) (FILESTREAM 二进制大型对象。 如果必须从 Transact-SQL 读取或写入 BLOB 数据，请确保在尝试从 Win32 打开 FILESTREAM BLOB 之前已使用所有 BLOB 数据。 未能使用所有 Transact-SQL 数据可能会导致任何连续的 FILESTREAM 打开或关闭操作失败。
避免将数据更新、追加或追加到 FILESTREAM BLOB 的 Transact-SQL 语句。 这会导致 BLOB 数据被假脱机保存到 tempdb 数据库中，然后回到新物理文件中。
避免将小型 BLOB 更新追加到 FILESTREAM BLOB 中。 每次追加都会导致复制基础 FILESTREAM 文件。 如果应用程序必须追加小型 BLOB，请将 BLOB 写入
varbinary(max)列，然后在 BLOB 数达到预设的限制时对 FILESTREAM BLOB 执行单次写入操作。避免在应用程序中检索大量 BLOB 文件的数据长度。 这是非常耗时的操作，因为大小未存储在 SQL Server 数据库引擎中。 如果必须确定 BLOB 文件的长度，请使用 Transact-SQL DATALENGTH () 函数来确定 BLOB 的大小（如果已关闭）。 DATALENGTH() 不会打开 BLOB 文件来确定其大小。
如果应用程序使用 Message Block1 (SMB1) 协议，则应以 60 KB 的倍数读取 FILESTREAM BLOB 数据以优化性能。
另请参阅
避免与 FILESTREAM 应用程序中的数据库操作冲突
使用 OpenSqlFilestream 访问 FILESTREAM 数据
二进制大型对象 (Blob) 数据 (SQL Server)
对 FILESTREAM 数据进行部分更新