Saturday 9 June 2012

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:
01CREATE PROCEDURE SELECT_STUDENT
02    @SortColumnName VARCHAR(100),
03    @SortOrderBy VARCHAR(4),
04    @NumberOfRows INT,
05    @StartRow INT
06AS
07BEGIN
08    SET NOCOUNT ON;
09SELECT *
10FROM   (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);
50END
For Oracle
Oracle Package and the PLSQL are quite similar as above:
01CREATE 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;
51END;
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:
001namespace 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
01public 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">
03Home 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">
17jQuery(document).ready(function () {
18jQuery("#list").jqGrid({
19url: '/Home/GetStudents/',
20datatype: 'json',
21mtype: 'POST',
22colNames: ['StudentID', 'FirstName', 'LastName', 'Email'],
23colModel: [
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}],
28pager: jQuery('#pager'),
29rowNum: 10,
30rowList: [5, 10, 20, 50],
31sortname: 'StudentID',
32sortorder: "asc",
33viewrecords: true,
34caption: '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  :)

sample code