Please enable Javascript for better experience...
Dapper in .NET
By Big | Jun 26, 2014 | In Articles | Update: Feb 13, 2015 | Total Views [ 3157 ]
(0 Like)
Rate

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.

Introduction

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.

Implementation

First of all we will see the dll to used for Dapper. We will use two dll to implement dapper in our project.

  • Dapper.dll
  • Dapper.Contrib.dll

We can put reference of these two dll in our project and we are continue with next step.

The Model

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.

CRUD operation

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.

NAMESPACE

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

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();
                     }
                 }
             }
         }

UPDATE

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();
                     }
                 }
             }
         }

DELETE

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

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.

Share this

About the Author

Big
Rahul Jha (Big)
Founder, Developer dotnet-concept.com
Has working experience in different phases of Software Development Life Cycle (SDLC) in CMS, Gaming, Health Care and Financial Services domain using Agile pattern. Working experience in Design patterns, ASP.NET, MVC, Windows application, WCF, ADO.NET, SQL Server and Test Driven Development (TDD) environment with JQuery, JavaScript, N-Unit, Entity Frameworks, LINQ, Code Refactoring and Business Objects Models.

 
Please SignUp/Login to comment...

Or comment as anonymous...
* Name
* Email ID
Comment