Easiest Repository Pattern Tutorial

I would like to explain how to implement repository pattern before we proceed any further with our single page application discussion.

What is repository pattern?
Repository pattern separates the logic that retrieves the data from the database and logic that uses the data by your application.  Thus it makes your data access layer independent of your presentation layer.

As shown in above figure:

1. Create database table
2. Create POCO class (Model class) with getters and setters mapping to all the properties of database table.
3. Create Interface which list down all the operations we are going to perform on that table.  Most of the time we are doing CRUD operation (Create, Read, Update and Delete operation on table).
4. Implementation of Interface.
5. Presentation layer consuming interface to perform database operation.

In summary, accessing database through interface is repository pattern.  (Disclaimers: Please note I am using few sentence which are very lame in nature just to make explanation as simple as possible for anyone to understand, ones user have good understanding he can judge the things better himself).

Advantages of making use of Repository Pattern

• Since we are accessing database through interface, presentation layer is independent of database layer.  That means you can have same data access logic reusable for multiple presentation layer (eg: console application, asp.net mvc, asp.net web form or windows form can use same data access logic.)  Similarly whenever you change the way you access data from database doesn't affect how it is rendered on presentation layer.  That means if you are using ado.net to access database, later you decide to make use of entity framework or micro-orm or web service or web api, will not require you to make any change on the presentation side.
• Code will be more maintainable and readable.
• Testable code.
• Flexibility of architecture and much more (Running out of time, so google it please).

Repository Pattern Implementation Step by Step

Download Complete Sourcecode for demo discussed in this tutorial for repository pattern.

Step 1: Create database table

For this example:  Please create 

• "Departments" table with 2 columns
• DeptId  int
• DeptName varchar(35)

Department table creation script

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Departments](

[DeptId] [int] IDENTITY(1,1) NOT NULL,

[DeptName] [varchar](35) NULL,

 CONSTRAINT [PK_Departments] PRIMARY KEY CLUSTERED 

(

[DeptId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

Insert records in table script

Insert into Departments values ('Human Resource');

Insert into Departments values ('Finance');

Insert into Departments values ('Payroll');

Insert into Departments values ('Transportation');

Insert into Departments values ('Logistic');

Insert into Departments values ('Information Technology');

Insert into Departments values ('Administration');

Insert into Departments values ('Customer Care');

Ones you are done your departments table will be as shown in figure:

Step 2: Create POCO class (Model class for departments table)

Create a VS.Net Class library project for creating POCO (Plain old CLR Object) class.

Create a class called "Departments.cs" and add getters and setters for all table property.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace MyAppDemo.Model
{
    public class Departments
    {        
        public int DeptId { get; set; }
        public string DeptName { get; set; }
    }
}

Step 3: Create Interface and list all CRUD methods

Create a separate VS.Net Class library project for Interface.  To do this right click solution file and add new project to existing solution.

Ones you create Interface project add project reference for Model project into interface project.  Create a

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MyAppDemo.Model;
 
namespace MyAppDemo.Interface
{
    public interface IDepartments
    {
        void Insert(Departments model);
        void Update(Departments model);
        void Delete(long Id);
        Departments SelectOne(long Id);
        IEnumerable<Departments> SelectAll();
    }
}

Step 4: Create Interface Implementation project

Create a separate VS.Net Class library project for Implementation.  To do this right click solution file and add new project to existing solution.

Add reference of both model project and interface project into Implementation project.

Since for this project I will be accessing data using entity framework.  

Lets add Entity Framework nuget package for this project.

In order to make use of entity framework we will need database context file.  So lets first create DB Context file and then Implementation file.

Create a "MyAppDemoContext.cs" file.

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MyAppDemo.Model;
 
namespace MyAppDemo.Implementation
{
    public class MyAppDemoContext : DbContext
    {
        public MyAppDemoContext()
            : base("DefaultConnection")
        {
            Database.SetInitializer<MyAppDemoContext>(null);
        }
 
        public DbSet<Departments> Department { get; set; }
    }
}

Now lets create implementation file.  "DepartmentsImpl.cs"

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MyAppDemo.Model;
using MyAppDemo.Interface;
 
namespace MyAppDemo.Implementation
{
    public class DepartmentsImpl : IDepartments
    {
        // Create a Instance of DB Context
        private MyAppDemoContext db = new MyAppDemoContext();
 
        public void Insert(Departments model)
        {
            db.Department.Add(model);
            db.SaveChanges();            
        }
 
        public void Update(Departments model)
        {
            Departments foundModel = 
                db.Department
                .Where(a => a.DeptId.Equals(model.DeptId))
                .FirstOrDefault();
            
            if (foundModel == null)
                throw new Exception("Model not found");
                        
            foundModel.DeptName = model.DeptName;
            db.Department.Add(foundModel);
            db.SaveChanges();            
        }
 
        public void Delete(long Id)
        {
            Departments foundModel = 
                db.Department
                .Where(a => a.DeptId.Equals(Id))
                .FirstOrDefault();
 
            if (foundModel == null)
                throw new Exception("Model not found");
                        
            db.Department.Remove(foundModel);
            db.SaveChanges();            
        }
 
        public Departments SelectOne(long Id)
        {
            return db.Department
                    .Where(a => a.DeptId.Equals(Id))
                    .FirstOrDefault();
        }
 
        public IEnumerable<Departments> SelectAll()
        {
            return db.Department.AsEnumerable();
        }
    }
}

Ones you are done with these steps your solution will look as under:

Step 5: Presentation Layer which will be making use of data access layer through interface.

Create a separate console project.  To do this right click solution file and add new project.

Add connection string in App.Config file and following code for making DB listing call to your "Program.cs"

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MyAppDemo.Model;
using MyAppDemo.Interface;
using MyAppDemo.Implementation;
 
namespace MyAppDemo.PresentationConsole
{
    class Program
    {   
        static void Main(string[] args)
        {
            IDepartments repository = new DepartmentsImpl();
 
            //List All Departments
            List<Departments> departmentsList = repository.SelectAll().ToList();
            foreach (var department in departmentsList)
            {
                Console.WriteLine(department.DeptName);
            }
 
            Console.WriteLine("Press any key to exit...");
            Console.ReadLine();
        }
    }
}

Ones you are done with all the steps your solution will look as shown in figure.

Similarly you can add one more project for Asp.net MVC and use same DB layer.

Download Complete Sourcecode for demo discussed in this tutorial for repository pattern.