With Microsoft now firmly pushing Entity Framework (currently at version 4) as their preferred data access solution, it’s refreshing to see a couple of new players on the scene albeit perhaps not in direct competition.
Dapper (Dynamic Query Mapper) and PetaPoco are Mirco ORMs. An ORM is an Object Relational Mapper which sit between your business logic and database to abstract away the direct link between the two. Micro ORMs are a simplier and more efficient option to larger ORM frameworks such as EF4 and NHibernate.
In this demo (full source code downloadable from the links at the end of this article) I’ll be giving examples of how to use each of the frameworks against a SQL Server database for your CRUD (create/insert, read/select, update, delete) commands. I’m using the tempdb database so there' is no need to run any database scripts.
It’s important to note that for performance you still get a marginal benefit creating a hand coded DAL with SQLConnection, SQLCommand and all those SQLParameter objects like this:
public Customer Select()
{
var customer = null;
using (var connection = SqlHelper.GetOpenConnection())
{
using (var command = new SqlCommand(SqlHelper.ReadStatement, connection)
{ CommandType = CommandType.Text })
{
command.Parameters.Add("@Id", SqlDbType.Int);
command.Parameters[0].Value = SqlHelper.TestId;
var reader = command.ExecuteReader();
while (reader.Read())
{
customer = new Customer()
{
CustomerId = reader.GetString(0),
CompanyName = reader.GetString(1)
};
}
}
}
return customer;
}
But to hand code your own entire DAL is pretty time consuming and hard to maintain particularly when you’ve got commands with plenty of parameters. Now you could use T4 to generate your DAL based on your database schema and that’s probably still your best bet if performance is everything to your application. Or, for a minimal perf overhead (most LOB apps wouldn’t notice) you could use one of our new Micro ORMs!
To carry out the same task as the hand coded DAL above, Dapper requires only two lines of code:
var mapperConnection = SqlHelper.GetOpenConnection();
mapperConnection.Query<Customer>(SqlHelper.ReadStatement, new { Id = SqlHelper.TestId });
Note: This is using the (customer) typed Query, you can also use a dynamic version that omits the generic parameter of Customer to return a Dynamic Object. Dynamic Objects were introduced in .NET Framework 4.
For PetaPoco only three lines of code are required:
var petaPoco = new PetaPoco.Database(SqlHelper.ConnectionString, "System.Data.SqlClient");
petaPoco.OpenSharedConnection();
var customer = petaPoco.Fetch<Customer>(SqlHelper.ReadStatement, SqlHelper.TestId);
Note: This example is using the default execution options, you can also tweak the options to boost performance shown later in this blog.
You can see from a RAD point of view Micro ORMs really come into their own compared to hand coding your DAL. Since the start of .NET plenty of frameworks have been created to generate your Sql commands and parameters (based on your DAL method attributes or parameters) but these Micro ORM frameworks are special.
Firstly, other than one file for each framework no other code is required to pollute your solution. No attributes clogging up your DAL or DTOs or Business Layers. But now for the really special bit, both frameworks are actually generating code dynamically using Dynamic Methods and ILGenerators which is pretty cool and inspiring stuff. The source code for both frameworks are included in the source code download to this blog post and I encourage you to take a look – kudos to the guys that put these frameworks together!
In terms of performance, the full Dapper project (link also at the end of the post) includes a test project that runs a simple select statement 500 times and compares performance against various data access solutions, the results of which can be seen here:
Note: Dapper is named as Mapper Query when using the typed/generic read method and Dynamic Mapper Query when using the non generic method.
2nd Note: This code was produced on a travel netbook (I’m writing this blog post whilst stuck in Delhi airport for the night being eaten alive by mosquitos :) ). I would expect the figures to be much faster on a newer PC or laptop.
Examples for C, R, U and D code
Dapper
private static void DapperExamples()
{
Console.WriteLine("Running Dapper examples");
SqlConnection mapperConnection = SqlHelper.GetOpenConnection();
// CREATE
mapperConnection.Execute(SqlHelper.CreateStatement,
new
{
Id = SqlHelper.TestId,
CompanyName = SqlHelper.InsertCompanyName
});
// READ Mapper Query
IEnumerable<Customer> customer1 =
mapperConnection.Query<Customer>(SqlHelper.ReadStatement, new {Id = SqlHelper.TestId});
// READ Dynamic Object Mapper Query
var customer2 = mapperConnection.Query(SqlHelper.ReadStatement, new {Id = SqlHelper.TestId});
// UPDATE
mapperConnection.Execute(SqlHelper.UpdateStatement,
new
{
Id = SqlHelper.TestId,
CompanyName = SqlHelper.UpdateCompanyName
});
// DELETE
mapperConnection.Execute(SqlHelper.DeleteStatement, new {Id = SqlHelper.TestId});
}
PetaPoco
private static void PetaPocoExamples()
{
Console.WriteLine("Running PetaPoco examples");
var petapoco = new Database(SqlHelper.ConnectionString, "System.Data.SqlClient");
petapoco.OpenSharedConnection();
// CREATE
petapoco.Execute(SqlHelper.CreateStatementWithIndexedParams, SqlHelper.TestId, SqlHelper.InsertCompanyName);
// READ with all default options
List<Customer> customer1 = petapoco.Fetch<Customer>(SqlHelper.ReadStatementWithIndexedParams,
SqlHelper.TestId);
// READ with some "smart" functionality disabled
var petapocoFast = new Database(SqlHelper.ConnectionString, "System.Data.SqlClient");
petapocoFast.OpenSharedConnection();
petapocoFast.EnableAutoSelect = false;
petapocoFast.EnableNamedParams = false;
petapocoFast.ForceDateTimesToUtc = false;
List<Customer> customer2 = petapocoFast.Fetch<Customer>(SqlHelper.ReadStatementWithIndexedParams,
SqlHelper.TestId);
// UPDATE
petapoco.Execute(SqlHelper.UpdateStatementWithIndexedParams, SqlHelper.TestId, SqlHelper.UpdateCompanyName);
// DELETE
petapoco.Execute(SqlHelper.DeleteStatementWithIndexedParams, SqlHelper.TestId);
}
SqlHelper class
internal static class SqlHelper
{
public static readonly string ConnectionString =
@"Data Source=.;Initial Catalog=tempdb;Integrated Security=True";
public static readonly string CreateStatement =
@"INSERT dbo.Customers (CustomerID, CompanyName) SELECT @id, @companyName";
public static readonly string ReadStatement = @"SELECT * FROM dbo.Customers WHERE CustomerId = @id";
public static readonly string UpdateStatement =
@"UPDATE dbo.Customers SET CompanyName = @companyName WHERE CustomerId = @id";
public static readonly string DeleteStatement = @"DELETE FROM dbo.Customers WHERE CustomerId = @id";
// PetaPoco parameters are named based on index
public static readonly string CreateStatementWithIndexedParams =
@"INSERT dbo.Customers (CustomerID, CompanyName) SELECT @0, @1";
public static readonly string ReadStatementWithIndexedParams =
@"SELECT * FROM dbo.Customers WHERE CustomerId = @0";
public static readonly string UpdateStatementWithIndexedParams =
@"UPDATE dbo.Customers SET CompanyName = @1 WHERE CustomerId = @0";
public static readonly string DeleteStatementWithIndexedParams =
@"DELETE FROM dbo.Customers WHERE CustomerId = @0";
public static readonly string TestId = "TEST";
public static readonly string InsertCompanyName = "Inserted company";
public static readonly string UpdateCompanyName = "Updated company";
internal static void EnsureDbSetup()
{
using (var cnn = GetOpenConnection())
{
var cmd = cnn.CreateCommand();
cmd.CommandText = @"
if (OBJECT_ID('Customers') is null)
begin
CREATE TABLE [dbo].[Customers](
[CustomerID] [nchar](5) NOT NULL,
[CompanyName] [nvarchar](40) NOT NULL,
[ContactName] [nvarchar](30) NULL,
[ContactTitle] [nvarchar](30) NULL,
[Address] [nvarchar](60) NULL,
[City] [nvarchar](15) NULL,
[Region] [nvarchar](15) NULL,
[PostalCode] [nvarchar](10) NULL,
[Country] [nvarchar](15) NULL,
[Phone] [nvarchar](24) NULL,
[Fax] [nvarchar](24) NULL)
end
";
cmd.Connection = cnn;
cmd.ExecuteNonQuery();
}
}
public static SqlConnection GetOpenConnection()
{
var connection = new SqlConnection(ConnectionString);
connection.Open();
return connection;
}
}
SQL Profiler
Using SQL Profiler we can view the TSQL that is being sent by Dapper and PetaPoco:
I hope you find this article helpful, happy coding!
Downloads
Blog source code (including source for Dapper and PetaPoco): http://www.stevenhollidge.com/blog-source-code/MicroOrmDemo.zip
Dapper (includes performance tests comparing Data Access solutions): http://code.google.com/p/dapper-dot-net/
PetaPoco: http://www.toptensoftware.com/petapoco/
Great post. Definately interested in using a micro-ORM...
ReplyDeleteHow well do you think dapper works with .NET 3.5?
The link to your source code doesn't appear to work. I took out the www in the file path and i found a file, but it doesn't have the example code...
Hi Kevin,
ReplyDeleteBoth work frameworks use the System.Dynamic. Expando object from .NET 4.0 so they won't immediately work out of the box with .NET 3.5. You have access to the source code so you could strip this part out if you wanted to.
Dapper has recently updated it's interface so I've updated the blog post to reflect the change.
My sample project now uses tempdb rather than Northwind so you run the code straight away. Oh and I fixed the link, thanks for the heads up :)
Steve
we actually just backported dapper to .net 3.5, see the source...
ReplyDeleteSteven, thanks for the post! Happy to hear the word get out :)
ReplyDeletehi, i have a question, i hope you can help me.
ReplyDeletei just start with asp.net and petapoco, actually its my first approach.
so i want to know how to display the info obtained from the example
List customer1 = petapoco.Fetch(SqlHelper.ReadStatementWithIndexedParams,
SqlHelper.TestId);
so i want to print the info gathered from customer 1, but didnt
i use response.write(customer1);
thanks in advance
the method is Fetch so return type is collection/list, better to use foreach or anyother looping mechanism
Deletehttps://bayanlarsitesi.com/
ReplyDeleteTokat
Kastamonu
Tekirdağ
Gümüşhane
KW00L
Batman
ReplyDeleteArdahan
Adıyaman
Antalya
Giresun
FQM30
görüntülüshow
ReplyDeleteücretli show
H2Cİ7
https://titandijital.com.tr/
ReplyDeletedenizli parça eşya taşıma
sinop parça eşya taşıma
artvin parça eşya taşıma
antep parça eşya taşıma
KLXEY4
Malatya Lojistik
ReplyDeleteAntep Lojistik
Urfa Lojistik
Sivas Lojistik
Erzurum Lojistik
EH8E
adana evden eve nakliyat
ReplyDeletebolu evden eve nakliyat
diyarbakır evden eve nakliyat
sinop evden eve nakliyat
kilis evden eve nakliyat
HBİFJ
F7CF1
ReplyDeleteSiirt Evden Eve Nakliyat
order masteron
Sakarya Evden Eve Nakliyat
buy pharmacy steroids
İzmir Evden Eve Nakliyat
Amasya Evden Eve Nakliyat
Kripto Para Nedir
Silivri Çatı Ustası
parabolan for sale
7DF41
ReplyDeleteKeçiören Boya Ustası
Samsun Şehir İçi Nakliyat
Bingöl Lojistik
Bolu Parça Eşya Taşıma
Karabük Şehirler Arası Nakliyat
Edirne Lojistik
Elazığ Evden Eve Nakliyat
Çerkezköy Fayans Ustası
Balıkesir Şehir İçi Nakliyat
9D7FA
ReplyDeleteAmasya Lojistik
Mardin Evden Eve Nakliyat
Rize Parça Eşya Taşıma
Ünye Oto Boya
Silivri Parke Ustası
Çankırı Evden Eve Nakliyat
Kayseri Parça Eşya Taşıma
Denizli Şehir İçi Nakliyat
Bayburt Şehirler Arası Nakliyat
3613E
ReplyDeleteFlare Coin Hangi Borsada
Tokat Evden Eve Nakliyat
Kocaeli Evden Eve Nakliyat
Kaspa Coin Hangi Borsada
İzmir Şehirler Arası Nakliyat
Karapürçek Boya Ustası
Bartın Parça Eşya Taşıma
Ağrı Evden Eve Nakliyat
Nevşehir Şehirler Arası Nakliyat
12C8F
ReplyDeleteDüzce Şehirler Arası Nakliyat
Çerkezköy Buzdolabı Tamircisi
Elazığ Şehir İçi Nakliyat
Ardahan Lojistik
Karaman Şehirler Arası Nakliyat
Ünye Evden Eve Nakliyat
Çankaya Parke Ustası
Urfa Lojistik
İzmir Lojistik
8D7C2
ReplyDeleteCoin Nedir
Bitcoin Madenciliği Siteleri
Kripto Para Madenciliği Nedir
Binance Nasıl Kayıt Olunur
Kripto Para Nasıl Alınır
Binance Borsası Güvenilir mi
Bitcoin Madenciliği Nedir
resimli magnet
Bitcoin Kazanma Siteleri
67AFB
ReplyDeleteyozgat goruntulu sohbet
Bartın Mobil Sohbet Et
düzce parasız görüntülü sohbet
adana canlı sohbet siteleri ücretsiz
sivas canlı sohbet odaları
eskişehir telefonda sohbet
yabancı sohbet
Muş Ücretsiz Sohbet Uygulaması
canli goruntulu sohbet siteleri
8CB8B
ReplyDeletegörüntülü sohbet uygulamaları ücretsiz
düzce mobil sesli sohbet
kırşehir sohbet odaları
random görüntülü sohbet
Samsun Ücretsiz Sohbet Siteleri
adana canli goruntulu sohbet siteleri
Antep Mobil Sohbet Chat
Ankara Ücretsiz Görüntülü Sohbet
kocaeli rastgele görüntülü sohbet ücretsiz
09F4F
ReplyDeleteSiirt Görüntülü Canlı Sohbet
Muğla Telefonda Görüntülü Sohbet
eskişehir muhabbet sohbet
burdur telefonda sohbet
elazığ rastgele sohbet uygulaması
Aksaray Görüntülü Sohbet Sitesi
agri görüntülü sohbet uygulama
afyon canli sohbet chat
nevşehir canlı görüntülü sohbet uygulamaları
72CCA
ReplyDeleteAnc Coin Hangi Borsada
Btcst Coin Hangi Borsada
Mefa Coin Hangi Borsada
Instagram Takipçi Hilesi
Coin Kazma
Bitcoin Hesap Açma
Bitcoin Kazanma
Coin Nasıl Alınır
Referans Kimliği Nedir
رقم المجاري بالاحساء KtgZXsmb2O
ReplyDeleteشركة تسليك مجاري بالاحساء xc020cdRzc
ReplyDeleteرقم مصلحة المجاري بالاحساء bRDnUlvKma
ReplyDeleteشركة تنظيف بالاحساء 5ZeFx0jlR5
ReplyDeleteتسليك مجاري بالهفوف 799MUXORmz
ReplyDeleteصيانه افران الغاز بمكه
ReplyDeleteox0rVhiFcj
شركة تنظيف مساجد بالاحساء GIGivFabVT
ReplyDeleteشركة تنظيف بالاحساء Bbgzfkr1Qi
ReplyDeleteشركة تنظيف خزانات GbmacZ0P94
ReplyDeleteشركة عزل خزانات بالرس mW1ByjGPgx
ReplyDelete