How to protect SqlDataReader from multiple access in C#

Question

I had a multi-threaded TCP server software written in C#. Many of the threads should read from database and i am using SqlDataReader for reading from database. When one thread wants to read from database, there is no problem but when two or more threads want to read from database, this situation starts to be a problem. .NET gives this exception: There is already an open DataReader associated with this Command which must be closed first.

How can i prevent from the error? How can i protect SqlDataReader from multiple access? Is mutex appropriate for this problem?

as a side note, there is also something called "Multiple Active Result Sets" (MARS) - however, I mention this for completeness only: the better answer is "don't share a connection" — Marc Gravell
– Marc Gravell, Commented May 1, 2012 at 8:37

Jon Skeet · Accepted Answer · 2012-05-01 08:28:10Z

3

How can i protect SqlDataReader from multiple access?

Don't even share the connection.

Create a new SqlConnection for each database operation (in a using statement to dispose it at the end of the operation), and let the connection pool handle making this efficient.

If you're not sharing the connection, you won't end up with this error, and you won't be serializing your database access via locking.

answered May 1, 2012 at 8:28

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

Comments

linkerro · Accepted Answer · 2012-05-01 09:09:31Z

3

You should be asking the .net runtime for a new sql connection inside each thread. This is the optimum solution as far as speed goes (this uses .net's sql connection pool so you don't loose time establishing connections).

edited May 1, 2012 at 9:09

answered May 1, 2012 at 8:29

linkerro

5,4783 gold badges28 silver badges31 bronze badges

1 Comment

Marc Gravell Over a year ago

the important thing here is the connection, not the reader

Adam Houldsworth · Accepted Answer · 2012-05-01 08:38:50Z

I suggest creating a new connection per request instead of trying to re-use an existing active connection. I assume you are using some sort of class member or static reference to the connection, instead make it method scoped:

private void RespondingToTcpRequest()
{
    using (var connection = new SqlConnection(""))
    using (var command = new SqlCommand("", connection))
    {
         connection.Open();

         using (var reader = command.ExecuteReader())
         {
             // No locks or sharing issues here!
         }
    }
}

If I had some sample code demonstrating what you currently have, I could adapt that idea for your situation.

As for locking, if you really want to go down this route (seriously, don't), all you need to do is lock (one of the basic synchronisation primitives):

private static readonly object ReaderLock = new object();

private void RespondingToTcpRequest()
{
    lock (ReaderLock)
    {
        // database connection, command, reader stuff here.
    }
}

A mutex won't necessarily be required as the problem will be process-local, where the lock keyword will suffice. Again, seriously, don't.

Collectives™ on Stack Overflow

How to protect SqlDataReader from multiple access in C#

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related