Dapper

What’s Dapper?

Dapper is a simple object mapper for .NET and owns the title of King of Micro ORM in terms of speed and is virtually as fast as using a raw ADO.NET data reader. An ORM is an Object Relational Mapper, which is responsible for mapping between database and programming language.

Dapper extends the IDbConnection by providing useful extension methods to query your database.

How Dapper Works?

It is a three-step process.

  • Create an IDbConnection object.
  • Write a query to perform CRUD operations.
  • Pass query as a parameter in the Execute method.

Installation

Dapper is installed through NuGet: https://www.nuget.org/packages/Dapper

  1. PM> Install-Package Dapper

Requirement

Dapper works with any database provider since there is no DB specific implementation.

Methods

Dapper will extend your IDbConnection interface with multiple methods:

  1. string sqlOrderDetails = "SELECT TOP 5 * FROM OrderDetails;";
  2. string sqlOrderDetail = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;";
  3. string sqlCustomerInsert = "INSERT INTO Customers (CustomerName) Values (@CustomerName);";
  5. using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
  6. {
  7.     var orderDetails = connection.Query<OrderDetail>(sqlOrderDetails).ToList();
  8.     var orderDetail = connection.QueryFirstOrDefault<OrderDetail>(sqlOrderDetail, new {OrderDetailID = 1});
  9.     var affectedRows = connection.Execute(sqlCustomerInsert,  new {CustomerName = "Mark"});
  11.     Console.WriteLine(orderDetails.Count);
  12.     Console.WriteLine(affectedRows);
  14.     FiddleHelper.WriteTable(orderDetails);
  15.     FiddleHelper.WriteTable(new List<OrderDetail>() { orderDetail });
  16. }

Try it: .NET Core | .NET Framework

Parameter

Execute and queries method can use parameters from multiple different ways:

  1. // Anonymous
  2. var affectedRows = connection.Execute(sql,
  3.                     new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},
  4.                     commandType: CommandType.StoredProcedure);
  6. // Dynamic
  7. DynamicParameters parameter = new DynamicParameters();
  9. parameter.Add("@Kind", InvoiceKind.WebInvoice, DbType.Int32, ParameterDirection.Input);
  10. parameter.Add("@Code", "Many_Insert_0", DbType.String, ParameterDirection.Input);
  11. parameter.Add("@RowCount", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
  13. connection.Execute(sql,
  14.     parameter,
  15.     commandType: CommandType.StoredProcedure);
  17. int rowCount = parameter.Get<int>("@RowCount");
  19. // List
  20. connection.Query<Invoice>(sql, new {Kind = new[] {InvoiceKind.StoreInvoice, InvoiceKind.WebInvoice}}).ToList();
  21. // String
  22. connection.Query<Invoice>(sql, new {Code = new DbString {Value = "Invoice_1", IsFixedLength = false, Length = 9, IsAnsi = true}}).ToList();

Result

The result returned by queries method can be mapped to multiple types:

  1. string sqlOrderDetails = "SELECT TOP 10 * FROM OrderDetails;";
  3. using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
  4. {
  5.     var anonymousList = connection.Query(sqlOrderDetails).ToList();
  6.     var orderDetails = connection.Query<OrderDetail>(sqlOrderDetails).ToList();
  8.     Console.WriteLine(anonymousList.Count);
  9.     Console.WriteLine(orderDetails.Count);
  11.     FiddleHelper.WriteTable(orderDetails);
  13.     FiddleHelper.WriteTable(connection.Query(sqlOrderDetails).FirstOrDefault());
  14. }

Try it: .NET Core | .NET Framework

Utilities

  1. // Async
  2. connection.QueryAsync<Invoice>(sql)
  4. // Buffered
  5. connection.Query<Invoice>(sql, buffered: false)
  7. // Transaction
  8. using (var transaction = connection.BeginTransaction())
  9. {
  10.     var affectedRows = connection.Execute(sql,
  11.     new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},
  12.     commandType: CommandType.StoredProcedure,
  13.     transaction: transaction);
  15.     transaction.Commit();
  16. }
  18. // Stored Procedure
  19. var affectedRows = connection.Execute(sql,
  20. new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},
  21. commandType: CommandType.StoredProcedure);