Computer Guy

I would suggest before getting started is to have some knowledge of the following. I wanted to first say thank you Phil Haack and his BLOG entry over at Haacked click here.

Anonymous types in .NET 3.x
- Wikipedia
- Anonymous Types in C# 3.0 by Rick Strahl
JSON (JavaScript Object Notation)
- Introducing JSON
- Wikipedia
Skip and Take for LINQ
- How to: Return or Skip Elements in a Sequence (LINQ to SQL)
jqGrid PlugIn
AdvantureWorksDB
- Direct Link
- Web Page (Download AdventureWorksDB.msi)
jQuery UI
- Themes

Here are a few steps to get a project started.

Create a ASP.NET MVC 2 or 1 Web Application.
Add jQuery, jqGridPlugIn, and the theme of your choice to the application,
1. Open the Them Roller pick your theme. I chose Start
2. Click Download and place the jquery-ui-custom file on you hard drive.
3. Make sure to select everything as to not to miss something for themes.
4. After downloading unzip the file and open the folder CSS.
5. In the CSS folder you will see a folder with the name of the theme, because I chose the Start theme I have a folder called Start.
6. Copy the subfolder in CSS theme folder into the Content folder of the MVC project.
7. The rest of the files in the jquery-ui-custom zip you don’t need.
8. Download jqGrid PlugIn and unzip.
9. Copy the i18n and the local will use into the Scripts folder.
10. Copy the jquery.jqGrid.min.js into the scripts folder.
11. Copy the ui.jqgrid.css into the Content folder.
Modify the Site.Master file to include jQuery. If you want you can get the latest version, but I’m going with jQuery 1.4.1 which comes with ASP.NET MVC 2
Modify the Site.Master file to include the jQuery Them CSS file. For my sample I’m including the in Contentstatjquery-ui-1.8.custom.css
Modify the Site.Master file to have a Content Place Holder for JavaScript.

After you are done Site.Master file will look similar to this.

Code Snippet

   .
   .
   .
<head runat="server">
    <title><asp:ContentPlaceHolder ID="TitleContent" runat="server" /></title>
    <link href="../../Content/Site.css" rel="stylesheet" type="text/css" />
 
    <script src="../../Scripts/jquery-1.4.1.min.js" type="text/javascript"></script>
    <link href="../../Content/start/jquery-ui-1.8.custom.css" rel="stylesheet" type="text/css" />
    <asp:ContentPlaceHolder ID="ScriptContent" runat="server" />
</head>
   .
   .
   .

When it comes to the AdventureWorks database and setting it up I’m going to make the assumption that is not a challenge. I’m going to use the SalesOrderHeader table from the database to build the grid from. I’m going to create a Data layer in the web Application, a data context, and a repository to read the records from the database.

Create a folder and name it Data
Add a New Item to the folder choose LINQ to SQL Classes
Name the file AdventureWorks
Add the table SalesOrderHeader to the DataContext design.
Add a Class to the folder and name it SalesOrderHeaderRepository

The ground work is in place now what we need to do is figure out what jqGrid does to get the data. This is where the documentation takes a dive. There is documentation on a URL you must setup for jqGrid to work. The is no explanation as to what parameters are passed to that URL and had to use those parameters to get the expected data. The only documentation that I have found that explains the parameters is here, but you need to scroll down to the prmNames section to see it. Another good example although not documentation, but you can see an explanation of the parameters in a PHP sample

Code Snippet

$page = $_GET['page']; // get the requested page
$limit = $_GET['rows']; // get how many rows we want to have into the grid
$sidx = $_GET['sidx']; // get index row – i.e. user click to sort
$sord = $_GET['sord']; // get the direction

From that we can see that a the page of data requested along with how may rows of data we need to get. These two parameters are called page and rows. Also being passed is the column to sort on and the direction to sort in. I will not get into sorting, but there are some good blog entries on how to implement that with dynamic LINQ (Thank you Phil). Now that we have the requirements down we need to build a method in our Sales Order Header Repository that will return paged data and only return a set number of records.

Open the SalesOrderHeaderRepository.cs file and past the following

Code Snippet

/// <summary>
/// Gets the sales orders starting at the specified page and return
/// specified number of rows.
/// </summary>
/// <param name="page">The page to start getting sales order.</param>
/// <param name="rows">The number of rows to get.</param>
/// <returns>
/// The specified number of sales orders rows at the specfied page.
/// </returns>
public IEnumerable<SalesOrderHeader> GetSalesOrders(int page, int rows)
{
    using (var dataContext = new AdventureWorksDataDataContext())
    {
        int startRow = page * rows;
        var results = dataContext.SalesOrderHeaders.Skip(startRow).Take(rows);
        foreach(var result in results)
        {
            yield return result;
        }
    }
}

One of the blessing of using ASP.NET 3.x is the routing and method calling done before creating a web page. Knowing how the routing works we can use a URL in for jqGrid PlugIn that calls an Action inside a Controller. It should be easy to create a method knowing that the Action mimics the same named parameters of the URL. Pulling together this knowledge of the parameters used by jqGrid PlugIn and how the parameters for an Action works you can create Controller class with methods to handle the request of the jqGrid.

Add to the Controllers folder a Controller and name it GridController
Create a Method in the controller that will return the data using the methods required by jqGrid

Code Snippet

public ActionResult GridData(string sidx, string sord, int page, int rows)
{
    
}

Now we need to use the repository to get the data based on the what is passed in and return the results as JSON. Let us start by looking at the requirements for the JSON results. You will see that a requirement is a count of total number of records.

Code Snippet

{ 
  total: "xxx", 
  page: "yyy", 
  records: "zzz",
  rows : [
    {id:"1", cell:["cell11", "cell12", "cell13"]},
    {id:"2", cell:["cell21", "cell22", "cell23"]},
      …
  ]
}

There are many was to go about doing this, but for a quick and dirty way I’m just going to fulfill this requirement by adding a method to the repository that returns the total number of records. Later on this can become useful if there are filters applied then the method can be modified to count only the records base on a filter.

Open the SalesOrderHeaderRepository.cs file and past the following

Code Snippet

/// <summary>
/// Gets the number of sale orders.
/// </summary>
/// <returns>The number of sale orders</returns>
public int GetNumberOfSalesOrder()
{
    int numberOfRecords = 0;
    using (var dataContext = new AdventureWorksDataDataContext())
    {
        numberOfRecords = dataContext.SalesOrderHeaders.Count();
    }
 
    return numberOfRecords;
}

We can now build out the method in the controller now that we have all the supporting pieces.

Open the GridController.cs
Add a using to the Data layer
Code Snippet
1. using jqGridDemo.Data;
Update the method to the following

Code Snippet

public JsonResult GridData(string sidx, string sord,  int page, int rows)
        {
            var repository = new SalesOrderRepository();
            
            int pageIndex = page – 1;
            int pageSize = rows;
            int totalRecords = repository.GetNumberOfSalesOrder();
            int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);
 
            var jsonData = new
            {
                total = totalPages,
                page = page,
                records = totalRecords,
                rows = (
                    from salesOrder in repository.GetSalesOrders(pageIndex, rows)
                    select new
                    {
                        i = salesOrder.SalesOrderID,
                        cell = new string[] { 
                            salesOrder.SalesOrderNumber, 
                            salesOrder.OrderDate.ToShortDateString(), 
                            salesOrder.DueDate.ToShortDateString(), 
                            salesOrder.TotalDue.ToString("###,###,##0.00")
                        }
                    }
                ).ToArray()
            };
 
            return Json(jsonData);
        }

I wont get into all the nuts and bolts of the logic you can take a look at the blogs mentioned above. Suffice to say that this will grab the data just the page of data from the database and return back a JSON Action Result.

The final piece to the puzzle is the HTML page. We will utilize the Content Placer we created in the Site.Master to hold the JavaScript. Some of the key options of the jqGrid PlugIn is the URL which is in the form of the default ASP.NET Routings which is /Controller/Action. You will also see that the data type is JSON. Finally and the most important one is the method type which is POST. With ASP.MVC 2 returning JSON results from a GET method type is not allowed by default because of security. Instead of use a GET as method to the call I changed it to use POST. I know the REST people are getting upset.

Code Snippet

<%@ Page Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage" %>
 
<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">
    Home Page
</asp:Content>
<asp:Content ID="script" ContentPlaceHolderID="ScriptContent" runat="server">
    <link href="../../Content/ui.jqgrid.css" rel="stylesheet" type="text/css" />
    <script src="../../Scripts/i18n/grid.locale-en.js" type="text/javascript"></script>
    <script src="../../Scripts/jquery.jqGrid.min.js" type="text/javascript"></script>
 
<script type="text/javascript">
    jQuery(document).ready(function() {
        jQuery("#list").jqGrid({
            url: ‘/Grid/GridData’,
            datatype: ‘json’,
            mtype: ‘POST’,
            colNames: ['Order Number', 'Order Date', 'Due Date', 'Amount'],
            colModel: [
              { name: 'salesordernumber', index: 'salesordernumber', width: 200 },
              { name: 'orderdate', index: 'orderdate', width: 200 },
              { name: 'duedate', index: 'duedate', width: 200 },
              { name: 'totaldue', index: 'tax', width: 200 }
            ],
            pager: ‘#pager’,
            rowNum: 20,
            rowList: [20, 40, 80],
            sortname: ‘invid’,
            sortorder: ‘desc’,
            viewrecords: true,
            caption: ‘My first grid’,
            height: "300"
            
        });
    }); 
</script>
 
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
 
<table id="list"></table> 
<div id="pager"></div> 
 
</asp:Content>

It is with hope that by stumbling through this and the other posts on the web you will be able to implement jqGrid PlugIn into ASP.NET MVC.

One other possibility is that I’m using the logic wrong. The image list needs to be reorder into Morton Layout column major.

This is the test method

Code Snippet

[TestMethod]
public void Build_Tile_For_Zoom_Level_5_Row_0_Column_0()
{
    // Location of the images.
    string imagePath = @"D:DataImages";
 
    // Sequential list used to store the file names.
    var rawSequentialImageList = new List<string>();
 
    // Build a sequential list of the images
    for(int imageCount = 0; imageCount < 24; imageCount++)
    {
        string fileName = string.Concat(imageCount, ".jpg");
        fileName = Path.Combine(imagePath, fileName);
        rawSequentialImageList.Add(fileName);
    }
 
    // List used to store the file names in Morton Layout column major.
    var mortonImageList = new List<ImageBag>();
 
    // List of morton number used as indexes to the sequential list.
    var numberList = new List<int>(TileBuilder.GetTileIds(24, 5, 0, 0, 256));
    
    // Iterate thru the morton numbers reordering the raw image list into the 
    // mortonImageList.
    foreach (var mortonNumber in numberList)
    {
        string fileName = rawSequentialImageList[mortonNumber];
        mortonImageList.Add(new ImageBag(fileName));
    }
 
    // Create the tile image based on the reorder image list.
    TileBuilder.CreateTileImage(mortonImageList, 5, 0, 0, "..\..\..\");
}