SQL Server

EF Core – How to add indexes

by

In this article, I’ll show how to add indexes using EF Core. If you’re not sure about why you would need indexes, take a look at this article explaining how indexes greatly improve query performance. Add an index with a single column The simplest way to add an index is to by adding the [Index] … Read more

EF Core – Add a foreign key

by

In this article, I’ll show how to add a foreign key using EF Core. Then I’ll show how foreign keys affect inserts and deletes. How to add a foreign key A Show has one or more Episodes. In this section, I’ll show how to link these two tables together to enforce the 1-to-many relationship. In … Read more

EF Core – Apply migrations programmatically

by

DbContext.Database has a few methods you can call to manage migrations programmatically. To apply any pending migrations: If the database doesn’t exist, MigrateAsync() will create it and then apply the migrations. To check if there are any pending migrations: To check which migrations have been applied: To apply a specific migration: This migrates up or … Read more

EF Core – Database schema changes

by

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

EF Core – How to create a database and a table

by

In this article, I’ll show how to use EF Core to create a database with one table in it. At the end, I’ll show a standalone console app that inserts a record into this database. Note: I’ll be using SQL Server. I haven’t tried this with other database providers. Please leave a comment if you’re … Read more

SQL Server – DB is currently in use

by

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

by

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

SQL Server – Bad SELECT query performance due to missing indexes

by

When you have a slow SELECT query, the most likely cause of the problem is that you’re missing an index. When your table has lots of data, having the right indexes will make a difference in the performance. Without indexes, SQL Server has to scan through your entire table to find the rows that match … Read more

C# – How to use SqlBulkCopy to do a Bulk Insert

by

If you find yourself doing SQL Inserts in a loop, you may want to consider doing a Bulk Insert instead. You can use SqlBulkCopy to do a Bulk Insert from C#. Make sure to prove you have a performance problem before switching your code to use Bulk Insert. Like any optimization, it adds complexity. In … Read more

C# – Mapping nullable columns with SqlDataReader

by

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