异步填充 C# DataTable [英] Populate C# DataTable Asynchronously

问题描述

我正在向 WebAPI 项目中使用的所有 SQL 基类添加 async 实现.我对 TAP 范式还很陌生，所以我仍在学习.

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.

我知道，多亏了其他帖子，通过 Task.Run() 生成线程在 ASP.NET 上下文中没有任何性能优势.所以我对我的实现格外小心.

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.

我已将我的 QueryExecutor 方法更改为下面的异步实现.但无法弄清楚加载 DataTable 的最佳方式是什么.我猜我最好使用 reader.ReadAsync() 来填充 DataTable，但似乎 .NET 4.5 框架中没有任何可用的东西.

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.

所以我想问一下是否值得编写我自己的扩展方法，例如DataTable.LoadAsync(DbDataReader reader)?如果它可以得到帮助，我有点不想，因为它几乎不会像托管的 .Net 代码那样万无一失.

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.

让我知道你们的想法.

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

谢谢

推荐答案

如果想要扩展方法，可以直接在命令上写

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

和你的方法:

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

这篇关于异步填充 C# DataTable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！