Sharing a db connection between threads in a C# application?

Question

I have found there to be very little information on this topic, was hoping someone could direct me to some information and possible sample code -

Thanks!

Answer 1

I would say dont share the connection object itself, just create a new connection and let ADO.net handle connection pooling.

Answer 2

Generally connections are not thread safe(SqlConnection ,MySqlConnection and OracleConnection specifically mention that they are not thread safe).

Don't share a connection between threads.

Answer 3

There is no sample code out in the wild because (almost) no one does it because it is such a very, very, bad idea.

Answer 4

To respond to the actual parameters of the question, rather than dismissing them, I would wrap the DbCommand to help synchronize access to the connection (if and only if you absolutely have to).

public class SyncedDbCommand : DbCommand
{
    private DbCommand _cmd;
    private object _sync;

    public SyncedDbCommand(DbCommand cmd, object sync)
    {
        _cmd = cmd;
        _sync = sync;
    }

    // omitted basic proxy method overrides

    protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior)
    {
        Monitor.Enter(_sync);
        return _cmd.ExecuteReader();
    }

    public override int ExecuteNonQuery()
    {
        Monitor.Enter(_sync);
        return _cmd.ExecuteNonQuery();
    }

    public override object ExecuteScalar()
    {
        Monitor.Enter(_sync);
        return _cmd.ExecuteScalar();
    }

    protected override void Dispose(bool disposing)
    {
        if (disposing)
        {
            Monitor.Exit(_sync);
        }
        base.Dispose(disposing);
    }
}

To use the sample, you have to instantiate it with an actual DbCommand as well as some object instance that is shared across all usages of a connection. In the simplest use, you could even pass the connection object itself as the lock object. The instantiation should occur in a using statement. This does not absolve you from knowing exactly what your usage requirements are and how the Monitor class and locking work.

One way or another, you want to synchronize usage of the connection across threads, and the above is one approach to doing so.