Click here to Skip to main content
Click here to Skip to main content

ASP.NET MVC Flexigrid sample

, 31 Oct 2008
Rate this:
Please Sign up or sign in to vote.
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.

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,      // 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.

{
  "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.Serialization
  • System.ServiceModel
  • System.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 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.

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.

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)

About the Author

Todd Smith
Software Developer (Senior)
United States United States
No Biography provided

Comments and Discussions

 
QuestionGrid Renders but does not show data PinmemberTerryR154723-Jul-09 10:23 
AnswerRe: Grid Renders but does not show data PinmemberTodd Smith23-Jul-09 10:43 
GeneralRe: Grid Renders but does not show data PinmemberTerryR154724-Jul-09 7:50 
GeneralRe: Grid Renders but does not show data PinmemberTodd Smith24-Jul-09 9:16 
GeneralRe: Grid Renders but does not show data [modified] PinmemberTerryR154724-Jul-09 15:37 
GeneralRe: Grid Renders but does not show data PinmemberTodd Smith25-Jul-09 5:09 
GeneralRe: Grid Renders but does not show data PinmemberTerryR154725-Jul-09 8:17 
GeneralRe: Grid Renders but does not show data Pinmembershraddha19041-Nov-10 16:57 
GeneralRe: Grid Renders but does not show data PinmemberTerryR154727-Jul-09 7:44 

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.140709.1 | Last Updated 31 Oct 2008
Article Copyright 2008 by Todd Smith
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid