WCF服务中的SQL FILESTREAM [英] SQL FILESTREAM from WCF Service

问题描述

我正在尝试使用WCF服务从SQL Server提供FILESTREAM.我在此看到的所有示例都使用了一个简洁的，包裹起来的TransactionScope模式，如下所示:

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

但是，如果我调用ts.Complete()并关闭括号，则会收到远程主机强行关闭现有连接"的消息.消息.但是，如果我创建一个TransactionScope而不关闭它，则可以下载2-3个文件. 然后我就超时了.

有人可以给我一些通过WCF返回Stream的最佳方法的示例代码，但仍要确保在成功发送Stream之后关闭服务上的对象吗?我更复杂的BAD代码:

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:

                              使用(TransactionScope ts =
                   新的TransactionScope(
               b TransactionScopeOption.Required，
               b新的TimeSpan(0，1，0)))
                             {
                    SqlConnection conn = Settings.SqlServer.GetSqlConnection();

                using (TransactionScope ts =
                    new TransactionScope(
                        TransactionScopeOption.Required,
                        new TimeSpan(0, 1, 0)))
                {
                    SqlConnection conn = Settings.SqlServer.GetSqlConnection();

推荐答案

为什么需要使用TransactionScope?是否因为您希望WCF服务具有事务性?如果可以的话，我会选择使用SqlTransaction.我整理了一下.

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.

示例(未经测试)

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

这篇关于WCF服务中的SQL FILESTREAM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！