Using DatabaseLogging and DAAB inside a transactionScope.

Topics: Data Access Application Block, General discussion, Logging Application Block
Apr 2, 2008 at 3:00 PM
Edited Apr 2, 2008 at 6:39 PM
Has anyone tried using a database trace listener logging, and a DAAB database execute (execute reader for example) inside a transaction scope ? NOTE : BOTH GO AGAINST SAME DATABASE IN SQL

something like ....

Database db = DatabaseFactory.CreateDatabase("Database");
using (TransactionScope ts = new TransactionScope (TransactionScopeOption.RequiresNew, TimeSpan.FromSeconds (0)))
{
Logger.Write("Transact Start","TestCategory");
IDataReader rdr = db.ExecuteReader("usp_GetFromSomeStoredProcedure", "SomeParamValue");
//Do some Business logic here
db.ExecuteNonQuery("usp_UpdateStoredProcedure","SomeParamValue");
Logger.Write("Transact Complete","TestCategory");
ts.Complete();
}


Though, Logger.Write implementation inside FormattedDatabaseTraceListener is implemented to use DbTransactions, and be outside the transaction scope if any, When the db.ExecuteReader tries to open the connection to the SQL Database it fails with
"Communication with the underlying transaction manager has failed"
exception. One thing to note is both logger and DAAB go against same database.

Im not sure, why the SQL database wouldn't let the second connection. Things work fine, outside the transaction scope.

Any help is much appreciated.
Apr 2, 2008 at 8:02 PM
Though Database Trace Listener uses DbTransaction to execute storedprocedures, the connection.Open(), will still enlist itself in the TransactionScope that is in the context. And so the Connection.Close() statement, puts the Transaction Scope is such a state, that, when the Db.ExecuteReader comes along, and tried to open a connection for its own, in the same transaction scope, it gets the above error.

Is my understanding right, or am i missing something?
Apr 2, 2008 at 8:35 PM
Edited Apr 3, 2008 at 12:48 PM
Good blog of LTM
http://blogs.msdn.com/adonet/archive/2008/03/26/extending-lightweight-transactions-in-sqlclient.aspx#comments