Sunday, June 29, 2008

LINQ to SQL architecture question

I have structured a project with a single LINQ to SQL .dbml file and a single DataAccess class that is used to call stored procs and query against this this DBML class. The DataAccess class implements the singleton pattern inasmuch as their's a static DataAccess property which is used for all DB access.

The business layer of the application creates an instance of the DataAccess object, calls the appropriate data access function and then returns.

When running as a web application it is being hit rapidly by 2 clients: (1) a browser calls to it about 1 to 50 times a second (writes data to DB) and (2) an excel spreadsheet calls the web app via a web service on an ad hoc basis and does a query for data.

When the spreadsheet does the query (the browser's hitting the site up to 50 times a second at the same time) I am getting several errors such as the following:

  •     Invalid attempt to call MetaData when reader is closed
  •     There is already an open DataReader associated with this Command which must be closed first.
  •     ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
  •     A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 18 - Connection has been closed by peer)
  •     A severe error occurred on the current command.  The results, if any, should be discarded.
  •     A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

The first 2 errors were solved by adding MARS to the connection string: MultipleActiveResultSets=true

The rest of them I solved by adding a static lock object to the DataAccess class and wrapping all calls to the database in a lock(lockObj) {}.

This solution doesn't feel right though.

Comments on the general approach I've taken... Comments on the lock() solution...


  1. I posted this question on the AZGroups mailing list and got a number of excellent responses. Basically my problem is that I shouldn't be using a singleton pattern for the DataContext but should be instantiating a new DataContext for each job unit that's performed.

  2. Maybe you shouldn't be using the singleton pattern at all. It is rarely a good idea. It is just a global variable with a cool name.

  3. Thanks for this information! It described one of those "elusive" errors I was having as well. I knew better than to use the singleton pattern in my data access layer, but I decided to be lazy. Oh well, back to change the architecture so I can rid myself of those same errors.
    If I had stopped for a moment and thought about it, since the WEB is multi-threaded, a singleton object might be busy when another caller makes a request -- there's my error!