ASP.NET MVC and LINQ to SQL Using Repository pattern

 The Repository Pattern

When building a Model for ASP.NET MVC applications always it is a good practice to use the Repository pattern so that the DAL layer is easy to change and test as per the need arises. When you use the Repository pattern, you create a separate repository class that contains all of your database access logic CURD with constraints.

When we create the repository class, we create an interface that represents all of the methods used by the repository class. Within the controllers, we write our code against the interface instead of the repository. That way, we can implement the repository using different data access technologies in the future.

In this post I am going to  build my Model using LINQ to SQL . let’s get started .  Now right click on Model Folder click new item and select the LINQ to SQL Classes template and add to the model as shown below

Now click Server Explorer link on LINQ to SQL Design Surface and  Add Data connection and choose the DB you wanted to work with.

Choose your DB server name and DB ( click Test Connection to validate the DB connection)

Now you added the Data source successfully.

Next step is to create a Repository pattern Interfaces .  Here  we are going to create a Repository  Interfaces for each Model Student, Course,Offering…

I am going to show you with Student model. so let us create IStudentRepository as shown below.

 

Next step is we need to  create StudentRepository class  that  implements (Inherits)   IStudentRepository .

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace MVCDataApp.Models 
{
    public class StudentRepository :IStudentRepository
    {
        // Create the Database context 
        private StudentDataContext studentdbconnection;

        public StudentRepository() 
        {
            studentdbconnection = new StudentDataContext();
        }
        public IQueryable<Student> GetAllStudents() 
        {

            var query = from student in studentdbconnection.Students
                        select student;
            return query.AsQueryable();
        }
        public void DeleteAStudent(string studentNo)
        {
            var q = from std in studentdbconnection.Students.Where(std => std.StdNo == studentNo) select std;

            studentdbconnection.Students.DeleteOnSubmit(q.FirstOrDefault());
            studentdbconnection.SubmitChanges();
        }

        public void AddStudent(Student std) 
        {
            studentdbconnection.Students.InsertOnSubmit(std);
            studentdbconnection.SubmitChanges();
        }

        public Student GetStudentById(string studentId) 
        {
            Student stdRecord = null;

            var std = from studentRecord in studentdbconnection.Students
                          where studentRecord.StdNo.Trim() == studentId.Trim()
                          select studentRecord;
 
            
            if (std.Count () !=0)
            {
                foreach (var item in std)
                {
                    stdRecord = new Student
                    {
                        StdNo = item.StdNo,
                        StdCity = item.StdCity,
                        StdLastName = item.StdLastName,
                        StdFirstName = item.StdFirstName,
                        StdClass = item.StdClass,
                        StdGPA = item.StdGPA,
                        StdMajor = item.StdMajor,
                        StdState = item.StdState,
                        StdZip = item.StdZip
                    };
                }
            }

            return stdRecord;
        }

       
    }
}

 

We are almost done with Model.  Now  we have the ASP.NET MVC project  LINQ to SQL classes Model next we need to access the data and pass it the views. 

 

The above  HomeController class has two constructors methods. The first constructor (the parameterless one) is called when application is running. This constructor creates an instance of the StudentRepository class and passes it to the second constructor. The second constructor has a single parameter an IStudentRepository parameter. This constructor simply assigns the value of the parameter to a stdRepo instance variable. This is an implementation of a software design pattern called the Dependency Injection pattern. In particular, Constructor Dependency Injection.

Now we need add some code in Index View  to display the students records.

<%@ Page Title="" Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage<IQueryable<MVCDataApp.Models.Student>>" %>

<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">
    Index
</asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">

    <h2>Index</h2>
    <table border="5" bordercolor="red">
     <thead>
     <tr>
     <th>Std No</th> <th>First Name</th> <th>Last Name</th>
     </tr>
     </thead>
     <% foreach (var item in ViewData.Model)
        { %>
          
         <tr>
          <td>
          <%=item.StdNo %>
          </td>
          
           <td>
          <%=item.StdFirstName %>
          </td>
          
            <td>
          <%=item.StdLastName %>
          </td>
          </tr>
       <%} %>  
        </table>
        
</asp:Content>

Here is the view result 

 

Suppose If you want to change the data access technology using  EF ( Entity Framework) instead of  LINQ TO SQL  you simply implement the IStudentRepository interface with a class that uses the alternative database access technology. For example, you could create a class EF_StudentRepository  class. Because the controller class is programmed against the interface, you can pass a new implementation of IStudnetRepository to the controller class and the class would continue to work. this is the advantage we have when we use Repository Pattern.

when we use  Repository pattern we can able to change data access technology from one to another very easily. 

 

Thanks(Nandri)

SreenivasaRagavan