.net - Populate C# DataTable Asynchronously

Question

I'm adding async implementations to all my SQL base classes used in my WebAPI projects. I'm fairly new to the TAP paradigm so I'm still learning.

I know, thanks to other posts, that spawning a thread via Task.Run() does not have any performance benefits in an ASP.NET context. So I'm being extra careful with my implementations.

I've changed my QueryExecutor method to the async implementation below. But cannot figure out what the best way to load the DataTable is. I'm guessing I would ideally want to use reader.ReadAsync() to populate the DataTable but it seems there isn't anything available in the .NET 4.5 framework for that.

So I wanted to ask would it be worth writing my own extension method such as DataTable.LoadAsync(DbDataReader reader)? I kind of don't want to if it can be helped since it won't be nearly as fool-proof as managed .Net code.

Let me know what you guys think.

private async Task<DataTable> ExecuteQueryInternalAsync(string commandText, CommandType commandType, SqlConnection sqlConnection, SqlTransaction transaction, params SqlParameter[] parameters)
{
    using (SqlCommand cmd = new SqlCommand(commandText, sqlConnection) { CommandType = commandType, CommandTimeout = this.config.MainConnectionTimeoutInSeconds })
    {
        if (transaction != null)
            cmd.Transaction = transaction;

        if (parameters != null)
        {
            foreach (var parameter in parameters)
            {
                if (parameter != null)
                {
                    if (parameter.Value == null)
                        parameter.Value = DBNull.Value;

                    cmd.Parameters.Add(parameter);
                }
            }
        }

        if (sqlConnection.State == ConnectionState.Closed)
            await sqlConnection.OpenAsync();

        using (var reader = await cmd.ExecuteReaderAsync())
        {
            //Want to use: reader.ReadAsync()
            var tb = new DataTable();
            tb.Load(reader);
            return tb;
        }
    }
}

Thanks

Answer 1

If you want an extension method, you can write directly on the command

public static class extensions
    {
         public async static Task<DataTable> ExecuteAndCreateDataTableAsync(this SqlCommand cmd)
         {
             using (var reader = await cmd.ExecuteReaderAsync().ConfigureAwait(false))
             {
                 var dataTable = reader.CreateTableSchema();
                 while (await reader.ReadAsync().ConfigureAwait(false))
                 {
                     var dataRow = dataTable.NewRow();
                     for (int i = 0; i < dataTable.Columns.Count; i++)
                     {
                         dataRow[i] = reader[i];
                     }
                     dataTable.Rows.Add(dataRow);
                 }
                 return dataTable;

             }
         }
         public static void LoadParams(this SqlCommand cmd, params SqlParameter[] parameters)
         {
             if (parameters != null)
             {
                 foreach (var parameter in parameters)
                 {
                     if (parameter != null)
                     {
                         if (parameter.Value == null)
                             parameter.Value = DBNull.Value;

                         cmd.Parameters.Add(parameter);
                     }
                 }
             }
         }


         private static DataTable CreateTableSchema(this SqlDataReader reader)
         {
             DataTable schema = reader.GetSchemaTable();
             DataTable dataTable = new DataTable();
             if (schema != null)
             {
                 foreach (DataRow drow in schema.Rows)
                 {
                     string columnName = System.Convert.ToString(drow["ColumnName"]);
                     DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"]));
                     dataTable.Columns.Add(column);
                 }
             }
             return dataTable;
         }
    }

and your medhod:

private async Task<DataTable> ExecuteQueryInternalAsync(string commandText, CommandType commandType, SqlConnection sqlConnection, SqlTransaction transaction, params SqlParameter[] parameters)
        {
            using (SqlCommand cmd = new SqlCommand(commandText, sqlConnection) { CommandType = commandType, CommandTimeout = this.config.MainConnectionTimeoutInSeconds })
            {
                if (transaction != null)
                    cmd.Transaction = transaction;

                cmd.LoadParams(parameters);

                if (sqlConnection.State == ConnectionState.Closed)
                    await sqlConnection.OpenAsync();

                var datatable =  await cmd.ExecuteAndCreateDataTableAsync();
                return datatable;
            }
        }