Picking a database framework

Back in the good old days, where PHP and ASP Classic ruled the Internet, the code for connecting to and manipulating with a database was usually built in to the framework, in the form of a set of functions and/or classes. These functions/classes would be enough for most purposes, because you would simply supply them with commands to the database (often in the form of SQL queries) and they would return to you with the results, either in the form of raw database rows or a number of affected rows.

Today, this is considered a bit old-school - instead, you are expected to use some kind of framework which will abstract away a lot, or almost all, of the SQL (or whichever language your database speaks), allowing you to work with objects instead of database rows. Examples of these types of frameworks include LINQ to SQL and the .NET Entity Framework, with the latter existing in a version for the Core .NET framework as well (EF Core).

ORM - Object-Relation Mapping

The Entity Framework is a so-called ORM - an Object-Relation Mapper. It will make the communication between your database and your code a lot easier, because as you may know, the .NET Framework (and a lot of other programming languages/frameworks) uses objects while a relational database uses tables, to represent the same thing. A common scenario would then be to have a "Movies" table in your database, to represent one or several movies, and a "Movie" class in your .NET project, to represent each movie found in the "Movies" table.

Without an ORM, you would be responsible for the communication between the table and the object: When pulling out the database rows, you would then have to manually populate the Movie objects, and when inserting data, you would have to add all relevant properties of the Movie object to the SQL command that would then insert it into the database table.

You can still use this old-school approach, thanks to the ADO.NET classes found in the System.Data.SqlClient namespace. That will give you all the control you can dream of, but they are a bit cumbersome to work with - there's absolutely no mapping between your objects and database tables and even simple operations requires several lines of code and the use of several classes like SqlConnection, SqlCommand, SqlDataReader and so on.

On the other hand, when you use an ORM, you can do a lot with even a single line of code because a lot of power has been packed into short commands. You don't have to write any SQL - instead, you tell the framework what you want to accomplish with a command like Select(), and it will transform it into something the target database engine understands, e.g. SQL, and in return, you will receive ready-to-use .NET objects.

The downside to this is a big loss of control: When you call these simple commands, the framework decides how they are best handled. When your database starts growing in size and complexity, you may end up seeing large performance problems because the ORM framework doesn't understand your data and/or intentions well enough to transform it into commands that perform well enough. Another downside is that you might not even understand why this is happening, because with all these abstractions, you don't know what a database table is or how to talk with it.

Picking an ORM

In most ASP.NET tutorials, especially the MVC-related ones, you will see that the Entity Framework is used. This would probably also be the official, Microsoft-recommended solution, but it won't be the one used in this tutorial. There are several good reasons for this, as outlined above, but the most important one is that I don't particularly like the Entity Framework. I have tried using it a couple of times, but I was never comfortable with the loss of control I paid for the ease-of-use. Another good reason to do things differently is to show you that there are alternatives and that with the huge flexibility you get with the .NET framework, it's always possible to do things differently.

I have instead chosen a compromise between the very abstract Entity Framework and just hand-coding almost everything with ADO.NET: It's called Dapper and is often referred to as a Micro-ORM. It abstracts away the most boring things, while still allowing you to take full control of how everything is done.

So, with Dapper, you don't have to manually map between tables and objects, but you will still write the SQL that fetches the data. This will also allow you to gain some knowledge about the underlying database and how it works, which you'll benefit from later on, especially if you start working on more complex, data-heavy projects.

Another cool thing about Dapper is the fact that it simply extends the IDbConnection interface, meaning that you can use the existing classes for dealing with your database of choice (e.g. SqlConnection, which implements IDbConnection) - in other words, if .NET supports your database and your database supports SQL, you can likely use Dapper!

Summary

We have now selected a database engine (MS SQL Server) and a database framework/ORM (Dapper). With that in place, we just need to get everything setup, so that we can start building our database-driven TodoList web application. More on that in the next article.