ORM-less Data Access in .Net Core

The use of Object Relational Mapper libraries (ORMs) is so prevalent today that it's uncommon to see anyone question their use.

There are good reasons for that. In the old days you'd see SQL code sprinkled everywhere. It was common to find examples where user input was concatenated directly with SQL statements opening the doors to SQL injection attacks (little Bobby Tables comes to mind).

Even though a lot of good came out of using ORMs, there's some less good things that came with it too. The first is performance, which is worse (sometimes much worse).

But apart from performance there are a set of other issues that although they are not disadvantages, they have a negative impact on the experience of using an ORM. They are all related to the fact that ORMs hide a lot of details about how the data is retrieved and saved. Frequently, people by not being aware of these details shoot themselves in the foot.

Just a few examples are the use of lazy loading even when that is arguably not a good idea (e.g. web applications) or N+1 problems stemming from the mechanism that triggers data being fetched (thinking specifically of Entity Framework here) not being obvious sometimes.

I'm not advocating that ORMs shouldn't be used, not at all. However, my perception is that nowadays most people working in the .Net ecosystem wouldn't be able to retrieve and create records in a database without using Entity Framework.

And that's unfortunate because it's not hard at all to go raw, and it might be quicker than to have to setup Entity Framework. I've been following that approach in small projects and I'm convinced that I can put something up faster without Entity Framework than with it (setting up EF can be a pain).

What I want to do in this blog post is show you how you can use the "raw" data access mechanisms (ADO.NET) available in .Net Core and also an alternative to Entity Framework named Dapper (which is used by Stack Overflow). Dapper is sometimes described as an ORM, but as we'll see it's more of an "object mapper".

CRUD with ADO.NET in .NET Core

To do data access without Entity Framework Core you need to master just three concepts. Those concepts are Connections, Commands and Data Readers.

A Connection object represents a connection to a database. You'll have to use the specific connection object for the database you want to interact with. For example for PostgreSQL we'd use NpgsqlConnection, for MySql MysqlConnection, for SQL Server SqlConnection. You get the idea. All these connection types implement the interface IDbConnection.

You need to install the right Nuget package for the database you want to use, for example for Postgres the package name is simply: Npgsql (An easy way to remember it, is N - for .Net and pgsql for PostGreSQL).

To create a connection object we need a connection string to the database we want to interact with. For example for a database named "example" with user "johnDoe" in Postgres we could create a connection this way:

var connection = new NpgsqlConnection("User ID=johnDoe;Password=thePassword;Host=localhost;Database=example;Port=5432");

A great resource to find information about how to create these connection strings is https://www.connectionstrings.com/.

After creating the connection object, to actually connect to the database we need to call Open on it:

connection.Open();

The connection objects are all IDisposable, so you should dispose of them. Because of this, usually the connection is created inside a using block:

using (var connection = new NpgsqlConnection("User ID=johnDoe;Password=thePassword;Host=localhost;Database=example;Port=5432"))
{
    connection.Open();
    //use the connection here
}

That's all you need to start.

Creating records

To create records we need to use a Command. A command is a container for all that is required to perform an operation in the database.

The easiest way to create a command is to ask the connection object for one:

using(var command = connection.CreateCommand()) 
{
    //use command here
}

You then specify the SQL you want to execute through the property CommandText and the values for the parameters in the SQL in the property Parameters. For example, if you want to add a record to a table named people with columns first_name, last_name, age it would look like this:

command.CommandText = "insert into people (first_name, last_name, age) values (@firstName, @lastName, @age)";
command.Parameters.AddWithValue("@firstName", "John");
command.Parameters.AddWithValue("@lastName", "Doe");
command.Parameters.AddWithValue("@age", 38);

You should use parameters because that prevents SQL injection attacks. If you don't and you create your SQL by using string-concatenation using data that was entered by the user you enable situations where a user can type something that will be interpreted as SQL.

The way a command is "executed" depends on the result you expect from it. For adding a record, and in case you don't care about any auto-generated column values (for example the new record's id) you can do this:

int numberOfUpdatedRows = command.ExecuteNonQuery();

This method returns the number of rows that were updated/created. Although it's not particularly useful on an insert statement, on an update that value might be useful.

Alternatively, if you want to insert and get the new record's id you can change the insert statement's SQL so that the new id is returned. The way you do this depends on which database you are using, for example in postgres the SQL would look like this insert into people (first_name, last_name, age) values (@firstName, @lastName, @age) returning id.

In sql server it would look like this insert into people (first_name, last_name, age) values (@firstName, @lastName, @age); select scope_identity().

To get the run the insert and get the new id you can use the ExecuteScalar method in the Command.

The ExecuteScalar method executes the SQL and returns the value (as type object) of the first column in the first row, for example in postgres:

command.CommandText = "insert into people (first_name, last_name, age) values (@firstName, @lastName, @age) returning id";
command.Parameters.AddWithValue("@firstName", "Jane");
command.Parameters.AddWithValue("@lastName", "Doe");
command.Parameters.AddWithValue("@age", 37);

var newId = (int)command.ExecuteScalar();

You might be thinking right now that these SQL statements involve a lot of typing. And on top of that, all of that is with no intellisense. Thankfully there are techniques around that. You can watch me creating an insert statement from scratch without actually having to manually type any column names.

Reads

To read data you simply need to write your SQL query and call the ExecuteReader method in the command object. That will return an instance of a DataReader which you can then use to retrieve the actual results of your query.

For example, if we want to retrieve all records in the people table:

command.CommandText = "select * from people";
DataReader reader = command.ExecuteReader();

Now, the way you get to the actual values is a little bit clunky. Definitely not as comfortable to do as with Entity Framework, but as I showed in the video on how to use Sublime to build the queries, you can also use the same techniques to create this code faster.

Here's how you could iterate over all results assuming that first_name and last_name are strings and age is an int.

command.CommandText = "select * from people";
using(DataReader reader = command.ExecuteReader()) 
{
    while(reader.Read())
    {
        string firstName = reader.GetString(reader.GetOrdinal("first_name"));
        string lastName = reader.GetString(reader.GetOrdinal("last_name"));
        int age = reader.GetInt32(reader.GetOrdinal("age"));

        //do something with firstName, lastName and age

    }
}

The GetString, GetIn32, GetBoolean, etc, methods expect a number that represents the column index. You can get that column index by calling reader.GetOrdinal("columnName").

Updates and Deletes

Updating and deleting records involves creating a command with the right SQL, and calling the ExecuteNonQuery in that command.

For example if we wanted to update all records on the people table that have the surname "Doe" to "Smith" we could do this

command.CommandText = "update people set last_name='Smith' where last_name='Doe'";
int numberOfAffectedRows = command.ExecuteNonQuery();

Deletions are very similar, for example, let's delete all records which have no last_name

command.CommandText = "delete from people where last_name is null";
int numberOfAffectedRows = command.ExecuteNonQuery();

Transactions

One thing that you get for free when using an ORM like Entity Framework is that when you persist your changes (i.e. call.SaveChanges()) that happens inside a transaction so that all the changes are persisted or none is.

Thankfully creating a transaction using ADO.NET is very simple. Here's an example where we add a new person, delete another and update another yet all inside a db transaction:

using (var transaction = connection.BeginTransaction())
{
    var insertCommand = connection.CreateCommand();
    insertCommand.CommandText = "insert into people (first_name) values (@first_name)";
    insertCommand.Parameters.AddWithValue("@first_name", "Jane Smith");
    insertCommand.ExecuteNonQuery();

    var deleteCommand = connection.CreateCommand();
    deleteCommand.CommandText = "delete from people where last_name is null";
    deleteCommand.ExecuteNonQuery();

    var updateCommand = connection.CreateCommand();
    updateCommand.CommandText = "update people set first_name='X' where first_name='Y'";
    updateCommand.ExecuteNonQuery();

    transaction.Commit();
}    

The easiest way to create a transaction is to request one from the connection object. A transaction is created using an IsolationLevel. Although we didn't specify one here (the particular database's default will be used) you should check the list of available isolation levels and choose the one that is appropriate to your needs.

After having the transaction created we can do all operations as before and in the end we call .Commit() on the transaction. If anything goes wrong before .Commit() is called all the changes are rolled back.

Getting metadata from the database

This is an aside but it's something that is useful to know. Using ADO.NET it is possible to extract metadata about your database. For example all the column names and their types form a particular table.

The following snippet shows how you can get all the column names and data types form all the columns in a particular database table:

command.CommandText = "select * from people";
using(var reader = command.ExecuteReader()) 
{
    var columnSchema = reader.GetColumnSchema();
    foreach(var column in columnSchema)
    {
        Console.WriteLine($"{column.ColumnName} {column.DataTypeName}");
    }
}

Using Dapper

Alternatively to using just ADO.NET using Dapper is just as easy and with small differences in terms of performance.

In case you are unfamiliar with Dapper, it's a project from StackExchange and it powers the StackExchange familiy of websites (StackOverflow, SuperUser, AskUbuntu, etc).

To use Dapper you need to install a Nuget package conveniently named Dapper along with the specific Nuget package for the database you are targeting. For example for Postgres:

$ dotnet add package Npgsql
$ dotnet add package Dapper

Dapper adds a few extension methods to your connection object, namely Query<T> and Execute.

Query<T> allows you to run a query and map the results to the type you specify in the generic parameter. For example, this is how you can get all records in the people table:

using Dapper; //you need this to get the extension methods on the connection object
//...

using (var connection = new NpgsqlConnection("theConnectionString"))
{
    IEnumerable<Person> people = connection.Query<Person>("select * from people");                        
}

The Query<T> method always returns an IEnumerable<T> even if you only expect one record. For example you could do this to insert a new person and get the new Id:

int newId = connection.Query<int>("insert into people (first_name, last_name, age) values (@FirstName, @LastName, @Age) returning id", new {
    FirstName = "John",
    LastName = "Doe",
    Age = "40"
}).FirstOrDefault();

In the example above we are providing 2 parameters to the Query method, the first is the SQL statement and the second one is an anonymous object with property names that match the parameters in the SQL statement. You can also use an instance of a class (e.g. new Person { ... }) instead.

If you don't care about any results, for example you just want to delete a record, you can use the Execute method instead which will return the number of records affected in the database. For example if you want to delete all the records for which last_name is null:

var numberOfDeletedRecords = connection.Execute("delete from person where last_name is null");

This was just a gentle introduction to Dapper, the github page for the project is a good resource if you are interested in learning more.

I hope this blog post has given you enough information about how to go ORMless in .Net Core. Let me know your thoughts in the comments.