synchronize sql connection in multithreading in .NET

Question

I am using multi threading concept to run some process. this process uses the sql connection object to get the data from database.This object is in another class. how to synchronize the sql connection while using multithreading?

Answer 1

The easiest way will be to wrap the operation in a lock() clause...

lock(sharedConnection_)
{
  // do your operations here
}

You can probably find a way to not have to duplicate that lock all over the place. Anyway. If you do that, you will ensure that no two threads will hold a lock on the same object at the same time.

There are more sophisticated things you might do but this is a good start.

Answer 2

Just create a new connection in each place you need it. Connection pooling will automatically ensure it only creates as many physical connections as you need.

using(var conn = new SqlConnection("..."))
{
    var cmd = new SqlCommand("...", conn);
    ...
}