How to retrieve data from the database using jQuery/Ajax/JSon In ASP.Net
Hi Everyone,
In this tutorial I'm going to show you how to retrieve data from SQL Server database using jQuery/Ajax/JSon on ASP.Net
Also in this example I have demonstrated how to use a splash screen(loading screen), until the data is retrieved to the client side.
You can generte a loading image through bellow mentioned url:
http://ajaxload.info/
Step 01:
======
Create a aspx page called "JqueryJsonDataSet"
Step 02:
======
Add bellow mentioned script/styles/html to "JqueryJsonDataSet.aspx" page
Add jQuery script file to the page:
<script src="jQuery/jquery-1.7.2.js" type="text/javascript"></script>
Add bellow styles:
<style type="text/css">
table.gridtable
{
font-family: verdana,arial,sans-serif;
font-size: 11px;
color: #333333;
border-width: 1px;
border-color: #666666;
border-collapse: collapse;
}
table.gridtable th
{
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #666666;
background-color: #dedede;
}
table.gridtable td
{
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #666666;
background-color: #ffffff;
}
</style>
Add bellow script:
<script type="text/javascript">
// calling jquery functions once document is ready
$(document).ready(function () {
// retreving data on button click
$("#btnLoad").click(LoadDataThroughAjaxCall);
//loading screen functionality - this part is additional - start
$("#divTable").ajaxStart(OnAjaxStart);
$("#divTable").ajaxError(OnAjaxError);
$("#divTable").ajaxSuccess(OnAjaxSuccess);
$("#divTable").ajaxStop(OnAjaxStop);
$("#divTable").ajaxComplete(OnAjaxComplete);
//loading screen functionality - this part is additional - end
});
// ajax call
function LoadDataThroughAjaxCall() {
$.ajax({
type: "POST",
url: "JqueryJsonDataSet.aspx/GetShipDetails",
data: '{}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnSuccess,
failure: OnFailure,
error: OnError
});
// this avoids page refresh on button click
return false;
}
// on sucess get the xml
function OnSuccess(response) {
//debugger;
var xmlDoc = $.parseXML(response.d);
var xml = $(xmlDoc);
var customers = xml.find("Table");
showOnATable(customers);
}
// show data on a table
function showOnATable(customers) {
//debugger;
var headers = [];
var rows = [];
// header section
headers.push("<tr>");
headers.push("<td><b>Ship City</b></td>");
headers.push("<td><b>Ship Postal Code</b></td>");
headers.push("<td><b>Ship Country</b></td>");
headers.push("</tr>");
// rows section
$.each(customers, function () {
var customer = $(this);
rows.push("<tr>");
rows.push("<td>" + $(this).find("ShipCity").text() + "</td>");
rows.push("<td>" + $(this).find("ShipPostalCode").text() + "</td>");
rows.push("<td>" + $(this).find("ShipCountry").text() + "</td>");
rows.push("</tr>");
});
var top = "<table class='gridtable'>";
var bottom = "</table>";
var table = top + headers.join("") + rows.join("") + bottom;
$("#divTable").empty();
$("#divTable").html(table);
}
// loading screen functionality functions - this part is additional - start
function OnAjaxStart() {
//debugger;
//alert('Starting...');
$("#divLoading").css("display", "block");
}
function OnFailure(response) {
//debugger;
alert('Failure!!!' + '<br/>' + response.reponseText);
}
function OnError(response) {
//debugger;
var errorText = response.responseText;
alert('Error!!!' + '\n\n' + errorText);
}
function OnAjaxError() {
//debugger;
alert('Error!!!');
}
function OnAjaxSuccess() {
//debugger;
//alert('Sucess!!!');
$("#divLoading").css("display", "none");
}
function OnAjaxStop() {
//debugger;
//alert('Stop!!!');
$("#divLoading").css("display", "none");
}
function OnAjaxComplete() {
//debugger;
//alert('Completed!!!');
$("#divLoading").css("display", "none");
}
// loading screen functionality functions - this part is additional - end
</script>
Add bellow html:
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btnLoad" runat="server" Text="Load Data" />
</div>
<br />
<div id="divTable">
</div>
<%--Loading screen section - start--%>
<div id="divLoading" style="display: none; position: absolute; top: 50%; left: 40%;
text-align: left;">
<span>
<img src='Images/Loading.gif' alt="Image not found." /></span>
<br />
<span style="text-align: left; padding-left: 8px;">Loading ...</span>
</div>
<%--Loading screen section - end--%>
</form>
</body>
Step 03:
======
Add bellow C# code to "JqueryJsonDataSet.aspx.cs" page
[System.Web.Services.WebMethod]
public static string GetShipDetails()
{
string query = "select top 10 ShipCity, ShipPostalCode, ShipCountry from Orders";
SqlCommand cmd = new SqlCommand(query);
return GetData(cmd).GetXml();
}
private static DataSet GetData(SqlCommand cmd)
{
string connString = "Data Source=192.168.180.xxx;Initial Catalog=Northwind;User ID=xxx;Password=xxx;MultipleActiveResultSets=True;";
using (SqlConnection con = new SqlConnection(connString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataSet ds = new DataSet())
{
sda.Fill(ds);
return ds;
}
}
}
}
Complete Source Code
JqueryJsonDataSet.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="JqueryJsonDataSet.aspx.cs"
Inherits="HelloWorld.JqueryJsonDataSet" %>
<!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 runat="server">
<title>Retrieving Data from Database using jQuery/Ajax/JSon</title>
</head>
<script src="jQuery/jquery-1.7.2.js" type="text/javascript"></script>
<link href="jQuery/demos/demos.css" rel="stylesheet" type="text/css" />
<style type="text/css">
table.gridtable
{
font-family: verdana,arial,sans-serif;
font-size: 11px;
color: #333333;
border-width: 1px;
border-color: #666666;
border-collapse: collapse;
}
table.gridtable th
{
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #666666;
background-color: #dedede;
}
table.gridtable td
{
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #666666;
background-color: #ffffff;
}
</style>
<script type="text/javascript">
// calling jquery functions once document is ready
$(document).ready(function () {
// retreving data on button click
$("#btnLoad").click(LoadDataThroughAjaxCall);
//loading screen functionality - this part is additional - start
$("#divTable").ajaxStart(OnAjaxStart);
$("#divTable").ajaxError(OnAjaxError);
$("#divTable").ajaxSuccess(OnAjaxSuccess);
$("#divTable").ajaxStop(OnAjaxStop);
$("#divTable").ajaxComplete(OnAjaxComplete);
//loading screen functionality - this part is additional - end
});
// ajax call
function LoadDataThroughAjaxCall() {
$.ajax({
type: "POST",
url: "JqueryJsonDataSet.aspx/GetShipDetails",
data: '{}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnSuccess,
failure: OnFailure,
error: OnError
});
// this avoids page refresh on button click
return false;
}
// on sucess get the xml
function OnSuccess(response) {
//debugger;
var xmlDoc = $.parseXML(response.d);
var xml = $(xmlDoc);
var customers = xml.find("Table");
showOnATable(customers);
}
// show data on a table
function showOnATable(customers) {
//debugger;
var headers = [];
var rows = [];
// header section
headers.push("<tr>");
headers.push("<td><b>Ship City</b></td>");
headers.push("<td><b>Ship Postal Code</b></td>");
headers.push("<td><b>Ship Country</b></td>");
headers.push("</tr>");
// rows section
$.each(customers, function () {
var customer = $(this);
rows.push("<tr>");
rows.push("<td>" + $(this).find("ShipCity").text() + "</td>");
rows.push("<td>" + $(this).find("ShipPostalCode").text() + "</td>");
rows.push("<td>" + $(this).find("ShipCountry").text() + "</td>");
rows.push("</tr>");
});
var top = "<table class='gridtable'>";
var bottom = "</table>";
var table = top + headers.join("") + rows.join("") + bottom;
$("#divTable").empty();
$("#divTable").html(table);
}
// loading screen functionality functions - this part is additional - start
function OnAjaxStart() {
//debugger;
//alert('Starting...');
$("#divLoading").css("display", "block");
}
function OnFailure(response) {
//debugger;
alert('Failure!!!' + '<br/>' + response.reponseText);
}
function OnError(response) {
//debugger;
var errorText = response.responseText;
alert('Error!!!' + '\n\n' + errorText);
}
function OnAjaxError() {
//debugger;
alert('Error!!!');
}
function OnAjaxSuccess() {
//debugger;
//alert('Sucess!!!');
$("#divLoading").css("display", "none");
}
function OnAjaxStop() {
//debugger;
//alert('Stop!!!');
$("#divLoading").css("display", "none");
}
function OnAjaxComplete() {
//debugger;
//alert('Completed!!!');
$("#divLoading").css("display", "none");
}
// loading screen functionality functions - this part is additional - end
</script>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btnLoad" runat="server" Text="Load Data" />
</div>
<br />
<div id="divTable">
</div>
<%--Loading screen section - start--%>
<div id="divLoading" style="display: none; position: absolute; top: 50%; left: 40%;
text-align: left;">
<span>
<img src='Images/Loading.gif' alt="Image not found." /></span>
<br />
<span style="text-align: left; padding-left: 8px;">Loading ...</span>
</div>
<%--Loading screen section - end--%>
</form>
</body>
</html>
JqueryJsonDataSet.aspx.cs
using System;
using System.Data;
using System.Data.SqlClient;
namespace HelloWorld
{
public partial class JqueryJsonDataSet : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
[System.Web.Services.WebMethod]
public static string GetShipDetails()
{
string query = "select top 10 ShipCity, ShipPostalCode, ShipCountry from Orders";
SqlCommand cmd = new SqlCommand(query);
return GetData(cmd).GetXml();
}
private static DataSet GetData(SqlCommand cmd)
{
string connString = "Data Source=192.168.180.xxx;Initial Catalog=Northwind;User ID=xxx;Password=xxx;MultipleActiveResultSets=True;";
using (SqlConnection con = new SqlConnection(connString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataSet ds = new DataSet())
{
sda.Fill(ds);
return ds;
}
}
}
}
}
}
Issue:
Some times when we try to load large amount of data, JSon returns an error.
SQL that returns large amount of data...
string query = "select * from Orders";
This is because web.config is not configured for large amount of JSon data, it will prompt bellow mentioned error.
Error during serialization or deserialization using the JSON JavaScriptSerializer. The length of the string exceeds the value set on the maxJsonLength property.
In that case you need to add bellow mentoned configurations to web.config file.
Section 01:
<sectionGroup name="system.web.extensions" type="System.Web.Configuration.SystemWebExtensionsSectionGroup, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35">
<sectionGroup name="scripting" type="System.Web.Configuration.ScriptingSectionGroup, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35">
<sectionGroup name="webServices" type="System.Web.Configuration.ScriptingWebServicesSectionGroup, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35">
<section name="jsonSerialization" type="System.Web.Configuration.ScriptingJsonSerializationSection, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="false"/>
</sectionGroup>
</sectionGroup>
</sectionGroup>
</configSections>
Section 02:
<system.web.extensions>
<scripting>
<webServices>
<jsonSerialization maxJsonLength="50000000"/>
</webServices>
</scripting>
</system.web.extensions>
Hope you enjoy the example. Happy Coding !!! :)
View comments