AJAX offers users a seamless way to work with your interface, no waiting for whole pages to load. jQuery has a set of tools to make it super simple to implement. We will walk you through how to use jQuery to communicate databse using jquery’s ajax method in asp.net.
Each example will feature the same requirement, and that is to obtain and display the Customer details relating to a specific CustomerID selected from a DropDownList on a page called Customer.aspx.
And we use following Database table:
CREATE TABLE [dbo].[tblCustomer]( [customerID] [int] NOT NULL, [firstName] [varchar](50) NULL, [surname] [varchar](50) NULL, [Company] [varchar](50) NULL,)
Once table created, need to insert some data into tblCustomer.
Here is Customer.aspx code:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Customer.aspx.cs" Inherits="Customer" %>
<!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>Customer Page</title>
<script src="jquery-1.4.4.min.js" type="text/javascript"></script>
</head>
<body>
<form id="form1" runat="server">
<div id="SelectCustomers">
Select Customer:
<asp:DropDownList ID="Customers" runat="server">
</asp:DropDownList>
</div>
<div id="CustomerDetails">
</div>
</form>
</body>
</html>
Code behind file customer.cs code:
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.Data;
using System.Configuration;
public partial class Customer : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string st= ConfigurationManager.ConnectionStrings["aaa"].ConnectionString;
SqlConnection con = new SqlConnection(st);
con.Open();
SqlCommand cmd = new SqlCommand("select * from tblCustomer",con);
Customers.DataSource=cmd.ExecuteReader();
Customers.DataValueField = "customerID";;
Customers.DataTextField = "firstName";
Customers.DataBind();
}
}
and webconfig file code:
<?xml version="1.0"?> <!-- For more information on how to configure your ASP.NET application, please visit http://go.microsoft.com/fwlink/?LinkId=169433 --> <configuration> <system.web> <compilation debug="true" targetFramework="4.0"/> </system.web> <connectionStrings> <add name="aaa" connectionString="Data Source=ASHISH\SQLEXPRESS;Initial Catalog=tblCustomers;Integrated Security=True;Pooling=False"/> </connectionStrings> </configuration>
So far our project VS solution explorer look like:
Method 1
Now, we first call ASPX File by jquery AJAX to get customer data into customer.aspx page.
create new page called fetchcustomer.aspx. put below code in fatchcustomer.cs file .
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.Data;
using System.Configuration;
public partial class FetchCustomer : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string st= ConfigurationManager.ConnectionStrings["aaa"].ConnectionString;
string id = Request.QueryString["customerID"];
if (id != null )
{
using (SqlConnection con = new SqlConnection(st))
{
using (SqlCommand cmd = new SqlCommand("select * from tblCustomer where customerID = @cid", con))
{
cmd.Parameters.AddWithValue("cid", Request.QueryString["customerID"]);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
Response.Write("<p>");
Response.Write("<strong> CustomerID:" + rdr["CustomerID"].ToString() + "</strong><br />");
Response.Write("<strong> First Name:" + rdr["Firstname"].ToString() + "</strong><br />");
Response.Write("<strong> Surname:" + rdr["surname"].ToString() + "</strong><br />");
Response.Write("<strong> Company" + rdr["company"].ToString() + "</strong><br />");
Response.Write("</p>");
}
}
}
}
}
else
{
Response.Write("<p>No customer selected</p>");
}
Response.End();
}
}
Now we use jQuery Ajax function to call fetchcustomer page. here is code of customer.aspx page
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Customer.aspx.cs" Inherits="Customer" %>
<!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></title>
<script src="jquery-1.4.4.min.js" type="text/javascript"></script>
<%-- with other asp page--%>
<script type="text/javascript">
$(document).ready(function () {
$('#Customers').change(function () {
$.ajax({
contentType: "text/html; charset=utf-8",
data: "customerID=" + $('#Customers').val(),
url: "FetchCustomer.aspx",
dataType: "html",
success: function (data) {
alert(data);
$("#CustomerDetails").html(data);
}
});
});
});
</script>
</head>
<body>
<form id="form1" runat="server">
<div id="SelectCustomers">
Select Customer:
<asp:DropDownList ID="Customers" runat="server">
</asp:DropDownList>
</div>
<div id="CustomerDetails">
</div>
</form>
</body>
</html>
Method 2
Now, we call Web service to get customer data into customer.aspx page.
create new web sevice called fetchcustomer.asmx. put below code in fetcustomer.cs file (web service file mostly in App_code folder) .
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
using System.Web.Script.Services;
/// <summary>
/// Summary description for FetchCustomer
/// </summary>
[WebService(Namespace = "http://ashishblog.com/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
public class FetchCustomer : System.Web.Services.WebService {
public FetchCustomer () {
//Uncomment the following line if using designed components
//InitializeComponent();
}
[WebMethod]
public string GetCustomer(string CustomerID)
{
string res = "<p>No customer selected</p>";
string st = ConfigurationManager.ConnectionStrings["aaa"].ConnectionString;
// Company com = new Company();
if (CustomerID != null)
{
StringBuilder sb = new StringBuilder();
using (SqlConnection con = new SqlConnection(st))
{
using (SqlCommand cmd = new SqlCommand("select * from tblCustomer where customerID = @cid", con))
{
cmd.Parameters.AddWithValue("cid",CustomerID );
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
//com.CompanyID = rdr["company"].ToString();
//com.CompanyName = rdr["surname"].ToString();
sb.Append("<p>");
sb.Append("<strong>" + rdr["customerid"].ToString() + "</strong><br />");
sb.Append("<strong>" + rdr["firstname"].ToString() + "</strong><br />");
sb.Append("<strong>" + rdr["company"].ToString() + "</strong><br />");
sb.Append( rdr["surname"].ToString() + "</p>");
res = sb.ToString();
}
}
}
}
}
return res;
}
}
Now we use jQuery Ajax function to call fetchcustomer web service. here is code (put into customer.aspx page)
<%--using web service with string output--%>
<script type="text/javascript">
$(document).ready(function() {
$('#Customers').change(function() {
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "FetchCustomer.asmx/GetCustomer",
data: "{ CustomerID: '" + $('#Customers').val() + "'}",
dataType: "json",
success: function(data) {
$("#CustomerDetails").html(data.d);
}
});
});
});
</script>
An alternative to above example, instead of string we can pass customer object from web service, here is code of webservice code file fetchcustomer.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
using System.Web.Script.Services;
/// <summary>
/// Summary description for FetchCustomer
/// </summary>
[WebService(Namespace = "http://ashishblog.com/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
public class FetchCustomer : System.Web.Services.WebService {
public FetchCustomer () {
//Uncomment the following line if using designed components
//InitializeComponent();
}
[WebMethod]
public Customer GetCustomer(string CustomerID)
{
//string res = "<p>No customer selected</p>";
string st = ConfigurationManager.ConnectionStrings["aaa"].ConnectionString;
Customer com = new Customer ();
if (CustomerID != null)
{
StringBuilder sb = new StringBuilder();
using (SqlConnection con = new SqlConnection(st))
{
using (SqlCommand cmd = new SqlCommand("select * from tblCustomer where customerID = @cid", con))
{
cmd.Parameters.AddWithValue("cid",CustomerID );
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
com.CustomerID = rdr["customerid"].ToString();
com.FirstName = rdr["firstname"].ToString();
com.Company = rdr["company"].ToString();
com.surname = rdr["surname"].ToString();
//sb.Append("<p>");
//sb.Append("<strong>" + rdr["customerid"].ToString() + "</strong><br />");
//sb.Append("<strong>" + rdr["firstname"].ToString() + "</strong><br />");
//sb.Append("<strong>" + rdr["company"].ToString() + "</strong><br />");
//sb.Append( rdr["surname"].ToString() + "</p>");
//res = sb.ToString();
}
}
}
}
}
return com;
}
public class Customer
{
public string CustomerID { get; set; }
public string FirstName { get; set; }
public string surname { get; set; }
public string Company { get; set; }
}
}
Now we use jQuery Ajax fuction to call fetchcustomer web service (customer object). here is code (put into customer.aspx page)
<%-- using web service with object or class output--%>
<script type="text/javascript">
$(document).ready(function () {
$('#Customers').change(function () {
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "FetchCustomer.asmx/GetCustomer",
data: "{ CustomerID: '" + $('#Customers').val() + "'}",
dataType: "json",
success: function (data) {
var Customer = data.d;
$('#CustomerDetails').append
('<p><strong>' + Customer.CustomerID + "</strong><br />" +
Customer.FirstName + "<br />" +
Customer.surname + "<br />" +
Customer.Company + "</p>")
}
});
});
});
</script>
Thanks.
Awesome information, many thanks to the article writer. It’s understandable to me now, the effectiveness and importance is mind-boggling. Thank you once again and good luck!
My accomplice and I actually enjoyed reading this weblog post. Keep it UP!!!!
Thanks -:)
I’ve recently started a weblog, the information you provide on this website has helped me tremendously. Thank you for your whole time & work.
hjhjhj
how to call asp.net web service in phonegap.
Sorry mate I don’t know much abt phonegap…
please can you give download link of your project ?
Hi Ashish,
This article is excellent. It helped me a lot. Thank you very much!
Best post found on the subject until now. And I’m looking for a long while. Thanks a lot, Ashish.
Hi
Thank your for sample. It is very usefull. I have problem Method 1. Have a questions:
1. I not understand what is @cid.
2. My samples working. DropDownList binding. Bu detals not write on the page.
3. Can you give download link of your project ?
Thank you.
test
Hi,
I do not understand why this much coding and calling is required w.r.t asp.. instead theses all task can be done with php using simple html inputs’.
Thabnk you alot for this post. I work all day without any result and nw you hepls me. Thnak you master.
Great post Thank you.
when jquery gives the div the data,it is does not appear in the source of the page. Any clues?Thanks
Ajax Database connection….
http://asp.net-informations.com/ajax/ajax-database.htm
riyan
i did n’t get exactly what u mentioned above.it showing all data at time or it will showing my database data in DDL only.i need to show once i selected one data in ddl that item data only need to show i am not getting this
please explain how to do that
This is simple and Smart Example I Like It You Are Great Bro Keep It Up…..