How does a Linq-to-SQL DataClasses connection get closed?

Question

I have a Linq-to-SQL DataClasses object that I utilize to make database calls. I've wrapped it like so:

  public class DataWrapper {

    private DataClassesDataContext _connection = null;
    private static DataWrapper _instance = null;
    private const string PROD_CONN_STR = "Data Source=proddb;Initial Catalog=AppName;User ID=sa;Password=pass; MultipleActiveResultSets=true;Asynchronous Processing=True";

    public static DataClassesDataContext Connection {
      get {
        if (Instance._connection == null)
            Instance._connection = new DataClassesDataContext(DEV_CONN_STR);

        return Instance._connection;
      }
    }

    private static DataWrapper Instance {
      get {
        if (_instance == null) {
          _instance = new DataWrapper();
        }
        return _instance;
      }
    }
  }

I have a couple of threads using this wrapper to make stored procedure calls, like this:

DataWrapper.Connection.Remove_Message(completeMessage.ID);

On very rare occasions, my DataClasses object will throw the exception:

ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

I'm not managing the connection's state in any way -- I figured Linq-to-SQL should handle this. I could check the connection state of the Connection each time I make a call and open it if it has been closed but that seems like a hack.

I've tried putting MultipleActiveResultSets=true and Asynchronous Processing=True on the connection string to try to handle the possibility of SQL forcibly closing connections, but that hasn't seemed to help.

Any ideas?

Why not open the connection, make the call, then close the connection and let the underlying ADO.NET handle the connection pooling? You didn't post any of your code for the actual calls - I would think a simple using block with the context would take care of the close portion. I haven't played much with EF so I could be off-base here though. — Tim, Mar 27 '13 at 18:19
@Tim I don't know. I don't think so. Jon Skeet's answer [here](http://stackoverflow.com/a/400888/84873) seems to imply the DataClasses object should be smart enough to handle it. — Daniel, Mar 27 '13 at 18:48
@Doc: Then the problem is not so obvious :) Any threading involved with database queries? If so, mark `DataWrapper _instance` as `ThreadStatic` (and check it for null too in the `Connection` property). — leppie, Mar 27 '13 at 18:51
@Tim: It is handy to keep a `DataContext` alive in WinForms/WPF for databinding, unlike a web app. — leppie, Mar 27 '13 at 18:54
@leppie I see that `[ThreadStatic]` will `Indicate that the value of a static field is unique for each thread.`. Are you saying that an `_instance` may be getting reused in a different thread but after the Connection has been closed? — Daniel, Mar 27 '13 at 18:59
@Doc: Yes. `ThreadStatic` will solve the problem in that case. With `ThreadStatic` remember that `_instance` will be null initially on every thread (unless assigned at some point via a static constructor, but that only happens once, and not per thread). IIRC, `DataContext` open/closes the connection on demand in LINQ2SQL, if it is not open already. — leppie, Mar 27 '13 at 19:01

score 1 · Answer 1 · answered Mar 27 '13 at 18:41

1

You should not cache and re-use a DB connection object ... especially from multiple threads.

You should open a connection, execute your operation(s) and close your connection each time you need to access the DB.

The underlying database access infrastructure (ASP.NET/OLEDB) will manage connection pooling in such a way that reduces most re-connection costs to (effectively) zero.

answered Mar 27 '13 at 18:41

Rich Turner

10,800
1
51
68

Thanks. Do you know of any documentation that gives these instructions? – Daniel Mar 27 '13 at 18:51
Apologies for the late reply. The [MSDN docs](http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx) should give you a pretty good overview of the best ways to maximize the benefits of connection pooling – Rich Turner Dec 06 '13 at 06:19

How does a Linq-to-SQL DataClasses connection get closed?

1 Answers1