SQL – How to use GROUP BY

Here’s an example of how to use GROUP BY in a SQL query: This produces the following query results: GROUP BY allows you to group rows based on one or more columns and calculate aggregate values for the group. In the example above, the COUNT(*) aggregate function was used to show the number of rows … Read more

Error: Host is not allowed to connect to this MySQL server

Problem When you try to connect to MySQL remotely, you get the following error: Host <hostname or IP> is not allowed to connect to this MySQL server In MySQL, hosts are allowed (whitelisted) per user. So this error means the user you’re trying to connect with doesn’t have your remote host whitelisted. You’re likely trying … Read more

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

Using TransactionScope is the simplest way to execute multiple SQL commands in a transaction. 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 and clean and works well … 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

ASP.NET Core – Log messages to the database in the background

I was reading about logging in ASP.NET when I came across this statement about logging to the database: When logging to SQL Server, don’t do so directly. Instead, add log messages to an in-memory queue and have a background worker dequeue and insert data to SQL Server. Paraphrased from Microsoft – No asynchronous logger methods … Read more

SQL Server – How to use INFORMATION_SCHEMA to find database metadata

Let’s say you want to find all columns in a database that have the word ‘Name’ in the column name. You can query INFORMATION_SCHEMA.COLUMNS, like this: This query returns the following five rows showing the table name, column name, and column data type: TABLE_NAME COLUMN_NAME DATA_TYPE Actors FirstName nvarchar Actors LastName nvarchar Actors Fullname nvarchar … Read more

Case sensitivity in SQL Server

In SQL Server, the collation property controls case sensitivity. Case sensitivity affects sorting and queries (even the column names must match exactly if you’re using a case-sensitive collation at the database-level). The default collation when you create a database in SQL Server is SQL_Latin1_General_CP1_CI_AS. The CI stands for case-insensitive, which means SQL Server databases are … Read more

SqlException: Cannot insert explicit value for identity column

When you have a table with an identity column, and you try to specify the value for identity column when inserting a record, you’ll get the following exception: Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table ‘Movies’ when IDENTITY_INSERT is set to OFF. This error means you have an identity column in … Read more

EF Core – Basic SELECT queries

In this article, I’ll show examples of how to execute basic SELECT queries when using EF Core. You can execute queries using LINQ or by writing raw SQL. I’ll use SQL Profiler to show the queries generated by LINQ. Note: I’ll be using .AsNoTracking().ToListAsync() in all cases. You’ll need to decide if that’s the right … Read more

EF Core – How to add a computed column

To add a computed column in EF Core, override DbContext.OnModelCreating() and specify the computed column using ModelBuilder, like this: In this article, I’ll show a full example of adding a computed column and then show how to specify that the computed column should be persisted. Example of adding a computed column Let’s say we have … Read more

EF Core – How to add indexes

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

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

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