Using jqGrid with ASP.NET

In this article I will explain how to use jqGrid with ASP.NET. jqGrid is one of the most popular jquery grid and it is one of the best grid component. With the help of jqGrid wecan represent and manipulate tabular data for representing and manipulating tabular data . The grid is Ajax enabled JavaScript control and data is fetched to the grid via Ajax calls i.e without PostBack. You can integrate jqGrid with any server side technology like ASP, PHP, Java Servlets etc. It was developed by Tony Tomov. Below I will explain how to integrate jqGrid with ASP.NET.

DB Part:

Let's start with DB part first. We are going to create a Users table with following columns UserID, UserName, FirstName, LastName, MiddleName and EmailID. Then 500 random rows will be inserted so that we can have enough data for the grid. Below is the script for table:

Create Table:


CREATE TABLE [Users]
(
[UserID] INT IDENTITY ,
[UserName] NVARCHAR(50),
[FirstName] NVARCHAR(50),
[LastName] NVARCHAR(50),
[MiddleName] NVARCHAR(50),
[EmailID] NVARCHAR(50)
)
GO

INSERT INTO Users
( UserName ,
FirstName ,
LastName ,
MiddleName ,
EmailID
)
SELECT dbo.GenerateRandomName(10),
dbo.GenerateRandomName(10),
dbo.GenerateRandomName(10),
dbo.GenerateRandomName(10),
dbo.GenerateRandomName(10)+'@'+ dbo.GenerateRandomName(3)+'.com'
GO 500


To insert random rows above I have used GenerateRandomName function whose script is below:

Random Function

CREATE FUNCTION [GenerateRandomName] ( @LENGTH INT )
RETURNS NVARCHAR(255)
AS
BEGIN

--DECLARE VARIABLES
DECLARE @RandomNumber NVARCHAR(255)
DECLARE @I SMALLINT
DECLARE @RandNumber FLOAT
DECLARE @Position TINYINT
DECLARE @ExtractedCharacter VARCHAR(1)
DECLARE @ValidCharacters VARCHAR(255)
DECLARE @VCLength INT

--SET VARIABLES VALUE
SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
SET @VCLength = LEN(@ValidCharacters)
SET @ExtractedCharacter = ''
SET @RandNumber = 0
SET @Position = 0
SET @RandomNumber = ''


SET @I = 1

WHILE @I < ( @Length + 1 ) BEGIN SET @RandNumber = ( SELECT RandNumber FROM [RandNumberView] ) SET @Position = CONVERT(TINYINT, ( ( @VCLength - 1 ) * @RandNumber + 1 )) SELECT @ExtractedCharacter = SUBSTRING(@ValidCharacters, @Position, 1) SET @I = @I + 1 SET @RandomNumber = @RandomNumber + @ExtractedCharacter END RETURN @RandomNumber END GO CREATE VIEW [RandNumberView] AS SELECT RAND() AS [RandNumber]

In the above function I have used "RandNumberView" View. Because I need to use the random number if I had used the rand() simply in the function I would have received this error "Invalid use of side-effecting or time-dependent operator in 'rand' within a function".
Now let's move to stored procedure part. In order to get rows from the DB the server side code will call the DB through SelectjqGridUsers SP which we are going to see below. This SP will accept the following params PageIndex(The index of the page), SortColumnName(The column on which sorting is needed), SortOrderBY(The order i.e asc or desc), NumberOfRows(The number of rows to be needed as the output), TotalRecords(It is the Output param it will give the total records present for that search). Below is the script for it:

Select Stored Procedure

CREATE PROC [SelectjqGridUsers]
@PageIndex INT ,
@SortColumnName VARCHAR(50) ,
@SortOrderBy VARCHAR(4) ,
@NumberOfRows INT ,
@TotalRecords INT OUTPUT
AS
BEGIN

SET NOCOUNT ON

SELECT @TotalRecords = ( SELECT COUNT(1)
FROM [Users]
)

DECLARE @StartRow INT
SET @StartRow = ( @PageIndex * @NumberOfRows ) + 1 ;


WITH CTE
AS ( SELECT ROW_NUMBER() OVER ( ORDER BY CASE
WHEN @SortColumnName = 'UserID'
AND @SortOrderBy = 'asc'
THEN UserID
END ASC, CASE
WHEN @SortColumnName = 'UserID'
AND @SortOrderBy = 'desc'
THEN UserID
END DESC, CASE
WHEN @SortColumnName = 'UserName'
AND @SortOrderBy = 'asc'
THEN UserName
END ASC, CASE
WHEN @SortColumnName = 'UserName'
AND @SortOrderBy = 'desc'
THEN UserName
END DESC, CASE
WHEN @SortColumnName = 'FirstName'
AND @SortOrderBy = 'asc'
THEN FirstName
END ASC, CASE
WHEN @SortColumnName = 'FirstName'
AND @SortOrderBy = 'desc'
THEN FirstName
END DESC , CASE
WHEN @SortColumnName = 'MiddleName'
AND @SortOrderBy = 'asc'
THEN MiddleName
END ASC, CASE
WHEN @SortColumnName = 'MiddleName'
AND @SortOrderBy = 'desc'
THEN MiddleName
END DESC , CASE
WHEN @SortColumnName = 'LastName'
AND @SortOrderBy = 'asc'
THEN LastName
END ASC, CASE
WHEN @SortColumnName = 'LastName'
AND @SortOrderBy = 'desc'
THEN LastName
END DESC, CASE
WHEN @SortColumnName = 'EmailID'
AND @SortOrderBy = 'asc'
THEN EmailID
END ASC, CASE
WHEN @SortColumnName = 'EmailID'
AND @SortOrderBy = 'desc'
THEN EmailID
END DESC ) AS RN ,
UserID ,
UserName ,
FirstName ,
MiddleName ,
LastName ,
EmailID
FROM [Users]
)
SELECT UserID ,
UserName ,
FirstName ,
LastName ,
MiddleName ,
EmailID
FROM CTE
WHERE RN BETWEEN @StartRow - @NumberOfRows
AND @StartRow - 1

SET NOCOUNT OFF


END


Front end part:

Let's come to front end part. Before moving further please go through these two links first :

http://www.trirand.com/blog/?page_id=6 (From where you can download the neccessary files for jqGrid)
http://www.trirand.com/jqgridwiki/doku.php?id=wiki:how_to_install#development_installation (From where you can read how to install it).
Client side code (ASPX)
Let's start with ASPX code, the client side code. Here we will be having jqGrid and it's corresonding configuration settings. User will do the operations on jqGrid and behind the scenes server side code will be hit via Ajax request. In the server side HTTP handler will be called in order to serve the request. In response HTTP handler will give the necessary response and accordingly, the data on the grid will be displayed. Below is the code for it:

Sample.aspx:

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.8/themes/ui-darkness/jquery-ui.css"
type="text/css" media="all" />
<link href="css/ui.jqgrid.css" rel="stylesheet" type="text/css" />

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js" type="text/javascript"></script>

<script src="js/i18n/grid.locale-en.js" type="text/javascript"></script>

<script src="js/jquery.jqGrid.min.js" type="text/javascript"></script>

<script type="text/javascript">
$(function() {
$("#UsersGrid").jqGrid({
url: 'jqGridHandler.ashx',
datatype: 'json',
height: 250,
colNames: ['UserID', 'UserName', 'FirstName', 'MiddleName', 'LastName', 'EmailID'],
colModel: [
{ name: 'UserID', index: 'UserID', width: 100, sortable: true },
{ name: 'UserName', width: 100, sortable: true },
{ name: 'FirstName', width: 100, sortable: true },
{ name: 'MiddleName', width: 100, sortable: true },
{ name: 'LastName', width: 100, sortable: true },
{ name: 'EmailID', width: 150, sortable: true }
],
rowNum: 10,
rowList: [10, 20, 30],
pager: '#UsersGridPager',
sortname: 'UserID',
viewrecords: true,
sortorder: 'asc',
caption: 'JSON Example'
});

$("#UsersGrid").jqGrid('navGrid', '#UsersGridPager', { edit: false, add: false, del: false });
});

</script>

</head>
<body>
<form id="HtmlForm" runat="server">
<table id="UsersGrid" cellpadding="0" cellspacing="0">
<div id="UsersGridPager">
</div>
</table>
</form>
</body>
</html>

HTTP Handler:

Let's come to the HTTP handler code which will be called by client side (ASPX). Request will be made from client side to the server and this HTTP handler will be called. Based on the params passed from client the handler will hit the database and fetch the rows from DB. Below is the code for it:

jqGridHandler.ashx:


<%@ WebHandler Language="C#" Class="jqGridHandler" %>

using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Script.Serialization;


public class jqGridHandler : IHttpHandler
{

public void ProcessRequest(HttpContext context)
{
HttpRequest request = context.Request;
HttpResponse response = context.Response;

string _search = request["_search"];
string numberOfRows = request["rows"];
string pageIndex= request["page"];
string sortColumnName= request["sidx"];
string sortOrderBy = request["sord"];


int totalRecords;
Collection users = GetUsers(numberOfRows, pageIndex, sortColumnName, sortOrderBy, out totalRecords);
string output = BuildJQGridResults(users, Convert.ToInt32(numberOfRows), Convert.ToInt32(pageIndex), Convert.ToInt32(totalRecords));
response.Write(output);
}

private string BuildJQGridResults(Collection users,int numberOfRows, int pageIndex,int totalRecords)
{

JQGridResults result = new JQGridResults();
List rows = new List();
foreach (User user in users)
{
JQGridRow row = new JQGridRow();
row.id = user.UserID;
row.cell = new string[6];
row.cell[0] = user.UserID.ToString();
row.cell[1] = user.UserName;
row.cell[2] = user.FirstName;
row.cell[3] = user.MiddleName;
row.cell[4] = user.LastName;
row.cell[5] = user.EmailID;
rows.Add(row);
}
result.rows = rows.ToArray();
result.page = pageIndex;
result.total = totalRecords / numberOfRows;
result.records = totalRecords;
return new JavaScriptSerializer().Serialize(result);
}

private Collection GetUsers(string numberOfRows,string pageIndex,string sortColumnName, string sortOrderBy,out int totalRecords)
{
Collection users = new Collection();
string connectionString =
"Data Source=YourServerName; Initial Catalog=YourDatabase; User ID=YourUserName; Password=YourPassword";
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = "SelectjqGridUsers";
command.CommandType = CommandType.StoredProcedure;

SqlParameter paramPageIndex = new SqlParameter("@PageIndex", SqlDbType.Int);
paramPageIndex.Value =Convert.ToInt32(pageIndex);
command.Parameters.Add(paramPageIndex);

SqlParameter paramColumnName = new SqlParameter("@SortColumnName", SqlDbType.VarChar, 50);
paramColumnName.Value = sortColumnName;
command.Parameters.Add(paramColumnName);

SqlParameter paramSortorderBy = new SqlParameter("@SortOrderBy", SqlDbType.VarChar, 4);
paramSortorderBy.Value = sortOrderBy;
command.Parameters.Add(paramSortorderBy);

SqlParameter paramNumberOfRows = new SqlParameter("@NumberOfRows", SqlDbType.Int);
paramNumberOfRows.Value =Convert.ToInt32(numberOfRows);
command.Parameters.Add(paramNumberOfRows);

SqlParameter paramTotalRecords= new SqlParameter("@TotalRecords", SqlDbType.Int);
totalRecords = 0;
paramTotalRecords.Value = totalRecords;
paramTotalRecords.Direction = ParameterDirection.Output;
command.Parameters.Add(paramTotalRecords);


connection.Open();
using (SqlDataReader dataReader = command.ExecuteReader())
{
User user;
while (dataReader.Read())
{
user = new User();
user.UserID = (int) dataReader["UserID"];
user.UserName = Convert.ToString(dataReader["UserName"]);
user.FirstName = Convert.ToString(dataReader["FirstName"]);
user.MiddleName = Convert.ToString(dataReader["MiddleName"]);
user.LastName = Convert.ToString(dataReader["LastName"]);
user.EmailID = Convert.ToString(dataReader["EmailID"]);
users.Add(user);
}
}
totalRecords = (int)paramTotalRecords.Value;
}

return users;
}

}
public bool IsReusable
{
// To enable pooling, return true here.
// This keeps the handler in memory.
get { return false; }
}
}

Above I have used 2 structs JQGridResults and JQGridRow and one business class User. Below is the code for them:


public struct JQGridResults
{
public int page;
public int total;
public int records;
public JQGridRow[] rows;

}
public struct JQGridRow
{
public int id;
public string[] cell;
}

[Serializable]
public class User
{
public int UserID
{ get; set; }

public string UserName
{ get; set; }

public string FirstName
{ get; set; }

public string MiddleName
{ get; set; }

public string LastName
{ get; set; }

public string EmailID
{ get; set; }
}


Below is the screenshot of the Firebug's Conole's tab. As you can see we made a request to the server by making a request to jqGridHandler.ashx . The querysting params value is sent accoriding to the settings we kept while declaring jqGrid settings at ASPX page :

Reference To: http://codeasp.net/articles/asp-net/229/using-jqgrid-with-asp-net