Part 1 - Listing Data from Database: Let us now learn ASP.NET MVC 2 from Scratch by actually developing a front end website for the Chinook database, which is an alternative to the traditional Northwind database. You can get the Chinook database from here. As always the best way to learn something is by working on it and doing something. The Chinook database has the following schema, a quick look will help us implementing the application in a efficient way. Let us first implement a grid view table with the list of Employees with some details, this table also has the Details, Edit and Delete buttons on it to do some operations. This is series of post will concentrate on creating a simple CRUD front end for Chinook DB using ASP.NET MVC 2. In this post, we will look at listing all the possible Employees in the database in a tabular format, from which, we can then edit and delete them as required. In this post, we will concentrate on setting up our environment and then just designing a page to show a tabular
information from the database. We need to first setup the SQL Server database, you can download the required version and then set it up in your localhost. Then we need to add the LINQ to SQL Classes required for us to enable interaction with our database. Now after you do the above step, just use your Server Explorer in VS 2010 to actually navigate to the database, expand the tables node and then drag drop all the tables onto the Object Relational Designer space and you go you will have the tables visualized as classes. As simple as that. Now for the purpose of displaying the data from Employee in a table, we will show only the EmployeeID, Firstname and lastname. So let us create a class to hold this
information. So let us add a new class called EmployeeList to the ViewModels. We will send this data model to the View and this can be displayed in the page. public class EmployeeList
{
public int EmployeeID { get; set; }
public string Firstname { get; set; }
public string Lastname { get; set; }
public EmployeeList(int empID, string fname, string lname)
{
this.EmployeeID = empID; this.Firstname = fname; this.Lastname = lname;
}
}
Ok now we have got the backend ready. Let us now look at the front end view now. We will first create a master called Site.Master and reuse it across the site. The Site.Master content will be
<%@ Master Language="C#" AutoEventWireup="true" CodeBehind="Site.Master.cs" Inherits="ChinookMvcSample.Views.Shared.Site" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
<style type="text/css">
html
{
background-color: gray;
}
.content
{
width: 880px;
position: relative;
background-color: #ffffff;
min-width: 880px;
min-height: 800px;
float: inherit;
text-align: justify;
}
</style>
<script src="../../Scripts/jquery-1.4.1.min.js" type="text/javascript"></script>
<asp:ContentPlaceHolder ID="head" runat="server">
</asp:ContentPlaceHolder>
</head>
<body>
<center>
<h1>
My Website</h1>
<div class="content">
<asp:ContentPlaceHolder ID="body" runat="server">
</asp:ContentPlaceHolder>
</div>
</center>
</body>
</html>
The backend Site.Master.cs does not contain anything. In the actual Index.aspx view, we add the code to simply iterate through the collection of EmployeeList that was sent to the View via the Controller. So in the top of the Index.aspx view, we have this inherits which says
Inherits="System.Web.Mvc.ViewPage<IEnumerable<ChinookMvcSample.ViewModels.EmployeeList>>"
In this above line, we dictate that the page is consuming a IEnumerable collection of EmployeeList. So once we specify this and compile the project. Then in our Index.aspx page, we can consume the EmployeeList object and access all its methods and properties.
<table class="styled" cellpadding="3" border="0" cellspacing="0">
<tr>
<th colspan="3">
</th>
<th>
First Name
</th>
<th>
Last Name
</th>
</tr>
<% foreach (var item in Model)
{ %>
<tr>
<td align="center">
<%: Html.ActionLink("Edit", "Edit", new { id = item.EmployeeID }, new { id = "links" })%>
</td>
<td align="center">
<%: Html.ActionLink("Details", "Details", new { id = item.EmployeeID }, new { id = "links" })%>
</td>
<td align="center">
<%: Html.ActionLink("Delete", "Delete", new { id = item.EmployeeID }, new { id = "links" })%>
</td>
<td>
<%: item.Firstname %>
</td>
<td>
<%: item.Lastname %>
</td>
</tr>
<% } %>
<tr>
<td colspan="5">
<%: Html.ActionLink("Create New", "Create") %>
</td>
</tr>
</table>
The Html.ActionLink is a Html Helper to a create a hyperlink in the page, in the one we have used, the first parameter is the text that is to be used for the hyperlink, second one is the action name, third one is the parameter to be passed, last one is the attributes to be added while the hyperlink is rendered in the page. Here we are adding the id=”links” to the hyperlinks that is created in the page.
In the index.aspx page, we add some jQuery stuff add alternate row colours and highlight colours for rows on mouse over.
Now the Controller that handles the requests and directs the request to the right view. For the index view, the controller would be
public ActionResult Index()
{
//var Employees = from e in data.Employees select new EmployeeList(e.EmployeeId,e.FirstName,e.LastName);
//return View(Employees.ToList());
return View(_data.Employees.Select(p => new EmployeeList(p.EmployeeId, p.FirstName, p.LastName)));
}
Let us also write a unit test using NUnit for the above, just testing EmployeeController’s Index.
DataClasses1DataContext _data;
public EmployeeControllerTest()
{
_data = new DataClasses1DataContext("Data Source=(local);Initial Catalog=Chinook;Integrated Security=True");
}
[Test]
public void TestEmployeeIndex()
{
var e = new EmployeeController(_data);
var result = e.Index() as ViewResult;
var employeeList = result.ViewData.Model;
Assert.IsNotNull(employeeList, "Result is null.");
}
In the first EmployeeControllerTest constructor, we set the data context to be used while running the tests. And then in the actual test, We just ensure that the View results returned by Index is not null.
Here is the zip of the entire solution files until this point. Let me know if you have any doubts or clarifications.
Cheers! Have a nice day.
