Micro ORM (Dapper) in C#

What is the ORM ?

 ORM framework enables developers to work with relational data as domain specific objects. 

 Eg : EntityFramwork, NHibernate

What is Micro ORM ? 

 Micro ORM is doing the same thing as ORM framework. But it has following additional features.

• Fast
• lightweight
• Simple/Easy to use

Eg : Dapper, OrmLite, PetaPoco, Massive, Simple.Data

Micro ORM Performance 

Source : https://github.com/StackExchange/Dapper

 Dapper Demo

You can download full source code from following github ULR.

Download Source Code 

1. Run the SQL script named by "DapperDemo.sql" which is under Script folder. This will create the database which  we are going to use through out our demo.

2. Create an empty solution called DapperDemo and add new class library called DapperDemo.

3. Create new folder named by Model inside DepperDemo project and create following model classes inside the folder.

• Department

• Employee

• Address

3. Add dapper library to DapperDemo project using nuget.

4. Create an interface called IEmpoyeeRepository and add following method signature on it.

5. Create new class called EmpoyeeRepository and implement it using IEmpoyeeRepository. Implement all the methods as below.

using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Dapper;
using System.Transactions;

namespace DapperDemo
{
    public class EmployeeRepository : IEmployeeRepository
    {
        private IDbConnection db;

        public EmployeeRepository()
        {
            IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["DapperDemoDB"].ConnectionString);
        }

        public Employee Find(int id)
        {
            return this.db.Query<Employee>("SELECT * FROM Employee WHERE Id = @id", new { id }).SingleOrDefault();
        }

        public Employee FindFullEmployee(int id)
        {
            var sqlQuery =
                "SELECT * FROM Employee WHERE Id = @Id; " +
                "SELECT * FROM Address WHERE EmployeeId = @Id";

            using (var multipleResult = this.db.QueryMultiple(sqlQuery, new { Id = id }))
            {
                var employee = multipleResult.Read<Employee>().SingleOrDefault();
                var addresses = multipleResult.Read<Address>().ToList();

                if (employee != null)
                {
                    employee.Addresses.AddRange(addresses);
                }

                return employee;
            }
        }

        public List<Employee> GetAll()
        {
            return this.db.Query<Employee>("SELECT * FROM Employee").ToList();
        }

        public Employee Add(Employee employee)
        {
            var sqlQuery =
                "INSERT INTO Employee(FirstName,LastName,Email,DepartmentId)" +
                " VALUES(@FirstName,@LastName,@Email,@DepartmentId);" +
                "SELECT CAST(SCOPE_IDENTITY() as int)";
            var id = this.db.Query<int>(sqlQuery, employee).Single();
            employee.Id = id;

            return employee;
        }

        public Employee Update(Employee employee)
        {
            var sqlUpdateQuery =
                "UPDATE Employee  SET "+
                "   FirstName    = @FirstName, "+
                "   LastName     = @LastName," +
                "   Email        = @Email,"+
                "   DepartmentId = @DepartmentId "+
                "WHERE Id=@Id";

            this.db.Execute(sqlUpdateQuery, employee);

            return employee;
        }

        public void Remove(int id)
        {
            db.Execute("DELETE Employee WHERE Id=@id", new { id });
        }

        public void AddFullEmployee(Employee employee)
        {
            using (var transactionScope = new TransactionScope())
            {
                if (employee.IsNew)
                {
                    this.Add(employee);
                }
                else
                {
                    this.Update(employee);
                }

                foreach (var addr in employee.Addresses.Where(a => !a.IsDeleted))
                {
                    addr.EmployeeId = employee.Id;

                    if (addr.IsNew)
                    {
                        this.Add(addr);
                    }
                    else
                    {
                        this.Update(addr);
                    }

                }

                foreach (var addr in employee.Addresses.Where(a => a.IsDeleted))
                {
                    this.db.Execute("DELETE FROM Address WHERE Id = @Id", new { addr.Id });
                }

                transactionScope.Complete();
            }
        }

        public Address Add(Address address)
        {
            var sql =
                "INSERT INTO Address (Street, Address1, Country, PostalCode, EmployeeId) VALUES(@Street, @Address1, @Country, @PostalCode, @EmployeeId); " +
                "SELECT CAST(SCOPE_IDENTITY() as int)";
            var id = this.db.Query<int>(sql, address).Single();
            address.Id = id;
            return address;
        }

        public Address Update(Address address)
        {
            this.db.Execute("UPDATE Address " +
                "SET Street = @Street, " +
                "    Address1 = @Address1, " +
                "    Country = @Country, " +
                "    PostalCode = @PostalCode, " +
                "    EmployeeId = @EmployeeId " +
                "WHERE Id = @Id", address);
            return address;
        }
    }
}

6. Now you can call this repository from Unit test project or  console application.

Happy Coding!!!!!!!!!!!!!