none
LINQ and connection pool RRS feed

  • Question

  • I have some standard code like:

                using (OrderClassesDataContext orderContext = new OrderClassesDataContext())
                {
    

    but I get a SQL exception with the message being that the connection pool is exhausted and there was a timeout. I was wondering if LINQ had the same problem with 'using' that 'using( SqlConnection connection = new SqlConnection(....' has, in that the connection is put on a dispose list so the connection is not closed (and hence returned to the pool) until the CLR gets around to GC? If the using is call more often than the GC happens then it exahsts the connection pool. If LINQ has the same issue I was wondering if there is an explicit way to close the connection rather than just disposing it with LINQ.

    THank you.

    Kevin
    Saturday, January 23, 2010 4:59 PM

All replies

  • You can access and close the connection using: dataContext.Connection.Close();

    However, typically Dispose should suffice. Once the result set is enumerated the connection gets closed and I haven't heard of a case where closing it explicitly was ever needed. There is a potential memory leak issue that Jon Skeet has posted on his site based on a talk with Microsoft's Matt Warren depending on how results are used.

    Document my code? Why do you think it's called "code"?
    Saturday, January 23, 2010 10:26 PM
  • You can access and close the connection using: dataContext.Connection.Close();

    However, typically Dispose should suffice and once the result set is enumerated the connection gets closed and I haven't heard of a case where closing it explicitly was ever needed. There is a potential memory leak issue that Jon Skeet has posted on his site based on a talk with Microsoft's Matt Warren depending on how results are used.

    Document my code? Why do you think it's called "code"?

    Thank you.

    The problem is that I think (I haven't verified yet) that the CLR doesn't do the GC (and hence dispose) as fast as I am calling the code with 'using' so I have alot of connections that will be closed when they are disposed but since the CLR hasn't collected garbage the connections will not be closed and eventually this timing mismatch causes the connection pool to run out of connections. At least this was the case with 'normal' SqlConnection objects. So when I was using SqlConnection I had to explicitly close the connection when I was done because if I waited for the CLR to call dispose it was not enough. Ideally I could close all of the connection objects that have not been disposed yet before I try to open a new connection thereby ensuring that the pool was replenished.

    Kevin
    Saturday, January 23, 2010 10:34 PM