How to make this asynchronous? (async, await - C#, MVC)

Question

I just tried to make a part of my ASP .NET MVC Application asynchronous but even after reading and trying out a lot I don't really understand the async-await pattern and hope someone could give me a hint.

Basically I have the following:

1. A javascript call to my controller which fetches a partial View for a chart (this happens several times after page load for a lot of charts)

   // Load content of one chart
   my.loadChartContent = function (data, callback) {
   $.post("/Dashboard/GetChartContent/", data, function (datain) {
       if (isFunction(callback))
           callback(datain);
   });
   };
   

2. A controller action which calls a database method in another class

   public ActionResult GetChartContent(int id, bool isDraft = false)
   {
       //do something
       //...
   
       var chartdata = _dataService.GetDataForChart(chart, isDraft, _user.Id); //long running query
   
       //do something with chartdata
   
       return View(chartdata);
   }
   

3. The data class (_dataService) which fetches the data from the database with a SqlDataReader and loads a DataTable with that data.

The problem is that although the javascript is executed asynchronously the Controller-Actions seems to be blocked until a result from the DataService class returns. I would like to start all queries to the database and wait for the results asynchronously, so that long-running queries don't block shorter ones. (In SQL Server Profiler I see the queries as Begin-End, Begin-End, Begin-End => but it should be begin-begin-begin - end-end-end)

Where should I use async-await? Is it enough to use it (somehow) for my controller action or is it necessary to make the whole "call-chain" asynchronous?

Update: When I use SQLConnection.OpenAsync and ExecuteReaderAsync the code never finishes...and I don't get why?

    public async Task<Query> GetSqlServerData(Query query)
    {
        var dt = new DataTable();
        var con = new SqlConnection(query.ConnectionString);

        await con.OpenAsync();
        var cmd = new SqlCommand(query.SelectStatement, con);
        var datareader = await cmd.ExecuteReaderAsync();

        dt.Load(datareader);

        con.Close();
        query.Result = dt;
        return query;
    }

Answer 1

Thank you all for your answers. But the real answer is way simpler than I thought. Stephen pointed me in the right direction with the sentence

All HTTP calls are naturally asynchronous.

I knew that and generally that's true but obviously not when you're using sessions because ASP.NET MVC waits for each request (from one user) to be completed to synchronize the session. You can find a better explanation here: http://www.stefanprodan.eu/2012/02/parallel-processing-of-concurrent-ajax-requests-in-asp-net-mvc/

So - just decorating my controller with ...

[SessionState(System.Web.SessionState.SessionStateBehavior.ReadOnly)]

...did it and now I have the result I wanted - simultaneous queries on my SQL Server and a lot faster response time.

@Stephen - it's more than 2 simultaneous requests => http://www.browserscope.org/?category=network

Answer 2

The problem is that although the javascript is executed asynchronously the Controller-Actions seems to be blocked until a result from the DataService class returns. I would like to start all queries to the database and wait for the results asynchronously, so that long-running queries don't block shorter ones.

The term "asynchronous" can be applied a few different ways. In JavaScript, everything is asynchronous. All HTTP calls are naturally asynchronous.

If you're not seeing any sql overlapping, then you should be sure that you're calling loadChartContent multiple times (not calling it once at a time chained through callbacks or anything like that). The browser will limit you to two simultaneous requests, but you should see two requests at a time hitting your sql server.

Making your server side async won't help you, because async doesn't change the HTTP protocol (as I describe on my blog). Even if you make your database access async, your controller action will still have to wait for them to complete, and the browser will still limit you to two outstanding requests. Server-side async is useful for scaling your web servers, but if your web server is talking to a single SQL Server backend, then there's no point in scaling your web server because your web server is not the determining factor in your scalability.

On a side note, I suspect the reason your async code never finishes is because you're using Result or Wait; I explain this on my blog.

So, back to your original problem: if you want to start all the queries to the database, then you'll need to change API to be "chunky" instead of "chatty". I.e., add a GetChartContents action which takes multiple ids and runs all of those queries in parallel. I'd recommend using async database methods with something like this:

public async Task<ActionResult> GetChartContents(int[] ids, bool isDraft = false)
{
  var charts = ...;
  var chartTasks = Enumerable.Range(0, ids.Length)
      .Select(i => _dataService.GetDataForChartAsync(charts[i], isDraft, _user.Id))
      .ToArray();
  var results = await Task.WhenAll(chartTasks);
  ...
  return View(results);
}

Answer 3

I think this tutorial gives a pretty good starting point for what you're trying to do.

http://www.asp.net/mvc/tutorials/mvc-4/using-asynchronous-methods-in-aspnet-mvc-4

Answer 4

If you have:

public async Task<Query> GetSqlServerData(Query query)
{
    var dt = new DataTable();
    var con = new SqlConnection(query.ConnectionString);

    await con.OpenAsync();
    var cmd = new SqlCommand(query.SelectStatement, con);
    var datareader = await cmd.ExecuteReaderAsync();

    dt.Load(datareader);

    con.Close();
    query.Result = dt;
    return query;
}

Then use:

query1 = await GetSqlServerData(query1);
query2 = await GetSqlServerData(query2);
query3 = await GetSqlServerData(query3);