Home > Asp.Net, ASP.NET MVC, C#, jqGrid, Jquery > jqGrid with ASP.NET MVC and MS SQL/Oracle Store Procedure

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  🙂


sample code

Advertisements
Categories: Asp.Net, ASP.NET MVC, C#, jqGrid, Jquery
  1. January 20, 2012 at 7:55 am

    Nice and simple tutorial

    I really love it

  2. Muthu
    January 23, 2012 at 4:25 pm

    Awesome tutorial. This explains what exactly i’m looking for . Thank you

  3. January 24, 2012 at 6:24 am

    Nice and detail.. I loved reading the whole

  4. Zohaib
    February 1, 2012 at 7:03 pm

    Cool

  5. February 3, 2012 at 8:53 am

    Download link is not working for some strange reason.

  6. frank
    March 29, 2012 at 7:04 am

    yes correct @Zeeshan Umar, Download link is not working for some strange reason.
    I also want to download it.

    • March 31, 2012 at 6:50 pm

      Sorry to inconvenience you, the link has been updated. you can download the sample.

  7. April 14, 2012 at 6:39 pm

    Great Article 🙂 We can also submit our .net related links on http://www.dotnettechy.com to improve traffic. The dotnettechy.com is a community of .Net developers joined together to learn, to teach, to find solutions, to find interview questions and answers, to find .net website / blog collection and to have fun programming.

  8. lucas
    April 23, 2012 at 8:08 am

    Great tutorial, Waqas! Simple and clean

  9. Saif
    June 15, 2012 at 11:07 am

    nice work, so what next with mvc ?

  10. chandan
    December 14, 2012 at 4:44 pm

    quick question when you use the FillStudentEntity to execute the sqldatareader what if we have a null value in our database and we want to display on jqgrid? compiler is throwing exception

    A first chance exception of type ‘System.InvalidCastException’ occurred in mscorlib.dll
    Object cannot be cast from DBNull to other types.

    your tutorial is perfect and flawless only in my situation i need a solution to handle null values form DB

    Thanks

    • December 17, 2012 at 5:15 am

      you may check at SP level, if the value is null in any specific column you may set your own define value, you can set the NULL string as well to make fix it.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: