Does my .Net SqlConnection object close on its own

Question

We have a simple loop process that does some stuff on some datarows.

When commiting changes the new row and a SqlConnection object are passed to a method that handles the row change or addition.

The process runs 5 times out of 10 all ok. The SqlConnection is opened at the start of the loop and closed after the loop however sometimes it does indeed close during the loop. The code is no calling close() at any point during the loop.

So my questions is why it might close on it's own.

Cheers

For a reference the code resembles the following

connection.Open();

foreach(DataRow row in rows)
{
  if(rubbish)
{
   //make some changes and save
   DatabaseConnector.Save(sqlStringToExecute, connection);
}
}

connection.Close();

Maybe you should use several connections for each operation ? — cnd
never do code like the one above, if anything happens and an exception is thrown your connection will not be closed, where you declare it, use a Using statement so in any case connection will be closed and disposed when running out of scope. — Davide Piras
Did really the connection close (what is the exception or errormessage)? Could it be that a (distributed) transaction is timing out underneath? A statement timeout maybe. — Christian.K

Marc Gravell Marc Gravell · Accepted Answer · 2011-05-25T08:42:02

A connection will not close on its own, no. The main times a connection would close would be:

disposal (for example via a using statement)
explicit call to Close()
a command is executed with the CommandBehaviour.CloseConnection behaviour
garbage collection - kind-of (although this is a bit different, really)
the server ceases to be available

The first is very possible if you are actually getting an exception (maybe timeout or deadlock), bouncing through the using block (disposing it), and perhaps swallowing the exception.

The third is very possible in well-meaning code.

To investigate, you could subscribe to the StateChange event and add a break-point in the handler; then walk backwards through the stack trace and you'll know exactly who is closing it, and why. If you use the connection beyond the scope of this code, remember to unsubscribe too.

Does my .Net SqlConnection object close on its own

1 Answers