从WCF服务返回流，使用SqlFileStream [英] Return Stream from WCF service, using SqlFileStream
问题描述
我有一个WCF服务，用户可以从中索取大量数据文件（存储在启用的FileStream SQL数据库）。这些文件应流，而不是加载到内存送他们出发前。
所以，我有以下的方法应该返回一个流，它是由WCF服务，称为，因此，它可以将流返回给客户端。
公共静态流的GetData（字符串tableName值，字符串COLUMNNAME，串primaryKeyName， GUID的PrimaryKey）
{
串的SQLQuery =
的String.Format（
选择{0} .PathName（），GET_FILESTREAM_TRANSACTION_CONTEXT（）FROM {1} WHERE {2} = @ PrimaryKey的，COLUMNNAME中，TableName，primaryKeyName）;
SqlFileStream流;使用（TransactionScope的TransactionScope的=新的TransactionScope（））
{
字节[] serverTransactionContext
;
串SERVERPATH;
使用
{
sqlConnection.Open（）（的SqlConnection SqlConnection的=新的SqlConnection（ConfigurationManager.ConnectionStrings [CONNSTRING]的ToString（）））;使用（的SqlCommand的SqlCommand =新的SqlCommand（sqlquery的，SqlConnection的））
{
sqlCommand.Parameters.Add（@的PrimaryKey，SqlDbType.UniqueIdentifier）.value的= PrimaryKey的
;
使用（SqlDataReader的SqlDataReader的= sqlCommand.ExecuteReader（））
{
sqlDataReader.Read（）;
SERVERPATH = sqlDataReader.GetSqlString（0）.value的;
serverTransactionContext = sqlDataReader.GetSqlBinary（1）.value的;
sqlDataReader.Close（）;
}
}
}
=流新SqlFileStream（SERVERPATH，serverTransactionContext，FileAccess.Read）;
transactionScope.Complete（）;
}
返回流;
}
我的问题是用的TransactionScope和SqlConnection的。我这样做是正确的，现在不上班的路上，我得到一个TransactionAbortedException说：该交易已中止。我可以关闭交易并返回流之前的连接？任何帮助表示赞赏，谢谢
编辑：
我创建对于SqlFileStream的包装，即实现IDisposable这样一旦流布置我可以关闭一切。似乎是工作的罚款
公共类WcfStream：流
{
私人只读的SqlConnection SqlConnection的;
私人只读SqlDataReader对象SqlDataReader的;
私人只读的SqlTransaction的SqlTransaction;
私人只读SqlFileStream sqlFileStream;
公共WcfStream（字符串的connectionString，串COLUMNNAME，串tableName值，字符串primaryKeyName中GUID的PrimaryKey）
{
串的SQLQuery =
的String.Format（
选择{0} .PathName（），GET_FILESTREAM_TRANSACTION_CONTEXT（）FROM {1} WHERE {2} = @PrimaryKey，
COLUMNNAME中，TableName，primaryKeyName）;
的SqlConnection =新的SqlConnection（的connectionString）;
sqlConnection.Open（）;
的SqlTransaction = sqlConnection.BeginTransaction（）;使用
（的SqlCommand的SqlCommand =新的SqlCommand（sqlquery的，SqlConnection的，的SqlTransaction））
{
sqlCommand.Parameters.Add（@的PrimaryKey，SqlDbType.UniqueIdentifier）.value的=首要的关键;
= SqlDataReader的sqlCommand.ExecuteReader（）;
}
sqlDataReader.Read（）;
串SERVERPATH = sqlDataReader.GetSqlString（0）.value的;
字节[] = serverTransactionContext sqlDataReader.GetSqlBinary（1）.value的;
sqlFileStream =新SqlFileStream（SERVERPATH，serverTransactionContext，FileAccess.Read）;
}
保护覆盖无效的Dispose（BOOL处置）
{
sqlDataReader.Close（）;
sqlFileStream.Close（）;
sqlConnection.Close（）;
}
公共覆盖无效的flush（）
{
sqlFileStream.Flush（）;
}
公众覆盖寻求长（长偏移，SeekOrigin原点）
{
返回sqlFileStream.Seek（偏移，产地来源）;
}
公共覆盖无效SetLength函数（长值）
{
sqlFileStream.SetLength（值）;
}
公共覆盖INT读（字节[]缓冲区，诠释抵消，诠释计数）
{
返回sqlFileStream.Read（缓冲区，偏移数）;
}
公共覆盖无效写入（字节[]缓冲区，诠释抵消，诠释计数）
{
sqlFileStream.Write（缓冲区，偏移数）;
}
公众覆盖BOOL的CanRead
{
{返回sqlFileStream.CanRead; }
}
公众覆盖布尔CanSeek
{
{返回sqlFileStream.CanSeek; }
}
公众覆盖布尔CanWrite
{
{返回sqlFileStream.CanWrite; }
}
公众覆盖长度长
{
{返回sqlFileStream.Length; }
}
公众覆盖多头头寸
{
{返回sqlFileStream.Position; }
集合{sqlFileStream.Position =价值; }
}
}
通常我会建议包装在封闭处置时交易的自定义流流，但IIRC WCF不保证哪个线程做什么，而是的TransactionScope 是线程特定的。这样，也许是更好的选择是将数据复制到的MemoryStream （如果它不是太大），并返回。在4.0 Stream.Copy 方法应该是一件轻而易举的事，但要记住最后的返回之前倒带内存流（ .POSITION = 0 ）。
显然，这将是一个大问题，如果流大。 ..但是，如果流是足够大了的是的引起人们的关注，那么的亲自的我会在它运行的事实有关的TransactionScope 的所有的，因为有内在的时间限制，并导致序列化隔离（默认情况下）。
最后一个建议是使用的SqlTransaction ，然后不是线程依赖;你可以写身边坐着一个流包装在 SqlFileStream ，并关闭读卡器，事务和连接（与包裹在流）的的Dispose（）。 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.
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; }
}
}
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.
这篇关于从WCF服务返回流，使用SqlFileStream的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
