WCF streaming SQLFileStream by Message Contract

Question

In my WCF service, I try to load a File from MS SQL table which has a FileStream column and I try to pass it as a stream back

        responseMsg.DocSqlFileStream = new MemoryStream();

        try
        {
            using (FileStreamDBEntities dbEntity = new FileStreamDBEntities())
            {
                ...
                using (TransactionScope x = new TransactionScope())
                {
                    string sqlCmdStr = "SELECT dcraDocFile.PathName() AS InternalPath, GET_FILESTREAM_TRANSACTION_CONTEXT() AS TransactionContext FROM dcraDocument WHERE dcraDocFileID={0}";
                    var docFileStreamInfo = dbEntity.Database.SqlQuery<DocFileStreamPath>(sqlCmdStr, new object[] { docEntity.dcraDocFileID.ToString() }).First();

                    SqlFileStream sqlFS = new SqlFileStream(docFileStreamInfo.InternalPath, docFileStreamInfo.TransactionContext, FileAccess.Read);
                    sqlFS.CopyTo(responseMsg.DocSqlFileStream);

                    if( responseMsg.DocSqlFileStream.Length > 0 )
                        responseMsg.DocSqlFileStream.Position = 0;

                    x.Complete();
                }
            }
            ...

I'm wondering whats the best way to pass the SQLFileStream back through a message contract back to take advantage of streaming. Currently I copied the SQLFilEStream to a memory stream because I got an error message in WCF trace which says: Type 'System.Data.SqlTypes.SqlFileStream' cannot be serialized.

score 0 · Answer 1 · answered Jul 02 '13 at 16:54

In WebApi there is such thing as PushStreamContent it allows delegating all transaction stuff to async lambda, don't know if there is something similar in WCF, but the following approach may be helpful:

http://weblogs.asp.net/andresv/archive/2012/12/12/asynchronous-streaming-in-asp-net-webapi.aspx

score -1 · Answer 2 · edited May 23 '17 at 10:25

-1

You can't stream an SQLFileStream back to the client because it can only be read within the SQL transaction. I think your solution with the MemoryStream is a good way of dealing with the problem.

I had a similar problem and was worried about the large object heap when using a new Memory Stream every time. I came up with the idea of using a temporary file on the disk instead of a memory stream. We are using this solution in several project now and it works really well.

See here for the example code: https://stackoverflow.com/a/11307324/173711

edited May 23 '17 at 10:25

Community

1
1

answered May 03 '13 at 08:48

flayn

5,272
4
48
69

Thanks for your idea and sharing your solution in the link, may be I keep it as is for now, the message contract which I created is I IDisposable and the dispose method can help to release the memory stream (which actually a Stream in msg contract) after reading in caller side. But your temp file solution is definitely an approach too. – Farhad May 03 '13 at 17:42

WCF streaming SQLFileStream by Message Contract

2 Answers2