Using The Repository Design Pattern For Data Access Code

In this article we will create a repository for our data base access code. The repository pattern is useful when:
• Some of your clients use Oracle and others use SQL Server you can create a repository for each.
• Your company decides to upgrade to a newer way of doing data base access. 

You only need to create a new repository class to do it. E.g. you might have one repository for ADO.Net and one for Entity Framework data access code.

To get started what we are going to do here is create a repository that will contain data base access code to retrieve MileageClaim information.

1. First we need a Data Transfer Object (DTO). This is where the mileage claim data will be placed that we retrieve from the database.

namespace DTO.Finance.Mileage {     public class MileageClaim     {           public int MileageClaimId { get; set; }                public string PayrollNumber { get; set; }         public string ReferenceNumber { get; set; }         public int? VehicleId { get; set; }         public int CreatedByPersonId { get; set; }         public DateTime DateCreated { get; set; }         public int? UpdatedByPersonId { get; set; }         public DateTime? DateUpdated { get; set; }     } }

You should place the DTO class in a separate class library project as you will reference these classes from many places. I’ve called the project DTO.Finance.Mileage.

2. Define an interface for the repository. Since its going to handle mileage claim data an appropriate name is IMileageClaimRepository.

namespace DataAccess.Finance.Mileage.Interfaces {     public interface IMileageClaimRepository     {          List<DTO.Finance.Mileage.MileageClaim> GetAllClaimsForEmployee(String employeePayrollNumber);         DTO.Finance.Mileage.MileageClaim GetClaimByReferenceNumber(String referenceNumber);         DTO.Finance.Mileage.MileageClaim GetClaimById(Int32 id);         void ChangeMileageClaimStatus(DTO.Finance.Mileage.MileageClaim claim, DTO.Common.Status status, String userIdentity);         void Save(ref DTO.Finance.Mileage.MileageClaim claim, String userIdentity);         void Delete(DTO.Finance.Mileage.MileageClaim claim);     } }

Again you should place these interfaces in a separate project as it makes it easier later if/when you create additional mileage claim repositories.

This interface defines the methods that the repository will provide. Because it’s going to contain data base access code it will handle the standard CRUD (create, read, update, delete) methods.

3. Next we need to create the actual repository.

3.1. You should place these data base access classes in a separate project; I’ve called it EF.DataAccess.Finance.Mileage because this repository is going to access the database using Entity Framework. If I had another repository for ADO.Net I’d call it ADO.DataAccess.Finance.Mileage. Inside this project I place all the data base access code including the model diagrams for Entity Framework.

3.2. Inside this project I place the repository class for accessing mileage claim data from the database, so I’ll call it MileageClaimRepository and place it in a Repositories folder. It will implement the IMileageClaimRepository interface.

namespace EF.DataAccess.Finance.Mileage.Repositories {     public class MileageClaimRepository : IMileageClaimRepository     {         public MileageClaimRepository()         {         }         public List<DTO.Finance.Mileage.MileageClaim> GetAllClaimsForEmployee(String employeePayrollNumber)         {             // Data base access code goes here         }         public DTO.Finance.Mileage.MileageClaim GetClaimByReferenceNumber(String referenceNumber)         {             // Data base access code goes here         }         public DTO.Finance.Mileage.MileageClaim GetClaimById(Int32 id)         {             // Data base access code goes here         }         public void ChangeMileageClaimStatus(DTO.Finance.Mileage.MileageClaim claim, DTO.Common.Status status, String userIdentity)         {             // Data base access code goes here         }         public void Save(ref DTO.Finance.Mileage.MileageClaim dtoClaim, String userIdentity)         {             // Data base access code goes here         }         public void Delete(DTO.Finance.Mileage.MileageClaim claim)         {             // Data base access code goes here         }     } }

4. We now have our data access layer. To use this layer and to use the separation of concerns principle I’m going to add 1 more layer, a service layer.  Note the service contains more methods than the repository (a service layer does not have to map 1 to 1 to the repository layer).

namespace Service.Finance.Mileage {     public class MileageClaimService     {          IMileageClaimRepository _mileageClaimRepository;         public MileageClaimService(IMileageClaimRepository mileageClaimRepository)         {             _mileageClaimRepository = mileageClaimRepository;         }         public List<DTO.Finance.Mileage.MileageClaim> GetAllClaimsForEmployee(String employeePayrollNumber)         {             return _mileageClaimRepository.GetAllClaimsForEmployee(employeePayrollNumber);         }         public DTO.Finance.Mileage.MileageClaim GetClaimByReferenceNumber(String referenceNumber)         {             return _mileageClaimRepository.GetClaimByReferenceNumber(referenceNumber);         }         public DTO.Finance.Mileage.MileageClaim GetClaimById(Int32 id)         {             return _mileageClaimRepository.GetClaimById(id);         }          public void Save(ref DTO.Finance.Mileage.MileageClaim claim)         {             String userIdentity = User.Identity.Name;             if (claim.MileageClaimId == 0)             {                 // It's a new claim so do some new claim stuff here                 // You could do stuff like automatically set the reference number here                  // Get the status object so we can set the new claim with an initial status of draft                 StatusService statusService = new StatusService();                 DTO.Common.Status status = statusService.GetStatus(                            (Int32)MileageClaimStatusTypeKeys.MileageClaim,                            (Int32) Service.Finance.Mileage.Enums.MileageClaimStatusKeys.Draft);                 // We want to make sure that if one of the 2 service calls fail then                 // neither will write any data into the db so lets create a transaction.                 using (TransactionScope scope = new                          TransactionScope(TransactionScopeOption.Required))                 {                     // Save any changes to the claim                     _mileageClaimRepository.Save(ref claim, userIdentity);                     // Change the claim status to draft                     ChangeMileageClaimStatus(claim, status);                     // If no exceptions have been thrown so far then commit the transaction                     // so everything is saved to the db                     scope.Complete();                 }             }             else             {                 // It’s an existing claim being edited so save any changes to the claim                 _mileageClaimRepository.Save(ref claim, userIdentity);             }         }         public void Delete(DTO.Finance.Mileage.MileageClaim claim)         {             _mileageClaimRepository.Delete(claim);         }         public void SubmitClaim(DTO.Finance.Mileage.MileageClaim claim)         {                        // Save any changes to the claim             Save(ref claim);              // Change the claim status to submitted             StatusService statusService = new StatusService();             DTO.Common.Status status = statusService.GetStatus(                         (Int32)MileageClaimStatusTypeKeys.MileageClaim,                         (Int32)Service.Finance.Mileage.Enums.MileageClaimStatusKeys.Submitted);             ChangeMileageClaimStatus(claim, status);         }     } }

This service accepts as a parameter on the constructor an interface for an IMileageClaimRepository. When we use the service in our code we tell the service what repository it should use e.g. Entity Framework or ADO.Net repositories.

Because all the repositories use the same interface they will all have the same method names that the service can call so we don’t need to change the service code at all to use different repositories.

5. From a client you just need to reference the service, DTO and the repository you want to use projects.  Then to call a method from the client you just:

// Get the mileage claim repository we want to use Entity Framework or ADO.Net.    IMileageClaimRepository repository = new EF.DataAccess.Finance.Mileage.Repositories.MileageClaimRepository();       // Initialise the service passing in the data access repository it should use   MileageClaimService mileageClaimService = new MileageClaimService( repository );        // Call a method on the service    DTO.Finance.Mileage.MileageClaim mileageClaimService.GetClaimById( 10 );

Note: You could use dependency injection (e.g Unity) to work out automatically which repository to use based on settings in a config file and in a future article we will look at doing just that.

Note: You can also use wcf on the service layer with minimal code changes if you wanted to move to a 3-tier architecture, this will be covered in a future article.