c# - Can I use Parallel.For with sql Commands?

Question

I have a class of range

public class avl_range
{
    public long start { get; set; }
    public long end { get; set; }
}

If I use a normal FOR works perfect, but have to wait for each command to finish and each query take 8 seconds, so 10 queries take 80 seconds.

In the Parallel version If I only print the ranges works perfect, but if try to execute the command say is already in progress.

{"An operation is already in progress."}

How can I solve this?

var numbers = new List<avl_range>();
using (var conn = new NpgsqlConnection(strConnection))
    {
        conn.Open();

        Action<avl_range> forEachLoop = number => //Begin definition of forLoop
        {
             // only the console write line works ok
            Console.WriteLine(number.start + " - " + number.end);

            using (var cmd = new NpgsqlCommand())
            {
                cmd.Connection = conn;                            
                cmd.CommandText = String.Format( "SELECT * FROM avl_db.process_near_link({0}, {1});"
                                                 , number.start
                                                 , number.end);
                // here cause the error.
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine(reader.GetString(0));
                    }
                }
            }
        };

        Parallel.ForEach(numbers, forEachLoop);
    }
 );

FYI: Im trying to solve this issue I post it before

Answer 1

An Npgsql connection can't be used concurrently - only one command may be running at any given point in time (in other words, no MARS support).

It may definitely make sense to open multiple connections to perform your queries in parallel. Although establishing a new physical connection is expensive, connection pooling is extremely lightweight, so there's very little overhead in reusing physical connections. The main reason not to do this is if you need your multiple operations to be in the same transaction.