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
– cnd, Commented May 25, 2011 at 8:36
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
– Davide Piras, Commented May 25, 2011 at 8:47
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
– Christian.K, Commented May 25, 2011 at 10:26

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

1

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.

answered May 25, 2011 at 8:42

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Robert Over a year ago

It really is as simple as the example above. The connection is passed to method that executes the sql and exits. Sometimes during the loop it is a closed connection, run the app again and it works ok. Stack trace etc and following the call stack shows nothing you can't see above. It's weird for sure.

Marc Gravell Over a year ago

@Robert - then hook StateChange to find out why

Robert Over a year ago

you mean hook into the statechanged event of the connection?

Marc Gravell Over a year ago

@robert yes, that is what I mean

Robert Over a year ago

Ok I will do this and the next run is tomorrow so i'll nurse it through and see what I can find out.

|

Collectives™ on Stack Overflow

Does my .Net SqlConnection object close on its own

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related