使用SqlFileStream从WCF服务返回流

I have a WCF service, from which users can request large datafiles (stored in an SQL database with FileStream enabled). These files should be streamed, and not loaded into memory before sending them off.

我有一个WCF服务，用户可以从中请求大型数据文件（存储在启用了FileStream的SQL数据库中）。这些文件应该流式传输，并且在发送之前不会加载到内存中。

So I have the following method that should return a stream, which is called by the WCF service, so that it can return the Stream to the client.

所以我有以下方法应该返回一个由WCF服务调用的流，以便它可以将Stream返回给客户端。

public static Stream GetData(string tableName, string columnName, string primaryKeyName, Guid primaryKey)
    {
        string sqlQuery =
            String.Format(
                "SELECT {0}.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() FROM {1} WHERE {2} = @primaryKey", columnName, tableName, primaryKeyName);

        SqlFileStream stream;

        using (TransactionScope transactionScope = new TransactionScope())
        {
            byte[] serverTransactionContext;
            string serverPath;
            using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnString"].ToString()))
            {
                sqlConnection.Open();

                using (SqlCommand sqlCommand = new SqlCommand(sqlQuery, sqlConnection))
                {
                    sqlCommand.Parameters.Add("@primaryKey", SqlDbType.UniqueIdentifier).Value = primaryKey;

                    using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                    {
                        sqlDataReader.Read();
                        serverPath = sqlDataReader.GetSqlString(0).Value;
                        serverTransactionContext = sqlDataReader.GetSqlBinary(1).Value;
                        sqlDataReader.Close();
                    }
                }
            }

            stream = new SqlFileStream(serverPath, serverTransactionContext, FileAccess.Read);
            transactionScope.Complete();
        }

        return stream;
    }

My problem is with the TransactionScope and the SqlConnection. The way I'm doing it right now doesn't work, I get a TransactionAbortedException saying "The transaction has aborted". Can I close the transaction and the connection before returning the Stream? Any help is appreciated, thank you

我的问题在于TransactionScope和SqlConnection。我现在正在做的方式不起作用，我得到一个TransactionAbortedException，说“事务已中止”。我可以在返回Stream之前关闭事务和连接吗？感谢任何帮助，谢谢

Edit:

编辑：

I've created a wrapper for a SqlFileStream, that implements IDisposable so that I can close everything up once the stream is disposed. Seems to be working fine

我已经为SqlFileStream创建了一个包装器，它实现了IDisposable，这样我就可以在流处理后关闭所有内容。似乎工作正常

public class WcfStream : Stream
{
    private readonly SqlConnection sqlConnection;
    private readonly SqlDataReader sqlDataReader;
    private readonly SqlTransaction sqlTransaction;
    private readonly SqlFileStream sqlFileStream;

    public WcfStream(string connectionString, string columnName, string tableName, string primaryKeyName, Guid primaryKey)
    {
        string sqlQuery =
            String.Format(
                "SELECT {0}.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() FROM {1} WHERE {2} = @primaryKey",
                columnName, tableName, primaryKeyName);

        sqlConnection = new SqlConnection(connectionString);
        sqlConnection.Open();

        sqlTransaction = sqlConnection.BeginTransaction();

        using (SqlCommand sqlCommand = new SqlCommand(sqlQuery, sqlConnection, sqlTransaction))
        {
            sqlCommand.Parameters.Add("@primaryKey", SqlDbType.UniqueIdentifier).Value = primaryKey;
            sqlDataReader = sqlCommand.ExecuteReader();
        }

        sqlDataReader.Read();

        string serverPath = sqlDataReader.GetSqlString(0).Value;
        byte[] serverTransactionContext = sqlDataReader.GetSqlBinary(1).Value;

        sqlFileStream = new SqlFileStream(serverPath, serverTransactionContext, FileAccess.Read);
    }

    protected override void Dispose(bool disposing)
    {
        sqlDataReader.Close();
        sqlFileStream.Close();
        sqlConnection.Close();
    }

    public override void Flush()
    {
        sqlFileStream.Flush();
    }

    public override long Seek(long offset, SeekOrigin origin)
    {
        return sqlFileStream.Seek(offset, origin);
    }

    public override void SetLength(long value)
    {
        sqlFileStream.SetLength(value);
    }

    public override int Read(byte[] buffer, int offset, int count)
    {
        return sqlFileStream.Read(buffer, offset, count);
    }

    public override void Write(byte[] buffer, int offset, int count)
    {
        sqlFileStream.Write(buffer, offset, count);
    }

    public override bool CanRead
    {
        get { return sqlFileStream.CanRead; }
    }

    public override bool CanSeek
    {
        get { return sqlFileStream.CanSeek; }
    }

    public override bool CanWrite
    {
        get { return sqlFileStream.CanWrite; }
    }

    public override long Length
    {
        get { return sqlFileStream.Length; }
    }

    public override long Position
    {
        get { return sqlFileStream.Position; }
        set { sqlFileStream.Position = value; }
    }
}

3 个解决方案

#1

Normally I might suggest wrapping the stream in a custom stream that closes the transaction when disposed, however IIRC WCF makes no guarantees about which threads do what, but TransactionScope is thread-specific. As such, perhaps the better option is to copy the data into a MemoryStream (if it isn't too big) and return that. The Stream.Copy method in 4.0 should make that a breeze, but remember to rewind the memory-stream before the final return (.Position = 0).

通常我可能会建议将流包装在自定义流中，以便在处理时关闭事务，但IIRC WCF不保证哪些线程执行什么操作，但TransactionScope是特定于线程的。因此，也许更好的选择是将数据复制到MemoryStream（如果它不是太大）并返回它。 4.0中的Stream.Copy方法应该可以轻而易举，但请记住在最终返回（.Position = 0）之前回放内存流。

Obviously this will be a big problem if the stream is big, ... but, if the stream is big enough for that to be a concern, then personally I'd be concerned at the fact that it is running in TransactionScope at all, since that has inbuilt time limits, and causes serializable isolation (by default).

显然，如果流很大，这将是一个大问题......但是，如果流足够大以至于成为一个问题，那么我个人会担心它在TransactionScope中运行的事实，因为它具有内置时间限制，并导致可序列化隔离（默认情况下）。

A final suggestion would be to use a SqlTransaction, which is then not thread-dependent; you could write a Stream wrapper that sits around the SqlFileStream, and close the reader, transaction and connection (and the wrapped stream) in the Dispose(). WCF will call that (via Close()) after processing the results.

最后的建议是使用SqlTransaction，然后它不依赖于线程;你可以编写一个围绕SqlFileStream的Stream包装器，并关闭Dispose（）中的reader，transaction和connection（以及包装的流）。在处理结果后，WCF将调用它（通过Close（））。

#2

Hmm I might be missing something here, but it seems to me a simpler approach would be to provide the stream to the WCF method and writing to it from there, rather than trying to return a stream which the client reads from?

嗯，我可能在这里遗漏了一些东西，但在我看来，更简单的方法是将流提供给WCF方法并从那里写入，而不是尝试返回客户端读取的流？

Here's an example for a WCF method:

以下是WCF方法的示例：

public void WriteFileToStream(FetchFileArgs args, Stream outputStream)
{
    using (SqlConnection conn = CreateOpenConnection())
    using (SqlTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted))
    using (SqlCommand cmd = conn.CreateCommand())
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "usp_file";
        cmd.Transaction = tran;
        cmd.Parameters.Add("@FileId", SqlDbType.NVarChar).Value = args.Id;

        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            if (reader.Read())
            {
                string path = reader.GetString(3);
                byte[] streamContext = reader.GetSqlBytes(4).Buffer;

                using (var sqlStream = new SqlFileStream(path, streamContext, FileAccess.Read))
                    sqlStream.CopyTo(outputStream);
            }
        }

        tran.Commit();
    }
}

In my app, the consumer happens to be an ASP.NET application, and the calling code looks like this:

在我的应用程序中，消费者恰好是一个ASP.NET应用程序，调用代码如下所示：

_fileStorageProvider.WriteFileToStream(fileId, Response.OutputStream);

#3

Logically none of the SQL related stuff belongs to Stream wrapper class (WcfStream) especially if you intend to send WcfStream instance to external clients.

逻辑上，没有SQL相关的东西属于Stream包装类（WcfStream），特别是如果你打算将WcfStream实例发送到外部客户端。

What you could’ve done was to have an event that would be triggered once WcfStream is disposed or closed:

您可以做的是在WcfStream处理或关闭后触发一个事件：

public class WcfStream : Stream
{
    public Stream SQLStream { get; set; }
    public event EventHandler StreamClosedEventHandler;

    protected override void Dispose(bool disposing)
    {
        if (disposing)
        {
            SQLStream.Dispose();

            if (this.StreamClosedEventHandler != null)
            {
                this.StreamClosedEventHandler(this, new EventArgs());
            }
        }
        base.Dispose(disposing);
    }
}

Then in you main code you would hook up an event handler to StreamClosedEventHandler and close all sql-related objects there as such:

然后在主代码中，您将事件处理程序连接到StreamClosedEventHandler并关闭所有与sql相关的对象：

...
    WcfStream test = new WcfStream();
    test.SQLStream = new SqlFileStream(filePath, txContext, FileAccess.Read);
    test.StreamClosedEventHandler +=
                new EventHandler((sender, args) => DownloadStreamCompleted(sqlDataReader, sqlConnection));

    return test;
}

private void DownloadStreamCompleted(SqlDataReader sqlDataReader, SQLConnection sqlConnection)
{
    // You might want to commit Transaction here as well
    sqlDataReader.Close();
    sqlConnection.Close();
}

This looks to be working for me and it keeps Streaming logic separate from SQL-related code.

这看起来对我有用，它使Streaming逻辑与SQL相关的代码分开。