In the past, I have written about A quick introduction to Dapper! This blog article is a continuation:
The most popular ways to access data in .Net are:
- ADO.Net
- Entity Framework
- 3rd party ORMs such as Dapper
ADO.Net needs a lot of code i.e opening IDataReader, iterating, reading elements and mapping etc… Similarly when calling Stored Procedures, adding params etc…
EntityFramework is a great choice for a lot of different situations.
When connecting to RDBMS such as SQL Server, MySQL, I prefer StoredProcs over dynamically generated SQL for several reasons. Consider this as part of some best practices from security and performance reasons rather than ease of code. In the past, I have been responsible and represented audits from development team perspective.
The 3rd choice – 3rd party ORMs and Micro ORMs. Dapper is a micro ORM. Dapper has excellent performance and probably the most suitable when handling Stored Procedures. Dapper supports many different RDBMS types.
Even Dapper call’s can be easily wrapped around and logging etc… can be done. Almost all the methods accept optional Transaction for wrapping multiple calls in a single transaction.
https://www.learndapper.com/ has excellent documentation.
Some of the nicer features of Dapper are:
- No need of strongly typed objects
- Multiple RDBMS support
- Ability to read multiple resultsets
Some code samples (MySQL example):
using Dapper;
using (var connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
await connection.ExecuteAsync(
"STORED_PROC_NAME",
new {
varParam1 = "value1",
varParam2 = 1
}, null, null, CommandType.StoredProcedure);
}Some of the other useful methods are:
connection.ExecuteReaderAsync //For any reason, if you need IDataReader
ExecuteScalar // For single object
// or
ExecuteScalar<T> //For single object of type T
// or
ExecuteScalarAsync(), ExecuteScalaAsync<T>() // The async equivalents
Query()
QueryFirst()
QueryFirstOrDeafult()
QuerySingle()
BTW, combining these with Polly and creating robust wrappers would be a recommended approach.
Polly library for writing resilient .Net code
–
Mr. Kanti Kalyan Arumilli
B.Tech, M.B.A
Founder & CEO, Lead Full-Stack .Net developer
ALight Technology And Services Limited
Phone / SMS / WhatsApp on the following 3 numbers:
+91-789-362-6688, +1-480-347-6849, +44-07718-273-964
+44-33-3303-1284 (Preferred number if calling from U.K, No WhatsApp)
kantikalyan@gmail.com, kantikalyan@outlook.com, admin@alightservices.com, kantikalyan.arumilli@alightservices.com, KArumilli2020@student.hult.edu, KantiKArumilli@outlook.com and 3 more rarely used email addresses – hardly once or twice a year.
