Solve real coding problems
When you want to select a single row with Dapper, the simplest option is to use QuerySingleOrDefault() with a SELECT + WHERE query (and pass in the parameters / model type like usual). Here’s an example: This SQL query returns a single row from the Movies table and Dapper maps it and returns a Movie … Read more
You can update records with Dapper by using Execute() with an UPDATE statement and passing in the parameters. Here’s an example: Dapper maps the properties from the param object to the query parameters (ex: it maps year to @year). You can pass in the parameters with an anonymous type (as shown above) or by passing … Read more
You can insert records with Dapper by using Execute() on an INSERT statement and passing in a model object. Here’s an example of inserting a single movie record: Notice that you can simply pass in an object (param: movie) and Dapper handles the tedious parameter mapping for you. It tries to map properties from the … Read more
You can delete records with Dapper by using Execute() with a DELETE statement and specifying the record ID as a parameter. Here’s an example: This deletes a single record from the Movies table. Delete multiple records When you’re deleting multiple records, you can efficiently execute a single DELETE statement with a WHERE clause including all … Read more
You can execute stored procedures with Dapper by specifying the name, parameters, and CommandType.StoredProcedure. Let’s say you have the following stored procedure (single parameter, returns movie rows): Here’s how to execute this stored procedure with Dapper and map its results to Movie objects: Use an output parameter To get a stored procedure’s output parameter, add … Read more
The simplest way to connect to a MySQL database in a .NET project is to use the MySql.Data package (from Oracle). It provides classes that implement the standard ADO.NET interfaces (such as IDbConnection). First, add the MySql.Data package to your project (this is using View > Other Windows > Package Manager Console): Now use the … Read more
You can declare a record with a single line of code: Note: This feature was added in .NET 5 / C# 9. Records are basically classes (reference types) that work very well as simple data containers (i.e. DTOs). Here’s an example of using a record: This outputs the following: As shown, when you declare a … Read more
When you insert a record into a table with an identity column, the value for the identity column is automatically generated for you. The simplest way to get the inserted identity value is to put OUTPUT INSERTED.<identity column name> in the insert statement: To get the output value with Dapper, use ExecuteScalar<int>(): This inserts the … Read more
The simplest way to add dynamic parameters when executing queries with Dapper is by passing in Dictionary<string, object>, like this: Read more about how to add items to a dictionary. You can also add dynamic parameter by using the DynamicParameters class. You can use whichever approach is simplest in the given scenario. In this article, … Read more
When you’re querying joined tables, you can map each row to multiple objects by using the multi mapping feature in Dapper. To multi map, you have to provide Dapper with the following: In this article, I’ll show examples of multi mapping. Note: If you don’t specify the split column, it’ll use the default of “Id”. … Read more
When you want to execute multiple SQL commands with Dapper in a transaction, using TransactionScope is the simplest option. Here’s an example of how to use it: When you call TransactionScope.Complete(), it commits the transaction. If you don’t call Complete(), it’ll rollback the transaction once it leaves the TransactionScope block. This keeps the code nice … Read more
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: 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. … Read more
When an anonymous type is defined in one assembly, it won’t match an anonymous type defined in another assembly. This causes problems when you’re unit testing and trying to mock a method that has an anonymous type parameter. For example, let’s say you’re trying to unit test the following method: To unit test this, you … Read more
The simplest way to get query results as JSON is to use FOR JSON PATH in the query (note: this was added in SQL Server 2016): It returns the results as a single JSON string with one JSON object per row: Note: SQL Server returns the JSON without indenting. All examples in this article show … Read more
Table-valued parameters (TVP) allow you to send multiple rows of data as a parameter into SQL queries. This is useful for SELECTs and INSERTs. In this article, I’ll show how to create a TVP in SQL Server and then use it in queries with Dapper. 1 – Create the TVP in SQL Server To be … Read more
Dapper makes your code difficult to unit test. The problem is that Dapper uses static extension methods, and static methods are difficult to mock out. One approach is to wrap the Dapper static methods in a class, extract out an interface for that wrapper class, and then dependency inject the wrapper interface. In the unit … Read more
Let’s say you have a SQL Query that uses IN and you want to execute it using Dapper. Your query looks something like this: Here’s how you’d execute that with Dapper: Then you’d call it like this: There are two key things to notice about this: Exclude the parentheses In a normal SQL Query you … Read more