C# – Execute a SELECT query with Dapper

by

You can query the database with Dapper by using Query() with a SELECT, specifying the type to map the results to, and optionally adding parameters. Here’s an example:

using System.Data.SqlClient;
using Dapper;

public IEnumerable<Movie> GetAllMovies()
{
	using (var con = new SqlConnection(connectionString))
	{
		return con.Query<Movie>("SELECT * FROM Movies");
	}
}
Code language: C# (cs)

Note: It returns an empty list when there’s no results.

Dapper abstracts away the repetitive code involved in executing SQL queries, including mapping parameters and query results. It does this without any configuration (it maps by using reflection). In the example above, it maps the query results against the Movies table to a list of Movie objects. Dapper can also handle mapping results to multiple types.

Adding query parameters

To execute parameterized queries with Dapper:

  • Put parameter placeholders in the WHERE clause (ex: @year).
  • Pass in an object for the param argument containing parameter values (names need to match the parameter placeholder names).

Here’s an example:

using Dapper;

public IEnumerable<Movie> GetMoviesForYear(int year)
{
	using (var con = new SqlConnection(connectionString))
	{
		return con.Query<Movie>("SELECT * FROM Movies WHERE YearOfRelease=@year", 
			param: new { year });
	}
}
Code language: C# (cs)

Dapper will try to map the properties from the param object to the parameter placeholders (i.e. “@year”) in the query. You can pass in any object, including anonymous types (as shown above). In addition to adding parameters like this, you can also add dynamic parameters.

Note: Parameterized queries guard against SQL Injection and have better performance. Always use parameterized queries instead of adding values directly to the query yourself.

Get the results as a List object

The Query() method’s return type is IEnumerable, but it actually returns a List object by default (unless you explicitly pass in buffered=false). When you want this as a List object, use AsList(), like this:

using Dapper;

List<Movie> GetAllMovies()
{
    using (var con = new SqlConnection(connectionString))
    {
        return con.Query<Movie>("SELECT * FROM Movie").AsList();
    }
}
Code language: C# (cs)

Don’t use ToList(). Since it’s already a List object, ToList() creates a copy needlessly. AsList() simply casts the List object to a List.

Note: AsList() is a Dapper extension method.

Related Articles