C# – Executing basic SQL queries with Dapper

Here’s an example of using Dapper to execute a basic SELECT query:

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)

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 this article, I’ll show more examples of using Dapper in common scenarios, such as adding query parameters and inserting records.

Note: If you haven’t done so already, add the Dapper nuget package.

Adding query parameters

To add query parameters with Dapper, pass in an object for the param argument:

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)

You can pass in any object, including anonymous types (as shown above). Dapper will try to map the properties from the param object to the parameter placeholders (i.e. “@year”) in the query.

Calling a stored proc

Let’s say you want to call the following stored proc:

CREATE PROCEDURE spGetMoviesForYear @year int AS BEGIN SELECT * FROM Movies WHERE YearOfRelease=@year END
Code language: plaintext (plaintext)

Here’s an example of how to call this stored proc with Dapper:

public IEnumerable<Movie> GetMoviesForYearSP(int year) { using (var con = new SqlConnection(connectionString)) { return con.Query<Movie>("dbo.spGetMoviesForYear", param: new { year }, commandType: System.Data.CommandType.StoredProcedure); } }
Code language: C# (cs)

You specify the stored proc name, any parameters, and set the commandType argument to CommandType.StoredProcedure.

Inserting records

Here’s an example of inserting a single Movie record:

private const string INSERT_SQL = @"INSERT INTO [Movies] ([Name] ,[YearOfRelease] ,[Description] ,[Director] ,[BoxOfficeRevenue]) VALUES (@Name, @YearOfRelease, @Description, @Director, @BoxOfficeRevenue)"; public void Insert(Movie movie) { using (var con = new SqlConnection(connectionString)) { con.Execute(INSERT_SQL, param: movie); } }
Code language: C# (cs)

Insertions are a little tedious to code because the INSERT statement is so verbose compared to other SQL statements. There are extension libraries for Dapper that abstract away the CRUD operations so you don’t have to write SQL, but I personally don’t use those. One of the benefits of Dapper is that it executes the SQL query you write, so there aren’t any surprises.

Being able to pass in the movie object for the param argument helps, since you don’t have to type out the parameter list. For writing the INSERT statement, I usually use Script Table as > INSERT INTO in SSMS to create a starting point script, or generate the queries using metadata.

Getting the inserted identity value

When you insert a record into a table with an identity column, you can get the inserted identity value by adding OUTPUT INSERTED.Id to the query. Use ExecuteScalar() to get the single returned value:

private const string INSERT_OUTPUT_ID_SQL = @"INSERT INTO [Movies] ([Name] ,[YearOfRelease] ,[Description] ,[Director] ,[BoxOfficeRevenue]) OUTPUT INSERTED.Id VALUES (@Name, @YearOfRelease, @Description, @Director, @BoxOfficeRevenue)"; public int InsertAndReturnId(Movie movie) { using (var con = new SqlConnection(connectionString)) { return con.ExecuteScalar<int>(INSERT_OUTPUT_ID_SQL, param: movie); } }
Code language: C# (cs)

Inserting multiple records

When you pass a list of objects for the param argument, Dapper will execute the SQL query for every object in the list:

private const string INSERT_SQL = @"INSERT INTO [dbo].[Movies] ([Name] ,[YearOfRelease] ,[Description] ,[Director] ,[BoxOfficeRevenue]) VALUES (@Name, @YearOfRelease, @Description, @Director, @BoxOfficeRevenue)"; public void InsertMultiple(List<Movie> movies) { using (var con = new SqlConnection(connectionString)) { con.Execute(INSERT_SQL, param: movies); } }
Code language: C# (cs)

Executing lots of INSERT statements sequentially can really degrade performance. Be sure to performance test your code if you’re expecting to insert lots of data like this frequently. I highly suggest doing a BULK INSERT if you’re running into performance problems.

Updating records

Here’s an example of how to update a record with Dapper:

public void UpdateYear(Movie movie) { using (var con = new SqlConnection(connectionString)) { con.Execute("UPDATE Movies SET YearOfRelease=@year WHERE Id=@id", param: new {year = movie.YearOfRelease, id = movie.Id }); } }
Code language: C# (cs)

You can keep the UPDATE statement as simple as possible by only including the bare minimum columns in the query. If a column doesn’t need to be in the update list or in the where clause, leave it out.

Updating multiple records

If you’re updating multiple records with different values, then you’ll need to execute multiple UPDATE statements. For example, let’s say you want to set:

  • Movie A YearOfRelease=2021
  • Movie B YearOfRelease=2022

To do this with Dapper, you can pass in a list for the param argument. It will execute the UPDATE statement for each movie object in the list:

public void UpdateMultipleToDifferentYears(List<Movie> movies) { using (var con = new SqlConnection(connectionString)) { con.Execute("UPDATE Movies SET YearOfRelease=@YearOfRelease WHERE Id=@Id", param: movies); } }
Code language: C# (cs)

If you’re updating multiple records with the same value, you can execute a single UPDATE statement with a WHERE IN clause. For example, let’s say you want to update several movies to have YearOfRelease=2022.

Here’s how to do this with Dapper:

using System.Linq; public void UpdateMultipleWithSameYear(int year, List<Movie> movies) { using (var con = new SqlConnection(connectionString)) { con.Execute("UPDATE Movies SET YearOfRelease=@year WHERE Id IN @ids", param: new { year, ids = movies.Select(m => m.Id) }); } }
Code language: C# (cs)

This is more efficient than executing multiple UPDATE statements when you only need one.

Deleting records

Deleting a record is straightforward with Dapper:

public void Delete(Movie movie) { using (var con = new SqlConnection(connectionString)) { con.Execute("DELETE FROM Movies WHERE Id=@id", param: new { id = movie.Id }); } }
Code language: C# (cs)

Deleting multiple records

When you’re deleting multiple records, you can execute a single DELETE statement with a WHERE IN clause. Here’s how to do that with Dapper:

using System.Linq; public void DeleteMultiple(List<Movie> movies) { using (var con = new SqlConnection(connectionString)) { con.Execute("DELETE FROM Movies WHERE Id IN @ids", param: new { ids = movies.Select(m => m.Id) }); } }
Code language: C# (cs)

This is more efficient than executing multiple DELETE statements.

Leave a Comment