How to Execute a SELECT query with Dapper in C#
By FoxLearn 3/10/2025 9:55:34 AM 120
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:
- Put parameter placeholders in the query, such as
@genre
. - 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
.
- How to use JsonConverterFactory in C#
- How to serialize non-public properties using System.Text.Json
- The JSON value could not be converted to System.DateTime
- Try/finally with no catch block in C#
- Parsing a DateTime from a string in C#
- Async/Await with a Func delegate in C#
- How to batch read with Threading.ChannelReader in C#
- How to ignore JSON deserialization errors in C#