none
SQL FILESTREAM from WCF Service RRS feed

  • Question

  • I am attempting to serve up a FILESTREAM from SQL Server by using a WCF Service. All the examples I've seen on this uses a neat little wrapped up TransactionScope pattern like this:

          using (TransactionScope ts = new TransactionScope())
          {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
              conn.Open();
     
              using (SqlCommand cmd = new SqlCommand(InsertTSql, conn))
              {
                cmd.Parameters.Add("@PhotoId", SqlDbType.Int).Value = photoId;
                cmd.Parameters.Add("@Description", SqlDbType.VarChar).Value = desc;
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                  rdr.Read();
                  serverPath = rdr.GetSqlString(0).Value;
                  serverTxn = rdr.GetSqlBinary(1).Value;
                  rdr.Close();
                }
              }
              SavePhotoFile(filename, serverPath, serverTxn);
            }
            ts.Complete();
          }

    However, if I call the ts.Complete() and close the parens I get the "an existing connection was forcibly closed by the remote host" message.  However, if I create a TransactionScope without closing it I can get 2-3 files to download.  Then after that I get timeouts.

    Can someone give me some example code on the best way to return a Stream through WCF but still make sure the objects on the service close down after the Stream has successfully been sent?  My more complicated BAD code:

                    using (TransactionScope ts =
                        new TransactionScope(
                            TransactionScopeOption.Required,
                            new TimeSpan(0, 1, 0)))
                    {
                        SqlConnection conn = Settings.SqlServer.GetSqlConnection();
                        SqlCommand cmd = new SqlCommand("spArchiveSelectFileInfo", conn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@ArchiveID", param.ArchiveID);
                        // string to hold the BLOB pathname
                        string outPathName = default(string);
                        // byte array to hold the txn context
                        byte[] outTransactionContext = default(byte[]);
                        conn.Open();
                        using (var outReader = cmd.ExecuteReader(
                            CommandBehavior.SingleRow))
                        {
                            outReader.Read();
                            outPathName = outReader.GetSqlString(0).Value;
                            outTransactionContext = outReader.GetSqlBinary(1).Value;
                            outReader.Close();
                        }
                        conn.Close();
                       
                        source = new SqlFileStream(
                            outPathName,
                            outTransactionContext,
                            FileAccess.Read);
                    }
                    result = new DownloadResponse()
                    {
                        ArchiveID = archive.ArchiveID,
                        FileID = archive.FileID,
                        FileVersionID = archive.FileVersionID.ToString(),
                        DownloadStream = source
                    };

    Thanks in advance for your help,

    Joel


    Application Developer Manufacturing


    Friday, March 31, 2017 9:23 AM

All replies

  • Why do you need to use TransactionScope? Is it because you want the WCF service to be transactional? I'd opt for using the SqlTransaction for this if you can. I cleaned things up a bit.

    Example (not tested)

            public DownloadResponse YourMethod()
            {
                string outPathName;
                byte[] outTransactionContext;
                var result = new DownloadResponse();
    
                using (var connection = new SqlConnection("your connection string"))
                {
                    connection.Open();
                    var command = new SqlCommand("spArchiveSelectFileInfo", connection);
    
                    var tran = connection.BeginTransaction(IsolationLevel.ReadCommitted);
                    command.Transaction = tran;
    
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            outPathName = reader.GetSqlString(0).Value;
                            outTransactionContext = reader.GetSqlBinary(1).Value;
    
                            var fileStream = new SqlFileStream(outPathName,
                                outTransactionContext,
                                FileAccess.ReadWrite,
                                FileOptions.SequentialScan, 0);
    
                            // Seek to the end of the file  
                            fileStream.Seek(0, SeekOrigin.End);
    
                            // Append a single byte   
                            fileStream.WriteByte(0x01);
                            fileStream.Close();
    
                            result.DownloadStream = fileStream;
                            result.ArchiveID = archive.ArchiveID;
                            result.FileID = archive.FileID;
                            result.FileVersionID = archive.FileVersionID.ToString();
                        }
                    }
    
                    tran.Commit();
                }
    
                return result;
            }
        }




    william xifaras


    Friday, March 31, 2017 5:19 PM
  • I was starting to wonder the same thing. Using a TransactionScope is vital when uploading files using the FileStream object in SQL. However, I'm not sure of its benefit on downloading.

    Thanks for the reply.  I'll try it out.

    Joel


    Application Developer Manufacturing

    Friday, March 31, 2017 11:20 PM
  • Doesn't your [fileStream.Seek(0, SeekOrigin.End ] defeat the beauty of File Streaming?  I don't want to pull this all into memory before I send it back to the caller. Can you explain what you're doing by appending a byte onto the FileStream and then closing it?

    Also, it looks like the TransactionScope is required.  SQL seems to use it to keep the context around what file to return.


    Application Developer Manufacturing


    • Edited by Joel Palmer Saturday, April 1, 2017 3:27 AM
    Friday, March 31, 2017 11:54 PM
  • I'm starting to think that this can't be done.  TransactionScope is required in order to pull the FileStream out of SQL.  However, on ts.Complete and the end of the bracket closes the FileStream.  I've reviewed about 6 books on this and the Pluralsight training (Stroebel) and all of them draw the SqlFileStream into a MemoryStream then return it. 

    Someone correct me if I'm wrong but... this isn't streaming.  Having to read the entire stream into memory before returning it means that if I have a 10 GB backup file of a database this host server is likely going to crash... or at least degrade.  If it must be done this way, then I'm going to have to introduce some chunking logic to break the binary into pieces.

    I have not come across one example of using SQL FILESTREAM with WCF Streaming... and keeping it a true stream.  I would really like someone to just tell me definitively that this can't be done.

    Here is my current working solution that draws the FILESSTREAM into a MemoryStream.  This is good code but it doesn't meet my objectives:

                var memoryStream = default(MemoryStream);
    
    
                using (SqlConnection conn = Settings.SqlServer.GetSqlConnection())
                {
                    using (SqlCommand cmd = new SqlCommand("spArchiveSelectFileInfo", conn))
                    {
                        using (TransactionScope ts = new TransactionScope(
                            TransactionScopeOption.Required,
                            new TimeSpan(0, 1, 0)))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue("@ArchiveID", archiveID);
    
                            // string to hold the BLOB pathname
                            string outPathName = default(string);
                            // byte array to hold the txn context
                            byte[] outTransactionContext = default(byte[]);
    
                            conn.Open();
                            using (SqlDataReader reader =
                                cmd.ExecuteReader(CommandBehavior.SingleRow))
                            {
                                if (reader.Read())
                                {
                                    outPathName = reader.GetSqlString(0).Value;
                                    outTransactionContext = reader.GetSqlBinary(1).Value;
                                }
                                reader.Close();
                            }
                            conn.Close();
    
                            using (SqlFileStream fileStream =
                                new SqlFileStream(
                                    outPathName,
                                    outTransactionContext,
                                    FileAccess.Read))
                            {
                                using (var dest = new MemoryStream())
                                {
                                    fileStream.CopyTo(dest, 4096);
                                    dest.Close();
                                    memoryStream = new MemoryStream(dest.ToArray());
                                }
                                fileStream.Close();
                            }
    
                            ts.Complete();
                        }
                    }
                }
    
                return memoryStream;


    Application Developer Manufacturing

    Sunday, April 2, 2017 1:14 AM
  • Hi Joel,

    Thanks for sharing the solution. I would suggest you mark your reply as answer, and then others who run into the same issue would find the solution easily.

    If there is any other issues, please feel free to post in this forum.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 3, 2017 2:53 AM
  • Who said I posted the solution? Don't be so ambitious to close tickets as to undermine the entire reason for a forum. I need someone to tell me if there is a way to stream SQL FILESTREAM objects from WCF. What I posted is a temporary fix but not a solution.

    Application Developer Manufacturing

    Monday, April 3, 2017 3:05 PM
  • Sorry for misunderstanding. This statement “Here is my current working solution” misleads me.

    Let’s go back to your issue. As my researching, it seems TimeOut settings may cause this issue. I would suggest you try to increase the timeOut settings for long time SQL operation in WCF web.config.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, April 4, 2017 8:09 AM