JqGrid In An ASP.NET MVC Application Using Entity Framework

Showing data in a grid is a basic need of any application. For better user experience, we need sorting, paging, filters, and search functionality in the grid. jqGrid is a very lightweight client side grid, where we can get a lot of functionality easily.

Let us see how we will create a GqGrid and show data in ASP.NET MVC application.

Create a database named Student and create a Table named StudentInfo, as shown below:

Script is shown below to create the table,

USE [Student]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[StudentInfo](
    [StudentId] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Address] [nvarchar](50) NULL,
    [City] [nvarchar](50) NULL,
    [State] [nvarchar](50) NULL,
    [Country] [nchar](10) NULL,
    [Department] [nchar](10) NULL,
    [Marks] [int] NULL,
 CONSTRAINT [PK_StudentInfo] PRIMARY KEY CLUSTERED
(
    [StudentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Insert some records to the StudentInfo table, as shown below:

 
To fetch the data, we will create a simple stored procedure named “GetStudentInfo“, as shown below:

USE [Student]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE  PROCEDURE [dbo].[GetStudentInfo]

AS
BEGIN

    SET NOCOUNT ON;

    SELECT  StudentId, Name, Address, City, State,
     Country, Department, Marks from StudentInfo
END

For test purposes, execute the stored procedure and you will get the output, as shown below:

 
Now our database is ready to use.

Now we will create a simple ASP.NET MVC Web Application. Go to Visual Studio and create a new project. 

Select MVC and click OK.



Now our project is created.

In the next step, we will use the Entity framework, which will call the above stored procedure to retrieve the data from the StudentInfo table.

For using Entity framework, we will create an Edmx, as shown below. Go to the project, add new Item, go to the data section and select “ADO.NET Entity Data Model”.

 
In the next step, select Generate from database.

 

 

Now, we will be able to see student entities that are created.



In the next step, we will get the jqGrid related file from the Nuget.

Download the Jquery.JQGrid from the Nuget package, as shown below:

 

We will get the required scripts for JqGrid from Nuget.

Now, we know that we have HomeController and we have the Index action, which returns the Index view.

We will call a different action, when the Index view is loaded.

public ActionResult Index()
        {

            return View();
        }

We have the different action- GetJqGridData, which gets the data using Entity framework .The action method code is shown below:

public ActionResult GetJqGridData()
    {

        StudentEntities s = new StudentEntities();
        var studenlist = s.GetStudentInfo();
        var jsonData = new
        {

            rows = studenlist
        };
        return Json(jsonData, JsonRequestBehavior.AllowGet);

    }

We will be calling by the index action. In the Index view, we have the studentGrid HTML table and studentGridPager as pager in a div and the required scripts for JqGrid.

Index.cshtml

<div>
    <table id="studentGrid"></table>
    <div id="studentGridPager"></div>
</div>
@section scripts{
    <link href="~/Content/themes/base/jquery-ui.css" rel="stylesheet" />
    <link href="~/Content/jquery.jqGrid/ui.jqgrid.css" rel="stylesheet" />
    <script src="~/Scripts/jquery-ui-1.10.4.js"></script>
    <script src="~/Scripts/i18n/grid.locale-en.js"></script>
    <script src="~/Scripts/jquery.jqGrid.min.js"></script>
    <script src="~/Scripts/script.js"></script>
}

When the script.js loads, it has the code to call the action GetJqGridData, gets the data, binds the columns and fills the data.

Script.js

$(function ()
{
    $("#studentGrid").jqGrid
   ({
        url: "/Home/GetJqGridData",
        datatype: 'json',
        mtype: 'Get',
        colNames: ['Enrollment No', 'Name','Address', 'City' , 'State', 'Country' ,'Department'],
        colModel: [
            { key: true, name: 'StudentId', index: 'StudentId',  sortable: true },
            { key: false, name: 'Name', index: 'Name',  sortable: true },

            { key: false, name: 'Address', index: 'Address', sortable: true },
            { key: false, name: 'City', index: 'City', editable: true },
            { key: false, name: 'State', index: 'State', editable: true },

                 { key: false, name: 'Country', index: 'Country', editable: true },
            { key: false, name: 'Department', index: 'Department', editable: true }

            ],
        pager: jQuery('#studentGridPager'),
        rowNum: 3,
        rowList: [10, 20, 30, 40, 50],
        height: '100%',
        viewrecords: true,
        caption: 'Students Records',
        emptyrecords: 'No Students Records are Available to Display',
        jsonReader:
        {
            root: "rows",
            page: "page",
            total: "total",
            records: "records",
            repeatitems: false,
            Id: "0"
        },
        autowidth: true,
        multiselect: false,
    }).navGrid('#studentGridPager',
   {
        edit: true, add: true, del: true, search: true,
        searchtext: "Search Student", refresh: true
    },

        {
            zIndex: 100,
            caption: "Search Students",
            sopt: ['cn']
        });

});

Let us see some important point here.

 

 

The output is shown below when we run the page.



This is basic information on jqGrid. We can do sorting, paging, filtering of the data in it. We can easily add steps to add, modify or delete the records. We can use custom formatters to add different input controls in it. All the events are there to do event handling. Some of the event handling can be found here.

I hope you understood the basic information on JqGrid. Thanks for reading.