ASP.NET

jqGrid in ASP.Net C#

The following is my Data Table in Design Mode from which I will show data in a jqGrid:

table design
                                       Image 1.

The following is the script of my Data Table:

  1. CREATE TABLE [dbo].[Employee](  
  2.     [Emp_ID] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Name] [varchar](50) NULL,  
  4.     [Designation] [varchar](50) NULL,  
  5.     [City] [varchar](50) NULL,  
  6.     [State] [varchar](50) NULL,  
  7.     [Country] [varchar](50) NULL,  
  8.  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED   
  9. (  
  10.     [Emp_ID] ASC  
  11. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
  12. ) ON [PRIMARY]  
  13. GO 

The data in My Table is:

table
                                                                    Image 2.

The following is my aspx code:

  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="jQGridExample.Default" %>  
  2.   
  3. <!DOCTYPE html>  
  4.   
  5. <html xmlns="http://www.w3.org/1999/xhtml">  
  6. <head runat="server">  
  7.     <title></title>  
  8.     <link type="text/css" href="http://jqueryrock.googlecode.com/svn/trunk/css/jquery-ui-1.9.2.custom.css" rel="stylesheet" />  
  9.     <link type="text/css" href="http://jqueryrock.googlecode.com/svn/trunk/jqgrid/css/ui.jqgrid.css" rel="stylesheet" />  
  10.     <script type="text/javascript" src="http://jqueryrock.googlecode.com/svn/trunk/js/jquery-1.8.3.js"></script>  
  11.     <script type="text/javascript" src="http://jqueryrock.googlecode.com/svn/trunk/js/jquery-ui-1.9.2.custom.js"></script>  
  12.     <script src="http://jqueryrock.googlecode.com/svn/trunk/jqgrid/js/grid.locale-en.js" type="text/javascript"></script>  
  13.     <script src="http://jqueryrock.googlecode.com/svn/trunk/jqgrid/js/jquery.jqGrid.min.js" type="text/javascript"></script>  
  14.   
  15.   
  16.     <script type="text/javascript">  
  17.   
  18.         $(function () {  
  19.             $("#dataGrid").jqGrid({  
  20.                 url: 'Default.aspx/GetDataFromDB',  
  21.                 datatype: 'json',  
  22.                 mtype: 'POST',  
  23.   
  24.                 serializeGridData: function (postData) {  
  25.                     return JSON.stringify(postData);  
  26.                 },  
  27.   
  28.                 ajaxGridOptions: { contentType: "application/json" },  
  29.                 loadonce: true,  
  30.                 colNames: ['Employee ID', 'Name', 'Designation', 'City', 'State', 'Country'],  
  31.                 colModel: [  
  32.                                 { name: 'Emp_ID', index: 'Employee ID', width: 80 },  
  33.                                 { name: 'Name', index: 'Name', width: 140 },  
  34.                                 { name: 'Designation', index: 'Designation', width: 160 },  
  35.                                 { name: 'City', index: 'City', width: 180 },  
  36.                                 { name: 'State', index: 'State', width: 180 },  
  37.                                 { name: 'Country', index: 'Country', width: 180 }  
  38.                 ],  
  39.                 pager: '#pagingGrid',  
  40.                 rowNum: 5,  
  41.                 rowList: [10, 20, 30],  
  42.                 viewrecords: true,  
  43.                 gridview: true,  
  44.                 jsonReader: {  
  45.                     page: function (obj) { return 1; },  
  46.                     total: function (obj) { return 1; },  
  47.                     records: function (obj) { return obj.d.length; },  
  48.                     root: function (obj) { return obj.d; },  
  49.                     repeatitems: false,  
  50.                     id: "0"  
  51.                 },  
  52.                 caption: 'jQ Grid Example'  
  53.             });  
  54.         }).pagingGrid("#pager", { edit: true, add: true, del: false });  
  55.   
  56.   
  57.     </script>  
  58. </head>  
  59. <body style="font-family: Arial; font-size: 10pt">  
  60.     <table style="border: solid 15px red; width: 100%; vertical-align: central;">  
  61.         <tr>  
  62.             <td style="padding-left: 20px; padding-top: 20px; padding-bottom: 20px; background-color: skyblue; font-family: 'Times New Roman'; font-weight: bold; font-size: 20pt; color: chocolate;">jQ Grid Example In  ASP.NET C#  
  63.             </td>  
  64.         </tr>  
  65.         <tr>  
  66.             <td style="text-align: center; vertical-align: central; padding: 50px;">  
  67.                 <table id="dataGrid" style="text-align: center;"></table>  
  68.                 <div id="pagingGrid"></div>  
  69.             </td>  
  70.         </tr>  
  71.     </table>  
  72.   
  73. </body>  
  74. </html> 

The following is my aspx.cs code:

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Data.SqlClient;  
  8. using System.Configuration;  
  9. using System.Data;  
  10. using System.Web.Script.Serialization;  
  11. using System.Web.Services;  
  12.   
  13. namespace jQGridExample  
  14. {  
  15.     public partial class Default : System.Web.UI.Page  
  16.     {  
  17.   
  18.         protected void Page_Load(object sender, EventArgs e)  
  19.         {  
  20.   
  21.         }  
  22.   
  23.         [WebMethod]  
  24.         public static List<Dictionary<string, object>> GetDataFromDB()  
  25.         {  
  26.             DataTable dt = new DataTable();  
  27.             using (SqlConnection con = new SqlConnection(@"Data Source=INDIA\MSSQLServer2k8; Initial Catalog=EmployeeManagement; Uid=sa; pwd=india;"))  
  28.             {  
  29.                 using (SqlCommand cmd = new SqlCommand("SELECT Emp_ID, Name, Designation, City, State,Country FROM Employee ORDER BY Emp_ID,Country,State, City", con))  
  30.                 {  
  31.                     con.Open();  
  32.                     SqlDataAdapter da = new SqlDataAdapter(cmd);  
  33.                     da.Fill(dt);  
  34.                     System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();  
  35.                     List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();  
  36.                     Dictionary<string, object> row;  
  37.                     foreach (DataRow dr in dt.Rows)  
  38.                     {  
  39.                         row = new Dictionary<string, object>();  
  40.                         foreach (DataColumn col in dt.Columns)  
  41.                         {  
  42.                             row.Add(col.ColumnName, dr[col]);  
  43.                         }  
  44.                         rows.Add(row);  
  45.                     }  
  46.                     return rows;  
  47.                 }  
  48.             }  
  49.         }  
  50.     }  
  51. } 

Now run the application.

All the records are in the jqGrid. Here I set the Page Size to 5.

jQGrid
                                                                        Image 3.

Now do paging as in the following:

jQGrid example
                                                                        Image 4.

paging
                                                                     Image 5.

Now do paging
                                                                        Image 6.

jQGrid image
                                                                              Image 7.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs