Using Dapper in ASP.NET Core

Dapper is a free micro-ORM which can be used in any ASP.NET application to communicate with the database.

Dapper is free, simple, small, and very fast – its distributors claim that it is almost just as fast as the raw ADO.NET Data Reader!  At the same time, it is much clearer and intuitive for the developer. Besides, its set of methods works with any database in a similar way: you don't need to use separate methods for MS SQL database, MySQL, Oracle, PostgreSQL, and so on.

The logic behind how Dapper works can be explained in 3 simple steps:

• You create an IDbConnection object (Dapper extends the IDbConnection interface from the System.Data namespace);
• You write a query to perform a CRUD (Create, Read, Update, Delete) operation;
• You call the Execute() method, passing the query as its parameter.

In order to use it in you project, you need to install Dapper from NuGet Package Manager, for instance by writing the following command in the Package Manager Console in Visual Studio:

PM> Install-Package Dapper -Version 1.50.5

After a successful installation, you can create POCO classes from SQL databases, and you can also create C# methods from SQL procedures. In order to do this, you have to create a file with a .tt extension, and add it anywhere in the project folder. The .tt file will automatically generate classes and other necessary elements in a .cs file which it will create.

Methods offered by Dapper are:

• Execute()
• Query()
• QueryFirst()
• QueryFirstOrDefault()
• QuerySingle()
• QuerySingleOrDefault()
• QueryMultiple()

Parameters passed to Dapper's methods may be:

• anonymous (using var);
• dynamic (using a DynamicParameters object);
• list;
• string.

The result of a Dapper's method may be of the following types:

• anonymous;
• strongly typed;
• multi-mapping;
• multi-result;
• multi-type.