Dapper

What’s Dapper?

Dapper is a simple object mapper for .NET and own the title of King of Micro ORM in terms of speed and is virtually as fast as using a raw ADO.NET data reader. An ORM is an Object Relational Mapper, which is responsible for mapping between database and programming language.

Dapper extend the IDbConnection by providing useful extension methods to query your database.

How Dapper Works?

It is a three step process.

  • Create an IDbConnection object.
  • Write a query to perform CRUD operations.
  • Pass query as a parameter in Execute method.

Installation

Dapper is installed through NuGet: https://www.nuget.org/packages/Dapper

PM> Install-Package Dapper

Requirement

Dapper work with any database provider since there is no DB specific implementation.

Methods

Dapper will extend your IDbConnection interface with multiple methods:

string sqlInvoices = "SELECT * FROM Invoice;";
string sqlInvoice = "SELECT * FROM Invoice WHERE InvoiceID = @InvoiceID;";
string sp = "EXEC Invoice_Insert";

using (var connection = My.ConnectionFactory())
{
	var invoices = connection.Query<Invoice>(sqlInvoices).ToList();
	var invoice = connection.QueryFirstOrDefault(sqlInvoice, new {InvoiceID = 1});
	var affectedRows = connection.Execute(sp, new { Param1 = "Single_Insert_1" }, commandType: CommandType.StoredProcedure);
}

Parameter

Execute and queries method can use parameters from multiple different ways:

// Anonymous
var affectedRows = connection.Execute(sql,
                    new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},
                    commandType: CommandType.StoredProcedure);

// Dynamic
DynamicParameters parameter = new DynamicParameters();

parameter.Add("@Kind", InvoiceKind.WebInvoice, DbType.Int32, ParameterDirection.Input);
parameter.Add("@Code", "Many_Insert_0", DbType.String, ParameterDirection.Input);
parameter.Add("@RowCount", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

connection.Execute(sql,
	new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},
	commandType: CommandType.StoredProcedure);

// List
connection.Query<Invoice>(sql, new {Kind = new[] {InvoiceKind.StoreInvoice, InvoiceKind.WebInvoice}}).ToList();

// String
connection.Query<Invoice>(sql, new {Code = new DbString {Value = "Invoice_1", IsFixedLength = false, Length = 9, IsAnsi = true}}).ToList();

Result

The result returned by queries method can be mapped to multiple types:

string sql = "SELECT * FROM Invoice;";

using (var connection = My.ConnectionFactory())
{
    connection.Open();

    var anonymousList = connection.Query(sql).ToList();
    var invoices = connection.Query<Invoice>(sql).ToList();
}

Utilities

// Async
connection.QueryAsync<Invoice>(sql)

// Buffered
connection.Query<Invoice>(sql, buffered: false)

// Transaction
using (var transaction = connection.BeginTransaction())
{
	var affectedRows = connection.Execute(sql,
		new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},
		commandType: CommandType.StoredProcedure,
		transaction: transaction);

	transaction.Commit();
}

// Stored Procedure
var affectedRows = connection.Execute(sql,
	new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},
	commandType: CommandType.StoredProcedure);