Based on this answer, here is my minimal Dapper extension to query SQL Server for a DataTable and optionally pass query parameters:
public static class DapperExtensions
{
public static async Task<DataTable> QueryDataTable(
string connectionString,
string sql,
object? param = null)
{
await using var con = new SqlConnection(connectionString);
await con.OpenAsync();
var table = new DataTable();
table.Load(await con.ExecuteReaderAsync(sql, param));
return table;
}
}
Example usage:
var table = await QueryDataTable(
"Server=myServer;Database=myDb;User Id=myUser;Password=myPw",
"SELECT * FROM Customers WHERE ID > @id",
new { id = 5 });
Another example:
var table = await QueryDataTable(
"Server=myServer;Database=myDb;User Id=myUser;Password=myPw",
"SELECT * FROM Customers WHERE IsLocked = 0");
You might want to change SqlConnection to another database type like e.g. NpgsqlConnection for PostgreSQL.
To query only for a single DataRow, the Dapper extension class could be enhanced like this:
public static async Task<DataRow?> QueryDataRow(
string connectionString,
string sql,
object? param = null)
{
var table = await QueryDataTable(connectionString, sql, param);
return table.AsEnumerable().FirstOrDefault();
}
And to finish this chain, although it makes no sense at all in the Dapper context, to query for a single value, the Dapper extension class could also contain this method:
public static async Task<object?> QueryValue(
string connectionString,
string sql,
object? param = null)
{
var row = await QueryDataRow(connectionString, sql, param);
var result = row?[0];
return result == DBNull.Value ? null : result;
}
Additionally, the QueryValue could also get a generic overload:
public static async Task<T?> QueryValue<T>(
string connectionString,
string sql,
object? param = null)
{
var value = await QueryValue(connectionString, sql, param);
return value == null ? default : (T)value;
}
