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=
|
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
|
Below is extension method which executes query and maps the
result to dynamic objects.
public static
IEnumerable
|
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
{
List
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
{
string query = "SELECT *
FROM Employee WHERE EmpName like '%" + findString + "%'";
return
this._db.Query
}
|
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
{
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
(product, subCategory) => {
product.SubCategory = subCategory; return product; });
}
}
|
Query result maps to dynamic objects Example
Example of dynamic results mapping
public IEnumerable
{
string query = "SELECT
EmpId,EmpName FROM Employee WHERE EmpName like '%" + findString +
"%'";
return
this._db.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
var orders = multi.Read
var returns = multi.Read
...
}
|
Select using stored procedure
Dapper fully supports stored procs.
var user =
cnn.Query
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
int c = p.Get
|
Support for IEnumerable parameter to the query
Dapper allow you to pass in IEnumerable and will
automatically parameterize your query. E.g.
connection.Query
//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
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.
Reference Links
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();
}
Sample
public interface IEmployeeDashBoard
{
List
List
Employee Add(Employee employee);
Employee Update(Employee employee);
void Remove(int id);
IEnumerable
}
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
{
List
return empList;
}
public List
{
string query = "SELECT * FROM Employee WHERE EmpName like '%" + findString + "%'";
return this._db.Query
}
public IEnumerable
{
string query = "SELECT EmpId,EmpName FROM Employee WHERE EmpName like '%" + findString + "%'";
return this._db.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
employee.EmpID = employeeId;
return employee;
}
public void BulkInsert()
{
List
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
string name = p.Get
DateTime date = p.Get
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
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();
}
