Use Dapper ORM With ASP.NET Core

Dapper.NET is not just another ORM tool, it’s considered as the king of ORM. Because it’s fast, easy to integrate, requires fewer lines of code, supports bulk data insertion and supports static and dynamic object binding. And Dapper ORM can be used with .NET. So in this post, let’s find out how to use Dapper ORM with ASP.NET Core for CRUD operations.

Use Dapper ORM With ASP.NET Core

For this post, we will be using SQL Server as a database with Dapper ORM. So let’s first create the database. Open SQL Server Management Studio and create a new database called “DapperDemo” or you can name it anything of your choice. And create a table named “Products”.

Okay. Let’s create an ASP.NET Core Web API project. The default Web API template comes with a controller named “Value”. We don’t need this for demo, so I deleted it. To use Dapper ORM, we need to install following nuget package.

This controller has methods for GET, POST, PUT and DELETE. That’s all to code. Now, let’s just run the application and execute the GET API. Since the table is empty, so you should see following.
Now, let’s add a product via Post API.

And now call the GET Product API again and you should see that product you just added is returned.

Here is the gif version of all 5 methods.

That’s it. Dapper.NET ORM is considered to be the fastest ORM as it directly executes the RAW SQL without any additional overhead. We saw how easy it was to integrate and use it with ASP.NET Core. If you want to support any other database providers like SQLite, then you need to change in public IDbConnection Connection property.

You will also need to include the SQLite nuget package "Microsoft.Data.Sqlite": "1.0.0" in your project.json. Of course, based on database provider the SQL query syntax may differ. Hope you liked it. Just for your information Project.json for ASP.NET Core is going away.

Dapper forces you to write raw SQL in the code, and if your’re looking for DAL that is similar to Dapper but with real isolation from the database (SQL commands generation), take a look to the NReco.Data – this is new library that was specially designed for .NET Core – for example, it implements lightweight DataTable replacement for schema-less data access.