C# – Connect to a MySQL database

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

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

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

The simplest way to add dynamic parameters with Dapper is by passing in Dictionary<string, object>: 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, I’ll show examples of adding dynamic parameters in different scenarios. Add dynamic parameters one at … Read more

C# – Map query results to multiple objects with Dapper

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: Which types to map to. Which column(s) to split on. This tells Dapper which columns it should try to map to which type. … Read more

C# – Executing basic SQL queries with Dapper

Here’s an example of using Dapper to execute a basic SELECT query: 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 … Read more

Get SQL Server query results as JSON

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# – How to use table-valued parameters (TVP) with ADO.NET, Dapper, and EF Core

Table-valued parameters (TVP) allow you to send multiple rows of data as a parameter into SQL queries. TVPs are flexible. They can be used in both raw SQL queries and with stored procs, and can be used in all types of queries, from inserts to selects. In this article, I’ll show how to use TVPs … Read more

C# – How to unit test code that uses Dapper

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