Loading ...

Using jqGrid with ASP.NET | CodeAsp.Net

Using jqGrid with ASP.NET

(81171)
0
/5
Avg: 0/5: (0 votes)
Published: 6/13/2013 by Raghav Khunger

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 SP:

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 :

  1. http://www.trirand.com/blog/?page_id=6 (From where you can download the neccessary files for jqGrid)
  2. 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<User> 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<User> users,int numberOfRows, int pageIndex,int totalRecords)
    {

        JQGridResults result = new JQGridResults();
        List<JQGridRow> rows = new List<JQGridRow>();
        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<User>  GetUsers(string numberOfRows,string pageIndex,string sortColumnName, string sortOrderBy,out int totalRecords)
    {
        Collection<User> users = new Collection<User>();
                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 :

 $('#UsersGrid').jqGrid({
               ......
            });


 

That's it we are done with implementing jqGrid with ASP.NET. Above we discussed how to sent rquest to server , how to handle the request at server side code through ASHX handler. And finally how to get the records through DB based on the parameters sent from the client side.

Do let me know your feedback, comments.

 

Comments (12)

raghav_khunger

Hi,

Yes I will. In the meanwhile what is that which you are not able to understand ?

2/5/2011
 · 
 
by
raghav_khunger

Please include the "User" class which I have mentioned in my code.

3/27/2011
 · 
 
by
vijaypratap
vijay pratap said:
Great work Raghav :) Correct if I am Wrong. There is a issue with this JQgrid in IE 7 the pager is not shown. Can be fixed By adding this
outside the table. Once again great example and I learned a lot from this article. Thanx
11/10/2011
 · 
 
by
vijaypratap
vijay pratap said:
IE 8 also
11/10/2011
 · 
 
by
raghav_khunger
Test
2/21/2012
 · 
 
by
ocando
Edward Ocando said:
jqGrid is not free, but jTable yes. (www.jtable.org)
11/29/2012
 · 
 
by
latif127
Abdul Latif said:
Nice and easy example!!! Thanks
1/17/2013
 · 
 
by
raghav_khunger
Thanks
1/29/2013
 · 
 
by
siva
siva said:
hi
can you give me an example of how to save jqgrid bulk data to database ..
2/11/2013
 · 
 
by
ganesh
ganesh said:
Hi Raghav, actually I dont know much knowledge on jquery but some what i can. Now i have requirement to use the jqgrid in that meanwhile I found ur blog. so simply I added all js files and css as well as I wrote all the code( i.e .aspx, .ashx) according my app then I debugged in that i didnt get any errors simply it shows empty gridview so please help me...... and one thing is it better compare to asp.net gridview control.
4/17/2013
 · 
 
by
alex
alex said:
greate example. it is telling me i'm not passing @SortColumnName parameter
6/19/2013
 · 
 
by
Rama
Rama said:
Thanks, Nice article, if possible could please post the code how to display same JQgrid with Columns from Data table columns as the list of columns has been changing from database
11/16/2013
 · 
 
by
Merx
Merx said:
There is a jqGrid JSON Serializer that greatly simplifies the task of sending JSON response to jqGrid. It goes under the MIT license and can be found here http://codemerx.com/jqgrid/products/json-serializer-for-jqgrid

Well of course there is a full fledged jqGrid ASP.NET server control here http://codemerx.com/jqgrid/products/codemerx-jqgrid-for-asp-net

That one is free but it comes branded.
1/24/2014
 · 
 
by
Kishan
Kishan said:
Thank you so much . It Really Works as per my requirement.
Now i want to insert,update and delete functionality to this JQGrid, Can you please help me??
23 days ago
 · 
 
by
  • Name:*
  • Email:*
  • Website:
Type the characters you see in the image *

Top articles

Related articles

  • Calling web service using jQuery in ASP.NET

    In this article I will show how to call ASP.NET webservice using jQuery. Webservice call using jQuery will let you to prevent the re-rendering the entire content of the page. The request is made partially and you can replace the content of the page without postback. Thus increasing usability and leads to less data transfer for the same cause
    6/13/2013 by Raghav Khunger

Quick Vote

What kind of email newsletter would you prefer to receive from CodeAsp.Net?