Dapper.NET is a Micro ORM written by the developers behind Stack Overflow. It is simple to use, and is compatible with any database which implements a provider for .NET (i.e.: provides an implementation of the IDbConnection interface).We have been using Dapper.Net extensively for our projects.

Here is Small Example to Implement Dapper.NET 

We have an Entity class Employee with following properties: 

public class Employee
{
    public int EmployeeID { get; set; }
    public string Name { get; set; }
    public string Location { get; set; }
    public string Salary { get; set; }
}

 

Ex:1 This is example shows how to retrieve values from database using Dapper

 

public IEnumerable<Employee> SelectEmployeeDetails()
{
using (IDbConnection connection = OpenConnection())
    {
        const string query = "SELECT EmployeeID, " +
              " Name,  Location , Salary FROM Employee " ;
        return connection.Query<Employee>(query);
    }
}

Ex2: Instead of writing inline query statements here it demonstrates by using stored procedure

 

public int AddNewEmployee(Employee value)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@Name", value.Name, DbType.Int32, ParameterDirection.Input);
            parameters.Add("@Location",value.Location, DbType.String, ParameterDirection.Input);
            using (IDbConnection connection = OpenConnection ())
            {
                const string storedProcedure = "dbo.DeleteUsers";
               int rowsaffected = connection.Execute(storedProcedure, parameters,                                                                               commandType:CommandType.StoredProcedure);
               return rowsaffected;
            }
        }

 

 

Ex3: Dapper.NET supports Transactions. For Example, the following code deletes the employee record from company  table as well as  the employee table:

 

public int DeleteEmployeeRecord(Employee value)
{
try
{
    using (IDbConnection connection = OpenConnection())
    {               
        const string deleteEmployeeFromCompany = "DELETE FROM Company " +
                                                                                      "WHERE EmployeeID = @ EmployeeID ";
        const string deleteEmployeeDetails = "DELETE FROM Employee" +
                                                                           " WHERE EmployeeID = @ EmployeeID ";
        IDbTransaction transaction = connection.BeginTransaction();
        int rowsAffected = connection.Execute(deleteEmployeeFromCompany, new { EmployeeID = value.EmployeeID}, transaction);
        rowsAffected += connection.Execute(deleteEmployeeDetails,  new { EmployeeID = value.EmployeeID}, transaction);
        transaction.Commit();
        return rowsAffected;
    }
 }
catch (Exception ex)
     {
         transaction.Rollback();
     }
 }