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

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.

Now, SQL server can run on both Windows and Linux system. But, we use SQL server in Windows here. 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

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.

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.

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    public ICollection<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

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

public class AppContext : DbContext
{
    public AppContext(DbContextOptions<AppContext> options)
        : base(options)
    { }

    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }
}

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

public void ConfigureServices(IServiceCollection services)
{
    services.Configure<CookiePolicyOptions>(options =>
    {
        // This lambda determines whether user consent for non-essential cookies is needed for a given request.
        options.CheckConsentNeeded = context => true;
        options.MinimumSameSitePolicy = SameSiteMode.None;
    });

    var connection = "Server=localhost;Port=5432;Database=Test;User Id=postgres;Password=postgres;";
    services.AddDbContext<AppContext>(options => options.UseNpgsql(connection));

    services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
}

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

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

dotnet ef migrations add InitialDb

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.

public class BlogsController : ControllerBase
{
    private readonly AppContext _context;

    public BlogsController(AppContext context)
    {
        _context = context;
    }

    // GET: api/Blogs
    [HttpGet]
    public async Task<ActionResult<IEnumerable<Blog>>> GetBlogs()
    {
        return await _context.Blogs.ToListAsync();
    }

    // GET: api/Blogs/5
    [HttpGet("{id}")]
    public async Task<ActionResult<Blog>> GetBlog(int id)
    {
        var blog = await _context.Blogs.FindAsync(id);

        if (blog == null)
        {
            return NotFound();
        }

        return blog;
    }

    [HttpPost]
    public async Task<ActionResult<Blog>> PostBlog(Blog blog)
    {
        _context.Blogs.Add(blog);
        await _context.SaveChangesAsync();

        return CreatedAtAction("GetBlog", new { id = blog.BlogId }, blog);
    }
}

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.

[HttpGet]
public async Task<ActionResult<IEnumerable<Blog>>> GetBlogs()
{
    var connection = _context.Database.GetDbConnection();
    var data = await connection.QueryAsync<Blog>("SELECT * FROM Blogs");
    return data.ToList();
}

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 !

Javier Huthon

Javier is Content Specialist and also .NET developer. He writes helpful guides and articles, assist with other marketing and .NET community work