0

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();
Theodor Zoulias
  • 24,585
  • 5
  • 40
  • 69
  • Could you give more details about the errors that are occurring? Also showing the code that throws these errors would be helpful. – Theodor Zoulias Mar 12 '22 at 14:50
  • 3
    Use a connection per thread. Go even farther don't try to reuse connections at all. The system has a connection pooling system. Open as late as possible and close them as early as possible and use a new instance afterwards. And please put the additional info into your questions and not in the comments. Questions are editable. – Ralf Mar 12 '22 at 15:10
  • ......Edited....... –  Mar 12 '22 at 16:15
  • 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

1 Answers1

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.

Stephen Cleary
  • 406,130
  • 70
  • 637
  • 767