Part 1: Searchable Ajax Grid

In this series of posts we will present a searchable data grid. The grid is provided by the jqGrid plugin for jQuery. On the server side we use an ASPMVC Controller to provide the data. This instalment will cover the setting up of the data and the basic grid.

The data that we display in the grid will be log4net entries written by an ADODB appender. The schema for the table follows

You should create a SQL Server database or use an existing one and execute the above statement to create the table. I will attach some sample data to this post to load into the table.

Our sample web application will be an ASP MVC2 application so start by creating an empty “ASP MVC 2 Web Application” in Visual Studio. I’m currently using VS2008 so you need to install ASP MVC2. (TIP: Use the Platform SDK from Microsoft). For VS2010 users its already included.

We will use LINQ to SQL as our data access object so we need to create a Data Context. This is done by adding a “LINQ to SQL Classes” item to the Models folder of your solution. Create a connection to your database in Server Explorer and drag the DetailedLog table onto the design surface

No that we have our data setup we can dive into some code. We will work from the database up to the front end so with our Data Context defined we can write a simple data access routine. We will used the deferred execution style.

We should note that we are using the dynamic LINQ capabilities provided by the ASPMVC2 Futures library. Download the Futures library and add the dynamic.cs file to your solution. The MainentanceDao class we defined above has a static method which will result in two queries. The first will return the total number of records and the second will return a subset of records the governed by the page and pageSize parameters. The order of the records is governed by the sidx and sord parameters, the jqGrid is the ultimate source of the values for these parameters. So with our data access method defined we can write a basic controller.

The Log4NetController calls our GetDetailedLog to get the raw records into the variable rowData we create an anonymous object containing the data to be passed to the jqGrid. This object is then passed to the Json method to construct a JsonResult object. The data to be displayed in the grid is represented as a dictionary with a key identified as ID and a value identified as CELL which is an array of string. No that we have our controller we need the Index view. This is a straight forward view with the code needed to render the jqGrid.

The following is the complete source for our view. When the page loads an asynchronous request is made to the DetailedJson action of our Log4NetController class resulting in a rendered grid.

The following image is how our rendered grid looks.

In Part 2 we will show how to perform advanced searching on the grid data.

Tagged with: , , ,
Posted in Ajax, ASPMVC, jQuery

Leave a Reply

Your email address will not be published.