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" %>
  3. <!DOCTYPE html>
  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>
  16. <script type="text/javascript">
  18. $(function () {
  19. $("#dataGrid").jqGrid({
  20. url: 'Default.aspx/GetDataFromDB',
  21. datatype: 'json',
  22. mtype: 'POST',
  24. serializeGridData: function (postData) {
  25. return JSON.stringify(postData);
  26. },
  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 });
  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>
  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;
  13. namespace jQGridExample
  14. {
  15. public partial class Default : System.Web.UI.Page
  16. {
  18. protected void Page_Load(object sender, EventArgs e)
  19. {
  21. }
  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.