Fill DataTable asynchronously?

Question

I have the following function in a .NET Core 2.0 app.

public DataTable CallDb(string connStr, string sql)
{
    var dt = new DataTable();
    var da = new SqlDataAdapter(sql, connStr);
    da.Fill(dt);
    return dt;
}

How to convert it to an async function?

public async Task<DataTable> CallDb(string connStr, string sql)
{
    var dt = new DataTable();
    var da = new SqlDataAdapter(sql, connStr);
    da.Fill(dt); // No FillAsync to await?
    return dt;
}

I need to use DataTable because the sql may return data with different schema. Any better way to handle the dynamical schema?

Answer 1

SqlDataAdapter was never updated to include the TPL version of the methods. You could do this:

await Task.Run(() => da.Fill(dt));

But that would be creating a thread that would do nothing useful.

A good approach would be to use something like this:

public async Task<DataTable> CallDb(string connStr, string sql)
{
    var dt = new DataTable();
    var connection = new SqlConnection(connStr);
    var reader = await connection.CreateCommand().ExecuteReaderAsync();
    dt.Load(reader);

    return dt;
}

Of course, some changes like using statements should be made. However, here you are using asynchronous calls the right way.

Answer 2

Although the initial call to ExecuteReaderAsync() will not block in this case, dt.Load(reader) probably does the equivalent of reader.Read() rather than await reader.ReadAsync(), and may block the calling thread while retrieving rows.

If you do need a DataTable for use with an external API, or because you don't know the field definitions in advance, but require fully asynchronous behaviour, you might be better off to use your own code to construct a DataTable, add the required columns e.g. based on reader.GetName() and reader.GetFieldType(), and then populate it with rows in a loop using await reader.ReadAsync() and dt.Rows.Add().

Answer 3

Building off Camilo Terevinto answer I wanted to asynchronously handle loading the data table from the async reader (DataTable.Load).

I created a SqlCommand extension which reads the data in one record at a time. Although the data is still loaded synchronously the data is at least iterated asynchronously one record at a time using ReadAsync.

public static class SqlExtensions
{
    public static async Task<DataTable> FillDataTableAsync(this SqlCommand cmd)
    {
        DataTable dt = null;
        using (var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SequentialAccess))
        {

            DataTable schemaTable = await reader.GetSchemaTableAsync();
            dt = new DataTable();
            foreach (DataRow row in schemaTable.Rows)
                dt.Columns.Add(row.Field<string>("ColumnName"), row.Field<Type>("DataType"));


            while (await reader.ReadAsync())
            {
                DataRow dr = dt.Rows.Add();
                foreach (DataColumn col in dt.Columns)
                    dr[col.ColumnName] = reader[col.ColumnName];
            }
        }
        return dt;
    }
}

and use it like so;

DataTable dt = null;
using (SqlCommand cmd = new SqlCommand("SELECT * FROM TABLE", conn))
{
    dt = await cmd.FillDataTableAsync();
}

If I need to asynchronously update the DataTable I delete the records then use SqlBulkCopy WriteToServerAsync(dt) inside a transaction;

using (SqlCommand cmd = new SqlCommand("DELETE FROM TABLE", transaction.Connection))
{
    cmd.Transaction = transaction;
    await cmd.ExecuteNonQueryAsync();

}
using (SqlBulkCopy copy = new SqlBulkCopy(transaction.Connection, SqlBulkCopyOptions.Default, transaction))
{
    copy.DestinationTableName = "TABLE";
    await copy.WriteToServerAsync(dt);
}

Note: I have not tested this for efficiency vs SqlDataAdapter.