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.
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.
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.
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…