C# DataSource Class and Thread Safety

Question

What would be a good way to writein C# a .NET3.5 Thread Safe DataSource Class. The class would connect to SQL Server and every method would execute a Stored Procedure.

When the code worked single threaded. I had a Singleton DataSource Class with a private SqlConnection member. Each method opened and closed that connection. When running this with multiple threads it is causing trouble when under certain scenarios the connection has already been opened by another thread. What would be the best way to re-write this class?

Note: By DataSource dont mean any built in .NET class, but a Model class that provides data to the controller.

Answer 1

You'd have to elaborate more about what your DataSource class should do. You don't need to implement any thread safety code if each of your Create/Read/Update/Delete methods do not alter any state.

UPDATE: In your case, I recommend to just recreate a new SqlConnection instance in every method of your DataSource class, because ADO.NET already handles the pooling for you.

Answer 2

SQL Server already coordinates threads through very complex mechanisms. You don't need to do anything specific to achieve thread safety to simply execute a Store Procedure.

Answer 3

Why don't you just use built-in ado.net pooling: create and open the connection right before the operation and dispose is asap - for every method.

Answer 4

The problem seems to be from the Singleton design. That can still work, just make sure you do not store the connection as a field. Only use a local variable in each method to hold the connection. That should be thread-safe by design.

And make it Exception-safe as well:

 using (var conn = new SqlConnection(...))
 {
    // call SP
 }