CODEWITHSUNDEEP
c#asynchronousado.netdataadapter
Adrian Anttila
Adrian Anttila

Reputation: 2038

What is the best practice to fill a DataSet or DataTable asynchronously in ASP.NET?

Given the following code, I have a few questions about best practices:

string connectionString = @"Server=(local)\sqlexpress; Database=master; Integrated Security=true;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    using (SqlDataAdapter dataAdapter = new SqlDataAdapter("select * from information_schema.columns", connection))
    {
        await connection.OpenAsync();

        DataTable dataTable = new DataTable();
        await Task.Run(() => dataAdapter.Fill(dataTable));
        return dataTable;
    }
}

I've seen several examples that wrap the whole block of code in a Task.Run() call, but I'm not sure if that's better than calling Task.Run() only for the DataAdapter.Fill() method, which feels more flexible and specific (only using await on async tasks).

Is the approach of calling Task.Run() on the Fill() method better than wrapping the whole code block?

Are there any negative side-effects to calling Fill() in Task.Run()? I'm thinking of something along the lines of losing call stack and/or exception information if Fill() has an error.

Is there a better way to write this in ASP.NET?

Upvotes: 12

Views: 9853

Answers (2)

John Kosh
John Kosh

Reputation: 1

Have you tried using a DataReader and the new ExecuteReaderAsync? What I recall is the SqlDataAdapter already uses a DataReader internally without the async. You may also want to skip using a DataTable altogether if possible to cut down on some overhead.

For small result sets that very rarely change such querying schema columns I would maybe just cache it on the web server in one of many ways. Heck, for schema changes you could even create a simple DDL trigger to update a single row table with a timestamp field to let you know a change has been made, then only run the query when necessary. Another option is CHECKSUM_AGG for tables other than schema tables.

Upvotes: 0

usr
usr

Reputation: 171246

In ASP.NET it almost never helps to use Task.Run. What exactly would it improve? It only introduces overhead.

That said, Fill will perform IO (draining the data reader) so you might want to call it asynchronously. Unfortunately, there is no async version of this method.

If you insist on using async IO (which is questionable for database access) you need to find an alternative. Maybe async Entity Framework or raw ADO.NET can help you.

Upvotes: 1

Related Questions