DataAdapter Fill Async Exception

ARidder101 picture ARidder101 · Mar 6, 2017 · Viewed 10.7k times · Source

I have a set of async methods I wrote to handle a large amount of database pulls and compiles in a speedy manner. For the most part these work fantastic and have really worked wonders for my software. However, recently I have run into a small problem with the methods: Every now and then the user messes up and the time frames the software is pulling the data between become enormous and the data adapter times out before obtaining the information. Normally in a sync method you would use a try/catch to handle such issues but I have tried this to no avail. Is there a way to asynchronously handle exceptions to simply throw as a sync method would so my catch all try/catch can work properly?

This is an example of the data adapter async method i use:

private async Task<DataTable> WWQuery2Run
(string A, string B, string C, string D)
{
  using ( var conn = new System.Data.SqlClient.SqlConnection(ReportResources.ConnString) )
  {
    var temp = new DataTable();
    var DA = new SqlDataAdapter(string.Format(ReportResources.Instance.CureInfoQueries["WWQuery2"], A, B, C, D), conn);
    await Task.Run(() => DA.Fill(temp));
    return temp;
  }
}

EDIT:

I realized after all the trouble of trying to handle the exceptions from the timeout that it is not a good practice to work that way. I went ahead and added a method to calculate the duration before entering the shown async method and warning the user of the length and gave them an option to abort the compile. With that in place I increased the timeout of the query to an amount that should cover all but the worst of the worst scenarios for if the user wishes to continue. I also added to the description on the items within the program a calculated duration so they know that it went long before trying to query and compile.

Thank you @Murad Garibzada for your assistance.

Answer

William Xifaras picture William Xifaras · Mar 8, 2017

Along with increasing the command timeout, you can use a try / catch block. Since you are awaiting, control will not return to your calling code until WWQuery2Run has completed. If WWQuery2Run throws SomeException, it will be caught and handled by the code.

private async Task<DataTable> WWQuery2Run(string A, string B, string C, string D)
{
    try 
    {
        using ( var conn = new System.Data.SqlClient.SqlConnection(ReportResources.ConnString) )
        {
            var temp = new DataTable();
            var DA = new SqlDataAdapter(string.Format(ReportResources.Instance.CureInfoQueries["WWQuery2"], A, B, C, D), conn);
            await Task.Run(() => DA.Fill(temp));
            return temp;
        } 
    } 
    catch (SomeException ex)
    {
        // handle exception
    }
}