Is it best practice to use a static database connection across multiple threads?
backgroundworker, c#, database, multithreading, sql-server-2012
Solution
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
Problem
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?