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();

Answer 1

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.