Dapper Async Methods Discover How to Execute Query Asynchronously
Description
Dapper has several methods that allow you to execute asynchronous queries. To use the async functionality, you need to pass in a System.Data.IDbConnection
and call one of the extension methods provided by Dapper.
Dapper extends the IDbConnection
interface with the following Async (asynchronous) methods.
- ExecuteAsync: It executes a query asynchronously and returns the number of rows affected.
- QueryAsync: It executes a query asynchronously and returns the results of the query as an
IEnumerable<T>
. - QueryFirstAsync: It executes a query asynchronously and returns the first result of the query.
- QueryFirstOrDefaultAsync: It executes a query asynchronously and returns the first result of the query, or the default value for the type T if no result is found.
- QuerySingleAsync: It executes a query asynchronously and returns a single result of the query.
- QuerySingleOrDefaultAsync: It executes a query asynchronously and returns a single result of the query, or the default value for the type T if no result is found.
- QueryMultipleAsync: It executes multiple queries asynchronously within the same command and maps the results to strong entities.
We only added a non-asynchronous version in this tutorial to make it easier to read.
ExecuteAsync
The ExecuteAsync
method can execute a query one or multiple times asynchronously and return the number of affected rows. The following example shows how to use the ExecuteAsync
method by inserting a customer
string sql = "INSERT INTO Customers (CustomerName) VALUES (@CustomerName);"; using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools())) { var affectedRows = await connection.ExecuteAsync(sql, new {CustomerName = "Mark"}).ConfigureAwait(false); Console.WriteLine(affectedRows); var customers = await connection.QueryAsync<Customer>("SELECT * FROM CUSTOMERS WHERE CustomerName = 'Mark'").ConfigureAwait(false); FiddleHelper.WriteTable(customers); }
Try it: .NET Core
QueryAsync
The QueryAsync
can execute a query and map the result asynchronously.
string sql = "SELECT TOP 10 * FROM OrderDetails"; using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools())) { var orderDetails = await connection.QueryAsync<OrderDetail>(sql).ConfigureAwait(false); Console.WriteLine(orderDetails.Count()); FiddleHelper.WriteTable(orderDetails); }
Try it: .NET Core
QueryFirstAsync
The QueryFirstAsync
can execute a query and map asynchronously the first result.
string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;"; using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools())) { var orderDetail = await connection.QueryFirstAsync<OrderDetail>(sql, new {OrderDetailID = 1}).ConfigureAwait(false); FiddleHelper.WriteTable(new List<OrderDetail>() { orderDetail } ); }
Try it: .NET Core
QueryFirstOrDefaultAsync
The QueryFirstOrDefaultAsync
can execute a query and map asynchronously the first result, or a default value if the sequence contains no elements.
string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;"; using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools())) { var orderDetail = await connection.QueryFirstOrDefaultAsync<OrderDetail>(sql, new {OrderDetailID = 1}).ConfigureAwait(false); FiddleHelper.WriteTable(new List<OrderDetail>() { orderDetail } ); }
Try it: .NET Core
QuerySingleAsync
The QuerySingleAsync
can execute a query and map asynchronously the first result and throws an exception if there is not exactly one element in the sequence.
string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;"; using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools())) { connection.Open(); var orderDetail = await connection.QuerySingleAsync<OrderDetail>(sql, new {OrderDetailID = 1}).ConfigureAwait(false); FiddleHelper.WriteTable(new List<OrderDetail>() { orderDetail } ); }
Try it: .NET Core
QuerySingleOrDefaultAsync
The QuerySingleOrDefaultAsync
can execute a query and map asynchronously the first result, or a default value if the sequence is empty; this method throws an exception if there is more than one element in the sequence.
string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;"; using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools())) { var orderDetail = await connection.QuerySingleOrDefaultAsync<OrderDetail>(sql, new {OrderDetailID = 1}).ConfigureAwait(false); FiddleHelper.WriteTable(new List<OrderDetail>() { orderDetail } ); }
Try it: .NET Core
QueryMultipleAsync
The QueryMultipleAsync
can execute multiple queries within the same command and map results asynchronously.
var sql = "SELECT TOP 3 * FROM Orders; SELECT TOP 3 * FROM OrderDetails;"; using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools())) { connection.Open(); using (var multi = await connection.QueryMultipleAsync(sql).ConfigureAwait(false)) { var orders = multi.Read<Order>().ToList(); var orderDetails = multi.Read<OrderDetail>().ToList(); FiddleHelper.WriteTable(orders); FiddleHelper.WriteTable(orderDetails); } }
Try it: .NET Core
ZZZ Projects