SqlConnection Thread-Safe?

Question

I searched on Google about my problem, but I did not find a solution to this.

I have a log class that puts logs in a Windows log and in an SQL table. To optimize my code, I would like to use only one SqlConnection.

At MSDN, he said: any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members do not guarantee thread safety.

My question is:

private static readonly SqlConnection conn = new SqlConnection(ConfigParameters.Instance.UIDConnection); 

is it thread safe? If so, when using Open () and Close ()?

If not, how to use SqlConnection correctly?

Here is my complete class code:

 private static readonly SqlConnection conn = new SqlConnection(ConfigParameters.Instance.UIDConnection); public static long WriteLog(string sSource, string sMessage, int iErrorCode, EventLogEntryType xErrorType) { // Windows Logs if (ConfigParameters.Instance.WindowsLog) EventLog.WriteEntry(sSource, sMessage, xErrorType, iErrorCode); // SQL Logs // TODO return 0; } 

Thanks in advance:)

Answer 1

This is not a general way to share SqlConnection, and should only be used in special cases.

First, you are sure that resource pooling is a common template used to increase performance when working with sockets, network streams, web services ...

But especially for SqlConnection, you don’t need to worry about it, because the infrastructure already does this for you, thanks to the Sql Connection Pool .

Whenever a user calls Open in a connection, the pool searches for an available connection in the pool. If there is a merged connection, it returns it to the caller, rather than opening a new connection. when the application calls Close on the connection, the pool will return it to the combined set of active connections instead of closing it. once the connection returns to the pool, it is ready to be reused the next Open call

You can consider SqlConnection as a wrapper around a real connection. Do not believe that creating a new SqlConnection is expensive: it is not, and many high-traffic websites are built with it.

The default strategy (at least for sql server) is that it will work automatically. You just need to know about closing your connection (using a block). There are also many settings for managing the pool.

The code also contains incorrect error management: if the connection is interrupted (DBA, network failure, ...), you will throw an exception during registration ... not ideal

To this end, I do not think that sharing a SQL connection is appropriate in your case. You will get much more performance using the asynchronous protocol library.

Do not focus on this now until you are sure that this is a real problem.

We must forget about little efficiency, say, about 97% of the time: premature optimization is the root of all evil, Donald Knuth