Can I get a reference to a pending transaction from a SqlConnection object?
- by Rune
Hey,
Suppose someone (other than me) writes the following code and compiles it into an assembly:
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (var transaction = conn.BeginTransaction())
{
/* Update something in the database */
/* Then call any registered OnUpdate handlers */
InvokeOnUpdate(conn);
transaction.Commit();
}
}
The call to InvokeOnUpdate(IDbConnection conn) calls out to an event handler that I can implement and register. Thus, in this handler I will have a reference to the IDbConnection object, but I won't have a reference to the pending transaction. Is there any way in which I can get a hold of the transaction? In my OnUpdate handler I want to execute something similar to the following:
private void MyOnUpdateHandler(IDbConnection conn)
{
var cmd = conn.CreateCommand();
cmd.CommandText = someSQLString;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
However, the call to cmd.ExecuteNonQuery() throws an InvalidOperationException complaining that
"ExecuteNonQuery requires the command
to have a transaction when the
connection assigned to the command is
in a pending local transaction. The
Transaction property of the command
has not been initialized".
Can I in any way enlist my SqlCommand cmd with the pending transaction? Can I retrieve a reference to the pending transaction from the IDbConnection object (I'd be happy to use reflection if necessary)?
