dapper-query

C# – Execute a stored procedure with Dapper

by

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

C# – Connect to a MySQL database

by

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

C# – Use records as a shortcut for defining DTOs

by

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

C# – Get inserted identity value with Dapper

by

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

C# – Adding dynamic parameters with Dapper

by

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

C# – Map query results to multiple objects with Dapper

by

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

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: 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

C# – How to match an anonymous type parameter in a mocked method

by

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

Get SQL Server query results as JSON

by

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

C# – The nameof() operator

by

The nameof() operator outputs the name of the class/method/property/type passed into it. Here’s an example: Note: nameof() was added in C# 6. nameof() eliminates duplication The DRY principle – Don’t Repeat Yourself – warns us against having duplication in the code. Whenever information or code is duplicated, it’s possible to change something in one spot … Read more

C# – How to unit test code that uses Dapper

by

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

C# – How to use IN with Dapper

by

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