如何正确地进行异步/并行数据库调用

I'm looking for the proper way to handle multiple database calls that would likely benefit from running simultaneously. The queries are just to stored procedures that are either doing inserts or merges using data that is programmatically assembled into DataTables in my ASP.NET MVC app.

我正在寻找处理多个数据库调用的正确方法，这些调用可能会同时运行。查询仅针对使用在ASP.NET MVC应用程序中以编程方式组装到DataTable中的数据进行插入或合并的存储过程。

Of course I have seen some information on async and await, and that appears to be what I would need to do, but I don't have a clear understanding of how to implement it. Some information is saying that the calls would still be sequential, and that one would still be waiting on another to complete. That seems pointless.

当然我已经看到了关于异步和等待的一些信息，这似乎是我需要做的，但我对如何实现它没有清楚的理解。一些信息表明这些调用仍然是顺序调用的，并且仍然会等待另一个调用完成。这似乎毫无意义。

Ultimately, I would like a solution that allows me to run all the queries in the time it takes for the longest procedure to complete. I would like all the queries to return the number of records affected (as they do now) as well.

最后，我想要一个解决方案，允许我在完成最长程序所需的时间内运行所有查询。我希望所有查询都能返回受影响的记录数（就像现在一样）。

Here is what I have going on now (which is in no way parallel):

这就是我现在要做的事情（这绝不是平行的）：

// Variable for number of records affected
var recordedStatistics = new Dictionary<string, int>();

// Connect to the database and run the update procedure
using (var dbc = new SqlConnection(db.Database.Connection.ConnectionString))
{
    dbc.Open();

    // Merge One procedure
    using (SqlCommand cmd = new SqlCommand("MergeOneProcedure", dbc))
    {
        // 5 minute timeout on the query
        cmd.CommandTimeout = 300;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TVP", MergeOneDataTable);

        // Execute procedure and record the number of affected rows
        recordedStatistics.Add("mergeOne", cmd.ExecuteNonQuery());
    }

    // Merge Two procedure
    using (SqlCommand cmd = new SqlCommand("MergeTwoProcedure", dbc))
    {
        // 5 minute timeout on the query
        cmd.CommandTimeout = 300;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TVP", MergeTwoDataTable);

        // Execute procedure and record the number of affected rows
        recordedStatistics.Add("mergeTwo", cmd.ExecuteNonQuery());
    }

    // Merge Three procedure
    using (SqlCommand cmd = new SqlCommand("MergeThreeProcedure", dbc))
    {
        // 5 minute timeout on the query
        cmd.CommandTimeout = 300;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TVP", MergeThreeDataTable);

        // Execute procedure and record the number of affected rows
        recordedStatistics.Add("mergeThree", cmd.ExecuteNonQuery());
    }

    // Merge Four procedure
    using (SqlCommand cmd = new SqlCommand("MergeFourProcedure", dbc))
    {
        // 5 minute timeout on the query
        cmd.CommandTimeout = 300;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TVP", MergeFourDataTable);

        // Execute procedure and record the number of affected rows
        recordedStatistics.Add("mergeFour", cmd.ExecuteNonQuery());
    }

    // Merge Five procedure
    using (SqlCommand cmd = new SqlCommand("MergeFiveProcedure", dbc))
    {
        // 5 minute timeout on the query
        cmd.CommandTimeout = 300;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TVP", MergeFiveDataTable);

        // Execute procedure and record the number of affected rows
        recordedStatistics.Add("mergeFive", cmd.ExecuteNonQuery());
    }

    dbc.Close();
}

return recordedStatistics;

All of that code is within the same method that assembles the data for the DataTables. My limited understanding of async would lead me to believe that I would need to extract the previous code into its own method. I would then call that method and await the return. However, I don't even know enough about it to begin.

所有这些代码都在组装DataTables数据的相同方法中。我对异步的有限理解会让我相信我需要将以前的代码提取到自己的方法中。然后我会调用该方法并等待返回。但是，我甚至不知道它开始了。

I have never done any asynchronous/parallel/multithreaded coding before. This situation just makes me feel like it is the perfect time to jump in. That said, I would like to learn the best way, instead of having to unlearn the wrong way.

我之前从未做过任何异步/并行/多线程编码。这种情况让我觉得这是进入的最佳时机。也就是说，我想学习最好的方法，而不是忘记错误的方式。

1 个解决方案

#1

Here is an example of how you would do it:

以下是如何执行此操作的示例：

Here I am creating two methods to wrap two operations, you need to do the same for the other operations:

这里我创建两个方法来包装两个操作，你需要为其他操作做同样的事情：

public async Task<int> MergeOneDataTableAsync()
{
    // Merge One procedure
    using (SqlCommand cmd = new SqlCommand("MergeOneProcedure", dbc))
    {
        // 5 minute timeout on the query
        cmd.CommandTimeout = 300;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TVP", MergeOneDataTable);

        return await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
    }
}


public async Task<int> MergeTwoDataTableAsync()
{
    // Merge Two procedure
    using (SqlCommand cmd = new SqlCommand("MergeTwoProcedure", dbc))
    {
        // 5 minute timeout on the query
        cmd.CommandTimeout = 300;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TVP", MergeTwoDataTable);

        return await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
    }
}

Notice that I am using the ExecuteNonQueryAsync method to execute the query.

请注意，我使用ExecuteNonQueryAsync方法来执行查询。

And then your original method would look like this:

然后您的原始方法将如下所示：

using (var dbc = new SqlConnection(db.Database.Connection.ConnectionString))
{
    dbc.Open();

    Task<int> task1 = MergeOneDataTableAsync();
    Task<int> task2 = MergeTwoDataTableAsync();

    Task.WaitAll(new Task[]{task1,task2}); //synchronously wait

    recordedStatistics.Add("mergeOne", task1.Result);
    recordedStatistics.Add("mergeTwo", task2.Result);
}

Please note that I am keeping this method synchronous. Another option (actually a better one) is to convert the method into an asynchronous one like this:

请注意，我保持此方法同步。另一个选项（实际上是更好的选项）是将方法转换为异步方法，如下所示：

public async Task<Dictionary<string, int>> MyOriginalMethod()
{
    //...
    using (var dbc = new SqlConnection(db.Database.Connection.ConnectionString))
    {
        dbc.Open();

        Task<int> task1 = MergeOneDataTableAsync();
        Task<int> task2 = MergeTwoDataTableAsync();

        int[] results = await Task.WhenAll(new Task<int>[]{task1,task2});

        recordedStatistics.Add("mergeOne", results[0]);
        recordedStatistics.Add("mergeTwo", results[1]);
    }

    //...
    return recordedStatistics;
}

But this would mean that you have to invoke it asynchronously (async all the way).

但这意味着你必须异步调用它（异步一直）。