EF Core – Database schema changes

Anytime you change the definition of the database – from renaming a column to creating a table – it’s referred to as a database schema change. With EF Core, you deal with database schema changes by using migrations. When you first create the database, you create a migration that contains the initial definition of the … Read more

SQL Server – DB is currently in use

Problem Let’s say you’re running in a test/dev environment and you’re trying to alter the database. For example, you may be trying to drop the database: But you get the following error message and your command fails: Cannot drop database “CSI” because it is currently in use. This means there are other open connections on … 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

C# – Mapping nullable columns with SqlDataReader

When you have a database table with null columns, and you’re using SqlDataReader to map the results, there are a few things to do: Use nullable types in the model class. Example: instead of int use int? so the value can be null. Use SqlDataReader.IsDBNull() to check if you need to set the value to … Read more

C# – Using SqlDataReader to process multiple result sets

In this article I’ll show how to use the SqlDataReader ADO.NET class in two scenarios involving multiple result sets: Batches – When you execute multiple SELECTs in a single query. Each SELECT returns a different result set. You use a single reader to process the batch. Nested Queries – When you use multiple SqlDataReaders on … 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