How to correctly make asynchronous / parallel database calls

Question

I'm looking for a suitable way to handle multiple database queries that are likely to work at the same time. Queries are simply stored procedures that either insert or merge using data that is programmatically collected in DataTables in my ASP.NET MVC application.

Of course, I saw some information about async and await , and this is similar to what I will need to do, but I don't have a clear understanding of how to implement it. Some information suggests that the calls will continue to be sequential, and that they will still wait differently. It seems pointless.

Ultimately, I would like the solution to allow me to run all the requests in the time needed to complete the longest procedure. I would like all queries to return the number of records affected (as of now).

Here is what I'm doing right now (this is by no means 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 this code is in the same method that collects data for DataTables. My limited understanding of async would make me think that I would need to extract the previous code into my own method. Then I would call this method and await return. However, I don’t even know how to start it.

I have not done asynchronous / parallel / multi-threaded coding before. This situation only makes me feel that now is the right time for the transition. This suggests that I would like to learn the best way, instead of weaning the wrong way.

Answer 1

Here is an example of how you will do this:

Here I create two methods for transferring two operations, you need to do the same for 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); } } 

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

And then your original method will 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 support this method synchronously. Another option (actually the best) is to convert the method to asynchronous, for example:

 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 that would mean that you should asynchronously call it ( async ).