Here I will explain how to bind data in jqgrid. First of all create a Table for save the data and then after stored procedure for retrive data from table.
CREATE TABLE [dbo].[Student]( [StudentID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [Standard] [int] NULL, [Division] [nvarchar](5) NULL, [Address] [nvarchar](500) NULL, [Mobileno] [varchar](20) NULL, CONSTRAINT [PK__Student__32C52A795B736AEC] 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] CREATE PROCEDURE [dbo].[WB_Fill_StudentInfo] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT * FROM Student ORDER BY StudentID ENDIn this code snippest i will set cell color conditionally, Open fancybox popup, Display Image and also add checkbox selection.
Default.aspx
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>jQ Grid In ASP.NET C#</title>
<link rel="stylesheet" href="code/jqGrid-4.5.2/css/ui.jqgrid.css" type="text/css" />
<link rel="stylesheet" href="code/fancybox/source/jquery.fancybox.css" type="text/css" media="screen" />
<link type="text/css" href="css/jquery-ui-1.9.2.custom.css" rel="stylesheet" />
<link type="text/css" href="css/ui.jqgrid.css" rel="stylesheet" />
<script type="text/javascript" src="Script/jquery-1.8.3.js"></script>
<script type="text/javascript" src="Script/jquery-ui-1.9.2.custom.js"></script>
<script src="Script/grid.locale-en.js" type="text/javascript"></script>
<script src="Script/jquery.jqGrid.min.js" type="text/javascript"></script>
<script type="text/javascript" src="fancybox/source/jquery.fancybox.pack.js"></script>
<style type="text/css">
.my-highlight {
background-color: green;
}
</style>
<script type="text/javascript">
$(function () {
var width = $(window).width() - 50; // for get window width and set grid width
var height = $(window).height() - 100;//for get window height and set grid height
var grid = $("#dataGrid");
$("#dataGrid").jqGrid({
url: 'Default.aspx/GetDataFromDB',
datatype: 'json',
mtype: 'POST',
serializeGridData: function (postData) {
return JSON.stringify(postData);
},
ajaxGridOptions: { contentType: "application/json" },
loadonce: true,
width: 900,
height: 500,
search: true,
autowidth: false,
shrinkToFit: true,
colNames: ['Select', 'Profile', 'Student ID', 'First Name', 'Last Name', 'Standard', 'Division', 'Address', 'Mobileno'],
colModel: [
{ name: 'StudentID', width: 50, id: 'StudentID', index: 'StudentID', editable: true, edittype: 'checkbox', editoptions: { value: "True:False", defaultValue: "False" }, formatoptions: { disabled: false } },
{
name: 'StudentID',
width: 50,
fixed: true,
formatter: function () {
return "<img src='Images/profile.png' alt='my image' />";
}
},
{ name: 'StudentID', index: 'Student ID', width: 80 },
{ name: 'FirstName', index: 'FirstName', width: 100, align: 'center', editable: false, sortable: false, formatter: fancyBoxFormatter },
{ name: 'LastName', index: 'LastName', width: 100, align: 'center', editable: false, sortable: false, formatter: fancyBoxFormatter },
{
name: 'Standard', index: 'Standard', width: 90, formatter: function (cellvalue, options, rowObject) {
if (cellvalue == '12')
return '<span style="background-color: red; ">' + cellvalue + '</span>';
else if (cellvalue == '10')
return '<span style="background-color: green; ">' + cellvalue + '</span>';
else
return cellvalue;
}
},
{ name: 'Division', index: 'Division', width: 90 },
{ name: 'Address', index: 'Address', width: 150 },
{ name: 'Mobileno', index: 'Mobileno', width: 100 }
],
viewrecords: true,
gridview: true,
rowNum: 100000,
sortable: true,
sortorder: "asc",
loadComplete: function () {
$(".fancybox").fancybox();
},
jsonReader: {
root: function (obj) {
return obj.d;
},
repeatitems: false
},
caption: 'Nikhil Sangani'
});
});
function fancyBoxFormatter(cellvalue, options, rowObject) {
var result,
link,
fancyBoxHTML,
fancyBoxContent;
link = "<a class=\"fancybox\" href=\"#data" + options.rowId + "\">" + cellvalue + "</a>";
fancyBoxHTML = "<div style=\"display:none\"><div id=\"data" + options.rowId + "\">" + cellvalue + "</div></div>";
return link + fancyBoxHTML;
}
</script>
</head>
<body style="font-family: Arial; font-size: 10pt">
<form runat="server" id="form1">
<asp:HiddenField runat="server" ID="hdnlist" ClientIDMode="Static" />
<table id="dataGrid" style="text-align: center;"></table>
</form>
</body>
</html>
Default.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Web.Script.Serialization;
using System.Web.Services;
namespace jQGridExample
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
[WebMethod]
public static List<Dictionary<string, object>> GetDataFromDB()
{
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection(@"Data Source=(local); Initial Catalog=emp; Uid=sa; pwd=sw;"))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "WB_Fill_StudentInfo";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row;
foreach (DataRow dr in dt.Rows)
{
row = new Dictionary<string, object>();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
return rows;
}
}
}
}
}
When the json data length is increse then it gives error so here I will set maxJsonLength in webconfig.
<configuration>
<system.web.extensions>
<scripting>
<webServices>
<jsonSerialization maxJsonLength="50000000"/>
</webServices>
</scripting>
</system.web.extensions>
</configuration>
Comments
Post a Comment