Introduction
This article will demonstrate, step-by-step, how to create a basic demo application using Microsoft's new ASP.NET MVC framework and Flexigrid. To keep this demo simple, we will not be covering unit testing, input validation, error handling, data encapsulation, authentication, routing, or any other facets of a well designed MVC application.
Prerequisites
At the time of publishing this article, ASP.NET MVC saw its first Beta release. In order for ASP.NET MVC and LINQ to SQL to work properly, you'll need to make sure you have VS2008 SP1 and .NET 3.5 SP1 installed. Important: There are several pre-release products such as Microsoft Silverlight Tools Beta 1 which will prevent VS2008 SP1 from installing successfully. Therefore, the VS2008 Service Pack Preparation Tool should be run prior to installing VS2008 SP1.
Create a New Project
The first step is to create a new ASP.NET MVC Web Application project.
For this sample, we will not be covering unit testing, so select No in the Create Unit Test Project dialog.
Now that a skeleton project has been created, we can do a bit of cleanup. Let's remove a few of the items we won't be using for this sample.
- Go ahead and delete AccountController.cs, the Account folder under Views, About.aspx, and LoginUserControl.ascx.
- Open up Index.aspx and remove the markup inside the
asp:Content tags.
- Open up Site.Master and change the title and body content with the following:
<title></title>
<body>
<div class="page">
<div id="main">
<asp:ContentPlaceHolder ID="MainContent" runat="server" />
</div>
</div>
</body>
Open up HomeController.cs and remove the About method and remove everything in the Index method except for return View();.
Once the cleanup is complete, the project should look like the following:
Go ahead and debug the application now, and make sure everything runs OK.
Add a Database
Next, place a copy of Northwind.mdf and Northwind_log.ldf in the App_Data folder. Right-click the App_Data folder in VS and select Add Existing Item, and locate the Northwind.mdf file, then click OK. When doing this, VS2008 will automatically update the web.config file with a connection string for the database named NorthwindConnectionString.
Now, lets create a data access layer using LINQ to SQL. Right-click the Models folder in VS and select Add New Item, and create a LINQ to SQL class named Customer, then click Add.
LINQ to SQL
Double-click the Northwind.mdf file to open the Object Relational Designer. Find the Customers table and drag it onto the designer page to create the data access layer. Click Save, then re-open the Solution Explorer, and you should now have a Customer.dbml file in the Models folder. This file contains the data context for accessing the database, and is automatically generated by LINQ to SQL. For the Customer data class, the data context will be called CustomerDataContext. We'll need to add a using Flexigrid.Models before accessing the CustomerDataContext class.
Go ahead and run the application now to make sure everything is functioning properly.
Flexigrid and JSON
Next, we'll create a controller method for retrieving the customer data from the database and returning it as a JSON object for Flexigrid. Right-click on the Controllers folder and select Add New Item, then create a new MVC Controller class named JsonController.cs.
Now, we need to write the code that will query the database for the Customer records and return a data class that, when serialized to JSON, will be in the proper format for the Flexigrid to consume. Flexigrid can support both paging and sorting, both of which are handled server-side. The JSON format that Flixgird expects is as follows:
{
page: 1,
total: 15,
rows: [
{id: 3879, cell: ["", "", "", ""]},
]
}
This is what the first two records of the Customer table would look like. Note that the data has been truncated (, ... ,) slightly for readability.
{
"page": 1,
"total": 2,
"rows":[
{"id": "ALFKI", "cell": ["ALFKI","Alfreds Futterkiste",
"Maria Anders", ... ,"030-0076545"]},
{"id": "ANATR", "cell": ["ANATR","Ana Trujillo Emparedados y helados",
"Ana Trujillo","Owner", ... ,"(5) 555-3745"]}
]
}
Add a new class named FlexigridObject to the project, with the following code. When serializing to JSON, the property name will become the key name. In the Customer table, the primary key type is nchar(5), so we will use a string to hold the row ID.
public class FlexigridRow
{
public string id;
public List<string> cell = new List<string> ();
}
public class FlexigridObject
{
public int page;
public int total;
public List<FlexigridRow> rows = new List<FlexigridRow> ();
}
Now, let's open up the JsonController.cs file and add the following two methods. CustomerList() will retrieve the full list of customers from the Customers table and then populate a FlexigridObject with the results. In the GetPropertyList method, we use Reflection to iterate over the Customer properties, adding each property value to a List. Be sure to handle the case where a value is null, by returning an empty sting.
using Flexigrid.Models;
public ActionResult CustomersList ()
{
var db = new CustomerDataContext ();
var q = from c in db.Customers
select c;
List<Customer> customers = q.ToList ();
FlexigridObject flexigridObject = new FlexigridObject ();
flexigridObject.page = 1;
flexigridObject.total = db.Customers.Count ();
foreach (Customer customer in customers)
{
FlexigridRow row = new FlexigridRow ()
{
id = customer.CustomerID,
cell = GetPropertyList (customer)
};
flexigridObject.rows.Add (row);
}
return View ("FlexigridObject", flexigridObject);
}
private List<string> GetPropertyList (object obj)
{
List<string> propertyList = new List<string> ();
Type type = obj.GetType ();
PropertyInfo[] properties = type.GetProperties (BindingFlags.Instance |
BindingFlags.Public);
foreach (PropertyInfo property in properties)
{
object o = property.GetValue (obj, null);
propertyList.Add (o == null ? "" : o.ToString ());
}
return propertyList;
}
Adding a View
Even though we haven't added the view yet, let's go ahead and compile and run the code then navigate to the CustomersList action we just added (e.g.: http://localhost:1526/Json/CustomersList). We should get the following error:
The view 'FlexigridObject' or its master could not be found.
The following locations were searched:
~/Views/json/FlexigridObject.aspx
~/Views/json/FlexigridObject.ascx
~/Views/Shared/FlexigridObject.aspx
~/Views/Shared/FlexigridObject.ascx
This tells us that the default routing logic in ASP.NET MVC is expecting a view page (.aspx) or view control (.ascx) named "FlexigridObject" located in the Views folder. Since we only need to output a JSON result, we can get by with a simple view control. Let's create a sub-folder in Views named json and add a new ASP.NET MVC View User Control named FlexigridObject.ascx.
Now, we need to set the data type for the view user control data model. Open FlexigridObject.ascx.cs and modify the class to be the following:
public partial class FlexigridObject : ViewUserControl <Flexigrid.FlexigridObject>
LINQ to SQL
Next, we need to add a method which will serialize the FlexigridObject into a JSON result. Add a new class named JsonSerializer.cs with the following code:
public class JsonSerializer
{
public static string ToJsonObject (object obj)
{
DataContractJsonSerializer serializer =
new DataContractJsonSerializer (obj.GetType ());
using (MemoryStream ms = new MemoryStream ())
{
serializer.WriteObject (ms, obj);
StringBuilder sb = new StringBuilder ();
sb.Append (Encoding.Default.GetString (ms.ToArray ()));
return sb.ToString ();
}
}
}
In order to use the JSON serialization classes, we need to add the following references to the project:
System.Runtime.SerializationSystem.ServiceModelSystem.ServiceModel.Web
Now, we can go to the Flexigrid view user control (FlexigridObject.ascx) and add the following line:
<%= Flexigrid.JsonSerializer.ToJsonObject (ViewData.Model) %>
Let's compile and run the code, then navigate to the CustomerList action (e.g.: http://localhost:1526/Json/CustomersList). Woah! If all goes well, you should see a giant blob of data containing all the rows from the Customers table formatted as a Fleixgrid JSON object. Now, we have a URL which we can feed to a Flexigrid table.
Finishing the View
We're almost done. We need to add the Flexigrid library to our project. First, download Flexigrid and extract the zip to the Contents folder. We still need to add the files to our solution, so open Windows Explorer and navigate to the Contents folder. Now, drag the flexigrid folder from Windows Explorer and drop it onto the Contents folder in VS2008. You should see the following results:
Open Site.Master and add the following lines to the header. Note: the jquery.js include must come before flexigrid.js, or you will receive a JavaScript error.
<script src="../../Content/flexigrid/lib/jquery/jquery.js" type="text/javascript"></script>
<script src="../../Content/flexigrid/flexigrid.js" type="text/javascript"></script>
<link href="../../Content/flexigrid/css/flexigrid/flexigrid.css"
rel="stylesheet" type="text/css" />
Next, open up the Index.aspx page and add the following code:
<div>
<table id="customers" style="display:none"></table>
<script type="text/javascript">
$("#customers").flexigrid({
url: '/Json/CustomersList',
dataType: 'json',
colModel: [
{display: 'ID', name: 'CustomerID', width: 40,
sortable: true, align: 'left'},
{display: 'Company', name: 'CompanyName', width: 100,
sortable: true, align: 'left'},
{display: 'Name', name: 'ContactName', width: 100,
sortable: true, align: 'left'},
{display: 'Title', name: 'ContactTitle', width: 100,
sortable: true, align: 'left'},
{display: 'Address', name: 'Address', width: 100,
sortable: true, align: 'left'},
{display: 'City', name: 'City', width: 80,
sortable: true, align: 'left'},
{display: 'Region', name: 'Region', width: 60,
sortable: true, align: 'left'},
{display: 'Postal Code', name: 'Postalcode', width: 80,
sortable: true, align: 'left'},
{display: 'Country', name: 'Country', width: 80,
sortable: true, align: 'left'},
{display: 'Phone', name: 'Phone', width: 80,
sortable: true, align: 'left'},
{display: 'Fax', name: 'Fax', width: 80,
sortable: true, align: 'left'}
],
searchitems: [
{display: 'Company', name: 'CompanyName'},
{display: 'Name', name: 'ContactName'},
{display: 'Title', name: 'ContactTitle'},
{display: 'Address', name: 'Address'},
{display: 'City', name: 'City'},
{display: 'Country', name: 'Country'}
],
sortname: 'CustomerID',
sortorder: 'asc',
usepager: true,
title: 'Customers',
useRp: true,
rp: 15,
showTableToggleBtn: true,
width: 1040,
height: 380
});
</script>
</div>
If all goes well and you haven't committed any typos, you should be presented with a Flexigrid containing all of the records from the Northwind.Cusotmers table. However, you may notice that none of the Flexigrid control features work yet such as column sorting, searching, or paging. For that, we will need to call upon a mysterious magic that is LINQ.
LINQ and Beyond
In Flexigrid, all of the sorting, searching, and paging is done server-side. Flexigrid passes the following form variables to our controller:
page - when paging is enabled, represents the page number Flexigrid is requesting.rp - when paging is enabled, represents the number of results per page.qtype - the name of the column to search in when performing a keyword search.query - contains the keyword to use when performing a searchsortname - the column name to sort by.sortorder - ascending or descending.
We need to use the above form variables to modify our select query to return a subset of records. Add another action handler to the JSON controller called FlexigridList.
public ActionResult FlexigridList ()
{
int page = int.Parse (Request.Form["page"]);
int rp = int.Parse (Request.Form["rp"]);
string qtype = Request.Form["qtype"].ToString ();
string query = Request.Form["query"].ToString ();
string sortname = Request.Form["sortname"].ToString ();
string sortorder = Request.Form["sortorder"].ToString ();
var q = from c in db.Customers
select c;
if (!string.IsNullOrEmpty (qtype) && !string.IsNullOrEmpty (query))
{
q = q.Like(qtype, query);
}
q = q.Skip ((page - 1) * rp).Take (rp);
if (!string.IsNullOrEmpty (sortname) && !string.IsNullOrEmpty (sortorder))
{
q = q.OrderBy (sortname, (sortorder == "asc"));
}
List<Customer> customers = q.ToList ();
FlexigridObject flexigridObject = new FlexigridObject ();
flexigridObject.page = page;
flexigridObject.total = db.Customers.Count ();
foreach (Customer customer in customers)
{
FlexigridRow row = new FlexigridRow ()
{
id = customer.CustomerID,
cell = GetPropertyList (customer)
};
flexigridObject.rows.Add (row);
}
return View ("FlexigridObject", flexigridObject);
}
To support sorting and searching, we can extend LINQ with a couple of extension methods, as follows:
public static class ExtensionMethods
{
public static IQueryable<T> OrderBy<T> (
this IQueryable<T> source, string propertyName, bool asc)
{
var type = typeof (T);
string methodName = asc ? "OrderBy" : "OrderByDescending";
var property = type.GetProperty (propertyName);
var parameter = Expression.Parameter (type, "p");
var propertyAccess = Expression.MakeMemberAccess (parameter, property);
var orderByExp = Expression.Lambda (propertyAccess, parameter);
MethodCallExpression resultExp = Expression.Call (typeof (Queryable), methodName,
new Type[] { type, property.PropertyType },
source.Expression, Expression.Quote (orderByExp));
return source.Provider.CreateQuery <T> (resultExp);
}
public static IQueryable<T> Like<T> (this IQueryable<T> source,
string propertyName, string keyword)
{
var type = typeof (T);
var property = type.GetProperty (propertyName);
var parameter = Expression.Parameter (type, "p");
var propertyAccess = Expression.MakeMemberAccess (parameter, property);
var constant = Expression.Constant ("%" + keyword + "%");
var like = typeof(SqlMethods).GetMethod ("Like",
new Type[] {typeof (string), typeof (string)});
MethodCallExpression methodExp =
Expression.Call (null, like, propertyAccess, constant);
Expression<Func<T, bool>> lambda =
Expression.Lambda<Func<T, bool>> (methodExp, parameter);
return source.Where (lambda);
}
}
Open Index.aspx and update the URL '/Json/CustomersList' to point to the URL '/Json/FlexigridList'.
You should now have a fully functional ASP.NET MVC / Flexigrid / JSON / LINQ to SQL demo for impressing all of your friends or padding your resume.
History
- 10.31.2008 > Initial publishing.
- 11.03.2008 > Updated based on user feedback.
