Today we can find a bunch of third party tools created by a n number of groups to use in project for different purposes. Few of them are paid and few are open source I mean we can get it free of cost. They all fulfill different requirements raised by clients. Dapper is one of those and is created by Stack Overflow developers.
Dapper is a simple object mapper which can be used to map database objects with model. It is an Open-Source, lightweight ORM (object-relational-mapper) written by the developers in Stack Overflow. It can be easily implemented in our project and is compatible with any database. It works with IDBConnection interface in .Net and can map any simple or complex data model. It has in-build methods to perform CRUD operations like create, update, delete or select. We will discuss all one by one by simple examples.
First of all we will see the dll to used for Dapper. We will use two dll to implement dapper in our project.
We can put reference of these two dll in our project and we are continue with next step.
Now when we can add references of Dapper dll so our next step should be to create a model which will be used later for different operations. Here I have created a class with name StockDetail and an interface with name IStockDetail. You can only create a class also if you want but I have created interface for this class as I follow this practice.
using System;
using System.Collections.ObjectModel;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DapperAttribute = Dapper.Contrib.Extensions;
namespace MyProjectProect
{
public interface IStockDetail
{
/// <summary>
/// Gets or sets the identifier.
/// </summary>
/// <value>
/// The identifier.
/// </value>
int ID { get; set; }
/// <summary>
/// Gets or sets the stock type.
/// </summary>
/// <value>
/// The stock type.
/// </value>
string StockType { get; set; }
/// <summary>
/// Gets or sets the stock name.
/// </summary>
/// <value>
/// The stock name.
/// </value>
string StockName { get; set; }
/// <summary>
/// Gets or sets the date time.
/// </summary>
/// <value>
/// The date time.
/// </value>
DateTime CreatedDate { get; set; }
/// <summary>
/// Gets or sets the average.
/// </summary>
/// <value>
/// The average.
/// </value>
decimal Average { get; set; }
/// <summary>
/// Gets or sets the keyed percentage average.
/// </summary>
/// <value>
/// The keyed percentage average.
/// </value>
decimal KeyedPercentageAverage { get; set; }
/// <summary>
/// Gets or sets the customer ID.
/// </summary>
/// <value>
/// The customer ID.
/// </value>
string CustomerID { get; set; }
}
/// <summary>
/// class for StockDetail
/// </summary>
[DapperAttribute.Table("StockDetail")]
public sealed class StockDetail : IStockDetail
{
/// <summary>
/// Initializes a new instance of the <see cref="StockDetail"/> class.
/// </summary>
public StockDetail()
{
this.StockDetailListings = new Collection<StockDetailListing>();
}
/// <summary>
/// Gets or sets the identifier.
/// </summary>
/// <value>
/// The identifier.
/// </value>
[DapperAttribute.Key]
public int ID { get; set; }
/// <summary>
/// Gets or sets the stock type.
/// </summary>
/// <value>
/// The stock type.
/// </value>
public string StockType { get; set; }
/// <summary>
/// Gets or sets the stock name.
/// </summary>
/// <value>
/// The stock name.
/// </value>
public string StockName { get; set; }
/// <summary>
/// Gets or sets the date time.
/// </summary>
/// <value>
/// The date time.
/// </value>
public DateTime CreatedDate { get; set; }
/// <summary>
/// Gets or sets the average.
/// </summary>
/// <value>
/// The average.
/// </value>
public decimal Average { get; set; }
/// <summary>
/// Gets or sets the keyed percentage average.
/// </summary>
/// <value>
/// The keyed percentage average.
/// </value>
public decimal KeyedPercentageAverage { get; set; }
/// <summary>
/// Gets or sets the customer ID.
/// </summary>
/// <value>
/// The customer ID.
/// </value>
public string CustomerID { get; set; }
}
}
Now when my model is ready then let us play with CRUD operation. Before doing this let me explain one or two attributes I used in my model. First of all I used [DapperAttribute.Table("StockDetail")] attribute on the top of my class. Here this will point to the table in my database with name StockDetail. I have used another attribute [DapperAttribute.Key] which will say that ID is the primary key in my table. Now we should move to our next step.
To ignore properties in Model simple add DapperAttribute.Computed to that property.
Now it is the time play with some CRUD operation like we can try to insert a record in our table or we can go for update a record or delete that. Or if we want we can query table to fetch a set of record also. So let us do all one by one.
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Dapper;
using Dapper.Contrib.Extensions;
Insert with the help of Dapper is very simple. For this I just need to put data in my model and will simply call Insert() method. Here I am writing simple method for that.
public long Add(IStockDetail obj)
{
if (obj == null) throw new ArgumentNullException("null object");
using (IDbConnection connection = new SqlConnection(ConnectionString))
{
IDbTransaction transaction = null;
try
{
connection.Open();
transaction = connection.BeginTransaction();
var id = connection.Insert((StockDetail)obj, transaction: transaction);
transaction.Commit();
return id;
}
catch
{
if (transaction != null)
{
transaction.Rollback();
}
throw;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
}
Like insert update is also very simple. We first fetch a record we want to update and then can update accordingly.
public bool Update(IStockDetail obj)
{
if (obj == null) throw new ArgumentNullException("null object");
using (IDbConnection connection = new SqlConnection(ConnectionString))
{
IDbTransaction transaction = null;
try
{
connection.Open();
transaction = connection.BeginTransaction();
var result = connection.Update<StockDetail>((StockDetail)obj, transaction: transaction);
transaction.Commit();
return result;
}
catch
{
if (transaction != null)
{
transaction.Rollback();
}
throw;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
}
Below is the method to delete a record with the help of Dapper.
public bool Delete(IStockDetail obj)
{
using (IDbConnection connection = new SqlConnection(ConnectionString))
{
IDbTransaction transaction = null;
try
{
connection.Open();
transaction = connection.BeginTransaction();
var result = connection.Delete<StockDetail>((StockDetail)obj, transaction: transaction);
transaction.Commit();
return result;
}
catch
{
if (transaction != null)
{
transaction.Rollback();
}
throw;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
}
Select operation is quite tricky in Dapper. I mean we can either fetch one table or we can query multi table also. Here we will fetch records from only one table. We have two way for this
public List<StockDetail> GetList()
{
using (IDbConnection connection = new SqlConnection(ConnectionString))
{
try
{
connection.Open();
var query = "select * from StockDetail";
var result = connection.Query<StockDetail>(query, null).ToList();
return result;
}
catch
{
throw;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
}
public StockDetail GetObjectByID(int Id)
{
using (IDbConnection connection = new SqlConnection(ConnectionString))
{
try
{
connection.Open();
var result = connection.Get<StockDetail>(Id);
return result;
}
catch
{
throw;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
}
We can use these method in our code and can perform any of these CRUD operation. Hope it can help you.
