connectionstring

C# – Select a single row with Dapper

by

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# – Get column values by name instead of by number with SqlDataReader

by

When you execute a SQL query and read the results with SqlDataReader, you have two options for getting column values by name (instead of by ordinal number): I’ll show examples below. Read column by name with the indexer You can use the indexer to read columns by name (or by ordinal number). This returns an … Read more

C# – Connect to a MySQL database

by

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

by

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

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

C# – How to unit test code that uses Dapper

by

Dapper makes your code difficult to unit test. The problem is that Dapper uses static extension methods, and static methods are difficult to mock out. One approach is to wrap the Dapper static methods in a class, extract out an interface for that wrapper class, and then dependency inject the wrapper interface. In the unit … Read more

C# – Using SqlDataReader to process multiple result sets

by

In this article I’ll show how to use the SqlDataReader ADO.NET class in two scenarios involving multiple result sets: I have a StreamingService database that has Movies, Shows, and Episodes tables (linked to the shows). First I’ll show the model classes I’m mapping the data into. Then I’ll show the two scenarios where I’m using … Read more

How to add User Secrets in a .NET Core console app

by

The User Secrets feature in .NET is a safe, simple way to override values in appsettings.json. The overridden values only exist in a file sitting in your own dev environment, so you don’t accidently commit them to your source control repository. This feature is enabled in ASP.NET by default, and the framework does most of … Read more

How to modify app.config at runtime

by

When you try to modify the app.config at runtime, if you don’t do it right, you’ll run into a few problems: System.Configuration.ConfigurationErrorsException: The configuration is read only. This article will show you how to update the app.config the right way to avoid these problems. This shows three different scenarios: inserting a new connection string, modifying … Read more

SqlTypeException: SqlDateTime overflow

by

Problem I am executing a SQL query and trying to set a datetime column to DateTime.MinValue. I’m getting the following exception: System.Data.SqlTypes.SqlTypeException: ‘SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.’ The problem is that DateTime.MinValue is 1/1/0001 12:00:00 AM and the SQL Server DateTime minimum value is 1/1/1753 12:00 AM. Solution … Read more