placement of ado.net transactions

[Guest]

Hi group, I have an app with a table gateway-style data access layer that
does atomic crud operations on individual tables. but sometimes i want to do
two inserts/updates/etc. on multiple tables as a transaction. I could write
custom data access methods to handle these special cases, but I'd rather
"compose" these operations by having a layer above wrap the existing CRud
methods into a transaction. this would be like a business logic layer. Has
anyone organized their code like this? It seems like I would have to start
the trx in the bus logic layer and pass the trx down to each of the DAL
methods, but that seems ugly. Any thoughts or suggestions on the "best" way
to do this?
Thanks!

 

[Mariano Omar Rodriguez]

Two posibilities:

1. Use EnterpriseServices transactions
(System.EnterpriseServices.ServiceDomain &
System.EnterpriseServices.ServiceConfig)
2. Keep the connection open in the context (e.g.: using Thead.GetData &
Thead.SetData)

Mariano

 

[Sahil Malik [MVP]]

Actually, I wouldn't recommend using enterprise services or
system.transactions for this operation - especially if you are limiting all
transactions to one single database.

You may be able to twist my arm and make me agree to System.Transactions,
but certainly not ES.

Anyway to answer your question, you have two choices -

a) Pass in an array of prepared SqlCommands, with parameters in place and
never expose an open connection from your data layer.
b) Create a class - specific datalayer - that inherits from your data layer
class. Create a method in there and wrap the open connection, begin
transaction - do your stuff - commit./rollback - close connection - within
that one method (or scope). This way your business layer does not deal with
the nitty gritty of individual commands on a per transaction basis.

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync

 

[Jeff Jarrell]

I had a situation where I had multiple database updates related to what a
user would consider a single, logical transaction. Each specific database
update was independent of one another so I didn't want to create depenencies
between them.

So, I created collection of these update tasks. Then just before iterating
the collection I would open the connection and begin the transaction. Then
I would iterate the collection, setting an interace.transaction object and
calling an interface.execute on each element. if any step fails, I would
fall out of the iteration and rollback everything. If it alls works, then
commit.

I called it a "pipeline", but that's not exactly right in the strict sense
of the pattern world. I do code generation for stored procedure wrappers
and all in all is a snap to work with.

 

[Sahil Malik [MVP]]

That sounds like a neat approach
BTW, the code generation thing sounds interesting.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Jeff Jarrell]

coming from you thats a big ego boost. I have followed and been influenced
by your work. thanks.

jeff

 

[Sahil Malik [MVP]]

Holy crap dude, I'm just a normal guy .. don't put me on such a pedestal
where I am deluded of the pleasure of making silly mistakes.
But Thanks. And yeah, sorry for deflating your ego .. hehe

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx