DataAdapter Fill Async Exception

Question

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.

There is currently an effort to implement async functionality to DataAdapter.Fill in .NET at https://github.com/dotnet/runtime/issues/22109 — Lachlan Ennis, Jun 21 '20 at 22:56

William Xifaras · Answer 1 · 2017-03-08T16:00:46.467

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
    }
}

score 2 · Answer 2 · answered Sep 11 '21 at 08:05

Because your connection conn is wrapped in a using statement, there is a possibility that it is disposed before your call to Fill is executed.

Probably something like the following would work:

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

Murad · Answer 3 · 2017-03-06T18:57:25.900

0

You can increase adapter command timeout like below:

 SqlDataAdapter adapter= new SqlDataAdapter(strSQLString, conUS);
 adapter.SelectCommand.CommandTimeout=120;

Handling exception:

 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);


Task task = new Task(() => DA.Fill(temp));
 try
{
    task.Wait();
}
catch (Exception ae)
{



}

return temp;
}
}

edited Mar 06 '17 at 18:57

answered Mar 06 '17 at 16:43

Murad

523
4
17

That works, yes, but i would prefer it to time out and then alert the user there is a problem if possible. I get that i am probably asking for something that is not proper but my users are kind of dim and will assume there is an issue with the software instead of an operator error in the event the times get overboard. – ARidder101 Mar 06 '17 at 16:53
It is very simple, go to the link : https://msdn.microsoft.com/en-us/library/dd537614(v=vs.110).aspx – Murad Mar 06 '17 at 17:10
Could you give an example of how the methods in that link will work? I have tried several times to make that method work but to me it appears that the methods in that link are not for async methods/tasks and thus do nothing for my issue. – ARidder101 Mar 06 '17 at 17:36
Check the above answer. – Murad Mar 06 '17 at 18:57
I tried that method and several variations of it and all it does is infinitely stall my program. It never throws an exception. – ARidder101 Mar 06 '17 at 20:20
Task.Wait is a blocking call and that example from MSDN is not using async. – William Xifaras Mar 08 '17 at 15:42

DataAdapter Fill Async Exception

3 Answers3