Return Stream from WCF service, using SqlFileStream

Question

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.

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.

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

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

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; }
    }
}

Gabfest

It could be pictures, documents, movies. So anywhere between a couple of KBs and up to one or two GB – Gabfest

Storing a few GB in a Db is a questionable practice. You'll have to use Tempfiles or MemoryStreams. – Eliathan

@Henk Holterman, I'm using FileStream in an SQL Server 2008 R2. I'm under the impression that file size doesn't matter in that case. – Gabfest

If your webservice is PerCall what are your thoughts on making it implement IDisposable and closing sqlTransaction and sqlFileStream in Dispose() ? – Dexamethasone

This is exactly what I am trying to do, could you please post the complete solution or how you changed your original code to use the WCFStream. – Rudelson

I think you could refine your Dispose bit better. I like this approach lostechies.com/chrispatterson/2012/11/29/idisposable-done-right – Speiss

Answer 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).

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).

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.

Datestamp

The files might be big and many users might want to stream them at the same time, so reading anything into memory, isn't an option unfortunately. I guess I don't know enough about TransactionScope to know the problems it might cause, but thank you for the advice, I will try the Stream-wrapper approach. – Gabfest

Seems to be working fine! Thank you @marc, I've edited my original post, please feel free to comment on the implementation. I'm not really sure I'm doing it the way I should – Gabfest

It doesn't seem right to me to return an object containing data access objects and relying on the client to dispose of these? Wouldn't my answer be a simpler approach? Or am I missing something? – Journeywork

Answer 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?

Here's an example for a WCF method:

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:

_fileStorageProvider.WriteFileToStream(fileId, Response.OutputStream);

Journeywork

Assuming, the OP is using transferMode = streamed(recommended for large files), this answer would be wrong. WCF would not allow you to have two parameters in the method call. – Finella

Answer 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.

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

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:

...
    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.

Dexamethasone

I'm a bit confused by your answer. IMO you correctly point out that returning SQL data access objects over the wire is not good practice, but then you seem to suggest that an event that will be fired on the client when it's done with the stream can be successfully handled on the server? – Journeywork

Client is responsible for closing the stream. When that happens WcfStream.Dispose will be called that would trigger the event to dispose SQL objects. All of that is server-side. – Dexamethasone