Using Database in ASP.NET Core

ASP.NET Core with database

When building an ASP.NET Core application, you will probably need to use a database to store your data. This post will explore several possibilities to setup a database for use in your ASP.NET Core application.

Prerequisites

1. A Windows / Linux machine to host your ASP.NET Core
2. A Web Server up and running in your instance
3. ASP.NET Core application that has been setup to run through the web server

If you need help to setup the prerequisites above, you can visit my previous post to get started for creating ASP.NET Core in Amazon Lightsail.

Installing the database

The first step is then to setup your database of choice. We’re going to also explore several popular option of databases for usage in ASP.NET Core.

SQL Server

SQL Server is the most popular database when talking about ASP.NET Core since it’s what Microsoft is also selling and also the first one that Entity Framework Core is developed for. It’s not free mind you.

You can now use SQL Server in Linux as Microsoft has developed a Linux version that you can use. We can use the SQL Server Developer Edition for free. But you need to pay for the licenses when you go into production. And you will also need a minimum of 2GB of RAM for using this on Linux.

You can follow the guidance from Microsoft to install SQL Server for Linux or Windows.

MySQL

The other popular database is MySQL. It’s #2 on DB-Engines and it’s still growing. It has been acquired by Oracle in 2010, but it remains an open source under the terms GNU General Public License.

Installation is quite straightforward, simply update the package index on your server and install the default package with apt-get

sudo apt-get update
sudo apt-get install mysql-server

You’ll be prompted to create a root password during the installation so choose a secure one.

Last one is to setup the security, you need to run this line

mysql_secure_installation

PostgreSQL

One other database that is quite popular and gain quite a lot of traction recently is PostgreSQL. It is open source and has a permissive license.

For installation, you can start with the import of the GPG key for PostgreSQL packages.

sudo apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Add the repository to the system

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'

Update your packages

sudo apt-get update

Install PostgreSQL

sudo apt-get install postgresql postgresql-contrib

Entity Framework Core

One of the option for connecting to your database is using EF Core. EF Core is an ORM which stands for Object Relational Mapper. This enables us developers to work with a database using .NET objects instead of data access codes.

New databases can also be created using the EF Core Migration feature.

Dependencies

In case you’re using ASP.NET Core application, you will need to install some dependencies that is available through nuget

• SQL Server (Microsoft.EntityFrameworkCore.SqlServer) is included in the Microsoft.AspNetCore.App metapackage so you don’t need to install the dependencies anymore.
• MySQL. Currently there are 2 options that you can use for using EF Core in MySQL. That is
  Pomelo.EntityFrameworkCore.MySql (created by the community based on MySqlConnector) and MySql.Data.EntityFrameworkCore (created by the MySQL team based on MySql.Data). Either one is fine and can get you up and running.
• PostgreSQL. You will need to install Npgsql.EntityFrameworkCore.PostgreSQL which is based on Npgsql, the data access library for PostgreSQL.

Setting up the code

Next thing to do is you will need to create a .NET objects that is a representation of your database tables.

Let’s say we have 2 tables called “Blog” and “Post”, so we create the class as below.

And then you will need to create the Context class derived from the DbContext

You will need to register the dependency injection in your application startup.

We’re using PostgreSQL for this sample

services.AddDbContext<AppContext>(options => options.UseNpgsql(connection));

But you can use MySQL easily by changing it to

services.AddDbContext<AppContext>(options => options.UseMySql(connection));

Or SQL Server

services.AddDbContext<AppContext>(options => options.UseSqlServer(connection));

If you need to know what to write in the connection string based on your database, I used to get this information from ConnectionStrings.com that cover all type of databases.

Create database

Firstly you need to create the migration file, type this using command line in your application folder

dotnet ef migrations add InitialDb

If the command executed successfully, you will see a “Migrations” folder inside your application project

Migrations folder

Execute below command in your command line to get your migrations applied to the database (e.g. This will create the database and the tables for you).

dotnet ef database update

Usage

You can then start using it in your logic code to access your data in the database through the AppContext class like a sample for BlogsController below. This code is using Dependency Injection to get the AppContext through its constructor. You can then use LINQ to get the data.

Dapper

EntityFramework Core have its advantages where you can have a type-safe code when using your database. You don’t have to create the query manually. If you do need to create the query manually, then I suggest you use Dapper for your data access code. It greatly simplify all the basic data access code.

Dapper is classified as a Micro ORM, where it does the code for the Query to the database and also map the query result to a .NET Data Type.

Taking an example from the GetBlogs() above, you can also create a query and still map the result to the Blog model. In this case, you can mix your data access code between EF Core and Dapper as the connection is retrieved from the AppContext that is injected through the constructor.

You can also provide the connection from creating it yourself.

//SQL Server
var connection = new SqlConnection(connectionString);
//MySQL
var connection = new MySqlConnection(connectionString);
//PostgreSQL
var connection = new NpgsqlConnection(connectionString);

There are other features by Dapper that you can use, so I suggest you check out Dapper for more detail on what it can do.

Summary

In this post, we learn about some popular databases that can be used with ASP.NET Core. There are also several options that you can use for your data access code which is EntityFramework Core that is developed by ASP.NET Core team as an Object Relational Mapper (ORM). And there is also Dapper which is a Micro ORM that has a great performance. You can use Dapper if you need to get all the performance you need from a specific query.

Happy Coding !