Tuesday, November 12, 2013

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
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 { getset; }
        public string DeptName { getset; }
    }
}

    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 { getset; }
        }
    }
    

    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.

    9 comments:

    Steve Dyson said...

    Nice post.

    We are using layered architecture, we have separate object and data layer. Object layer contains all properties ( model) and data layer contains implementation.

    Just wondering, how is this different to repository pattern? What is the need of interface here?

    Steve Dyson said...

    Further to my last comment why can't we use

    DepartmentsImpl repository = new DepartmentsImpl();

    DotNetGuts said...

    Interface is used to create loose coupling between implementation and consumer.

    Implementation - In this case will be making DB Operation.

    Consumer - Web Application or Mobile Application or any UI


    Example:
    Let say your repository is having implementation to connect SQL Server database and perform DB operation which are specific to SQL Server. Later the requirement comes up to have MongoDB. Here Interface is helping to separate implementation with UI. You don't have any breaking changes for UI to change SQL Server to MongoDB.

    Steve Dyson said...

    why can't we use this instead

    DepartmentsImpl repository = new DepartmentsImpl();

    Steve Dyson said...

    Hi, Thanks for your reply.

    I have two further questions;

    1- If we do decide to use MongoDB along with SQL Server then do we need to create a new implementation for MongoDB using same Interface? If yes then why can't i just create a separate db layer class for mongo database CRUD operations, in my current layered architecture ? I am not against repository or Interface , i am just trying to understand the benefits it can bring to one application

    2- why can't we use this in our presentation layer?

    DepartmentsImpl repository = new DepartmentsImpl();

    DotNetGuts said...

    By assigning implementation class to interface, we can achieve runtime polymorphism. This was old implementation to keep things simple, use IOC to avoid creating instance. That will give even more freedom. http://stackoverflow.com/questions/16832280/why-we-do-create-object-instance-from-interface-instead-of-class

    For understanding IOC - http://dotnetguts.blogspot.com/2015/06/example-of-inversion-of-control-and.html

    DotNetGuts said...

    When we are doing following implementation
    DepartmentsImpl repository = new DepartmentsImpl();

    We are hard-coding the implementation, so if anything changes in implementation, all the components consuming the implementation needs to be modified.

    Example: If you use same database access layer for web and mobile application. Then both the application requires to apply necessary changes when we switch from SQL Server to MongoDB.

    While if we choose to separate implementation with application consuming it by adding abstraction using interface, we don't need to do any changes when implementation changes. That is when we switch from SQL Server to MongoDB we don't have to modify any logic in web or mobile application. Because we are using method exposed by interface.

    public interface ICrudOperations
    {
    void Insert(Departments model);
    void Update(Departments model);
    void Delete(long Id);
    Departments SelectOne(long Id);
    IEnumerable SelectAll();
    }

    Interface expose methods to consuming application (i.e. Web and Mobile application in our example) and left the implementation logic abstract. That gives us flexibility to either use SQL Server or MongoDB as Implementation.


    public class SQLServerImpl : ICrudOperations
    {
    //here provide sql server implementation for all the CRUD Operations
    }


    similarly, later you decide to change implementation to MongoDB, all you need to change is DB Crud operations.


    public class MongoDBImpl : ICrudOperations
    {
    //here provide mongodb implementation for all the CRUD Operations
    }

    As of now, concept of interface has become old and now people use concept of dependency injection and IOC to get one step further in adding layer of abstraction.

    MORAL: Adding layer of abstraction from implementation will make life easier to maintain application. It make application flexible to take any major change, at the same time gives peace of mind that modifying part of application will not break other part of application.

    Hope this helps.

    Steve Dyson said...

    Thank you for the detail explanation.

    As per your last mongoDB example, we would need to make little change to our consumer application because when were using this code before for SQL;

    ICrudOperations rep = new SQLServerImpl;

    After switching to Mongo, we will have to change consumer code to this;


    ICrudOperations rep = new MongoDBImpl;

    If it is correct then the whole idea was not to make any changes to consumer application? Can't see any advantage of using interface here because i can have two separate classes in my data layer to accommodate sql and mongo db crud operation. And i can just point consumer to right class when needed.


    DotNetGuts said...

    And that's where a concept of Generic Repository, Dependency Injection and IOC comes into picture. Read more on those topic to get your answer, as that is a long topic for me to explain here.

    I would recommend that you first read more on interfaces and its advantage and also polymorphism. Interface add layer of abstraction between consumer and implementation, which gaves flexibility for consumer or implementation to change anytime.

    Most Recent Post

    Community Updates

    Subscribe Blog via Email

    Enter your email address:



    Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
    Page copy protected against web site content infringement by Copyscape