• Download jqGrid here
• Download a jQuery theme there
• Register jqGrid CSS and JavaScript with SharePoint
• Configure jqGrid
• Create a Data Adapter for jqGrid

Register jqGrid CSS and JavaScript with SharePoint

We'll build this jqGrid in a SharePoint Visual Web Part. Start with a SharePoint 2010 project containing a Visual Web Part, and add a "Layouts" Mapped Folder. It's a good idea to keep all the project stuff corralled, so the first subfolder under Layouts might well be named for the project. Then add css and js subfolders for the downloaded jQuery components.

Open the downloaded jQuery theme's css folder and copy the subfolder with your theme name to the SharePoint project's css folder. In this example, that's the "redmond" folder, including all its contents. Also from the downloaded jQuery theme, open the js subfolder, and copy the custom js file to the SharePoint project's js folder. In this example, that's the jquery-ui-1.8.20.custom.min.js file. OK, theme's done.

Now we just need four files from the jqGrid download:

• Copy to the SharePoint css folder: ui.jqgrid.css from the jqGrid download's css folder
• Copy to the SharePoint js folder:
1. jquery.jgGrid.min.js and jquery-1.7.2.min.js from the jqGrid download's js folder
2. grid.locale-en.js (or the language pack of your choice), from the download's js/i18n folder

Keeping things SharePoint-y, we'll register these with SharePoint constructs. To the Visual Web Part's Elements.xml file, add CustomAction elements similar to these just above the closing </Elements> tag:

<!--Register JQuery scripts-->

  <CustomAction

    Sequence="100"

    ScriptSrc="/_layouts/Mindsharp.OData.JQuery/js/jquery-1.7.2.min.js"

    Location="ScriptLink" />

  <CustomAction

    Sequence="110"

    ScriptSrc="/_layouts/Mindsharp.OData.JQuery/js/jquery-ui-1.8.20.custom.min.js"

    Location="ScriptLink" />

  <CustomAction

    Sequence="120"

    ScriptSrc="/_layouts/Mindsharp.OData.JQuery/js/grid.locale-en.js"

    Location="ScriptLink" />

  <CustomAction

    Sequence="130"

    ScriptSrc="/_layouts/Mindsharp.OData.JQuery/js/jquery.jqgrid.min.js"

    Location="ScriptLink" />

Most CustomAction elements add a link somewhere, but when the Location attribute is ScriptLink, it registers a script. The Sequence attribute is critical to getting these on the page in the right order. Be sure to edit the ScriptSrc value to match the path in your project.

To the Visual Web Part's ascx HTML source, add CssRegistration tags similar to this just below the default directives:

<SharePoint:CssRegistration runat="server"

  Name="/_layouts/Mindsharp.OData.JQuery/css/redmond/jquery-ui-1.8.20.custom.css"

  ID="CssRegistration1" >

</SharePoint:CssRegistration>

<SharePoint:CssRegistration runat="server"

  Name="/_layouts/Mindsharp.OData.JQuery/css/ui.jqgrid.css"

  ID="CssRegistration2" >

</SharePoint:CssRegistration>

Once again, the path in the Name attribute must match your project.

Configure jqGrid

This will be a very basic and minimal configuration. Check out the documentation for all the options. To the Visual Web Part's ascx source, just below the CssRegistration tags, add this html and script:

<div id="jqGrid">

      <table id="Northwinds"></table>

      <div id="NorthwindsPager" ></div>

</div>

<script type="text/JavaScript">

  $(document).ready(function () {

    $("#Northwinds").jqGrid({

      url: L_Menu_BaseUrl + '/_layouts/Mindsharp.OData.JQuery/NorthwindsXML.aspx',

      datatype: 'xml',

      mtype: 'GET',

      autowidth: true,

      height: 230,

      colNames: ['Customer ID', 'Company', 'Address', 'City', 'Country'],

      colModel: [

        { name: 'id', width: 125 },

        { name: 'company', width: 250 },

        { name: 'address', width: 125, sortable: false },

        { name: 'city', width: 100, sortable: false },

        { name: 'country', width: 100, sortable: false }

      ],

      pager: '#NorthwindsPager',

      rowNum: 10,

      rowList: [10, 20],

      sortname: 'id',

      sortorder: 'asc',

      viewrecords: true,

      gridview: true,  // insert all the data at once (speedy)

      caption: 'Northwinds Customers via OData'

    });

    // Add Navigator functions

    $("#Northwinds").jqGrid('navGrid', '#NorthwindsPager',

    { edit: false, add: false, del: false, search: false });

  });

</script>

The table with id="Northwinds" will be replaced by the grid (as specified by the $("#Northwinds").jqGrid script bit at the top of the script), and the div with id="NorthwindsPager" will be replaced by the navigation and paging bar below the grid (that's the pager: '#NorthwindsPager' bit near the middle of the script).

Like many jQuery-based controls, a url attribute specifies the datasource, which is the NorthwindsXML.aspx which we'll build soon. SharePoint relative urls can confuse jQuery controls, so help them out by using the SharePoint-generated javascript variable L_Menu_BaseUrl like this:

url: L_Menu_BaseUrl + '/_layouts/Mindsharp.OData.JQuery/NorthwindsXML.aspx'

Most of the configuration is self-explanatory. datatype and mtype tell jqGrid to use a GET request and expect an XML response. (JSON is another data type option.) colNames is an array of column headers for the grid. colModel defines those columns. Here, name is a unique name for the column, and will be used in the querystring, width sets a starting width (these will be adjusted to fit the parent element's width, due to the autowidth attribute also specified), and sortable determines whether that column can be sorted. Since jqGrid sends an Ajax query every time a sortable column header is clicked, or the page number is changed, or the pager dropdown is changed, or a pager button is clicked, only enable sorting on columns you'd like to code for. sortable is true by default, so turn it off where not wanted.

rowNum sets the initial number of rows to show in the grid, so it should be one of the values in rowList, which is the set of values for the number-of-rows-to-fetch dropdown in the pager bar. viewRecords: true instructs jqGrid to write the "View X to Y of Z" text on the right side of the pager bar.

sortname is the initial sort field and sortorder the initial sort direction ("asc" or "desc"). The Navigator bar is left of the pager, in the same bar. Since this is a read-only data source, all editing functions and search will be turned off by the navGrid part of the script, and only the refresh button will appear in the Navigator bar.

Create a Data Adapter for jqGrid

The grid needs a few global values returned in addition to the actual data. Specifically, it wants the number of the page returned, the total number of pages available, and the total number of records available. In XML data, jqGrid expects a "rows" root element, then page, total, and records elements, and a row element for each row of data returned. The row element should contain an id attribute (the primary index value), and cell elements for each column. CDATA sections are fine for data that needs it. Looks like this:

Since most web services don't emit this structure, we'll need to build a data adapter. Often this would be a generic HTTP handler (a .ashx file extension), but a simple aspx page will work just fine, and be a little less trouble to install in SharePoint. Add a text file to the project-named subfolder under the Layouts folder, but give it an .aspx extension; in this example, that's NorthwindsXML.aspx. Add another text file with the same name + .cs (like NorthwindsXML.aspx.cs), and Visual Studio will combine it with the .aspx added earlier. Paste something like this into the .aspx file (adapted to your project name and aspx name, of course):

<%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %>

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="NorthwindsXML.aspx.cs" Inherits="Mindsharp.OData.JQuery.NorthwindsXML" %>

Since we're only sending back XML, we don't need a lot of directives. To the code behind file, (NorthwindsXML.aspx.cs in this example), add something like:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Web;

using System.Xml.Linq;

using System.Collections.Specialized;

namespace Mindsharp.OData.JQuery

{

  public partial class NorthwindsXML :

    System.Web.UI.Page

  {

    protected void Page_Load(object sender, EventArgs e)

    {

      Response.ContentType = "text/xml";

      Response.Write(CreateXML());

    }

}

The Northwinds database is published as an OData feed courtesy of OData.org. If you've ever used ListData.svc in SharePoint, (example: http://intranet/_vti_bin/ListData.svc), you've used an OData feed. If OData is new to you, look into http://odata.org and Query SharePoint Foundation with ADO.NET Data Services. Basically it is entity classes served up in an Atom Pub style RSS feed, queryable by URL - it's REST for SharePoint List data. But back to the Northwinds OData - add a service reference to http://services.odata.org/Northwind/Northwind.svc, and give the proxy class a namespace- in this example it's NorthwindProxy.

Now we can create some XML from our OData feed. This particular feed is read-only, and has a server-side page limit of 20 items. For simplicity, we'll stay within that limit with our jqGrid configuration. Here's the CreateXML method:

    private string CreateXML()

    {

      NorthwindProxy.NorthwindEntities ctx =

          new NorthwindProxy.NorthwindEntities(

            new Uri("http://services.odata.org/Northwind/Northwind.svc"));

      int totalRecords = (from c in ctx.Customers

                          select c).Count();

      // Initialize variables to contain validated query string parameters

      string sortField = default(string);

      string sortDirection = default(string);

      int maxRowsToReturn = default(int);

      int pageToReturn = default(int);

      // Computed variable to send back to jqGrid

      int totalPages = default(int);

      CreateValuesFromQuerystring(Request.QueryString, totalRecords,

        out sortField, out sortDirection,

        out maxRowsToReturn, out pageToReturn, out totalPages);

      var query = CreateQuery(ctx, sortField, sortDirection,

        maxRowsToReturn, pageToReturn, totalRecords);

      var customerQuery = from c in query

                         select new

                         {

                           c.CustomerID,

                           c.CompanyName,

                           c.Address,

                           c.City,

                           c.Country

                         };

      XElement xrows = new XElement("rows",

        new XElement("page", pageToReturn),

        new XElement("total", totalPages),

        new XElement("records", totalRecords));

      foreach (var c in customerQuery)

      {

        xrows.Add(

          new XElement("row", new XAttribute("id", c.CustomerID),

            new XElement("cell", c.CustomerID),

            new XElement("cell", c.CompanyName),

            new XElement("cell", c.Address),

            new XElement("cell", c.City),

            new XElement("cell", c.Country))

          );

      }

      // XDocument does not provide the XML declaration, so here it is

      string xmlDeclaration = "<?xml version =\"1.0\" encoding=\"utf-8\"?>"

        + Environment.NewLine;

      return xmlDeclaration + xrows.ToString();

    }

The CreateValuesFromQuerystring() method reads the querystring and guarantees that all the variables harvested from it will have a valid value. A typical query for this project may look like this:

GET /_layouts/Mindsharp.OData.JQuery/NorthwindsXML.aspx?_search=false&nd=1337027722102&rows=10&page=2&sidx=id&sord=asc

Note that jqGrid throws in a Unix-style timestamp (nd=1337027722102), which helps defeat inadvertent browser caching.

Linq allows chaining of queries- meaning a single query can be built up in multiple statements, and a query can be the subject of another query. Take a look at these methods which build the Linq query dynamically by chaining:

    private IQueryable<NorthwindProxy.Customer> CreateQuery(

      NorthwindProxy.NorthwindEntities ctx,

      string sortField, string sortDirection,

      int maxRowsToReturn, int pageToReturn, int totalRecordsAvailable)

    {

      var query = from customer in ctx.Customers

                  select customer;

      query = AddOrderBy(query, sortField, sortDirection);

      query = query.Skip(maxRowsToReturn * (pageToReturn - 1))

                  .Take(maxRowsToReturn);

      return query;

    }

    private IQueryable<NorthwindProxy.Customer> AddOrderBy(

      IQueryable<NorthwindProxy.Customer> query, string field, string direction)

    {

      if (direction == "asc")

      {

        switch (field)

        {

          default:

          case "id":

            query = query.OrderBy(c => c.CustomerID);

            break;

          case "company":

            query = query.OrderBy(c => c.CompanyName);

            break;

        }

      }

      else

      {

        switch (field)

        {

          default:

          case "id":

            query = query.OrderByDescending(c => c.CustomerID);

            break;

          case "company":

            query = query.OrderByDescending(c => c.CompanyName);

            break;

        }

      }

      return query;

    }

There are cooler ways to dynamically add the OrderBy clause, but we're keeping things simple. For the curious, see Scott Guthrie's post on Dynamic Linq queries.

That should take care of the data adapter for the grid, so it's ready to deploy. Add the Web Part to a page and enjoy!