Using Dapper and SQLKata in .NET Core for High-Performance Application

Photo by Joe Neric on Unsplash

Introduction

Object-Relational Mapping (ORM) is a technique that lets you query and manipulates data from a database using an object-oriented paradigm. It creates a layer between your application and data source and returns you the relational objects instead of (in terms of c# that you are using) ADO.NET objects. This is the basic thing that every ORM does.

An ORM library is a completely ordinary library written in your language of choice that encapsulates the code needed to manipulate the data, so you don’t use SQL anymore; you interact directly with an object in the same language you’re using. There are a lot of ORM libraries around here:

• Java: Hibernate.
• PHP: Propel or Doctrine
• Python: the Django ORM or SQLAlchemy
• C#: NHibernate or Entity Framework

Besides ORM libraries above, there are also Micro-ORM libraries like Dapper in the .NET world.

ORM and Micro ORM (EF and Dapper)

Photo by Ralph Blvmberg on Unsplash

Entity Framework is an open-source ORM library for .NET applications supported by Microsoft. It is also a cross-platform framework which can run on Windows, Linux and Mac.

Dapper is a micro ORM or it is a simple object mapper framework which helps to map the native query output to a domain class or a C# class. It is a high-performance data access system built by the StackOverflow team and released as open-source.

Entity Framework has many good features like generating queries for you to make your application database independently, cache your data for future calls, manage unit of work for you and lot more; one of them is UnitOfWork. Tracking is one of the responsibilities of UoW. When the object is requested (SQL query) for the first time, it causes a round trip to the database. This object is then saved in-memory cache. Full ORM keeps track of changes done to this already loaded object(s). If the same object is requested again then EF returns the object from memory cache instead calling to the database. This way, considerable time is saved.

Note: Dapper does not support this feature.

But, this benefit is only applicable if the same object(s) loaded multiple times. Also, if the number of objects loaded in memory is too high, this will slow down your application as then the time required to check the objects in memory will be higher.

EF generally execute the query and map the returned DataReader to the POCO class. Dapper is limited up to here.

Dapper can’t generate a class model for you, It cannot generate queries for you and It also cannot track objects and their changes for you while EF can. But if you really want to improve your application performance then Dapper owns the title of King of Micro ORM in terms of performance.

Note: You can access here to see the details benchmark of Dapper with other ORM libraries (section Performance).

Let’s make a sample of using Dapper in .NET Core!

Working with Dapper

Dapper has no DB specific implementation details, it works across all .NET ADO providers including SQLite, SQL CE, Firebird, Oracle, MySQL, PostgreSQL and SQL Server.

Let’s use Dapper to make simple CRUD functions “ProductType” and “Product” table below:

figure 1: Product and ProductType table

Here is the SQL script to create tables:

First thing first, you should install the “Dapper” package from NuGet.

figure 2: install Dapper package from NuGet

Dapper majorly includes 3 steps:

• Step 1: Create an IDBConnection object with the Connection String to your database.
• Step 2: Write a query and store it in a normal string variable.
• Step 3: Call db.execute() and pass the query and it’s done.

Step 1

The SQLConnectionFactory class below will manage to open your connection string.

figure 2: SQLConnectionFactory class

We also need to register it for using Dependency Injection with your connection name.

figure 3: connection string in appsettings.json

figure 4: register DI

Note: If you’re not familiar with Dependency Injection in .NET Core, you can refer to my article about this.

[.NET Core] Dependency Injection in ASP .NET Core — “Old but gold”

In fact, you can open the connection to your database like this:

var cs = @"Server=localhost;Database=testdb;Trusted_Connection=True;";

using var con = new SqlConnection(cs);
con.Open();

But in a real project, we will never do that. Because it’s very hard to maintain as well as a lot of duplicated code.

Step 2 and Step 3

Write and Execute a Query

figure 5: fetch ProductType table

Write and Execute a Query with SQL Parameters

figure 6: get ProductType details with SQL Params

Note: You have to use SQL Parameterize to prevent SQL Injection issue.

Insert command

figure 7: insert ProductType table

Update command

figure 8: update ProductType

Delete command

figure 9: delete ProductType by ID

Execute a Stored Procedure

figure 9: execute Stored Procedure

Or use can use the command type parameter to call a Stored Procedure

figure 9: execute Stored Procedure with command type is StoredProcedure

Through the above samples, you can see that when you decide to apply Dapper to your application then most of the effort is for writing SQL Statements. No worries, there are a lot of SQL Builder libraries that could help to save your time. SQLKata is one of those.

SQLKata for Query Builder

Photo by Wil Stewart on Unsplash

SqlKata the dotnet SQL query builder

sqlkata/querybuilder

SqlKata is an elegant SQL Query Builder for C#, it helps you to talk with your database engine with a higher order of freedom, it allows you to write complex queries very easy and fast.

Let’s update the above sample to use SQLKata for the query builder!

figure 10: Fetch ProductType using SQLKata

figure 11: get ProductTypeDetails using SQLKata

figure 12: create Product Type using SQLKata

figure 13: update Product Type using SQLKata

figure 14: delete Product Type using SQLKata

Note: You can get the source code from my Github for your reference

thanhle0212/Self-Order-System

Conclusion

All comparisons are lame and only for reference. Although EF performance is not as good as Dapper, it still has a lot of good features like generating queries for you to make your application database independently, cache your data for future calls, manage unit of work for you and lot more.

The major drawback to using Dapper is that you have naked SQL queries in your code. If anybody fat-fingers anything, we won’t be aware of any issues until we run the tests against the code. Plus, the members of my group are more familiar with EF than Dapper, and therefore development time will be quicker.

References

SqlKata the dotnet SQL query builder

sqlkata/querybuilder

Entity Framework

Dapper