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.

13 Comments

Join the conversation! Your thoughts help the community grow.

Rohan Laghate10y ago
Good Work ! However, sorting and search functionality is not working ..What am I missing? I followed the same steps mentioned above.
Manav Pandya10y ago
Thanks sir ...
Mikhail Tatsky10y ago
It would be cool to read the same article
Santhakumar Munuswamy10y ago
Nice share
Dyansmith10y ago
Useful & Nice article with all required steps , Thanks for your effort.
Bhavik Patel10y ago
good sharing
Kuppurasu Nagaraj10y ago
Nice Sharing..
Vignesh Mani10y ago
Nice one
Raviteja Swayampu10y ago
Check this site for more applications like that www.mitechdev.com
Thiruppathi R10y ago
Nice..
kalu singh rao10y ago
Nice Articles
Raja10y ago
Good One...
Debasis Saha10y ago
Nice One..