Using ADO.NET transactions

Using ADO.NET transactions

Old forum URL: forums.lhotka.net/forums/t/7914.aspx


neste posted on Friday, October 30, 2009

I can't use TransactionScope in my environment, so I'm forced to use ADO.NET transactions. Could someone provide some code snippet on how to do that the right way in my business objects that have nested child collections? I'm quite new at using CSLA. Your help is appreciated. Thank you.

JonnyBee replied on Friday, October 30, 2009

Why can't you use TransactionScope? If you use connection in a proper way it will result in ADO.NET transactions and only elevate to DTC if transactions happen on more than one connection.

neste replied on Friday, October 30, 2009

Yes, you're right. I was opening a new connection when updating the child objects. Thanks for your help.

Regent replied on Friday, October 30, 2009

I was unable to use TransactionScope model because there was no support for it in database provider I used...

So I came up with the following solution (remember that you have no need to use transactions when you are fetching objects):

Parent class:

[Transactional(TransactionalTypes.Manual)]
protected override void DataPortal_Insert()
{
using (var ctx = ConnectionManager.GetManager("DbName"))
{
using (var transaction = ctx.Connection.BeginTransaction())
{
using (var command = transaction.Connection.CreateCommand())
{
command.Transaction = transaction;

command.CommandText =
"INSERT INTO ...";

// save the parent first
command.ExecuteNonQuerySafe();

// pass current IDbCommand instance along with the reference to current object
FieldManager.UpdateChildren(command, this);
transaction.Commit();
}
}
}


The in child classes I use:

private void Child_Insert(IDbCommand baseCommand, Parent parent)
{
using (var command = baseCommand.Connection.CreateCommand())
{
command.Transaction = baseCommand.Transaction;

command.CommandText =
"INSERT INTO ...";

// save child
command.ExecuteNonQuery();
}
}

RockfordLhotka replied on Friday, October 30, 2009

There's also Csla.Data.TransactionManager that is designed to support this scenario in a slightly simpler manner. It is simpler, because the context object exposes both the connection and transaction as top-level properties so they are easier to access in your code. Also, it automatically begins the transaction for you.

 

Regent replied on Friday, October 30, 2009

Thanks! I haven't noticed that class before...

But as for me it would be convenient to have non-generic TransactionManager class with the same connection creation logic as in non-generic ConnectionManager...

Copyright (c) Marimer LLC