C# Parallel.Foreach SQL 查询有时会导致连接

Parallel.Foreach SQL querying sometimes results in Connection

提问by Theodosius Von Richthofen

I need to speed up performing 12 queries in my application. I switched from a regular foreach to Parallel.ForEach. But sometimes I get an error saying "ExecuteReader requires an open and available connection The connection's current state is connecting." It is my understanding that since many of the 12 queries are using the same InitialCatalog, there is not really a new connection for of the 12 and that may be the problem? How can i fix this? "sql" is a list of type "Sql"- a class is just a string name, string connectiona and a List of queries. Here is the code:

我需要加快在我的应用程序中执行 12 个查询的速度。我从常规的 foreach 切换到 Parallel.ForEach。但有时我会收到一条错误消息，提示“ExecuteReader 需要一个打开且可用的连接。连接的当前状态是连接。” 我的理解是，由于 12 个查询中的许多查询都使用相同的 InitialCatalog，因此 12 个查询中并没有真正的新连接，这可能是问题所在？我怎样才能解决这个问题？“sql”是一个“Sql”类型的列表——一个类只是一个字符串名称、字符串连接a和一个查询列表。这是代码：

 /// <summary>
    /// Connects to SQL, performs all queries and stores results in a list of DataTables
    /// </summary>
    /// <returns>List of data tables for each query in the config file</returns>
    public List<DataTable> GetAllData()
    {
        Stopwatch sw = new Stopwatch();
        sw.Start();
        List<DataTable> data = new List<DataTable>();

         List<Sql> sql=new List<Sql>();

        Sql one = new Sql();
         one.connection = "Data Source=XXX-SQL1;Initial Catalog=XXXDB;Integrated Security=True";
         one.name = "Col1";
         one.queries.Add("SELECT Name FROM [Reports]");
         one.queries.Add("SELECT Other FROM [Reports2]");
         sql.Add(one);

        Sql two = new Sql();
         two.connection = "Data Source=XXX-SQL1;Initial Catalog=XXXDB;Integrated Security=True";
         two.name = "Col2";
         two.queries.Add("SELECT AlternateName FROM [Reports1]");
         sql.Add(two);

         Sql three = new Sql();
         three.connection = "Data Source=YYY-SQL2;Initial Catalog=YYYDB;Integrated Security=True";
         three.name = "Col3";
         three.queries.Add("SELECT Frequency FROM Times");
         sql.Add(three);


        try
        {
            // ParallelOptions options = new ParallelOptions();
            //options.MaxDegreeOfParallelism = 3;
            // Parallel.ForEach(sql, options, s =>
            Parallel.ForEach(sql, s =>
            //foreach (Sql s in sql)
            {
                foreach (string q in s.queries)
                {
                    using (connection = new SqlConnection(s.connection))
                    {
                        connection.Open();
                        DataTable dt = new DataTable();
                        dt.TableName = s.name;
                        command = new SqlCommand(q, connection);
                        SqlDataAdapter adapter = new SqlDataAdapter();
                        adapter.SelectCommand = command;
                        adapter.Fill(dt);
                        //adapter.Dispose();

                        lock (data)
                        {
                            data.Add(dt);
                        }
                    }
                }
            }
            );
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString(), "GetAllData error");
        }

        sw.Stop();
        MessageBox.Show(sw.Elapsed.ToString());

        return data;
    }

Here's the Sql class I made that you'd need:

这是我创建的您需要的 Sql 类：

/// <summary>
/// Class defines a SQL connection and its respective queries
/// </summary>
public class Sql
{
    /// <summary>
    /// Name of the connection/query
    /// </summary>
    public string name { get; set; }
    /// <summary>
    /// SQL Connection string
    /// </summary>
    public string connection { get; set; }
    /// <summary>
    /// List of SQL queries for a connection
    /// </summary>
    public List<string> queries = new List<string>();
}

采纳答案by Dustin Kingen

I would refactor out your business logic (connecting to the database).

我会重构您的业务逻辑（连接到数据库）。

public class SqlOperation
{
    public SqlOperation()
    {
        Queries = new List<string>();
    }

    public string TableName { get; set; }
    public string ConnectionString { get; set; }
    public List<string> Queries { get; set; }
}

public static List<DataTable> GetAllData(IEnumerable<SqlOperation> sql)
{
    var taskArray =
        sql.SelectMany(s =>
            s.Queries
             .Select(query =>
                Task.Run(() => //Task.Factory.StartNew for .NET 4.0
                    ExecuteQuery(s.ConnectionString, s.TableName, query))))
            .ToArray();

    try
    {
        Task.WaitAll(taskArray);
    }
    catch(AggregateException e)
    {
        MessageBox.Show(e.ToString(), "GetAllData error");
    }

    return taskArray.Where(t => !t.IsFaulted).Select(t => t.Result).ToList();
}

public static DataTable ExecuteQuery(string connectionString, string tableName, string query)
{
    DataTable dataTable = null;

    using (var connection = new SqlConnection(connectionString))
    {
        dataTable = new DataTable();
        dataTable.TableName = tableName;
        using(var command = new SqlCommand(query, connection))
        {
            connection.Open();

            using(var adapter = new SqlDataAdapter())
            {
                adapter.SelectCommand = command;
                adapter.Fill(dataTable);
            }
        }
    }

     return dataTable;
}

回答by faester

Ado.Net has a pretty clever connection pooling, so in general you should just open connections and close connections per command and let the pool handle whether they are really being opened or closed.

Ado.Net 有一个非常聪明的连接池，所以通常你应该只打开连接和关闭每个命令的连接，让池处理它们是否真的被打开或关闭。

So one connection per command:

所以每个命令一个连接：

  Parallel.ForEach(sql, s=>
            //foreach (Sql s in sql)
            {
                foreach (string q in s.queries)
                {
                    using (connection = new SqlConnection(s.connection))
                    {
                        connection.Open();
                        DataTable dt = new DataTable();
                        dt.TableName = s.name;
                        command = new SqlCommand(q, connection);
                        SqlDataAdapter adapter = new SqlDataAdapter();
                        adapter.SelectCommand = command;
                        adapter.Fill(dt);
                        //adapter.Dispose();

                        lock(data){
                            data.Add(dt);
                        }
                    }
                }
            }

回答by Vinod Kumar Gupta

You can also use MultipleActiveResultSets=true;in connection string to support multiple readers

您也可以使用MultipleActiveResultSets=true; 在连接字符串中支持多个阅读器