Click here to Skip to main content
15,867,568 members
Articles / Web Development / ASP.NET
Article

ASP.NET MVC Flexigrid sample

Rate me:
Please Sign up or sign in to vote.
4.75/5 (40 votes)
31 Oct 2008CPOL7 min read 574.4K   18.2K   189   70
How to create an ASP.NET MVC sample using LINQ to SQL, Flexigrid for JQuery, and JSON.

flexigrid.jpg

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.

Image 2

For this sample, we will not be covering unit testing, so select No in the Create Unit Test Project dialog.

Image 3

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:
  • HTML
    <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:

Image 4

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.

Image 5

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.

Image 6

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.

Image 7

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:

JavaScript
{
  page: 1,      // start at the first 'page' of records
  total: 15,      // 'total' number of records returned in the 'rows' field
  rows: [
    // 'id' contains the row identifier 
    // followed by an array of strings (one for each column) named 'cell'
    {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.

JavaScript
{
  "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.

Image 8

C#
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.

C#
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.

Image 9

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:

C#
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:

C#
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.Serialization
  • System.ServiceModel
  • System.ServiceModel.Web

Now, we can go to the Flexigrid view user control (FlexigridObject.ascx) and add the following line:

XML
<%= 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:

Image 10

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.

HTML
<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:

JavaScript
<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 search
  • sortname - 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.

C#
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:

C#
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.

flexigrid-complete.jpg

History

  • 10.31.2008 > Initial publishing.
  • 11.03.2008 > Updated based on user feedback.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralRe: Why not just return the data from the controller as a JsonResult? Pin
wgpubs124-Nov-08 9:41
wgpubs124-Nov-08 9:41 
GeneralRe: Why not just return the data from the controller as a JsonResult? Pin
Todd Smith25-Nov-08 7:12
Todd Smith25-Nov-08 7:12 
GeneralRe: Why not just return the data from the controller as a JsonResult? Pin
wgpubs125-Nov-08 9:16
wgpubs125-Nov-08 9:16 
GeneralRe: Why not just return the data from the controller as a JsonResult? Pin
Todd Smith26-Nov-08 20:45
Todd Smith26-Nov-08 20:45 
GeneralRe: Why not just return the data from the controller as a JsonResult? Pin
Joe Har3-Aug-10 6:25
Joe Har3-Aug-10 6:25 
GeneralRe: Why not just return the data from the controller as a JsonResult? Pin
wgpubs125-Nov-08 11:11
wgpubs125-Nov-08 11:11 
GeneralExcellent Pin
Pravesh Soni19-Nov-08 2:20
Pravesh Soni19-Nov-08 2:20 
GeneralThanks! Pin
deadbuddha3-Nov-08 9:06
deadbuddha3-Nov-08 9:06 
GeneralCustomerDataContext() [modified] Pin
Helza2-Nov-08 8:15
Helza2-Nov-08 8:15 
GeneralRe: CustomerDataContext() Pin
Todd Smith2-Nov-08 17:18
Todd Smith2-Nov-08 17:18 
GeneralDesign horror Pin
Radim Köhler1-Nov-08 22:04
Radim Köhler1-Nov-08 22:04 
GeneralRe: Design horror Pin
sefstrat2-Nov-08 0:04
sefstrat2-Nov-08 0:04 
GeneralRe: Design horror Pin
Radim Köhler2-Nov-08 0:42
Radim Köhler2-Nov-08 0:42 
GeneralRe: Design horror Pin
Todd Smith2-Nov-08 17:29
Todd Smith2-Nov-08 17:29 
GeneralRe: Design horror Pin
Radim Köhler3-Nov-08 20:28
Radim Köhler3-Nov-08 20:28 
GeneralRe: Design horror Pin
Charles E. Wagner Jr.7-Nov-08 10:32
Charles E. Wagner Jr.7-Nov-08 10:32 
GeneralRe: Design horror Pin
Todd Smith2-Nov-08 17:30
Todd Smith2-Nov-08 17:30 
GeneralGreat * 2 Pin
Joshua Lunsford31-Oct-08 14:21
Joshua Lunsford31-Oct-08 14:21 
GeneralGreat! Pin
Marc Clifton31-Oct-08 12:30
mvaMarc Clifton31-Oct-08 12:30 
GeneralRe: Great! Pin
Todd Smith31-Oct-08 14:23
Todd Smith31-Oct-08 14:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.