Dapper.net-a light weight ORM

I and my friend Gaurav has written this blog. We used Dapper.net in out project and found that when this is effectively used, it also causes performance gain.

Introduction

ORMs helps to flow data from relational database to object models. We are aware of many ORMs like LINQ-To-SQL, Entity Framework etc. They all have some pros and cons.

Dapper.net is a newly introduces light weight ORM. This is simple to use and is better performing hence getting popular now-a-days.

This is free open source software and distributed under dual license, either the Apache License 2.0 or the MIT License. Dapper was started by Sam Saffron and Marc Gravell for StackOverflow and is used by it.

Dapper is a micro-ORM i.e. it does not offer the full range of features of a full ORM such as Entity Framework. This is by design. It focuses on simplicity and performance rather than full automation. Dapper does not generate the SQL queries, but only maps the result to Plain Old CLR Objects (POCOs). Single class ORM is available on NUget.

Dapper's primary feature is mapping from .NET classes to database tables. Dapper is implemented as a series of Extension Methods which can be called on any object which implements the IDbConnection. This is compatible with any database which implement provider for .net (Idbconnection implementation).

We have emphasized below key features in this blog

1. Speed and fast in performance
2. Fewer lines of code
3. Static and dynamic object binding
4. Easy handling of SQL queries and stored procedures
5. Operating directly to IDBConnection class so that running queries directly on database instead of passing data through various objects like that in entity framework.
6. Multiple query support.
7. Bulk data insert.
8. Fetching multiple data at a time
9. Transaction support

Dapper has no DB specific implementation details, it works across all .NET ADO providers including SQLite, SQL CE, Firebird, Oracle, MySQL, PostgreSQL and SQL Server.

People shifted to ORMs because EF like ORMs generated queries under the hood and developer do not need all skills to write a sql query for complex operations. EF query language is simple and object based and easy to understand. Dapper.net does not generates queries for us. This can be one of the drawback of dapper.net. However it is faster over EF and other ORMs which is proved on many links. We can get the performance boost in our application by using dapper.net or we can decide its smart use in our application in combination of entity framework or other ORM and can get best performance benefits wherever we need a performance improvement.

Stack Overflow is the main site which is using dapper.net.

Extension methods provided by dapper.net

Once we declare IDBConnection object, we are ready for using dapper functionality. Note that all extension methods assume the connection is already open, they will fail if the connection is closed.

private IDbConnection _db = new SqlConnection("Data Source=; Database=; Integrated Security=True;");

Below is first extension method which is returning strongly types object results. It executes the query and maps the results to strongly type objects.

public static IEnumerable Query(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)

Below is extension method which executes query and maps the result to dynamic objects.

public static IEnumerable Query (this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)

Below is helper extension method which executes query and do not return results. This is used for creating database, tables, insert, update and delete operations.

public static int Execute(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null)

CRUD Operations using dapper.net

Select operation

Query result maps to strongly typed object Example

Select list of all employees is just a simple call as below. It is returning strongly typed object provided we match the Employee class to database Employee table.

public List GetAll()

{

List empList = this._db.Query("SELECT * FROM Employee").ToList();

return empList;

}

Find query is very simple and it is not returning any exception if I do not have matching record in database.

public List Find(string findString)

{

string query = "SELECT * FROM Employee WHERE EmpName like '%" + findString + "%'";

return this._db.Query(query).ToList();

}

Complex queries where data is coming from two different tables with some join, example:-

public class Product

{

public int ProductID { get; set; }

public string Name { get; set; }

public string ProductNumber { get; set; }

public DateTime ModifiedDate { get; set; }

public SubCategory SubCategory { get; set; }

}

public IEnumerable SelectProductsWithSubCategories()

{

using (IDbConnection connection = OpenConnection())

{

const string query = "SELECT p.ProductID, p.Name, p.ProductNumber, p.ModifiedDate, " +

"s.ProductSubcategoryId AS Id, s.ProductCategoryID AS CategoryId, " +

"s.[Name], s.ModifiedDate AS ModifiedOn " +

"FROM Production.Product p " +

"LEFT OUTER JOIN Production.ProductSubcategory " +

"s ON s.ProductSubcategoryId = p.ProductSubcategoryID";

return connection.Query(query,

(product, subCategory) => { product.SubCategory = subCategory; return product; });

}

Query result maps to dynamic objects Example

Example of dynamic results mapping

public IEnumerable FindWithDynamicSupport(string findString)

{

string query = "SELECT EmpId,EmpName FROM Employee WHERE EmpName like '%" + findString + "%'";

return this._db.Query(query);

}

private void button2_Click(object sender, EventArgs e)

{

string displayResult = "";

var results = _board.FindWithDynamicSupport(textBoxFind.Text);

foreach (var row in results)

{

displayResult += row.EmpId + " " + row.EmpName + "\n";

}

MessageBox.Show(displayResult);

}

Multiple query support

Dapper allows you to process multiple result grids in a single query.

var sql = @"select * from Customers where CustomerId = @id

select * from Orders where CustomerId = @id

select * from Returns where CustomerId = @id";

using (var multi = connection.QueryMultiple(sql, new {id=selectedId}))

{

var customer = multi.Read().Single();

var orders = multi.Read().ToList();

var returns = multi.Read().ToList();

...

}

Select using stored procedure

Dapper fully supports stored procs.

var user = cnn.Query("spGetUser", new {Id = 1},

commandType: CommandType.StoredProcedure).SingleOrDefault();

//or with more fancy way

var p = new DynamicParameters();

p.Add("@a", 11);

p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output);

p.Add("@c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

cnn.Execute("spMagicProc", p, commandType: CommandType.StoredProcedure);

int b = p.Get("@b");

int c = p.Get("@c");

Support for IEnumerable parameter to the query

Dapper allow you to pass in IEnumerable and will automatically parameterize your query. E.g.

connection.Query("select * from (select 1 as Id union all select 2 union all select 3) as X where Id in @Ids", new { Ids = new int[] { 1, 2, 3 });

//this is translated to the query as below

//select * from (select 1 as Id union all select 2 union all select 3) as X where Id in (@Ids1, @Ids2, @Ids3)"

// @Ids1 = 1 ,@Ids2 = 2 , @Ids2 = 3

Insert Operation

Dapper.net allows us to execute a command multiple times like below.

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"

This works for any parameter that implements IEnumerable for some T.

Bulk Insert

Dapper.net has simplified the bulk insert queries. Look at below sample:-

public void BulkInsert()

{

List list = new List() { new Employee() { EmpName = "Name1", EmpScore = 12 }, new Employee() { EmpName = "Name2", EmpScore = 13 } };

var sqlQuery = "INSERT INTO Employee (EmpName, EmpScore) VALUES (@EmpName, @EmpScore); ";

var result = this._db.Execute(sqlQuery, list);

}

Delete Operation

Using Execute extension for Remove operation, dapper.net has provided the delete functionality. Example is as below.

public void Remove(int id)

{

var sqlQuery = ("Delete From Employee Where EmpID = " + id + "");

this._db.Execute(sqlQuery);

}

Create Operations

Dapper.net has not provided create table functionality.

Transaction Support

Dapper also supports transactional operations. Syntax for transaction start and commit is as below. Execute the dapper.net queries in after starting transaction and have commit on successful execution.

IDbTransaction transaction = connection.BeginTransaction();

;

transaction.Commit();

Statistics

A key feature of Dapper is performance. The metrics shows statistics taken from github website provided by dapper.net people itself.

Ref link: https://github.com/StackExchange/dapper-dot-net

Reference Links

https://github.com/StackExchange/dapper-dot-net

http://www.codeproject.com/Articles/212274/A-Look-at-Dapper-NET

https://github.com/StackExchange/dapper-dot-net/blob/master/Dapper.Tests/PerformanceTests.cs

Sample
public interface IEmployeeDashBoard
{
List GetAll();
List Find(string findText);
Employee Add(Employee employee);
Employee Update(Employee employee);
void Remove(int id);
IEnumerable FindWithDynamicSupport(string findString);
}

public class EmployeeDashBoard : IEmployeeDashBoard
{
private IDbConnection _db; // = new SqlConnection("Data Source=pallavi-kul-LAP\\MSSQL2008; Database= test; Integrated Security=True;");
public EmployeeDashBoard(string connectionString)
{
_db = new SqlConnection(connectionString);
}

public List GetAll()
{
List empList = this._db.Query("SELECT * FROM Employee").ToList();
return empList;
}

public List Find(string findString)
{
string query = "SELECT * FROM Employee WHERE EmpName like '%" + findString + "%'";
return this._db.Query(query).ToList();
}

public IEnumerable FindWithDynamicSupport(string findString)
{
string query = "SELECT EmpId,EmpName FROM Employee WHERE EmpName like '%" + findString + "%'";
return this._db.Query(query);
}

public Employee Add(Employee employee)
{
var sqlQuery = "INSERT INTO Employee (EmpName, EmpScore) VALUES (@EmpName, @EmpScore); " + "SELECT CAST(SCOPE_IDENTITY() as int)";
var employeeId = this._db.Query(sqlQuery, employee).Single();
employee.EmpID = employeeId;
return employee;
}

public void BulkInsert()
{
List list = new List() { new Employee() { EmpName = "Name1", EmpScore = 12 }, new Employee() { EmpName = "Name2", EmpScore = 13 } };
var sqlQuery = "INSERT INTO Employee (EmpName, EmpScore) VALUES (@EmpName, @EmpScore); ";
var result = this._db.Execute(sqlQuery, list);
}

public string AddUsingSPN(Employee employee)
{
var p = new DynamicParameters();
p.Add("EmpName", employee.EmpName, null, ParameterDirection.Input, 50);
p.Add("EmpScore", employee.EmpScore, null, ParameterDirection.Input);
p.Add("EmpUpdatedName", null, DbType.AnsiString, ParameterDirection.Output, 50);
p.Add("EmpJoiningDate", null, DbType.DateTime, ParameterDirection.Output);

this._db.Query("CreateEmployeeRecord", p, commandType: CommandType.StoredProcedure);
string name = p.Get("EmpUpdatedName");
DateTime date = p.Get("EmpJoiningDate");
return name;
}

public string QueryMultiplePOC(int i = 4)
{
var p = new DynamicParameters();
p.Add("a", i);
p.Add("r", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
var grid = _db.QueryMultiple("spEcho", p, commandType: CommandType.StoredProcedure);

var result1 = grid.Read>(
(a, b) => Tuple.Create((object)a, (object)b)).ToList();
var result2 = grid.Read>(
(a, b) => Tuple.Create((object)a, (object)b)).ToList();

return ((int)(result1[0].Item1.Id)) + " | " + ((int)(result1[0].Item2.Id));
}

public Employee Update(Employee employee)
{
var sqlQuery =
"UPDATE Employee " +
"SET EmpName = @EmpName, " +
" EmpScore = @EmpScore " +
"WHERE EmpID = @EmpID";
this._db.Execute(sqlQuery, employee);
return employee;
}

public void Remove(int id)
{
var sqlQuery = ("Delete From Employee Where EmpID = " + id + "");
this._db.Execute(sqlQuery);
}
}

//use
EmployeeDashBoard _board = new EmployeeDashBoard(textBoxConnectionstring.Text);

private void RefreshListBox()
{
this.listBox1.DataSource = _board.GetAll();
}
private void buttonRemove_Click(object sender, EventArgs e)
{
_board.Remove(((Employee)(listBox1.SelectedItem)).EmpID);
RefreshListBox();
}
private void buttonAddUsingSP_Click(object sender, EventArgs e)
{
var msg = _board.AddUsingSPN(new Employee(textBox1.Text, double.Parse(textBox2.Text)));
MessageBox.Show(msg);
}
private void buttonQueryMultiplePOC_Click(object sender, EventArgs e)
{
/* ***************** below is SP ***********
* create proc spEcho
@a int
as
begin,

select @a Id, 'ping' Name, 1 Id, 'pong1' Name
select @a Id, 'ping' Name, 2 Id, 'pong2' Name
return @a
end
* */
/* ***************** Result ******************
*
*
(1 row(s) affected)

Id Name Id Name
----------- ---- ----------- -----
3 ping 2 pong2

(1 row(s) affected)

Return Value
------------
3

(1 row(s) affected)

* */

MessageBox.Show(_board.QueryMultiplePOC());

}

private void buttonFind_Click(object sender, EventArgs e)
{
string textToFind = textBoxFind.Text;
List empList = _board.Find(textToFind);
MessageBox.Show(empList.Count.ToString());
}

private void buttonReEstablishConnection_Click(object sender, EventArgs e)
{
_board = new EmployeeDashBoard(textBoxConnectionstring.Text);
}
private void buttonFindWithDynamicSupport_Click(object sender, EventArgs e)
{
string displayResult = "";
var results = _board.FindWithDynamicSupport(textBoxFind.Text);
foreach (var row in results)
{
displayResult += row.EmpId + " " + row.EmpName + "\n";
}
MessageBox.Show(displayResult);
}
private void buttonBulkInsert_Click(object sender, EventArgs e)
{
_board.BulkInsert();
RefreshListBox();
}

Pal's Space

Pages

Thursday, April 28, 2016