Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

.net - Populate C# DataTable Asynchronously

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...