Using oData with jQuery Hierarchical Grid

I have written a few posts in the past on how to bind data from oData services directly to the jQuery client controls that ship in NetAdvantage for jQuery. There are quite a few samples within our samples library that do so as well. In this post, I am going to show you how to connect a jQuery hierarchical grid with an oData feed just by supplying the initial oData service URL. For the most part, the way the hierarchical grid works with oData is very similar to the flat grid, however, it uses the “expand” attribute when the user tries to drill deeper into the data. This “expand” attribute in oData allows the hierarchical grid to work with the oData protocol and be able to display it’s hierarchical structure as demanded by the user.

Getting Started

There are a lot of open data exposed through oData feeds, in the past I’ve used the Netflix example extensively, but today I am going to use a NorthWind service. I have no idea where the service lives or what kind of a server stack it is developed upon. All I need is the URL that gives me access to it. Once I have that URL and the server implements all the require attributes especially the “expand” attribute, I can easily bind the service data to my hierarchical grid.

The service URL I am going to use for this blog post is the NorthWind services up on the oData.org itself.

http://services.odata.org/Northwind/Northwind.svc

This URL gives me access to all the NorthWind tables like Customer, Order, Product, Category..etc. I am going to use the hierarchical grid to display Customer –> Order –> Order Details hierarchy.

image

Coding the Grid

When something like displaying a hierarchical structure from a service data comes to my mind, it usually means writing a ton of data setup code, creating foreign key mappings, a lot of SQL to pull in the required data. If the application needs to apply sorting or filtering on that data then I end up with more code. With oData, one of the main benefit I get is all that stuff is now handled by the protocol conventions and the oData feed itself. In addition with the jQuery Grid in NetAdvantage for jQuery handling of the data to and from the service is baked into the product itself, makes my code a lot less straight forward. I just need to specify the oData URL the grid needs to fetch it’s data from, and it takes care of the rest. I am going to first begin with creating a place holder on my html page. A simple table element with an “id” is going to do the job. On jQuery ready event, I am going to convert it into an grid control.

   1: <table id="HierarchicalGrid"></table>

Next, in the jQuery ready event, I am going to reference the table defined above and convert that into a hierarchical grid. As you will see in the code snippet, most of the code is to setup the grid, setting options like autoGenerateColumns & Layout to false because we want to define those manually. Setting options on the grid related to it’s schema & hierarchy definition like the primaryKey & foreignKey and also enabling the sorting & filtering feature on the grid. The option that connects this grid to the oData feed is the dataSource option (dataSource: "http://services.odata.org/Northwind/Northwind.svc/Customers?$format=json&$callback=?"). This option tells the grid where to go and look for its data.

   1: $(function () {
   2:     $("#HierarchicalGrid").igHierarchicalGrid({
   3:         autoGenerateColumns: false,
   4:         autoGenerateLayouts: false,
   5:         initialDataBindDepth: 0,
   6:         primaryKey: "CustomerID",
   7:         odata: true,
   8:         columns: [
   9:                     { headerText: "Customer ID", key: "CustomerID", width: "150px" },
  10:                     { headerText: "Company Name", key: "CompanyName", width: "300px" },
  11:                     { headerText: "Contact Name", key: "ContactName", width: "200px" }
  12:                 ],
  13:         columnLayouts: [
  14:             {
  15:                 key: "Orders",
  16:                 primaryKey: "OrderID",
  17:                 foreignKey: "CustomerID",
  18:                 responseDataKey: 'd.results',
  19:                 autoGenerateColumns: false,
  20:                 autoGenerateLayouts: false,
  21:                 odata: true,
  22:                 columns: [
  23:                 { key: "OrderID", headerText: "Order ID", dataType: "number" },
  24:                 { key: "OrderDate", headerText: "Order Date", dataType: "date" },
  25:                 { key: "Freight", headerText: "Freight", dataType: "number" }
  26:                 ],
  27:                 columnLayouts: [
  28:                     {
  29:                         key: "Order_Details",
  30:                         foreignKey: "OrderID",
  31:                         responseDataKey: 'd.results',
  32:                         odata: true,
  33:                         autoGenerateColumns: false,
  34:                         autoGenerateLayouts: false,
  35:                         columns: [
  36:                           { key: "OrderID", headerText: "Order ID" },
  37:                           { key: "UnitPrice", headerText: "Unit Price", dataType: "number" },
  38:                           { key: "Quantity", headerText: "Quantity", dataType: "number" }
  39:                        ]
  40:                     }
  41:                 ]
  42:             }
  43:         ],
  44:         responseDataKey: "d.results",
  45:         dataSource: "http://services.odata.org/Northwind/Northwind.svc/Customers?$format=json&$callback=?",
  46:         height: '500px',
  47:         features: [ { name: 'Sorting' },
  48:                     { name: 'Filtering', filterDropDownItemIcons: false, filterDropDownWidth: 200 }
  49:                   ]
  50:     });
  51:  
  52: });

That is all the pain you have to go through in order to get the jQuery grid connected to oData feed. The hierarchical grid was added to the product in 2011 Volume 2.

Conclusion

In todays example, I walked you through on how you can bind the hierarchical data grid to an oData feed without having to know all the details of the protocol. Setting up the grid to consume data can be done by dataSource option and all the related data is fetched on demand when the user tried to expand any of the row. It is as simple as it looks, connecting to oData feed is quite easy to do with the data bound controls like the combo, tree & grids that ship in NetAdvantage for jQuery product. As always, should you have any questions about this blog post or the product in general, feel free to reach out to me at murtazaa@infragistics.com

Note : Make sure you are on the latest Service Release for this to work properly.

Check out the application running live here. You can also download the sample used to develop this article by clicking here.


Comments  (1 )

Damyan Petev's Blog
on Wed, Feb 15 2012 8:15 AM

The Hierarchical Grid (a fairly recent addition to our NetAdvatage for jQuery) is a client-side control