Dapper - Async

Description

Dapper also extend the IDbConnection interface with Async (asynchronous) methods:

• ExecuteAsync
• QueryAsync
• QueryFirstAsync
• QueryFirstOrDefaultAsync
• QuerySingleAsync
• QuerySingleOrDefaultAsync
• QueryMultipleAsync

We only added a non-asynchronous version in this tutorial to make it easier to read.

ExecuteAsync

The ExecuteAsync can execute a command one or multiple times asynchronously and return the number of affected rows.

string sql = "INSERT INTO Customers (CustomerName) Values (@CustomerName);";
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    var affectedRows = connection.ExecuteAsync(sql, new {CustomerName = "Mark"}).Result;
    Console.WriteLine(affectedRows);
    var customer = connection.Query<Customer>("Select * FROM CUSTOMERS WHERE CustomerName = 'Mark'").ToList();
    FiddleHelper.WriteTable(customer);
}

Try it: .NET Core | .NET Framework

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 = connection.QueryAsync<OrderDetail>(sql).Result.ToList();
    Console.WriteLine(orderDetails.Count());
    FiddleHelper.WriteTable(orderDetails);
}

Try it: .NET Core | .NET Framework

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 = connection.QueryFirstAsync<OrderDetail>(sql, new {OrderDetailID = 1}).Result;
    FiddleHelper.WriteTable(new List<OrderDetail>() { orderDetail } );
}

Try it: .NET Core | .NET Framework

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 = connection.QueryFirstOrDefaultAsync<OrderDetail>(sql, new {OrderDetailID = 1}).Result;
    FiddleHelper.WriteTable(new List<OrderDetail>() { orderDetail } );
}

Try it: .NET Core | .NET Framework

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()))
{            
    var orderDetail = connection.QuerySingleOrDefaultAsync<OrderDetail>(sql, new {OrderDetailID = 1}).Result;
    FiddleHelper.WriteTable(new List<OrderDetail>() { orderDetail } );
}

Try it: .NET Core | .NET Framework

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 = connection.QuerySingleOrDefaultAsync<OrderDetail>(sql, new {OrderDetailID = 1}).Result;
    FiddleHelper.WriteTable(new List<OrderDetail>() { orderDetail } );
}

Try it: .NET Core | .NET Framework

QueryMultipleAsync

The QuerySingleOrDefaultAsync 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 = connection.QueryMultipleAsync(sql).Result)
    {
        var orders = multi.Read<Order>().ToList();
        var orderDetails = multi.Read<OrderDetail>().ToList();
        FiddleHelper.WriteTable(orders);
        FiddleHelper.WriteTable(orderDetails);
    }
}

Try it: .NET Core | .NET Framework