Introduction to Dapper ORM

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.

Parameters of Dapper :

1. sql – The command to execute.

2. param – The command parameters (default = null).

3. commandTimeout – The command timeout (default = null).

4. commandType -The command type (default = null).

Methods of Dapper :

1. Execute : Execute is a method called from IDbConnection type object which can execute command one or multiple times and return the number of affected rows in the database tables. It can execute Stored Procedures, SELECT/INSERT/DELETE statements, etc.

Example :

C#

string sql = "INSERT INTO Companies (CompanyName) 
                Values (@CompanyName);";
  
using (var connection = new SqlConnection(
    FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    var affectedRows = connection.Execute(sql, 
              new {CompanyName = "GeeksforGeeks"});
}

2. Query : The query is a method called from IDbConnection type object which can execute a query and map the result.

C#

string sql = "SELECT * FROM Companies";
  
using (var connection = new SqlConnection(
          FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    var companies = connection.Execute(sql);
}

3. QueryFirst : QueryFirst is a method called from IDbConnection type object which can execute a query and map the first result.

C#

string sql = "SELECT * FROM Companies WHERE CompanyId = @CompanyId";
  
using (var connection = new SqlConnection(
     FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    var company = connection.QueryFirst(sql, 
                           new {CompanyId = 1});
}

Note: QueryFirstOrDefault method is a method that can execute a query and map the first result, or a default value if the sequence contains no elements.

4. QuerySingle : QuerySingle is a method called from IDbConnection type object which can execute a query and map the first result and throws an exception if there is not exactly one element in the sequence.

C#

string sql = "SELECT * FROM Companies WHERE CompanyId = @CompanyId";
  
using (var connection = new SqlConnection(
          FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    var company = connection.QuerySingle(sql, 
                             new {CompanyId = 1});
}

Note: QuerySingleOrDefault method is a method that can execute a query and map the first result, or a default value if the sequence contains no elements and throws an exception if there is more than one element in the sequence

Features:

• Speed and fast in performance.

• Fewer lines of code.

• Object Mapper.

• Static Object Binding.

• Dynamic Object Binding.

• Easy Handling of SQL Query.

• Easy Handling of Stored Procedure.

Advantages:

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.

Disadvantages:

Here are some of the disadvantages of Dapper:

• Dapper can't generate a class model for you.

• It cannot generate queries for you.

• It cannot track objects and their changes.

• The raw dapper library doesn't provide CRUD features, but the "contrib" additional package does provide basic CRUD.

The Tech Platform

www.thetechplatform.com