Multithreading with ADO.NET

Question

I want to select some data from tables at the same time. When I am trying to do this with parallel threads an exception occurs. Should I use different SqlConnection objects for every SqlCommand or is there any technique to share same SqlConnection between several threads and execute several SqlCommand at the same time without exception.

SqlConnection connection = new SqlConnection("...")
connection.Open();

List<Task> tasks = new List<Task>();

for (int i = 0; i < 10; i++)
{
    tasks.Add(Task.Run(() =>
    {
        var command = connection.CreateCommand();
        command.CommandText = "select top 1 * from Persons";
        var data = command.ExecuteReader();
        while (data.Read())
        {
            Console.WriteLine($"{data[0]} {data[1]} {data[2]}");
        }
    }));
}

await Task.WhenAll(tasks);

connection.Close();

Arguably you are going to be limited mostly by the IO performance of the database, so multi-threading is pointless anyway. Side note: you *must* dispose your connection, command and reader objects with `using` — Charlieface, Mar 13 '22 at 05:49

score 1 · Accepted Answer · answered Mar 12 '22 at 16:56

You need a separate connection for each concurrent operation. Since connections are pooled and reused, feel free to create a new one in the scope that you need it, and close it at the end (it is returned to the pool, not actually closed).

This is a limitation of the on-the-wire protocols for most (all?) database servers.

Multithreading with ADO.NET

1 Answers1