Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

Python

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


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.

Linq to SQL class

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.

Stored Proc to Linq to SQL

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.

Interface

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.

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.

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.

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.

Employees Data