C# – Select a single row with Dapper

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

C# – Update records with Dapper

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

C# – Insert records with Dapper

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

C# – Delete records with Dapper

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

C# – Execute a stored procedure with Dapper

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# – 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 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# – Using SQL transactions with Dapper

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

C# – Execute a SELECT query with Dapper

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

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 IN with Dapper

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