Selecting Multiple Rows Of Data With Dapper

Dapper provides a number of methods for selecting data, depending on how you want to work with the data that you retrieve.

Method Description Exception(s)
Query Returns an enumerable of dynamic types
Query<T> Returns an enumerable of the type specified by the T parameter
QueryAsync Returns an enumerable of dynamic types asynchronously
QueryAsync<T> Returns an enumerable of the type specified by the T parameter asynchronously

The Query methods

Query and Query<T>

The primary methods used for selecting data are Query and Query<T> and their async counterparts. The difference between Query and Query<T> is that Query returns results as an IEnumerable<dynamic> whereas Query<T> returns an IEnumerable of the type specified by the type argument. The following examples query the Customers table from the ubiquitous Microsoft Northwind sample database (in SQLite format for portability). The schema of the table is as follows:

Northwind Customers Schema

The first example returns dynamic types:

using (var connection = new SQLiteConnection(connString))
{
    var sql = "select * from customers";
    var customers = connection.Query(sql);
    foreach(var customer in customers)
    {
        Console.WriteLine($"{customer.CustomerID} {customer.CompanyName}");
    }
    Console.ReadLine();
}

Note that there is no explicit call to open the connection in the code above. Dapper will check the state of the connection and open it if it is closed.

Working with dynamic types should ordinarily be avoided because the risk of introducing run-time errors is high. It can be easy to mistype a property name, especially as they are case sensitive. For example, if you referred to the key column as CustomerId instead of CustomerID, there would be no help from Intellisense, or exceptions raised. Nothing would be output to the console window at all. Therefore the recommendation is to use one of the Query methods that takes a type parameter representing the data type that the query returns.

The next example works with a Customer type, which, following the table schema, has the following definition:

public class Customer
{
    public string CustomerId { get; set; }
    public string CompanyName { get; set; }
    public string ContactName { get; set; }
    public string ContactTitle { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string Region { get; set; }
    public string PostalCode { get; set; }
    public string Country { get; set; }
    public string Phone { get; set; }
    public string Fax { get; set; }

    public virtual ICollection<Order> Orders { get; set; } = new HashSet<Order>();
}

The example is identical to the first one except for the presence of the <Customer> type parameter:

using (var connection = new SQLiteConnection(connString))
{
    var sql = "select * from customers";
    var customers = connection.Query<Customer>(sql);
    foreach(var customer in customers)
    {
        Console.WriteLine($"{customer.CustomerId} {customer.CompanyName}");
    }
    Console.ReadLine();
}

Asynchronous Options

The asynchronous version of the Query method is QueryAsync:

using (var connection = new SQLiteConnection(connString))
{
    var sql = "select * from customers";
    var customers = await connection.QueryAsync(sql);
    foreach(var customer in customers)
    {
        Console.WriteLine($"{customer.CustomerID} {customer.CompanyName}");
    }
    Console.ReadLine();
}

The asynchronous version of the Query<T> method is QueryAsync<T>:

using (var connection = new SQLiteConnection(connString))
{
    var sql = "select * from customers";
    var customers = await connection.QueryAsync<Customer>(sql);
    foreach(var customer in customers)
    {
        Console.WriteLine($"{customer.CustomerId} {customer.CompanyName}");
    }
    Console.ReadLine();
}
Last updated: 21/05/2019 13:53:51