|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
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
My code for using adapters ends up looking like this:
using (SqlDataAdapter adapter =
((myTransaction == null) ? (new SqlDataAdapter(QuerySQLString,
new SqlDataAdapter(new SqlCommand(QuerySQLString,
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?