Introduction to Dapper

22 februari 2021 om 10:00 by ParTech Media - Post a comment

As time progressed, a lot of ways of presenting a C# based applications were discovered. For example, console applications, windows based applications, web-based applications, etc.

Though there were multiple changes in the front end side and there were a lot many different databases discovered, the concept of the database did not change. There was always a need to connect to the database even today.

In the past, tables were created in the database. And to access the tables, either inline queries or stored procedures were used. The problem with inline queries is that they are prone to SQL injection which would result in hacking of data from the database.

On the other hand, with stored procedures, it is difficult to maintain them as it needs to be compiled from a lower environment to the production environment. Missing even a single region would result in the application erroring out. And there are chances of it getting unnoticed and getting promoted up to the production environment. Also, there are chances that the object models of the programming language are not in sync with the data models in the relational database. So, to tackle all these issues the concept of Object Relational Mapper (ORM) was introduced.

In this post, let's see in detail about one of the micro-ORM based software - Dapper. We will start this post by answering the key question of ‘What is Dapper’.

Table of contents

  1. What is Dapper?
  2. When to use Dapper?
  3. Advantage of using Dapper
  4. Practical implementation of Dapper
  5. Conclusion

What is Dapper?

Dapper is an open-source, lightweight micro ORM, developed by the Stack overflow team for the .Net platform. It was developed considering its ability to reduce the code size and the time spent on mapping objects from data reader and the models. It also helped in improving the performance of various database operations.

As a micro ORM, Dapper performs a subset of the functionality of an ORM. Operations such as caching, change tracking, identity management, lazy loading are not possible with Dapper. However, it offers a huge advantage of improved better performance. This makes it easier to handle more traffic on the website.

When to use Dapper?

As we saw in the previous section, the main advantage of Dapper is its performance. Unlike ORM, Dapper does not convert the database queries and tables into C# language. So users who are familiar and want to work with SQL can opt for Dapper.

Dapper works with an ADO. NET (IDbConnection) object. So projects that use any database and have an ADO. Net compatibility can go for Dapper.

In summary, Dapper is the go-to option for developing database connected applications with lesser time, lesser effort, and better efficiency.

Advantage of using Dapper

Here are some of the key advantages of using Dapper

  1. Dapper is the second-fastest ORM
  2. It allows operations with simple and complex data types
  3. It can perform CRUD operations using IDBConnection object
  4. It supports SQL Query
  5. It supports stored procedures and eliminates rewriting of stored procedures into code
  6. It requires fewer lines of code for achieving database connectivity
  7. It has bulk data insert functionality.

Practical Implementation of Dapper

In this section, let's see how to implement Dapper in C#. To do this, we need an IDE (Visual Studio) to write the C# code and a database to connect and perform an operation in a particular table.

Step 1

Before we jump into the C# part of the implementation, we need to create a database and a table in the database to hold the records. In this example, we are creating a database called PARTECH and a table called CompanyData

Step 2

Once the table is created, add a sample data which will be used for an update operation from C# dapper code.

Step 3

Validate after inserting the data in the table.

Step 4

Once the database steps are completed, create a C# based console application.

Step 5

Provide a valid name and path to store the solution.

Step 6

Console App by default comes with the below template.

Step 7

To work with Dapper, we need to install two NuGet packages to get Dapper working from C# based code and update data in any database.

1st package to install - Dapper

2nd package to install - System.Data.SqlClient

Step 8

Once the packages are installed, the next step is to identify the connection string of the database in which the tables and the data are present. A typical connection string looks like below,

Data Source=XXX;Initial Catalog=PARTECH;Integrated Security=True;Trusted_Connection=True;

Step 9

Create a code block to connect to the database and table using the connection string through the SqlConnection class. Once the object is created, open the database connection and write the operation as a SQL query that needs to be executed in the database (in this example, we are going to do an update operation), and the last step is to capture the rows updated in the database.

Step 10

Once the code runs successfully, query the same table in the database, and validate the result data against the data that is being used in the code for updating.

Similar to update, all the other CRUD operations are possible to execute through Dapper from C# with very few lines of code. Also, it provides the ability to run stored procedures and map the objects of the code to the database. Here is a sample code to call the stored procedure from C# code.

​ db.Query("SP_NAME", commandType: CommandType.StoredProcedure);

Conclusion

Dapper is ta user-friendly micro-ORM. It is light-weight and easy to access and operate. Though it does not generate the SQL models and relationships in the code, it provides options to query the database and tables in an efficient manner. With the advent of NuGet, it is now easy to install Dapper and configure it as per the project needs.