Customers who sign-up prior to 30/06/2024 get unlimited access to free features, newer features (with some restrictions), but for free for at least 1 year.Sign up now! https://webveta.alightservices.com/
Categories
.Net C#

Dapper for data access in C# part 2

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:

  1. ADO.Net
  2. Entity Framework
  3. 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:

  1. No need of strongly typed objects
  2. Multiple RDBMS support
  3. 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

I don’t have any fake aliases, nor any virtual aliases like some of the the psycho spy R&AW traitors of India. NOT associated – “ass”, eass, female “es”, “eka”, “ok”, “okay”, “is”, “erra”, yerra, karan, kamalakar, diwakar, kareem, karan, sowmya, zinnabathuni, bojja srinivas (was a friend and batchmate 1998 – 2002), mukesh golla (was a friend and classmate 1998 – 2002), thota veera, uttam’s, bandhavi’s, bhattaru’s, thota’s, bojja’s, bhattaru’s or Arumilli srinivas or Arumilli uttam (may be they are part of a different Arumilli family – not my family).

Mr. Kanti Kalyan Arumilli

Arumilli Kanti Kalyan, Founder & CEO
Arumilli Kanti Kalyan, Founder & CEO

B.Tech, M.B.A

Facebook

LinkedIn

Threads

Instagram

Youtube

Founder & CEO, Lead Full-Stack .Net developer

ALight Technology And Services Limited

ALight Technologies USA Inc

Youtube

Facebook

LinkedIn

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.