Determine if a connection has a transaction started?

[Mark Hoffman]

All,

Is there a way to determine if a connection object is involved in a
transaction? If so, is there a way to get to the transaction object?

Here's what's happening:
I've written a generic database wrapper that lets me easily call SQL
Server, Oracle, Access, etc. In it, I can call a method and pass in an
IDbConnection object, a SQL statement, the command type and
parameters. Everything's been working fine and dandy, but I never
added the ability to handle transactions.

So in the client code, I create a concrete connection object, start a
transaction on that connection object and then toss the connection
object to my generic database layer. Of course, when I attempt to
execute the SQL statement, it now fails because since I've started a
transaction on my connection object, I have to set the Transaction
property on the Command object that is created inside my generic
database layer.

So inside my generic database layer, I have the connection object that
was passed in, I have the command object that the database layer
creates....How do I tell the command object to use the transaction on
the connection object? And of course, only do it if in fact there is a
transaction?

Or am I just screwed?

 

[William Ryan eMVP]

Hi Mark:

There's not a whole lot inherently you can do per se but there's a possible
work around. First though, if at all possible, I'd have a strong bias
toward server side transactions. You can pass X into a proc, start your
transaction, do your thing and then commit when your done. once it starts
it will happen irrespective of what happens on the client side. If at all
possible, I'd really look to this end.

However, to your specific problem...

First, any given transaction has a .Connection property that you can use.
It sounds like you want it the other way around, you want to know if a
connection has a connection associated with it...

To that end, you can easily set this up. If you have a connnection property
in your class, you could also specify a transaction object as a property.
you can easily implement your own event mechanism so that you could have a
TransactionStarting, TransactionStarted, TransactionEnding and
TransactionEnding. (You might not need all of this, it depends on how much
control you need). When it Starts you can assign the Transaction property
you created w/ respect to the connection, when the Ended event is fired you
can set it to null. In a nutshell it's just a matter of setting your class
up so that you have some properties and events and you can query them as you
need.

All in all though this is still a lot of work compared to implementing them
within stored procs, but I'm guessing this generic DAL probably doesn't have
implementations for stored procs and since this would couple the DAL with a
DB implementation (or require a cool but intense factory implementation)
it's not a viable option for your scenario.

Nonetheless you should be able to implement this fairly easy with some
simple events and properties.

I'll be glad to walk through this with you...if you want me to just let me
know if you like C# or VB.NET

Cheers,

Bill
--
W.G. Ryan MVP Windows - Embedded

www.devbuzz.com
www.knowdotnet.com
http://www.msmvps.com/williamryan/

 

[Mark Hoffman]

Thanks for the reply, William. Some good ideas!

The DAL that I wrote does have support for stored procs and it uses the
factory pattern for obtaining concrete objects. So, what I did was simply
allow the callers to pass in a transaction object to the DAL. This way, the
client code can be responsible for setting up the transaction then just pass
the transaction object in to the DAL.

For example, I've got a method in my DAL called ExecuteNonQuery that accepts
the provider to use, the SQL statement, parameters and the connection
object or a delegate to a user defined function that creates a connection. I
just overloaded this to accept an IDbTransaction object. When the DAL sets
up the Command object, it just attaches the transaction object. (If it's not
null, of course.) It's working fine. I should have thought of doing this
earlier.

Thanks again for taking the time to help.

Mark