SqlDataAdapter and optional transactions

Giganews Newsgroups
Subject: SqlDataAdapter and optional transactions
Posted by:  Israel (israeldipe…@hotmail.com)
Date: Thu, 17 Mar 2011

I recently switched from using MySQL to SQL server.  One of the
aspects of MySQL that I had inadvertently taken advantage of was that
if you perform a query and there's a pending transaction on the
connection the SqlDataAdapter will default to that pending transaction
if you don't specifically pass one.
The problem I have is that there are lots of infrastructure methods
which optionally take a transaction.  In some cases we're in the
middle of updating other things and need to perform a query so the
transaction gets passed in.  In other cases we just pass in null.
What I find annoying is that (in both Sql server and MySQL) the
SqlDataAdapter constructor can't take a null transaction and assume
that's the same as calling the other constructor which has not
transaction.
In addition if there's a requirement to pass in the active transaction
WHY is no constructor that even takes a transaction?  I have to resort
to constructing an SqlCommand and passing that into the
SqlDataAdapter.

My code for using adapters ends up looking like this:
using (SqlDataAdapter adapter =
    ((myTransaction == null) ? (new SqlDataAdapter(QuerySQLString,
myConnection)) :
        new SqlDataAdapter(new SqlCommand(QuerySQLString,
myConnection, myTransaction))))

In my opinion it's ugly and stupid that I have to go through all of
this just to create an SqlDataAdapter with an optional transaction.
Secondly, I was just ignoring the fact that I'm not sure if the
SqlCommand is even getting disposed in this case.

Has anyone come up with a cleaner solution to this issue?

Replies