jqGrid with ASP.NET with Sql Sever

As we know that, when we need to represent the data in tabular form, we have better option to use a grid, The Grid is the name of a control which not only represents data in a tabular form but also provides features like paging, sorting and some cool features to show the data on web page in an interactive and easy to read manner. We have various ways to show the data in tabular form and one of the simplest way is to use table tab of HTML language.
In this post I am going to discuss about jqGrid, which is one of finest plug-in of jQuery (assuming that you have basic idea about JQuery) The main beauty of the grid is that it works on client side and communicating with Server side by the help of AJAX hits. It’s quite fast responsive and easy to use; we can incorporate it with any server side languages like ASP.NET, PHP etc.  At the starting level, I am using it with basic features. I will be using ASP.NETMVC with simpleADO.NET for database interaction (we may also use any ORM like Entity Framework, nHibernate etc.), and the jqGrid binds with the data. So let’s move on to work on it.
At the very first step, I am going to create a database named “studentDB” which has a single table Student(we are using here MS SQL EXPRESS for this demo but Oracle can also be used). Student Table Contain following fields.

Student table contains four fields and StudentID is a primary Key. Sample recordsare showing below

For MS SQL
Now let’s move to write some TSQL .I’ll start with some very basic stored procedures:

01	CREATE PROCEDURE SELECT_STUDENT

02	@SortColumnName VARCHAR(100),

03	@SortOrderBy VARCHAR(4),

04	@NumberOfRows INT,

05	@StartRow INT

06

AS

07

BEGIN

08	SET NOCOUNT ON;

09

SELECT *

10	FROM   (SELECT Row_Number() Over(ORDER BY CASE

11	WHEN @SortColumnName = 'StudentID'

12	AND @SortOrderBy = 'asc'

13	THEN StudentID

14	END ASC, CASE

15	WHEN @SortColumnName = 'StudentID'

16	AND @SortOrderBy = 'desc'

17	THEN StudentID

18	END DESC, CASE

19	WHEN @SortColumnName = 'FirstName'

20	AND @SortOrderBy = 'asc'

21	THEN FirstName

22	END ASC, CASE

23	WHEN @SortColumnName = 'FirstName'

24	AND @SortOrderBy = 'desc'

25	THEN FirstName

26	END DESC, CASE

27	WHEN @SortColumnName = 'LastName'

28	AND @SortOrderBy = 'asc'

29	THEN LastName

30	END ASC, CASE

31	WHEN @SortColumnName = 'LastName'

32	AND @SortOrderBy = 'desc'

33	THEN LastName

34	END DESC , CASE

35	WHEN @SortColumnName = 'Email'

36	AND @SortOrderBy = 'asc'

37	THEN Email

38	END ASC, CASE

39	WHEN @SortColumnName = 'Email'

40	AND @SortOrderBy = 'desc'

41	THEN Email

42	END DESC) as Sno,

43	COUNT(*) Over() TOTALROWS,

44	StudentID,

45	FirstName,

46

LastName,

47

Email

48	FROM   STUDENT) as RECORDS

49	WHERE  RECORDS.Sno BETWEEN (@StartRow - @NumberOfRows) AND (@StartRow -1);

50

END

For Oracle
Oracle Package and the PLSQL are quite similar as above:

01	CREATE OR REPLACE PACKAGE BODY Student_Pkg IS

02	PROCEDURE SELECT_STUDENT(p_SortColumnName VARCHAR2,

03	p_SortOrderBy VARCHAR2,

04	p_NumberOfRows INT,

05	p_StartRow INT,

06	p_Studnet_Ref OUT Studnet_Ref) IS

07

BEGIN

08	OPEN p_Studnet_Ref FOR

09

SELECT *

10	FROM   (SELECT Row_Number() Over(ORDER BY CASE

11	WHEN p_SortColumnName ='StudentID'

12	AND p_SortOrderBy ='asc'

13	THEN STUDENTID

14	END ASC,CASE

15	WHEN p_SortColumnName ='StudentID'

16	AND p_SortOrderBy ='desc'

17	THEN STUDENTID

18	END DESC,CASE

19	WHEN p_SortColumnName ='FirstName'

20	AND p_SortOrderBy ='asc'

21	THEN FIRSTNAME

22	END ASC,CASE

23	WHEN p_SortColumnName ='FirstName'

24	AND p_SortOrderBy ='desc'

25	THEN FIRSTNAME

26	END DESC,CASE

27	WHEN p_SortColumnName ='LastName'

28	AND p_SortOrderBy ='asc'

29	THEN LASTNAME

30	END ASC,CASE

31	WHEN p_SortColumnName ='LastName'

32	AND p_SortOrderBy ='desc'

33	THEN LASTNAME

34	END DESC,CASE

35	WHEN p_SortColumnName ='Email'

36	AND p_SortOrderBy ='asc'

37	THEN EMAIL

38	END ASC,CASE

39	WHEN p_SortColumnName ='Email'

40	AND p_SortOrderBy ='desc'

41	THEN EMAIL

42	END DESC)as Sno,

43	COUNT(*) Over() as TOTALROWS,

44	STUDENTID,

45	FIRSTNAME,

46

LASTNAME,

47

EMAIL

48	FROM   STUDENT) RECORDS

49	WHERE  RECORDS.Sno BETWEEN(p_StartRow - p_NumberOfRows)AND(p_StartRow -1);

50

END;

51

END;

As we can see above the Store Procedure contains the sub query, outer query is responsible to manage the grid paging and inner query responsible to manage the sorting and total records of the table. The Store Procedure contains four parameters:
@SortColumnName/p_SortColumnName:- The data will be sorted by Column Name
@SortOrderBy/p_SortOrderBy:- It can be ‘desc’ for decneding and ‘asc’ for ascending order
@NumberOfRows/p_NumberOfRows:- Total Number of rows that will be showing at the jqGrid
@StartRow/p_StartRow:- Row number where the data will be started for new page of grid
The DB side work is done; now let’s move to work on coding side, I am going to create new project for ASP.NET MVC2 named “jqGridDemo”

For the Demo purpose I am not going with MVC Test project, so just use simple application project

Now to write the domain, it won’t be complex StudentModel

In StudentModel we have two Classes; one is Student and second is StudentDAL, the complete code of the StudentModel.cs file is being mentioned below:

001	namespace JQGridDemo.Models

002

{

003

///

004	<summary> /// Studnet Entity

005	/// </summary>

006	public class Student

007

{

008	public int StudentId { get; set; }

009	public string FirstName { get; set; }

010	public string LastName { get; set; }

011	public string Email { get; set; }

012	public int TotalRows { get; set; }

013	public int Sno { get; set; }

014

}

015

016

///

017	<summary> /// Student Data Access Layer

018	/// </summary>

019	public class StudentDAL

020

{

021

///

022	<summary> /// Fetch student collection as per grid criteria

023	/// </summary>

024	///sorted colum name

025	///sorting order

026	///page index of grid

027	///total number of rows of grid

028	/// Collection of Students

029	public static List SelectStudnets(string sidx, string sord, int page, int rows)

030

{

031	const string spName = "SELECT_STUDENT";

032	List studentCollection;

033

034	string connectionString = ConfigurationManager.ConnectionStrings["StudentDatabase"].ConnectionString;

035	if (string.IsNullOrEmpty(connectionString))

036	return null;

037	using (SqlConnection sqlConnection = new SqlConnection(connectionString))

038

{

039	SqlCommand sqlCommand = new SqlCommand(spName, sqlConnection);

040	sqlCommand.CommandType = CommandType.StoredProcedure;

041	SqlParameter[] sqlParameterCollection = SetParameter(sidx, sord, page, rows);

042	sqlCommand.Parameters.AddRange(sqlParameterCollection);

043	sqlConnection.Open();

044	studentCollection = FillStudentEntity(sqlCommand);

045

}

046	return studentCollection;

047

}

048

049

///

050	<summary> /// Fill SQL paramter for Stored Procedure

051	/// </summary>

052	///sorted colum name

053	///sorting order

054	///page index of grid

055	///total number of rows of grid

056	/// Collection of SQL paramters object

057	private static SqlParameter[] SetParameter(string sidx, string sord, int page, int rows)

058

{

059	SqlParameter sortColNameParam = new SqlParameter("@SortColumnName", SqlDbType.VarChar, 100);

060	sortColNameParam.Value = sidx;

061

062	SqlParameter sortOrderParam = new SqlParameter("@SortOrderBy", SqlDbType.VarChar, 4);

063	sortOrderParam.Value = sord;

064

065	SqlParameter numberOfRowsParam = new SqlParameter("@NumberOfRows", SqlDbType.Int);

066	numberOfRowsParam.Value = rows;

067

068	SqlParameter startRowParam = new SqlParameter("@StartRow", SqlDbType.Int);

069	startRowParam.Value = page;

070

071	return new SqlParameter[]

072

{

073	sortColNameParam,sortOrderParam,numberOfRowsParam,startRowParam

074

};

075

}

076

077

///

078	<summary> /// Fill the Studnet Entity by using sql reader of command

079	/// </summary>

080	///sql command object for excute reader

081	/// colletion of students

082	private static List FillStudentEntity(SqlCommand sqlCommand)

083

{

084	List students = new List();

085	using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())

086

{

087	while (sqlDataReader.Read())

088

{

089	students.Add(new Student

090

{

091	StudentId = Convert.ToInt32(sqlDataReader["StudentID"]),

092	FirstName = sqlDataReader["FirstName"].ToString(),

093	LastName = sqlDataReader["LastName"].ToString(),

094	Email = sqlDataReader["Email"].ToString(),

095	Sno = Convert.ToInt32(sqlDataReader["Sno"]),

096	TotalRows = Convert.ToInt32(sqlDataReader["TotalRows"])

097

});

098

}

099

}

100	return students;

101

}

102

}

As I have mentioned above that we are using MS SQL Express, so the code is according to MS SQL, for Oracle user can replace SQL objects with oracle objects and rest of the logic is similar as above. There are three methodsin Student DAL Class, which are responsible to fetch student records from the Database, comments have been mentioned at methods and classes, so we can easily identify that what the methods or classes are performing. The connection string of the database is available in Web.Config file with the key name ‘StudentDatabase’. Now the Model is completed, it’s time to move towards Controller.
For the Controller I am using existence available controller, HomeController , I am going to add GetStudents method in HomeController

01	public JsonResult GetStudents(string sidx, string sord, int page, int rows)

02

{

03	int pageIndex = page;

04	int pageSize = rows;

05	int startRow = (pageIndex * pageSize) + 1;

06	List students = StudentDAL.SelectStudnets(sidx, sord, startRow, rows);

07	int totalRecords = students.Select(x => x.TotalRows).FirstOrDefault();

08	int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);

09	var jsonData = new

10

{

11	total = totalPages,

12

page,

13	records = totalRecords,

14

rows = (

15	from student in students

16	select new

17

{

18	i = student.StudentId,

19	cell = new string[] { student.StudentId.ToString(), student.FirstName, student.LastName, student.Email }

20	}).ToArray()

21

};

22	return Json(jsonData);

23

}

As we can see above the GetStudents method returns the JsonResult object and performing all the necessary logic which is required for make the functionality of the paging and sorting. The important object of the method is JsonData , which is responsible to provide the desired data of the grid and it should be required all fields for grid input, otherwise the gird will not be showing data on web page.
Here we need to work on View, where the GetStudent method would be calling by AJAX; we have to add some files in our project. You may take the jqGrid scripts from following URL
http://www.trirand.com/blog/?page_id=6
Here you may take idea about that how the jqGrid can be installed.
http://www.trirand.com/jqgridwiki/doku.php?id=wiki:how_to_install#development_installation
I have to included jqGrid scripts and its CSS/images files in project solution

Now here I need to replace Index.aspx markup with existence markup

01	<%@ Page Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage" %>

02	<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">

03

Home Page

04	</asp:Content>

05	<asp:Content ID="Content3" ContentPlaceHolderID="HeadContent" runat="server">

06	<%--CSS Files--%>

07	<link href="/Content/jquery-ui-1.8.7.css" rel="stylesheet" type="text/css" />

08	<link href="/Content/ui.jqgrid.css" rel="stylesheet" type="text/css" />

09	<link href="/Content/ui.multiselect.css" rel="stylesheet" type="text/css" />

10	<%--jQuery Library--%>

11	<script src="/Scripts/jquery-1.4.1.min.js" type="text/javascript"></script>

12	<%--Must load language tag BEFORE script tag--%>

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

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

15	<%--jqGrid Code - refer http://www.trirand.com/blog/jqgrid/jqgrid.html --%>

16	<script type="text/javascript">

17	jQuery(document).ready(function () {

18	jQuery("#list").jqGrid({

19	url: '/Home/GetStudents/',

20	datatype: 'json',

21	mtype: 'POST',

22	colNames: ['StudentID', 'FirstName', 'LastName', 'Email'],

23	colModel: [

24	{ name: 'StudentID', index: 'StudentID', width: 150, align: 'left' },

25	{ name: 'FirstName', index: 'FirstName', width: 150, align: 'left', sortable: true },

26	{ name: 'LastName', index: 'LastName', width: 150, align: 'left', sortable: true },

27	{ name: 'Email', index: 'Email', width: 200, align: 'left', sortable: true}],

28	pager: jQuery('#pager'),

29	rowNum: 10,

30	rowList: [5, 10, 20, 50],

31	sortname: 'StudentID',

32	sortorder: "asc",

33	viewrecords: true,

34	caption: 'JQgrid'

35

});

36

});

37

</script>

38	</asp:Content>

39	<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">

40	<%-- HTML Required--%>

41	<h2>Grid Data</h2>

42	<table id="list" class="scroll" cellpadding="0" cellspacing="0"></table>

43	<div id="pager" class="scroll" style="text-align:center;"></div>

44	</asp:Content>

First I have added reference of the CSS and all the JavaScript files like jQuery and jqGrid at the top of the page. For implementing jqGrid we table’s markup on the aspx page, as you can see above I have used table with ID name list, and for the paging of the grid, we have a div markup with the ID name pager. After that I would like to give a basic idea about some properties of the jqGrid, after DOM initialization of jQuery, we have to create a jQuery object of the table and add initialize the jqGrid object, there are some properties of the jqGrid which are being mentioned below,

• url :- url which should be called by AJAX
• datatype :- here we are using json type it can be xml.
• mtype :- method type it can be post or get
• colNames :- name of the columns which would be displaced at jqgrid header
• colModel :- define the model of the columns
• pager :- object of jquery object of the html element where the page will be render
• rowNum:- default number of rows will be displayed at grid
• rowList:- number of rows list will be showing on row list of the grid
• sortname:- by default sorting of the column name
• sortorder :- sorting order , it can be des or asc
• viewrecords :- total number of rows at bottom of the grid will be display or not
• caption:-caption of the grid

You can take further knowledge of above or more properties of the jqGrid by following URL
http://www.trirand.com/jqgridwiki/doku.php?id=wiki:options
Finally the grid is ready to show the data on page

The grid is AJAX based, so all the communication with server is done by using JSON objects to transfer or receive  the data, for clarification we may take idea by the help of  IE9 Networking tab.

I explained basic features of the jqGrid , in my future article I will be exploring  more features of the grid. Complete solution of the Project has been attached in the article. I hope you enjoyed it and give me your valuable feedback to encourage me to write more.
Happy Coding  :)