Most of the time I use Dapper in some form or another, mainly through my Dapper.Repository or Dapper.QX libraries for all data access. Sometimes, I need to use ADO.NET DataTables for really dynamic things, such as in my SqlIntegration project. The syntax for querying DataTables isn't terribly convenient, so that's why this library exists.

Nuget package: DataTables.Library (formerly AdoUtil.Library)

Version 2.x of this library offers these extension methods:

DataTables.Library.DataTableExtensions DataTableExtensions.cs

Methods

• Task<string> SerializeAsync (this DataTable dataTable, [ JsonSerializerOptions options ])
• Task FromJsonAsync (this DataTable dataTable, string json, [ JsonSerializerOptions options ])

DataTables.Library.Abstract.QueryRunner QueryRunner.cs

Methods

• DataSet QueryDataSet (IDbConnection connection, string sql, [ object parameters ], [ CommandType? commandType ])
• Task<DataSet> QueryDataSetAsync (IDbConnection connection, string sql, [ object parameters ], [ CommandType? commandType ])
• DataTable QueryTable (IDbConnection connection, string sql, [ object parameters ], [ CommandType? commandType ])
• Task<DataTable> QueryTableAsync (IDbConnection connection, string sql, [ object parameters ], [ CommandType? commandType ])
• DataTable QuerySchemaTable (IDbConnection connection, string sql, [ object parameters ])
• Task<DataTable> QuerySchemaTableAsync (IDbConnection connection, string sql, [ object parameters ])
• Task<string> SqlCreateTableAsync (IDbConnection connection, string schema, string name, string sql, [ object parameters ])
• IEnumerable<string> SqlCreateColumns (DataTable schemaTable)

Tips

• Yes, you can use table value parameters. See the test for an example
• The serialization methods are there because the built-in XML support does not seem to work in async methods.
• The parameters object on any of the methods can be in anonymous object form (Dapper-style), or you can use Dictionary form
• Because the *Async methods here use await Task.Run, don't use these in ASP.NET controllers, according to .NET performance best practices. Use Dapper instead, which has native async methods.