ASP .Net MVC3 - Linq To SQL Project
MVC3 - Linq To SQL Generator for DataContext and Model
This article describes how to create an MVC 3 Project and use a Linq To SQL Class to map to a SQL Stored Procedure that returns a DataSet. The LinqToSQL class generator creates the classes that you would create in a class for the Model and for the DataContext within the Designer Code. Additionally, this article will touch on Dependency Injection, specifically, Constructor Dependency Injection in the Controller using an Interface and a Repository.
Creating the ASP .Net MVC3 Project for LINQ to SQL
In Visual Studio, create an MVC 3 project and click OK when the Project Template pop up comes up with Internet Application highlighted.
Right click on the Model folder and click Add New Item and select LINQ to SQL Classes and name the dbml file and click Add.
Adding the Stored Procedure to the MVC3 LINQ to SQL Project
Click on the Database Explorer tab and open up the SQL Database, Stored Procedures folder. Drag and drop a Stored Procedure (that returns a dataset) to the Toolbox.
Check out the Designer.cs file for the dbml file created to get the names of both the DataContext and the Model class name (the name of the Stored Proc + "Result").
public partial class GetEmpsDataContext : System.Data.Linq.DataContext
public partial class GetEmpDeptDataResult
{
private string _EmpName;
private string _Department;
private string _GroupName;
private string _StartDate;
private string _EndDate;
private string _EmpStatus;
Adding an MVC3 Interface
To add an interface, Right Click on Models and select Interface from the Visual C# templates.
Add code to the interface to require a call to an IList method.
public interface IEmployees
{
IList<GetEmpDeptDataResult> ListAll();
}
Creating an MVC3 Repository
Add a class for a repository.
Add code for the repository to declare the DataContext, a constructor for the repository and the IList method of the Interface that it is inheriting from.
public class EmployeesRepository : IEmployees
{
private GetEmpsDataContext _dataContext;
public EmployeesRepository()
{
_dataContext = new GetEmpsDataContext();
}
public IList<GetEmpDeptDataResult> ListAll()
{
var emps = _dataContext.GetEmpDeptData();
return emps.ToList();
}
}
Adding the MVC3 Controller
Right click on the Controllers folder and Add Controller.
Add code for the Controller to create an instance of the Repository which is constructed with the Employee DataContext, overload the Constructor of the Controller to accept the IEmployees interface. Finally, in the ActionResult Index method pass the List of Employees to the View.
private IEmployees _repository;
private EmployeesRepository employeeRepository;
public EmployeeController() : this(new EmployeesRepository())
{ }
public EmployeeController(IEmployees repository)
{
_repository = repository;
}
public ActionResult Index()
{
return View(_repository.ListAll());
}
Adding the MVC3 View
To create the view, right click next to the ActionResult Index method and select Add View.
@model IEnumerable<MvcLinqToSQL.Models.GetEmpDeptDataResult>
@{
ViewBag.Title = "Employees";
Layout = "~/Views/Shared/_Layout.cshtml";
}
<h2>Employees</h2>
<table>
<tr>
<th>Name</th>
<th>Department</th>
<th>Start Date</th>
<th>End Date</th>
</tr>
@foreach (MvcLinqToSQL.Models.GetEmpDeptDataResult item in Model
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.EmpName)
</td>
<td>
@Html.DisplayFor(modelItem => item.Department)
</td>
<td>
@Html.DisplayFor(modelItem => item.StartDate)
</td>
<td>
@Html.DisplayFor(modelItem => item.EndDate)
</td>
</tr>
}
</table>
In the _Layout.cshtml, add the highlighted List Item to add the tab to the employees page on all pages for the site.
<nav>
<ul id="menu">
<li>@Html.ActionLink("Home", "Index", "Home") </li>
<li>@Html.ActionLink("About", "About", "Home") </li>
<li>@Html.ActionLink("Employees","Index","Employee") </li>
</ul>
</nav>
Test the Layout code
Click on the tab to get the Employees page with the data from the stored procedure.
