Using JqGrid in ASP.NET

Problem that made me think about JqGrid

I am working in an application with more than two million users in it; in that application there is an option to search the users with their First Name, Last Name and with the last four digits of SSN, we were using our grid view to display the search result.

As number of users increase, the search result also started growing enormously, we started getting more

than 300 search results with the same combination.

This made the user really difficult to find the exact person or user. The solution suggested was to provide one more filtering or searching feature in the client side. The next question aroused here is how we can add these features for the existing grid or is there any control which provides the feature available in market.

This search took us to JqGrid which is a free control which has got all the above mentioned features, which can be done with very less coding effort

A Few Words about JqGrid

• JqGrid is Stylish and Featured Tabular data presentation control.
• JqGrid is a JavaScript control for representing and manipulating tabular data on the web.
• JqGrid is Ajax Enabled.
• JqGrid Can be integrated with any of the server-side technologies like ASP, JavaServelets, JSP, PHP etc.
• JqGrid was developed by Tony Tomov at Trirand Inc.
• JqGrid is very simple to integrate with ASP.NET.

Integrating JqGrid with ASP.NET step by step

• Download JqGrid from Source.
• Add required files to ASP.NET application (including Scripts and CSS).
• Initialize JqGrid in page we require.
• Bind Data.

As I mentioned in the point one you can download the JqGrid and associated files from http://www.trirand.com/blog/?page_id=6 here.

I tried downloading it and I got zip file named jquery.jqGrid-4.5.2.zip. From the name of the zip file itself I could make out that I had downloaded the 4.5.2 version of JqGrid.

Lets extract and explore the zip file and see what all are there. I found some CSS folder some script folder etc inside. let’s go and see what all files we can make use of from this. For your better understanding, I have created a demo application called JQGridDemo to show the Integration and features.

The main thing what we want is the jQuery file, because our hero relays on JavaScript and jQuery.

Only for the purpose of saving files related to JqGrid I’ve created a folder in our solution called JQGridReq. As a first step I added the jQuery file in to it.

Added JQuery 1.9 to it .I think 1.9 JQuery is the latest stabilized version of jQuery. And what we require next, if we are using any UI related stuffs of jQuery? Yes of course its' the CSS file. But I searched inside the folder for the jQuery CSS file, I couldn't find that. No Issues… OK, let's do one thing, browse and download from the Internet.

Better, right?

Ok at last I found from internet. Jquery-ui-1.9.2.custom.css, let’s go and add this also to our application. Make sure that you also add the corresponding image folder also, because we are adding the CSS that might also require the images related to the CSS.

So we are now ready with jQuery stuff.

Let’s start adding our JqGrid files one by one

1. jquery.jqGrid-4.5.2\src\ jquery.jqGrid.js
2. jquery.jqGrid-4.5.2\js\i18n\ grid.locale-en.js
3. jquery.jqGrid-4.5.2\css\ ui.jqgrid.css

Did you notice the second file? which is very important one. Also we have added CSS files too. We are done with prerequisites now. You can see the files which we have added and their folder structure in the below snapshot.

Let’s go to the .aspx page to add the JqGrid.

For the sample project, I am using a Simple case study of Employee Management. From the solution itself, you can identify that am using master page. So what we have to do is we have to call all the required files in the master page itself, in the head section of the HTML page.

You can see in the below section, what I added in head region of the master page.

Ok now what I did is, I went right a way to our page in which we are planning to place the JqGrid. Here it is the Default.aspx page.

Placed an HTML table inside that page with ID jQGridDemo. (This grid is going to act as our hero the JqGrid.)

What my intention here is to transform the table to JqGrid. How is it possible? Did you worked with Dialog in JQuery before, or JSON?

Leave it ..Not an issue.

We have to initialize the table as JqGrid with the fields which we are planning to display in the Grid. Here I like to display,

1. Id
2. First Name
3. Last Name
4. Last 4 SSN
5. Department
6. Age
7. Salary
8. Marital Status
9. Address.

You can use the below script to initialize the table as grid.

<script type="text/javascript">
    jQuery("#jQGridDemo").jqGrid({
        url: '',
        datatype: "json",
        colNames: ['Id','First Name', 'Last Name', 'Last 4 SSN', 'Department', 
                'Age', 'Salary',  "Address",'Marital Status'],
        colModel: [
        { name: '_id', index: '_id', width: 20, stype: 'text' },
        { name: 'FirstName', index: 'FirstName', width: 150 },
        { name: 'LastName', index: 'LastName', width: 150 },
           { name: 'LastSSN', index: 'LastSSN', width: 100 },
        { name: 'Department', index: 'Department', width: 80, align: "right" },
        { name: 'Age', index: 'Salary', width: 80, align: "right" },
        { name: 'Salary', index: 'Salary', width: 80, align: "right" },
        { name: 'Address', index: 'Address', width: 150, sortable: false },
        { name: 'MaritalStatus', index: 'MaritalStatus', width: 100, sortable: false }
      ],
        rowNum: 10,
        sortname: 'id',
        viewrecords: true,
        sortorder: "desc",
        caption: "List Employee Details"
    });
</script> 

Run the application. You will get a stylish grid as below, with no data displayed.

I think after the Grid got displayed, you can read out what I have written while initializing the grid with script right?

The colNames are the header of the Grid.The colModel property is a JSON object data field that is used to configure each column of the grid.

I can say it’s the data model to be displayed in the grid like what we use in data set field name.colNames property should match up with the items in the colModel property.You might be noticed that the url property is left blank. Why?And also you might be thought that why data is not displayed in the grid right? For both the question I can give you one single answer. That’s for AJAX callback.

I’ll show you how the grid gets populated.

Here for ease of use am using MongoDb as my backend. The next question arouses here is how the URL serves the purpose of data?

What I have done is, I created Custom HTTP handler, and the function inside the http handler will fetch the employee records and writes in the HTTP Response.

The HTTP response would be a JSON string in the above mentioned format. Our JqGrid reads the JSON response and display in the JqGrid with the help of colMode mentioned before. One thing you have to make note is, we are working with JSON objects now. JqGrid works with XML data, JSON data and also with Array here we uses JSON.As I mentioned I have created an .ashx handler called JQGridHandler.ashx. And provided the url of the handler as http://localhost:58404/JQGridHandler.ashx. I am here going to show you how the data is being red from the handler which is passing from the grid, by simply calling the URL.As usual if we are passing any data to the handler and to read it from the Handler, in which parameter we look in to?Yes we will definitely first go and look in to the context.Request, right? Here also we are going to search whether any data is passed in the context request inside Form parameter.

Let’s go debug and see.

By looking at the de-bugger and the watch I have kept, you can see that I am looking for something called oper inside by calling forms.Get(“oper”); and I hope you also noticed that the Form{} (it’s an array) property in empty here.

Then how come I get the oper? Yes so the grid data operation depends on the oper param. Going forward I’ll show you what all data could come in oper. So here I am inferring that if oper==null it’s a grid first time load. Don’t get confused.

I’ll make you understand within few sec. Here I am using MONGO db as my data store.

You can see the data fetching code inside the if statement. I hope till here every thing is clear.

Any way I am not going in depth of the handlers, JSON ,Mongo and all, you can use any data base. Only the connection and data fetching mechanism will vary.

I used mongo because it’s very simple to use. OK now we are done with all required plumbing for JqGrid let’s see whether it works or not.

Will data get populate in the grid?

Run it...

Hey, it worked fine …

Come lets go and have a party. Can you believe that the table jQGridDemo is behaving like this? Yea that’s it, That’s JqGrid....So now you might be thinking that what’s special in this grid... As a simple grid view only... Did you notice the storable symbol came on the column header First Name? Did we write code for that? Yea...

That’s the beauty of the grid. Not only for FirstName but for every column on clicking in the column you will get the sorting button on top column header.

Unless otherwise you add an attribute like below.

{name: 'Address', index: 'Address', width: 150, sortable: false}

(Here in the example the address filed is not sortable) This is only a sample.

Going forward you can see exiting features like this. Tighten your seat belts...

Are you ready to add one more div on bottom of the JqGrid table (jQGridDemo)? I’ll show you the magic. Go and add it...

<table id="jQGridDemo">
</table>
<div id="jQGridDemoPager">
</div>

Yes, I have added one div on bottom of the div called jQGridDemoPager. Some changes in the script as below.

Please note the highlighted, which are newly added.

Let’s see how our Grid looks now.

The pagination bar with Refresh and Search option came up.

The beauty of the bottom bar is that, for searching and all you need not have to write even a single line of code. Not only for searching but also for sorting. JqGrid will do all those functionality by himself for us.

Let’s go and try search. Did you notice the bottom Search button? Next to Search, a Refresh button too. But its label is not displayed there right?

Just a button only. If we want to add a label to the refresh button. Just go to the grid.locale-en.js file and add the respective text. I’ll show you how I added the Label for that button.

There inside the file a section called nav you can see in the above image, In that you can see refreshtext. Initially that would be blank add the text which you require. Here I added Refresh as label text.

Did you notice the label of the refresh button now ? Yes it has been changed. There is another option also to change the button label.

I told you this method because I wanted you to explore what is there inside the file. Then what about the search pop up.? It’s quite beautiful, right? And also I know that you have also noticed the search criteria like equal, not equal, begins with Etc. Did you?

OK, just have a look at above image. You can clearly see the search criteria in the drop down. Many Conditions are there, any way I am not going to use all those conditions in our POC. I want to remove all those unwanted conditions.

How?

If you have gone through the file which I mentioned before (grid.locale-en.js). You might have seen all those conditions too inside the search portion.

Yes the search Section, He is the only culprit for that. Let’s try removing that and see what happens.

Yes I removed those unwanted search criteria. If you want you can compare with the above images, meanwhile also check whether the search and sort happens as you expected. For me it worked fine.

What about you guys? :) I hope till here almost clear right? My next intention is to make the grid responsive. How can we edit the grid? Do you think that this also can be done without much effort? Ok let’s see.

JqGrid Edit/Add/Delete

Here in JqGrid all the events and properties are configurable. It’s very simple to do but be careful. Even a small change may make your grid not work as you expected.

To provide your grid with above mentioned functionality, you have to add the below mentioned code in the exact same order as i mentioned below.

$('#jQGridDemo').jqGrid('navGrid', '#jQGridDemoPager',
{
   edit: true,
   add: true,
   del: true,
   search: true,
   searchtext: "Search",
   addtext: "Add",
   edittext: "Edit",
   deltext:"Delete"
},
{//EDIT EVENTS AND PROPERTIES GOES HERE },
{//ADD EVENTS AND PROPERTIES GOES HERE},
{//DELETE EVENTS AND PROPERTIES GOES HERE},
{//SEARCH EVENTS AND PROPERTIES GOES HERE}
);    

There is a mantra for the order in which I mention events and properties.

Inside the first curly brackets you have added some parameter as true. See the image below The portion I am actually explaining from the above code snippet.

That means in the grids navigation bar (bottom bar) all the mentioned buttons will display with respective text mentioned as below.

For better understanding I have marked with same colors. This portion you have to declare explicitly after the JqGrid initialization events and parameters.

Let’s go and have a look at the grid, what all changes had happened.

Please see the highlighted region. You can see all the buttons got displayed, which you set to true in above code segment. Let’s go and add a record.

Click on the Small + Add sign on bottom navigation bar. Small window will pop out like below screen shot.

Fill this out and Click the save button. What will happen? Nothing will happen :). Because you didn’t configure any thing for edit operation.

To perform Edit/Delete and Add operation you have to set one more URL property also in the grid i.e. the editurl. This will call the .ashx handler to perform the Add operation.

You have to set editurl: 'http://localhost:58404/JQGridHandler.ashx'. Beneath the caption.

You can see image 7 to locate Captions' position. Fill the form then click save. Let’s see what happens...

I kept a debugger in the handler to check whether the save reaches in the handler. I know that you would be wondering, how the data from UI will reach the handler to insert in to the db.

Yea... As expected the debugger got caught in the handler on clicking the Save button.

Can you see what’s there in the form (context.Request.Form)? Yes exactly what we wanted. The data to be inserted as key value pair. Did you noticed the oper=add now.

So I hope you got a bit idea about oper now than before, when I explained on loading data to grid. And also the Key of the Key value pair would be the key specified in the grid like FirstName, LastName, Age etc.

Hope you understood.

So as the value of oper the code inside the add if condition will execute to add the record to DB. How you read the value from Form? Its very simple

string strFirstName = forms.Get("FirstName").ToString();

Like above, you can read any value from the form property of the HTTP request ,replace FirstName with what you wanted. After add is successful the handler will write a success string to the HTTP response. That string will write in the Add/Edit popup over the grid as below .

Can you see the response message displayed on top in a red colored Div as "Employee record successfully added."

This response is sent from the handler, which is user defined.

You might be thinking which event in the client side will receive the response from the server side after Insert right?

******************************************************
Complete Script
******************************************************
$('#jQGridDemo').jqGrid('navGrid', '#jQGridDemoPager',
{
   edit: true,
   add: true,
   del: true,
   search: true,
   searchtext: "Search",
   addtext: "Add",
   edittext: "Edit",
   deltext:"Delete"
},
{   //EDIT portion
    //Can also set the width and height of the editing window as below commented way 
   //height: 300,
   //width: 400,
   //top: 50,
   //left: 100,
   //dataheight: 280,
   closeOnEscape: true,//Closes the popup on pressing escape key
   reloadAfterSubmit: true,
   drag: true,
   afterSubmit: function (response, postdata) {
       if (response.responseText == "") {
           $(this).jqGrid('setGridParam', 
             { datatype: 'json' }).trigger('reloadGrid');//Reloads the grid after edit
           return [true, '']
       }
       else {
           $(this).jqGrid('setGridParam', 
             { datatype: 'json' }).trigger('reloadGrid'); //Reloads the grid after edit
           return [false, response.responseText]
           //Captures and displays the response text on th Edit window
       }
   },
   editData: {
       EmpId: function () {
           var sel_id = $('#jQGridDemo').jqGrid('getGridParam', 'selrow');
           var value = $('#jQGridDemo').jqGrid('getCell', sel_id, '_id');
           return value;
       }
   }
},
{//ADD portion
   closeAfterAdd: true,//Closes the add window after add
   afterSubmit: function (response, postdata) {
       if (response.responseText == "") {
           $(this).jqGrid('setGridParam', 
             { datatype: 'json' }).trigger('reloadGrid')//Reloads the grid after Add
           return [true, '']
       }
       else {
           $(this).jqGrid('setGridParam', 
             { datatype: 'json' }).trigger('reloadGrid')//Reloads the grid after Add
           return [false, response.responseText]
       }
   }
},
{   //DELETE
   closeOnEscape: true,
   closeAfterDelete: true,
   reloadAfterSubmit: true,
   closeOnEscape: true,
   drag: true,
   afterSubmit: function (response, postdata) {
       if (response.responseText == "") {
           $("#jQGridDemo").trigger("reloadGrid", [{ current: true}]);
           return [false, response.responseText]
       }
       else {
           $(this).jqGrid('setGridParam', { datatype: 'json' }).trigger('reloadGrid')
           return [true, response.responseText]
       }
   },
   delData: {
       EmpId: function () {
           var sel_id = $('#jQGridDemo').jqGrid('getGridParam', 'selrow');
           var value = $('#jQGridDemo').jqGrid('getCell', sel_id, '_id');
           return value;
       }
   }
},
{//SEARCH
   closeOnEscape: true
}
);

The above code itself is self descriptive. I have included the code for edit and delete in the above snippet.

One Important thing I would like to say is about Editing. While performing Edit operation, we always update the data based on some primary key right? Here the primary Key is _id. (_id is employeeid)

While editing or updating, only the data from the Field marked as editable will be transferred to the Handler. No additional data will be transferred.

If you look at the edit window there the _id is not displayed or marked as editable (with out adding edit parameter by default all fields are editable) and hence it will not be transferred to the handler in the context.request.Form.

To pass that _id (i.e., the employeeid) to the server from our JqGrid, I have used editData property of the JqGrid. We can use JqGrid delData property to pass the _id for deleting a record.

Note: Not only for _id you can pass any additional data to handler with the help of editData,delData in JSON format.

You can see that in above JS code. Another important tip which I have to mention is fetching data from specific cell of the JqGrid.

var sel_id = $('#jQGridDemo').jqGrid('getGridParam', 'selrow');
//Gets the Id of selected row

var value = $('#jQGridDemo').jqGrid('getCell', sel_id, '_id');
//Gets the value of column of selectedrow.(_id is the na,e of the column). 
*******************************************************
Code in HTTP  handler
*******************************************************
public class JQGridHandler : IHttpHandler
{
    public void ProcessRequest(HttpContext context)
    {
        System.Collections.Specialized.NameValueCollection forms = context.Request.Form;
        string strOperation = forms.Get("oper");
        MONGOConnect objMC = new MONGOConnect();//Helper Class
        var collectionEmployee = 
          objMC.GetMongoCollection("EMPLOYEE");//Gets Employee Collection
        string strResponse = string.Empty;
        if (strOperation == null)
        {
            //oper = null which means its first load.
            var jsonSerializer = new JavaScriptSerializer();
            context.Response.Write(jsonSerializer.Serialize(
              collectionEmployee.AsQueryable<Employee>().ToList<Employee>()));
        }
        else if (strOperation == "del")
        {
            var query = Query.EQ("_id", forms.Get("EmpId").ToString());
            collectionEmployee.Remove(query);
            strResponse = "Employee record successfully removed";
            context.Response.Write(strResponse);
        }
        else
        {
             string strOut=string.Empty;
             AddEdit(forms, collectionEmployee, out strOut);
             context.Response.Write(strOut);
        }
                   
    }
    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
    private void AddEdit(NameValueCollection forms, 
      MongoCollection collectionEmployee,out string strResponse)
    {
        string strOperation = forms.Get("oper");
        string strEmpId = string.Empty;
        if (strOperation == "add")
        {
             strEmpId = forms.Get("EmpId").ToString();
        }
        else if (strOperation == "edit")
        {
            var result = collectionEmployee.AsQueryable<Employee>().Select(c => c._id).Max();
             strEmpId = (Convert.ToInt32(result) + 1).ToString();
        }
       
        string strFirstName = forms.Get("FirstName").ToString();
        string strLastName = forms.Get("LastName").ToString();
        string strLastSSN = forms.Get("LastSSN").ToString();
        string strDepartment = forms.Get("Department").ToString();
        string strAge = forms.Get("Age").ToString();
        string strSalary = forms.Get("Salary").ToString();
        string strAddress = forms.Get("Address").ToString();
        string strMaritalStatus = forms.Get("MaritalStatus").ToString();
        Employee objEmp = new Employee();
        objEmp._id = strEmpId;
        objEmp.FirstName = strFirstName;
        objEmp.LastName = strLastName;
        objEmp.LastSSN = strLastSSN;
        objEmp.Department = strDepartment;
        objEmp.Age = Convert.ToInt32(strAge);
        objEmp.Address = strAddress;
        objEmp.MaritalStatus = strMaritalStatus;
        objEmp.Salary = strSalary;
        collectionEmployee.Save(objEmp);
        strResponse = "Employee record successfully updated";
    }
}

As per the value in strOperation(forms.Get("oper")) null, add, edit, and del will carry out the operations from handler and send custom response back. That's all.

You can see how delete looks like.

Second Part :JqGrid Inline Editing
Third Part : JqGrid With MVC

 

Conclusion

JqGrid is very stylish and simple to integrate grid with lots of amazing functionality.

If somebody ask you to build an application with all these search, sort, filter options with in no time you can very much go for JqGrid.

And also a suggestion from my side is using Mongo DB for rapid application development is very use full. For this demo application I have used MONGO as back end DB so I could able to do this with in no time.

If you use our grid view and SQL server for development how many hours will it take to develop an application with all these features? It’s considerably low to develop using JqGrid and even more if you use MONGO. I am not promoting mongo, but this I wrote from my experience. There are many more functionalities to be explored in this JqGrid, that’s why in introduction I mentioned it’s a tip of an iceberg.