Introduction to Dapper

One of the most important operation in your enterprise application would be reading/writing to database. Sometimes it’s very much important when you have lot of customers, the reading/writing should happen faster.

In our .NET Applications, we have a class called SqlCommand and you should be able to perform read/write operations but,

Brace yourselves, Dapper is very much faster and efficient

Dapper is an .NET library which can be used for executing SQL queries built on top of ADO.NET. And also you can do Object Relational Mapping. It’s also known as King of Micro ORM in terms of speed and visualization of data.

Why Dapper?

Dapper is available in NuGet such that it can be used in any .NET Projects.
It is quite light weight and provides high performance.
It works with any database – SQL, Oracle, MySQL etc.,
It will drastically reduce the database access code.
Don’t worry about opening and closing of connections.
You can map the relational objects at ease.

In this blog, we will see how we can use Dapper in our .NET Projects and also we will compare Dapper with SqlCommand class.

Dapper with Visual Studio 2019

If you don’t have Visual Studio 2019, download using this link. Go for Community Edition, because it’s free.

Step 1:

Open Visual Studio 2019 and click Create a New Project and it will provide you some list of project templates. Please select Console App(.NET Framework) and Click Next.

This image has an empty alt attribute; its file name is image-22.png

Step 2:

Mention the project name and the directory of your solution and click Create.

Step 3:

A console app will be created and first we are going the install the Dapper package for your project. So Right-click on your project and select Manage NuGet Packages.

This image has an empty alt attribute; its file name is image-24.png

And search for Dapper and select the respective package and click install.

Step 4:

After installing dapper, we are going to define our connection string to access our database and we will perform some operations using dapper.

Program.cs

var connectionString = "Server = localhost; 
                        Database = Dapper; 
                         Integrated Security=True;";
using (var connection = new SqlConnection(connectionString))
{
   var query = "CREATE TABLE [dbo].[CUSTOMER]" +                            
               "(CustomerId UNIQUEIDENTIFIER," +                
                "CustomerName NVARCHAR(50))";
   connection.Execute(query); return 0-fai
}

On the above code snippet, you can see we have an query to create a table and it’s just one line to execute the query. And if it’s success the execute method will return you -1.

Step 5:

We will do some more operations in this respective table to understand dapper even better.

Insert data

using (var connection = new SqlConnection(connectionString))
{
    var query = "INSERT INTO [dbo].[CUSTOMER] " +                                                             
                "(CustomerId, CustomerName) " +
		$"VALUES('{Guid.NewGuid()}', 'nullablereference')";
    connection.Execute(query);
}

It’s just insert statement and we are just executing the query. Once you execute it, you will see a new row into your respective table. Next we are going to read the data.

Read data

using (var connection = new SqlConnection(connectionString))
{
   var query = "SELECT * FROM [dbo].[CUSTOMER]";
   var customer = new List<Customer>();

   //dapper takes care of mapping
   customer = connection.Query<Customer>(query).ToList();
}

The real magic of dapper occurs whenever you read the data. You can see, I have straight away mapped the query output with the list of customer. We don’t have to do manual mapping like in SqlCommand class, dapper takes care of it.

Query Multiple

And also you can execute multiple queries at a time.

using (var connection = new SqlConnection(connectionString))
{
   var query1 = "SELECT * FROM [dbo].[CUSTOMER]";
   var query2 = "SELECT * FROM [dbo].[USER]";
   customer = connection.QueryMultiple(query1,query2);
}

Additional Methods in Dapper

There are various methods available in dapper to execute your query such as,

QueryFirst
QueryFirstOrDefault
QuerySingle
QuerySingleOrDefault etc.,

You can also do async operations with the methods available in dapper.

BenchMarking Dapper with SqlCommand

If you haven’t heard of BenchMark Dotnet, I have written a blog about it. Basically, it’s used to compare the performance between two methods or packages.

In this example we are going to benchmark Dapper and SqlCommand.

So Dapper is performing well than SqlCommand and if you make complex queries with Joins etc, you would be able to see a drastic perfomant difference between these two things.

Source code link => https://github.com/SuhasParameshwara/Dapper

Conclusion

As you see, dapper is very much powerful than SqlCommand. It would be really helpful in your enterprise project. And also there’s an another package available called Dapper Plus where there are lot of methods available which will be very helpful to perform read/write opertations.

In our next blog, we will explore about WiX Toolset

Happy Coding!

Cheers! 🙂