In this article we will discuss How to bind gridview with json in asp.net c#. On the page load bind the gridview using datatable on the same time use jquery to bind gridview.
We will be using Department table
Step 1: Create a table using the following script with data:
USE [ShoppingZone]
GO
/****** Object: Table [dbo].[Department] Script Date: 05/11/2016 00:22:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Department](
[DepartmentID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Description] [nvarchar](50) NULL,
CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
(
[DepartmentID] 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
SET IDENTITY_INSERT [dbo].[Department] ON
INSERT [dbo].[Department] ([DepartmentID], [Name], [Description]) VALUES (1, N'Software', N'software developer')
INSERT [dbo].[Department] ([DepartmentID], [Name], [Description]) VALUES (2, N'Q.C Engineer', N'mechanical department')
INSERT [dbo].[Department] ([DepartmentID], [Name], [Description]) VALUES (3, N'Inspectors', N'workers')
INSERT [dbo].[Department] ([DepartmentID], [Name], [Description]) VALUES (4, N'Marketing', N'marketing responisbility')
INSERT [dbo].[Department] ([DepartmentID], [Name], [Description]) VALUES (5, N'Human Resources', N'HR team')
SET IDENTITY_INSERT [dbo].[Department] OFF
Step 2: Create a class and name it DeptDetails for the department table.
Step 3: Copy and paste the following code in the design page Default.aspx.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Asp.net Bind Data to Datatable using JQuery or JSON</title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "Default.aspx/BindDatatable",
data: "{}",
dataType: "json",
success: function (data) {
for (var i = 0; i < data.d.length; i++) {
$("#grid").append("<tr><td>" + data.d[i].DepartmentId + "</td><td>" + data.d[i].Name + "</td><td>" + data.d[i].Description + "</td></tr>");
}
},
error: function (result) {
alert("Error");
}
});
});
</script>
<style type="text/css">
table, th, td {
border: 1px solid black;
border-collapse: collapse;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<asp:GridView ID="grid" runat="server">
<HeaderStyle BackColor="#DC5807" Font-Bold="true" ForeColor="White" />
</asp:GridView>
</form>
</body>
</html>
Step 4: Copy and paste the following code in the Default.aspx.cs.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindColumnToGridview();
}
}
/// <summary>
/// This method is used to bind dummy row to gridview to bind data using JQuery
/// </summary>
private void BindColumnToGridview()
{
DataTable dt = new DataTable();
dt.Columns.Add("DepartmentId");
dt.Columns.Add("Name");
dt.Columns.Add("Description");
dt.Rows.Add();
grid.DataSource = dt;
grid.DataBind();
grid.Rows[0].Visible = false;
}
[WebMethod]
public static DeptDetails[] BindDatatable()
{
DataTable dt = new DataTable();
List<DeptDetails> details = new List<DeptDetails>();
using (SqlConnection con = new SqlConnection("Data Source=BIG-PC;Initial Catalog=ShoppingZone;Integrated Security=true"))
{
using (SqlCommand cmd = new SqlCommand("select DepartmentId,Name,Description from Department", con))
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
foreach (DataRow dtrow in dt.Rows)
{
DeptDetails dept = new DeptDetails();
dept.DepartmentId = dtrow["DepartmentId"].ToString();
dept.Name = dtrow["Name"].ToString();
dept.Description = dtrow["Description"].ToString();
details.Add(dept);
}
}
}
return details.ToArray();
}
public class DeptDetails
{
public string DepartmentId { get; set; }
public string Name { get; set; }
public string Description { get; set; }
}
}
Output: