0

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();
Robert
  • 1,835
  • 4
  • 25
  • 30
  • 3
    Can you show your code? It will make your question clearer. – Darin Dimitrov May 25 '11 at 08:35
  • Maybe you should use several connections for each operation ? – cnd May 25 '11 at 08: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 May 25 '11 at 08: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 May 25 '11 at 10:26

1 Answers1

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.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • 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. – Robert May 25 '11 at 08:43
  • @Robert - then hook StateChange to find out why – Marc Gravell May 25 '11 at 08:44
  • you mean hook into the statechanged event of the connection? – Robert May 25 '11 at 08:44
  • @robert yes, that is what I mean – Marc Gravell May 25 '11 at 08:47
  • Ok I will do this and the next run is tomorrow so i'll nurse it through and see what I can find out. – Robert May 25 '11 at 09:16
  • It closes on it's own for no reason that I can ascertain, so i've just added a if connection is closed open it and it counts how many times this happens which varies between zero times and about 12 every run. – Robert Jun 08 '11 at 09:36
  • @Robert did you subscribe to the event and log the stacktrace when it closes? – Marc Gravell Jun 08 '11 at 10:21
  • Yes there is nothing unusual at all and it's completely random. I am aware when you 'open' a connection it's not really opened I am given a connection from the connection pool so I think it's the server itself that is doing something to cause the connection to close. – Robert Jun 09 '11 at 14:44