Is it best practice to use a static database connection across multiple threads?

Question

Is there any consensus out there about whether it's best practice for a multi-threaded application to (1) use a single, shared, static connection to a SQL database, or (2) for each BackgroundWorker to open its own unique connection to the database?

I'm obviously assuming that each thread needs to connect to the same database.

And does the type of usage impact the answer? For instance, what if each thread is only running SELECT statements? Or if some threads may do UPDATE statements as well? Or does the usage not really make a difference, and you should always/never share a static connection regardless?

Answer 1

As discussed on the comments of your question. The best practice would be to leave the connection handling to ADO.Net as it contains connection pooling control so all you should do is open a connection every time you need execute a bit of SQL and then close it. The connection pool will not immediately close the connection as it will leave it open for configurable time to be able to pass it over to other threads requesting a new connection to be open. Additionally connections are not thread safe so each thread should have its on connection but again ADO.Net will deal with that.

If you want to learn more about the connection pool i suggest the following MSDN article: http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx

I also highly recommend that you read Microsofts Best practices for ado .net here: http://msdn.microsoft.com/en-us/library/ms971481.aspx

Some other articles:

• ADO.Net Best practices http://www.codemag.com/Article/0311051
• GOOD READ is the Enterprise Patterns and Practices for Improving .Net application has a great part on ADO.net: http://msdn.microsoft.com/en-us/library/ff649152.aspx

Answer 2

From the DbConnection (or SqlConnection) documentation:

Thread Safety

Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.

So a connection object is not thread safe, meaning you should not share a connection instance across multiple threads.