1
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.

nskater
  • 11
  • 3

0 Answers0