public SqlFileStream GetStream(Guid streamedReportId, Guid orgReportId)
{
string tmpFileName = string.Empty;
using (var ts = new TransactionScope(TransactionScopeOption.Required, TimeSpan.FromHours(1)))
{
using (SqlConnection conn = GetConnection())
using (SqlCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = @"SELECT p.Person.PathName() AS path,
GET_FILESTREAM_TRANSACTION_CONTEXT() as txnToken
FROM [dbo].[Persons] p
WHERE p.Id = @Id";
cmd.Parameters.AddWithValue("@Id", personId);
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
var path = reader.GetString(reader.GetOrdinal("path"));
byte[] txnToken = reader.GetSqlBinary(reader.GetOrdinal("txnToken")).Value;
Stream sqlFileStream = new SqlFileStream((string)path, txnToken, FileAccess.Read);
return sqlFileStream;
}
reader.Close();
}
}
ts.Complete();
}
return null;
}
I am trying to create a function in the data access layer that is going to return a SqlFileStream
. The function should be called from the business logic layer. The SqlFileStream
is wrapped in TransactionScope
and several using statements. I read about TransactionScope
that ts.Complete()
method will not be executed if the return is called earlier. How can I solve this problem. Please help with alternative solutions.