Introduction: In this article I
am going to share how to populate gridview clientside from sql server database using
jquery, json in asp.net in both C# and VB.Net through ajax call.
In previous articles i explained jQuery JSON to insert data into sql server database without postback and jQuery json to bind dropdownlist dynamically from database and jquery to upload,crop and store image in folder in asp.net and jQuery to validate file extension while uploading image and Call server side function using Javascript and AJAX PageMethods without any PostBack
Implementation:
Let’s create a page to demonstrate the concept.
- First of all create a table and add dummy data into it using the following sql script:
CREATE TABLE tbBookDetails
(
BookId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
BookName VARCHAR(100),
Author VARCHAR(100),
Publisher VARCHAR(100),
BookpPrice DECIMAL(10,2)
)
INSERT INTO tbBookDetails VALUES
('Asp.Net','Ajay','Rozy Publication',1200),
('C#.Net','Bhavuk','Jai Publication',1000),
('VB.Net','Nancy','Rozy Publication',970),
('MVC','Sahil','Amar Publication',1480),
('JAVA','Supreet','Sam Publication',850),
('PHP','Parvesh','Maya
Publication',800)
- Now create a stored procedure to fetch all the records from table to bind in repeater data control
CREATE PROCEDURE spBookDetails
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM tbBookDetails
END
HTML Source
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script
type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
<script
type="text/javascript">
$(function () {
GetBookDetails();
});
function GetBookDetails() {
$.ajax({
type: "POST",
contentType: "application/json;
charset=utf-8",
url: "Default.aspx/GetBookDetails",
data: "{}",
dataType: "json",
success: OnSuccess,
failure: function
(response) {
alert("Error: " + response.d);
},
error: function
(response) {
alert("Error: "+response.d);
}
});
}
function OnSuccess(response) {
var xmlDoc = $.parseXML(response.d);
var xml = $(xmlDoc);
var booksData = xml.find("BooksData");
var row = $("[id*=grdBookDetails] tr:last-child").clone(true);
$("[id*=grdBookDetails] tr").not($("[id*=grdBookDetails]
tr:first-child")).remove();
$.each(booksData, function
() {
var bookData = $(this);
$("td", row).eq(0).html(bookData.find("BookId").text());
$("td", row).eq(1).html(bookData.find("BookName").text());
$("td", row).eq(2).html(bookData.find("Author").text());
$("td", row).eq(3).html(bookData.find("Publisher").text());
$("td", row).eq(4).html(bookData.find("BookPrice").text());
$("[id*=grdBookDetails]").append(row);
row = $("[id*=grdBookDetails]
tr:last-child").clone(true);
});
};
</script>
<style
type="text/css">
table, th, td {
border: 1px solid #808080;
padding: 5px;
text-align: left;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<asp:GridView
ID="grdBookDetails" runat="server" HeaderStyle-BackColor="#1384d5" HeaderStyle-ForeColor="#ffffff" HeaderStyle-Font-Bold="true">
</asp:GridView>
</form>
</body>
</html>
- Now In the <configuration> tag of web.config file add your connectionstring as:
<connectionStrings>
<add name="conStr" connectionString="Data Source=LALIT-PC;Initial Catalog=MyDataBase;Integrated
Security=True"/>
</connectionStrings>
Note: Replace Data Source and
Catalogue i.e. Database name
as per your application
Asp.Net C# code to bind gridview from sql server database table using
jquery, json, ajax
- In code behind file(.aspx.cs)write the code as:
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindHeaderRowInGridview();
}
}
// Bind dummy header
row to gridview
private void BindHeaderRowInGridview()
{
DataTable dt = new DataTable();
//Add columns to
datatable
dt.Columns.Add("BookId");
dt.Columns.Add("Book Name");
dt.Columns.Add("Author");
dt.Columns.Add("Publisher");
dt.Columns.Add("Book Price");
//Add row to
datatable
dt.Rows.Add();
//Bind row having 5
columns to gridview as a Gridview's header row.
grdBookDetails.DataSource = dt;
grdBookDetails.DataBind();
}
[WebMethod]
public static String GetBookDetails()
{
DataSet ds=new DataSet();
//Create datatable
object and name it.
DataTable dt = new DataTable("BooksData");
//Create Connection
object and get connection string defined in web.config file
SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);
using (sqlCon)
{
using (SqlCommand cmd = new SqlCommand("spBookDetails", sqlCon))
{
cmd.CommandType = CommandType.StoredProcedure;
sqlCon.Open();
using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
{
adp.Fill(dt);
//add filled datatable to dataset so that
we can convert it to xml
ds.Tables.Add(dt);
}
}
}
//return dataset
data in xml format using DataSet's GetXml method
return ds.GetXml();
}
Asp.Net VB code to bind gridview from sql server database table using
jquery, json, ajax
- In code behind file(.aspx.vb)write the code as:
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services
Protected Sub Page_Load(sender As Object,
e As EventArgs)
Handles
Me.Load
If Not IsPostBack Then
BindHeaderRowInGridview()
End If
End Sub
' Bind dummy header
row to gridview
Private Sub BindHeaderRowInGridview()
Dim dt As New DataTable()
'Add columns to
datatable
dt.Columns.Add("BookId")
dt.Columns.Add("Book Name")
dt.Columns.Add("Author")
dt.Columns.Add("Publisher")
dt.Columns.Add("Book Price")
'Add row to
datatable
dt.Rows.Add()
'Bind row having 5
columns to gridview as a Gridview's header row.
grdBookDetails.DataSource = dt
grdBookDetails.DataBind()
End Sub
<WebMethod> _
Public Shared Function GetBookDetails() As [String]
Dim ds As New DataSet()
'Create datatable
object and name it.
Dim dt As New DataTable("BooksData")
'Create Connection
object and get connection string defined in web.config file
Dim sqlCon As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)
Using sqlCon
Using cmd As New SqlCommand("spBookDetails", sqlCon)
cmd.CommandType = CommandType.StoredProcedure
sqlCon.Open()
Using adp As New SqlDataAdapter(cmd)
adp.Fill(dt)
'add filled datatable to dataset so that
we can convert it to xml
ds.Tables.Add(dt)
End Using
End Using
End Using
'return dataset data
in xml format using DataSet's GetXml method
Return ds.GetXml()
End Function
Now over to you:
"A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."
2 comments
Click here for commentsGreat Article
ReplyC# Training
Sometime i got some issue when we try to make gridview with the help of sql , But after seeing your this article and your method of coding am understand how can we fix my problem of gridview.
ReplyPromo Codes
If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..