How to Execute a SELECT query with Dapper in C#

Below is an example using a Books table:

using System.Data.SqlClient;
using Dapper;

public IEnumerable<Book> GetAllBooks()
{
    using (var con = new SqlConnection(connectionString))
    {
        return con.Query<Book>("SELECT * FROM Books");
    }
}

Note: If there are no results, it will return an empty list.

Dapper simplifies database interaction by reducing repetitive code, such as mapping query results to objects. In the example above, the query results are automatically mapped from the Books table to a list of Book objects. Dapper handles mapping without the need for configuration, using reflection to map columns to properties.

Adding query parameters

To execute parameterized queries with Dapper:

1. Put parameter placeholders in the query, such as @genre.
2. Pass an object containing the parameter values (the names must match the placeholders).

Here’s an example that queries books by genre:

using Dapper;

public IEnumerable<Book> GetBooksByGenre(string genre)
{
    using (var con = new SqlConnection(connectionString))
    {
        return con.Query<Book>("SELECT * FROM Books WHERE Genre=@genre", 
            param: new { genre });
    }
}

Dapper automatically maps the properties of the param object to the placeholders (e.g., @genre) in the query. You can pass in any object, including anonymous types as shown above.

Note: Parameterized queries improve performance and protect against SQL Injection. Always use parameters instead of concatenating values directly into the query.

Get the results as a List object

The Query() method returns an IEnumerable, but by default, it returns a List object unless buffered=false is passed. If you specifically want the result as a List, you can use AsList():

using Dapper;

public List<Book> GetAllBooks()
{
    using (var con = new SqlConnection(connectionString))
    {
        return con.Query<Book>("SELECT * FROM Books").AsList();
    }
}

Don’t use ToList() as it unnecessarily creates a new copy of the list. Instead, use AsList(), which simply casts the result to a List.