How to make parallel database calls and bind to model ASP.NET Core MVC

Question

I'm using ASP.NET Core MVC 2.0 and I've got an API that calls a method which opens a DB connection and inserts values into a model.

I'm hitting the same method 7 times and I'm just passing different information into it the parameters to build out my model. I HAVE to hit it seven different times, no rewrite of code will prevent this.

Instead of creating a loop and calling that method I wish to make parallel calls to the db so the response is faster. I've found multiple articles explaining how to do this e.g. How to properly make asynchronous / parallel database calls but there's enough difference that I can't seem to get it to work.

Following is the my method hitting the DB:

    public async Task<RS_Model> Get_Results(RequestS_Model values)
    {
       //Deleted a bunch of code to keep it simple
        string strQS = @"EXEC Get param1,param2,etc";

        using (SqlConnection conSQL = new SqlConnection(connectionString))
        {
            using (SqlCommand cmdSQL = new SqlCommand(strQS, conSQL))
            {
                conSQL.Open();

                using (SqlDataReader dtrSQL = cmdSQL.ExecuteReader())
                {
                    while (dtrSQL.Read())
                    {
                        Double.TryParse(dtrSQL["Value1"].ToString(), out dblVal1);

                    } //Ends While
                } //end SQLDataReader
            } //Ends cmdSQL
        } //ends using

        results.Price = dblVal1;

        return  results;
    } //Ends Get Results

My IActionResult for the api is:

    [HttpGet]
    public async Task<IActionResult> Get([FromQuery] RequestS_Model values)
    {
        SV_Results Results = new SV_Results();

        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }
        RS_Model model = new RS_Model();

        model.dblr[0] = await  Results.Get_Results(values);
        values.Parm1 = 2;
        model.dblr[1] = await    Results.Get_Results(values);
        values.Parm1 = 3;
        model.dblr[2] = await    Results.Get_Results(values);
        values.Parm1 = 4;
        model.dblr[3] = await    Results.Get_Results(values);
        values.Parm1 = 5;
        model.dblr[4] = await    Results.Get_Results(values);
        values.Parm1 = 6;
        model.dblr[5] = await    Results.Get_Results(values);
        values.Parm1 = 7;
        model.dblr[6] = await    Results.Get_Results(values);

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


        return new OkObjectResult(model);
    } //IActionResults

I know that I've forced them into a synchronous call be doing what I am, but I can't seem to make the 7 calls asynchronous and then wait for them to be all done before I build my final model. The final response needs to be in Json, but I haven't even gotten that far yet.

The DB queries themself are inherently single-tasked. There are implicit, row to table level locks around every Query. As long as you can do it all in a single execute Query, there will not be issues. Once you split it over multiple queries (like getting a value and writing it), that is when race conditions can occur. So try to keep any math to the Query Langauge. — Christopher
– Christopher, Commented Dec 14, 2019 at 1:08
Mostly I am really confused why you do not just make a version of GetResults that takes an array and does some basic loop/wair all on the sub-operations. Or use a table valued Stored Procedure. — Christopher
– Christopher, Commented Dec 14, 2019 at 1:12
@Chris, in your question you pass the same values object to Results.Get_Results(), that is slightly confusing. Please elaborate 1) how to you bind model with the sql query (inline into SQL string, pass as parameters to command...) 2) why do you pass RequestS_Model 7 times unmodified (where it is changed ) — fenixil
– fenixil, Commented Dec 14, 2019 at 1:17
@fenixil I deleted that code out to try and make it more simple to read. However, you are correct. In my quest to make it a little more simple to follow I added a little confusion. — Chris
– Chris, Commented Dec 14, 2019 at 2:34
@Christopher This stored procedure only makes a couple select statements (but no update, alter, delete etc) against a database table so a chance of any row locks are at a minimum. The rest of the 2,000 lines of code is doing lots and lots of math. I can't rewrite the procedure because of some of the legacy requirements surrounding it. — Chris
– Chris, Commented Dec 14, 2019 at 2:44

John Wu · Accepted Answer · 2019-12-14 02:04:17Z

2

Instead of this:

 model.dblr[0] = await Results.Get_Results(values);
 model.dblr[1] = await Results.Get_Results(values);
 model.dblr[2] = await Results.Get_Results(values);
 model.dblr[3] = await Results.Get_Results(values);
 model.dblr[4] = await Results.Get_Results(values);
 model.dblr[5] = await Results.Get_Results(values);
 model.dblr[6] = await Results.Get_Results(values);

Create a list of tasks and await them as a group:

var tasks = Enumerable.Range(0,7).Select( i => Results.Get_Results(values) ).ToList();
await Task.WhenAll(tasks);
for (int i=0; i<7; i++) model.dblr[i] = tasks[i].Result;

answered Dec 14, 2019 at 2:04

John Wu

52.5k8 gold badges50 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to make parallel database calls and bind to model ASP.NET Core MVC

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related