从CLR存储过程中访问Sql FILESTREAM [英] Accessing Sql FILESTREAM from within a CLR stored procedure
问题描述
我正在尝试从CLR存储过程访问Sql文件流.我已经建立了一个非常简单的数据库,其中包含一个包含文件流列的表.我可以使用一个简单的控制台应用程序成功读取文件流.这是失败的proc的一些示例代码:
I'm trying to access a Sql filestream from a CLR stored procedure. I've set up a very simple database with a single table which includes a filestream column. I can successfully read from the filestream using a simple console app. Here's some example code for the proc that fails:
[SqlProcedure]
public static void GetDataFromFileStream(string path, out int data)
{
using (var connection = new SqlConnection("context connection=true"))
{
connection.Open();
var transaction = connection.BeginTransaction();
var transactionContext = GetTransactionContext(connection, transaction);
// the following line throws an exception
var sqlFileStream = new SqlFileStream(path, transactionContext, FileAccess.Read);
var buffer = new byte[4];
sqlFileStream.Read(buffer, 0, 4);
data = BitConverter.ToInt32(buffer, 0);
}
}
private static byte[] GetTransactionContext(SqlConnection connection, SqlTransaction transaction)
{
using (var cmd = connection.CreateCommand())
{
const string myGetTxContextQuery = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";
cmd.CommandText = myGetTxContextQuery;
cmd.CommandTimeout = 60;
cmd.CommandType = CommandType.Text;
cmd.Transaction = transaction;
return (byte[])cmd.ExecuteScalar();
}
}
尝试构造SqlFileStream实例时抛出异常:
An exception is thrown when trying to construct the SqlFileStream instance:
发生System.ComponentModel.Win32Exception Message =不支持该请求" Source ="System.Data" 错误代码= -2147467259 NativeErrorCode = 50
System.ComponentModel.Win32Exception occurred Message="The request is not supported" Source="System.Data" ErrorCode=-2147467259 NativeErrorCode=50
有人知道我在做什么错吗?
Anyone know what I'm doing wrong?
推荐答案
由于上述问题,我无法使用SqlFileStream直接从CLR内部访问文件流.我最终采用的解决方案是使用SQL存储过程来获取所需的文件流数据的子集.尽管在某些情况下这不是特别有效,但对于我的应用程序来说已经足够了
I was unable to use SqlFileStream to access the filestreams directly from within the CLR (because of the problems identified above). The solution I eventually adopted was to use a SQL stored procedure to get hold of the subset of filestream data I needed. Although this is not particularly efficient in some scenarios, it sufficed for my application
CREATE PROC ReadFromFilestream
(
@pfilestreamGUID UNIQUEIDENTIFIER,
@pOffsetIntoData INT,
@pLengthOfData INT,
@pData VARBINARY(MAX) OUTPUT
)
AS
BEGIN;
SELECT @pData = SUBSTRING(ValueData, @pOffsetIntoData, @pLengthOfData)
FROM [MESL].DataStream
WHERE DataStreamGUID = @pfilestreamGUID;
END;
这篇关于从CLR存储过程中访问Sql FILESTREAM的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!