jqGrid with ASP.NET MVC and MS SQL/Oracle Store Procedure
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)which is made by Tony Tomov. 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:
CREATE PROCEDURE SELECT_STUDENT
@SortColumnName VARCHAR(100),
@SortOrderBy VARCHAR(4),
@NumberOfRows INT,
@StartRow INT
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM (SELECT Row_Number() Over(ORDER BY CASE
WHEN @SortColumnName = 'StudentID'
AND @SortOrderBy = 'asc'
THEN StudentID
END ASC, CASE
WHEN @SortColumnName = 'StudentID'
AND @SortOrderBy = 'desc'
THEN StudentID
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 = 'LastName'
AND @SortOrderBy = 'asc'
THEN LastName
END ASC, CASE
WHEN @SortColumnName = 'LastName'
AND @SortOrderBy = 'desc'
THEN LastName
END DESC , CASE
WHEN @SortColumnName = 'Email'
AND @SortOrderBy = 'asc'
THEN Email
END ASC, CASE
WHEN @SortColumnName = 'Email'
AND @SortOrderBy = 'desc'
THEN Email
END DESC) as Sno,
COUNT(*) Over() TOTALROWS,
StudentID,
FirstName,
LastName,
Email
FROM STUDENT) as RECORDS
WHERE RECORDS.Sno BETWEEN (@StartRow - @NumberOfRows) AND (@StartRow -1);
END
For Oracle
Oracle Package and the PLSQL are quite similar as above:
CREATE OR REPLACE PACKAGE BODY Student_Pkg IS
PROCEDURE SELECT_STUDENT(p_SortColumnName VARCHAR2,
p_SortOrderBy VARCHAR2,
p_NumberOfRows INT,
p_StartRow INT,
p_Studnet_Ref OUT Studnet_Ref) IS
BEGIN
OPEN p_Studnet_Ref FOR
SELECT *
FROM (SELECT Row_Number() Over(ORDER BY CASE
WHEN p_SortColumnName ='StudentID'
AND p_SortOrderBy ='asc'
THEN STUDENTID
END ASC,CASE
WHEN p_SortColumnName ='StudentID'
AND p_SortOrderBy ='desc'
THEN STUDENTID
END DESC,CASE
WHEN p_SortColumnName ='FirstName'
AND p_SortOrderBy ='asc'
THEN FIRSTNAME
END ASC,CASE
WHEN p_SortColumnName ='FirstName'
AND p_SortOrderBy ='desc'
THEN FIRSTNAME
END DESC,CASE
WHEN p_SortColumnName ='LastName'
AND p_SortOrderBy ='asc'
THEN LASTNAME
END ASC,CASE
WHEN p_SortColumnName ='LastName'
AND p_SortOrderBy ='desc'
THEN LASTNAME
END DESC,CASE
WHEN p_SortColumnName ='Email'
AND p_SortOrderBy ='asc'
THEN EMAIL
END ASC,CASE
WHEN p_SortColumnName ='Email'
AND p_SortOrderBy ='desc'
THEN EMAIL
END DESC)as Sno,
COUNT(*) Over() as TOTALROWS,
STUDENTID,
FIRSTNAME,
LASTNAME,
EMAIL
FROM STUDENT) RECORDS
WHERE RECORDS.Sno BETWEEN(p_StartRow - p_NumberOfRows)AND(p_StartRow -1);
END;
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:
namespace JQGridDemo.Models
{
///
<summary> /// Studnet Entity
/// </summary>
public class Student
{
public int StudentId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
public int TotalRows { get; set; }
public int Sno { get; set; }
}
///
<summary> /// Student Data Access Layer
/// </summary>
public class StudentDAL
{
///
<summary> /// Fetch student collection as per grid criteria
/// </summary>
///sorted colum name
///sorting order
///page index of grid
///total number of rows of grid
/// Collection of Students
public static List SelectStudnets(string sidx, string sord, int page, int rows)
{
const string spName = "SELECT_STUDENT";
List studentCollection;
string connectionString = ConfigurationManager.ConnectionStrings["StudentDatabase"].ConnectionString;
if (string.IsNullOrEmpty(connectionString))
return null;
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
SqlCommand sqlCommand = new SqlCommand(spName, sqlConnection);
sqlCommand.CommandType = CommandType.StoredProcedure;
SqlParameter[] sqlParameterCollection = SetParameter(sidx, sord, page, rows);
sqlCommand.Parameters.AddRange(sqlParameterCollection);
sqlConnection.Open();
studentCollection = FillStudentEntity(sqlCommand);
}
return studentCollection;
}
///
<summary> /// Fill SQL paramter for Stored Procedure
/// </summary>
///sorted colum name
///sorting order
///page index of grid
///total number of rows of grid
/// Collection of SQL paramters object
private static SqlParameter[] SetParameter(string sidx, string sord, int page, int rows)
{
SqlParameter sortColNameParam = new SqlParameter("@SortColumnName", SqlDbType.VarChar, 100);
sortColNameParam.Value = sidx;
SqlParameter sortOrderParam = new SqlParameter("@SortOrderBy", SqlDbType.VarChar, 4);
sortOrderParam.Value = sord;
SqlParameter numberOfRowsParam = new SqlParameter("@NumberOfRows", SqlDbType.Int);
numberOfRowsParam.Value = rows;
SqlParameter startRowParam = new SqlParameter("@StartRow", SqlDbType.Int);
startRowParam.Value = page;
return new SqlParameter[]
{
sortColNameParam,sortOrderParam,numberOfRowsParam,startRowParam
};
}
///
<summary> /// Fill the Studnet Entity by using sql reader of command
/// </summary>
///sql command object for excute reader
/// colletion of students
private static List FillStudentEntity(SqlCommand sqlCommand)
{
List students = new List();
using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
{
while (sqlDataReader.Read())
{
students.Add(new Student
{
StudentId = Convert.ToInt32(sqlDataReader["StudentID"]),
FirstName = sqlDataReader["FirstName"].ToString(),
LastName = sqlDataReader["LastName"].ToString(),
Email = sqlDataReader["Email"].ToString(),
Sno = Convert.ToInt32(sqlDataReader["Sno"]),
TotalRows = Convert.ToInt32(sqlDataReader["TotalRows"])
});
}
}
return students;
}
}
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
public JsonResult GetStudents(string sidx, string sord, int page, int rows)
{
int pageIndex = page;
int pageSize = rows;
int startRow = (pageIndex * pageSize) + 1;
List students = StudentDAL.SelectStudnets(sidx, sord, startRow, rows);
int totalRecords = students.Select(x => x.TotalRows).FirstOrDefault();
int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);
var jsonData = new
{
total = totalPages,
page,
records = totalRecords,
rows = (
from student in students
select new
{
i = student.StudentId,
cell = new string[] { student.StudentId.ToString(), student.FirstName, student.LastName, student.Email }
}).ToArray()
};
return Json(jsonData);
}
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
<%@ Page Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage" %>
<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">
Home Page
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="HeadContent" runat="server">
<%--CSS Files--%>
<link href="/Content/jquery-ui-1.8.7.css" rel="stylesheet" type="text/css" />
<link href="/Content/ui.jqgrid.css" rel="stylesheet" type="text/css" />
<link href="/Content/ui.multiselect.css" rel="stylesheet" type="text/css" />
<%--jQuery Library--%>
<script src="/Scripts/jquery-1.4.1.min.js" type="text/javascript"></script>
<%--Must load language tag BEFORE script tag--%>
<script src="/Scripts/grid.locale-en.js" type="text/javascript"></script>
<script src="/Scripts/jquery.jqGrid.min.js" type="text/javascript"></script>
<%--jqGrid Code - refer http://www.trirand.com/blog/jqgrid/jqgrid.html --%>
<script type="text/javascript">
jQuery(document).ready(function () {
jQuery("#list").jqGrid({
url: '/Home/GetStudents/',
datatype: 'json',
mtype: 'POST',
colNames: ['StudentID', 'FirstName', 'LastName', 'Email'],
colModel: [
{ name: 'StudentID', index: 'StudentID', width: 150, align: 'left' },
{ name: 'FirstName', index: 'FirstName', width: 150, align: 'left', sortable: true },
{ name: 'LastName', index: 'LastName', width: 150, align: 'left', sortable: true },
{ name: 'Email', index: 'Email', width: 200, align: 'left', sortable: true}],
pager: jQuery('#pager'),
rowNum: 10,
rowList: [5, 10, 20, 50],
sortname: 'StudentID',
sortorder: "asc",
viewrecords: true,
caption: 'JQgrid'
});
});
</script>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
<%-- HTML Required--%>
<h2>Grid Data</h2>
<table id="list" class="scroll" cellpadding="0" cellspacing="0"></table>
<div id="pager" class="scroll" style="text-align:center;"></div>
</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 :)
