Dapper.NET and its overview

Nikhil

Dapper.NET which is a free, Open-Source, lightweight “Micro-ORM” written by the developers behind Stack Exchange that is distributed under dual license, either the Apache License 2.0 or the MIT License.

It’s a framework to map domain model to the relational database.

One of its strong points is its “genericness” to databases. Dapper internally uses 3 interfaces: IDbConnection, IDbCommand, IDbTransaction.So any data provider which implements these interfaces can use Dapper.

Dapper is the second fastest ORM.

It’s being used by StackExchange community as well.

Few Key Features are

The following are the key features of Dapper:

  • Object Mapper
  • Key Feature of Dapper is Performance
  • Require less lines of code.
  • Supports Static Object Binding.
  • Supports Dynamic Object Binding.
  • Dapper allow storing bulk data at once
  • Easy Handling of SQL Query.
  • Easy Handling of Stored Procedure.
  • Operate directly to IDBConnection class that provides smoothness and runs query directly to the database instead of passing data using various objects as we do in EF and ADO.NET
  • Multiple Query Support.
  • Support for Stored Procedure, Linq & Joins, etc
  • Supports Bulk Data insert functionality.
  • If u pass the list of objects, Dapper will identify that insertion is a bulk insert
  • Fetch data based on various parameters and Dapper will automatically convert the array into CSV and return all in a list of objects.

Dapper Performance Comparison with other ORM’s

Performance of Select mapping over 500 iterations
MethodDuration (in ms)
Hand Coded(Using SqlDataReader)47
Dapper (ExecuteMapperQuery)49
ServiceStack OrmLite(QueryById)50
PetaPoco52
BLToolkit80
NHibernate SQL104
Linq 2 SQL ExecuteQuery181
EntityFramework631

 

Installing Dapper in .Net project

You can download dapper using NuGet, the command in the Package Manager Console is:

PM> Install-Package Dapper

Steps to Use Dapper

Step 1

Create an IDBConnection object with Connection String.

IDbConnection db = new SqlConnection(ConnectionString)  

Step 2

Write a query and store it in a normal string variable.

String query= ”select * from tbl_Employee”;

Step 3

Call db.Query() and pass the query. Done!

List employee= (List<Employee>)db.Query<Employee>(query);

Dapper Extension Methods

It provides 4 helpers:

  1. Execute a query and map the results to a strongly typed List

public List<Employee> employees = _db.Query<Employee>
   (“SELECT EmployeeId, EmployeeCode, Name, Address, IsActive FROM tbl_Employee”).ToList();

  1. Execute a query and map it to a list of dynamic objects

var rows = connection.Query(“SELECT EmployeeId, Name, Address FROM tbl_Employee“);

  1. Execute a Command that returns no results

string query=”Insert into tbl_Employee values(‘EmployeeName’, ‘Emp100’, ‘IT’)”;

_db.Execute(query);

  1. Execute a Command multiple times

connection.Execute(@”insert MyTable(colA, colB) values (@a, @b)”,
    new[] { new { a=1, b=1 }, new { a=2, b=2 }, new { a=3, b=3 } }).IsEqualTo(3); // 3 rows

inserted: “1,1”, “2,2” and “3,3”

  • Query():
    The Query() extension method in the Dapper framework enables you to retrieve data from the database and populate data in your domain object model or dynamic object.

Example:

public class Employee
{
   private readonly IDbConnection _db = new SqlConnection(“ConnectionString”);
   public List<Employee> employees = _db.Query<Employee>
   (“SELECT EmployeeId, EmployeeCode, Name, Address, IsActive FROM tbl_Employee”).ToList();
}

As you can see above, dapper has reduced the data fetching code to just 2 lines of code as compared to usual ADO.Net code. Also it maps the data from relational database to a domain object (Employee).

  • Execute():
    The Execute() method of the Dapper framework can be used for database create, update, or delete operations. Execute method returns integer value stating the number of rows being affected with this method execution.

Example:

public class Employee
{
private readonly IDbConnection _db = new SqlConnection(“ConnectionString”);

private int Insert()

{

string query=”Insert into tbl_Employee values(“EmployeeName”, “Emp100”, “IT Department”)”;

int rowsAffected = _db.Execute(query);

}

}

As you can see from the code above, it is very simple to also use the Execute() method to insert data into the database. The same format can be used for using the updating and deleting commands in a database.

Dapper With Stored Procedures

Dapper fully supports stored procedures.

var user = con.Query<User>(“spGetEmployees”, new {Id = 1},
       commandType: CommandType.StoredProcedure).SingleOrDefault();

Passing Parameters to Stored procedure using Dapper

var p = new DynamicParameters();
p.Add(“@EmployeeCode”, 100);
p.Add(“@EmployeeId”, dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add(“@EmployeeName”, dbType: DbType.String, direction: ParameterDirection.ReturnValue);

con.Execute(“spInsertData”, p, commandType: CommandType.StoredProcedure);
int EmployeeCode= p.Get<int>(“@EmployeeId”);
String EmployeeName= p.Get<int>(“@EmployeeName”);

Dapper Code Comparison with Entity Framework code

Entity Framework LINQ query:

public class Employee
{

public Employee GetAllEmployees()

{
using (var context = new SchoolDBEntities())
{
    var employees= (from emp in context.Employee
                   where IsActive ==true
                   select st).ToList();

// Automapper code to map result from above L2E query to domain model goes here
    }

}

}

Dapper LINQ query:

public class Employee
{

public Employee GetAllEmployees()

{

   public List<Employee> employees = _db.Query<Employee>
   (“SELECT EmployeeId, EmployeeCode, Name, Address, IsActive FROM tbl_Employee

where IsActive ==true).ToList();

// No mapper code required as Dapper internally maps the result into specified domain model
}

}

 

360 Videos

about the author

Nikhil