Saturday, August 8, 2009

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

image

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

image

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

image

Now you added the Data source successfully.

image

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.

image

 

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. 



image



 



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 



image



 



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

4 comments:

Simon said...

I am looking to build new website in MVC. I bought a terrible book. I think I am better off reading this post than reading the book. You show it all and get to the point a developer like me need know.

Great Thanks!

Real Web Developers said...

This seems to be a poor demonstation of the respository pattern (and it's no the only one on the net that falls into the same category). By using LINQ to SQL to generate the Domain Objects you have tightly bound your whole rspository to SQL Server, the interface is redundent in this case since in CANNOT be implemented using (say) LINQ to XML without redeveloping (using the GUI in VS admittedly but still...) the Domain Objects.

Unknown said...

I have developed a simple MVC application to View the data in database. I have used Repository pattern. Now I want to add a record into database through repository pattern. Please send me some useful links OR code example. Thank you.

Unknown said...

I have looked at numerous repository representations but still failed to see how one would mock a repository method that takes a parameter e.g.

interface IProduct{

IProduct GetProductId(int id);
}

Every where people use tests on GetAllProducts(). How can one test using Moq or generate implementations to test a method such as in the interface above.

There is something wrong with Moq testing according to my understanding. Why is every body using GetAll() as a representation of the Moq tests in Repository methods, yet methods that take paramters should be tested. Does any one understand MVC out there??? I truly don't see why nobody gives an example of parametized Repository method tests.....