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;
}

Montoya

I found this : codeproject.com/Articles/62249/… Can it be a solution ? – Montoya

Why do you want to use only one connection? That's going to increase your overhead to prevent threading issues. ADO.NET already has built-in connection pooling, and you can minimize the amount of time a given connection is used by wrapping it in a using statement. – Emogeneemollient

Also, this has nothing to do with WCF, so you can remove that tag. – Emogeneemollient

I put WCF because my webservice needs connect to SQL. But you're right. I can put the sqlConnection in instances of the object. But I'm afraid nb_threads*sqlconnection use a lot of memory. In another hand, if I use only a static sqlconnection, may the transactions will be longer. Do you think it's better to put the SqlConnection in the static method ? – Montoya

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

Aminoplast

I put the SqlConnection in the method :) – Montoya