Querying Multiple Rows With Dapper

You can use Dapper to access a database and write queries in a more object-oriented manner. For example, it allows you to execute SQL commands and map the resulting data into a list of objects. With Dapper, you can easily select data from a database and project the result into a list of objects.

Dapper provides several methods for selecting data, depending on how you want to work with the data you retrieve.

To select data from a table or view using Dapper, you need to create an instance of the IDbConnection interface. Then you can use the Query<T>() or QueryAsync<T> method to execute the SELECT query and store the results in a list of objects.

Dapper Query

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> and the Query<T> returns an IEnumerable of the type specified by the type argument. The following examples query a Product table.

For example, suppose you have a database table called Product. You could use the following code to select all rows from the Product table and project them into a dynamic list of objects:

var sql = "SELECT * FROM Product WHERE CategoryID = @categoryID";
var products = connection.Query(sql, new { categoryID = 1 }).ToList();

foreach(var product in products)
{
	Console.WriteLine($"ProductID: {product.ProductID}; Name: {product.Name}");
}	

Online Example

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 ProductId instead of ProductID, 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 the Query<T> methods that takes a type parameter representing the data type that the query returns.

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

public class Product
{
	public int ProductID { get; set; }
	public int CategoryID { get; set; }
	public string Name { get; set; }
	public string Description { get; set; }		
}

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

var sql = "SELECT * FROM Product WHERE CategoryID = @categoryID";
var products = connection.Query<Product>(sql, new { categoryID = 1 }).ToList();

foreach(var product in products)
{
	Console.WriteLine($"ProductID: {product.ProductID}; Name: {product.Name}");
}

Online Example

Dapper QueryAsync

To execute the query asynchronously, Dapper provides the QueryAsync method, an asynchronous version of the Query method.

var sql = "SELECT * FROM Product WHERE CategoryID = @categoryID";
var products = (await connection.QueryAsync(sql, new { categoryID = 1 })).ToList();

foreach(var product in products)
{
	Console.WriteLine($"ProductID: {product.ProductID}; Name: {product.Name}");
}	

Online Example

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

using (var connection = new SqlConnection(connectionString))
{
    var sql = "SELECT * FROM Customers";
    var customers = await connection.QueryAsync<Customer>(sql);
	
    foreach(var customer in customers)
    {
        Console.WriteLine($"{customer.CustomerID} {customer.CompanyName}");
    }
}

Online Example

Related Articles

• Querying
• Query Unbuffered Async
• Stored Procedure
• Using Parameters
• Buffered & Unbuffered Queries