SqlConnection Thread-Safe?

Question

I have a Log class which put logs in Windows journal and in a SQL table. In order to optimize my code, I would like use only one SqlConnection.

In MSDN, it says: Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.

My question is :

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

Is it thread-safe ? If yes, when use Open() and Close()?

If no, how use properly SqlConnection?

Here is my full 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;
}

Answer 1

It's not a common way to share a SqlConnection, and it should be used only under special uses cases.

First, You're true that resource pooling is a common pattern used to improve performance when working with sockets, network streams, web services...

But especially for SqlConnection, you don't have to worry about this because the framework already do this for you, thanks to Sql Connection Pool.

Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call

You can consider SqlConnection as a wrapper around real connection. Do not beleive that instanciating a new SqlConnection is costly : it's not and many web sites with high trafic are built with it.

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

You code also contains an incorrect error management : if the connection is aborted (DBA, network failure, ...) you will throw exceptions when logging ... not ideal

At this end, I don't think that sharing a sql connection is appropriate in your case. You will gain much more perf using an async logging library.

Do not focus on this now until you're sure it's a real problem.

We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil, Donald Knuth