Object Relational Mappers
If you're reading this, I assume that you know what an ORM is. If you don't: just imagine having a database with some tables that have columns. If you want to access that data in an application or a website (or ...) you need to have a representation of those tables in your code. That representation is usually reffered to as a collection of POCO's (plain old CLR objects).
To create these POCO's you usually have 2 choices:
- You can create them yourself manually (create a POCO with its own properties for each table in your database, or for each table that you need in your application). You can then write custom code to map your database object to your POCO.
- You can have these POCO's automatically created and mapped by an ORM.
For small applications, the former might be a good solution, but for applications that have huge databases with lots of tables and columns, orm's are most definitely the way to go. On top of automatically creating poco's for each table, ORM's also provide a so called "context" that tracks all changes done to a certain object. Having this context enables you to just fetch an item from your database, change some properties of the object and call SaveChanges() (or something like that, depending on the orm). Another key feature is that they also allow you to use linq & lambda expressions, that in turn is converted/translated to SQL statements.
There are lots of ORMs for .NET, the most popular being Entity Framework, NHibernate and Linq2Sql (which is obsolete but still widely used). Orm's like Entity Framework obviously do more than what I've just described above here, and sometimes that causes an issue: performance. There are lots of articles on ORMS like Entity framework and how slow they are. If you're interested in the how and why, google is your best friend :) If you're already convinced that these popular ORM's are not doing it for you/your application, let me introduce you to
Dapper.NET is a micro ORM created by the same guys that run/created stackoverflow.com (that should give you some confidence in Dapper.NET already)
"Dapper is a micro-ORM: it does not offer the full range of features of a full ORM such as nHibernate or 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). Third party extensions are available for mapping objects to INSERT and UPDATE queries. It is optimized for use with .NET 4.0 or higher."
Basically what the above says is: if you dont need bloated and huge data models, and you require speed above anything else: go with something like Dapper.NET (there are other so called micro ORM's, like PetaPoco, Massive, Simple.Data,...)
How does it work?
You can install Dapper through Nuget (Install-Package Dapper).
Once you have it installed, you can get to work immediately. All you have to do is create a SqlConnection (in our case, but it works across all .net ado providers including sqlite, sqlce, firebird, oracle, MySQL, PostgreSQL and SQL Server)
Then create some classes (in this case I did it manually, but you can always use a T4 template to do this.
You're all set now, you can start querying.
The sample above showcases various scenarios:
- First query will retrieve a list of deployments and automatically map them to the classes we created above.
- Second query shows you how to use a where clause
- Third query shows how you can use inner/left/..joins
- All of the above queries were automatically mapped to a predefined class. This is not required, you can also just pass in an SQL statement. You will get back an object of type <dynamic> with all the values you requested.
There's obviously much more you can do with Dapper.NET, check their GitHub page for more info.
Apart from the easy usage, the key aspect ofcourse is speed/performance.
This is taken from Dapper.NET's GitHub page. More benchmarks are available there.
Performance of SELECT mapping over 500 iterations - POCO serialization
|Hand coded (using a
|Linq 2 SQL
As you can see, Dapper's mapping from sql object to POCO is among the fastest around (granted, benchmarks always need to be taken with a grain of salt).
There are also some disadvantages. Because it is so lightweight, many feature that ORMs ship with are stripped out. There are no helpers for update / select and so on. This is imho the biggest drawback, although it is open source so if you want you can just add it in yourself!
Bottomline is: if you need something that is lightweight, fast, and easy to use: instead of going for Entity Framework or Linq2Sql like you usually do, try out something different and experiment a little with it!