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# – 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

EF Core – Inheritance mapping

There are two ways to do inheritance mapping in EF Core: Table-per-Hierarchy (TPH) = There’s a single table for all classes in the hierarchy. Table-per-Type (TPT) = There’s one table per class in the hierarchy. This is available in EF Core 5 and above. Let’s say we have a database with employees. All employees have … 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

SQL Server – Getting and storing date/time

In this article, I’ll show built-in functions in SQL Server for getting the current datetime and how to get individual parts of the datetime (such as the year). Then show I’ll show how to store datetimes using the four different date/time data types (date, time, datetime2, and datetimeoffset). Getting the current datetime SQL Server has … Read more

Modifying JSON in SQL Server

There are two ways to modify JSON data in a SQL Server table: Use the JSON_MODIFY() function in an UPDATE statement to change individual properties inside the JSON data. Completely replace the JSON data with a regular UPDATE statement. In this article, I’ll show how to insert, update, and delete JSON data by using the … Read more

Querying JSON in SQL Server

In SQL Server 2016 they added support for handling JSON data. You can add JSON data, query it, and modify it. The JSON API in SQL Server is well-suited for simple scenarios where you only need to do basic querying and manipulation. For advanced querying, the SQL Server JSON API gets complicated and hard to … Read more