Ok, felt this bug was worth noting.
I'm maintaining some code that was originally written to utilize a sql connection code generator called bonebox. I was notified of an error that occured where a few reports that were running were timing out. Upon reviewing my log messages I discovered that the sql server appeared to be blocking during connection attempts. (as a side note: I was informed later that another application I'm not responsible for was stuck and was eating up most of the processing power of our sql box)
So I proceeded to run an sp_who2 sproc on my sql database to watch the number of connections that were being made. I noted that there were a rediculus number of connections, some 150 from just one copy of my application. This seemed like an absolutely rediculus number of connections for a single instance of an application.
So, I proceeded to go through the code of the generator and I made a few changes. Wherever a connection was implicitly opened, I closed it when it appeared the code would be finished with it.
I also changed some DataReader commands from
cmd.ExecuteReader(CommandBehavior.Default);
to
cmd.ExecuteReader(CommandBehavior.CloseConnection);
Primarily because a DataReader does not close connections when it's finished unless you add this parameter (apparently in my reading it was suggested that even when specified, it may not close it, but I did not encounter this in my efforts)
So, after re-runing my report and checking the sp_who2 sproc on my sql box, I happily noted that there were at most 3-4 connections now where there used to be 150.
A job well done... or so I thought.
Shortly thereafter I was notified of an error that occured during the processing of a different part of the application that utilizes transactions to save a bulk of data.
The error that was thrown:
The SqlTransaction has completed; it is no longer usable
Googling and searching yielded little help on this issue as most of the suggestions I could find didn't relate to my problem. Seeing that the only recent change I had made was the closing of connections, I assumed that this must be the cause of my error, I just didn't know where to go to solve it.
After much digging and putting a watch on the transaction as it passed through various methods, I noted that the IsolationLevel property of the transaction was displaying the following message after a few operations
IsolationLevel <error: an exception of type: {System.InvalidOperationException} occurred> System.Data.IsolationLevel
So after too much line by line debugging, I realised that the connection shouldn't be closed during the operation of a transaction or it probably would invalidate it, which would explain the transaction completed error. So, I went back through my code and added checks to determine whether or not a transaction was in use, if so, I left the connection open, if not, I closed it.
Example:
if
( trans != null )
{
cmd =
new SqlCommand("select Scope_Identity()", conn, trans);
reader = cmd.ExecuteReader(CommandBehavior.Default);
}
else
{
cmd =
new SqlCommand("select Scope_Identity()", conn);
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
... and at the end of the method when finished with the reader ...
reader.Close();
if (conn.State == ConnectionState.Open && trans == null)
conn.Close();
Finally, I set the connection to be closed when a transaction is committed to ensure that connections arn't left open and I run into pooling issues when I've got multiple users trying to use the application at once.
In summary, it's been a pain in the butt to track this one down and I'm hoping that I've now solved the issue. I've of course noted it here incase anyone else runs into a similar error and needs to save themselves a few hours of banging their head on a desk