ASP.Net connect to database via AJAX using JQUERY

Daily News Letter

Twitter

1

2

ASP.Net connect to database via AJAX using JQUERY

June 30th, 2009 | Posted in ASP.Net, Web | by Shyam

One of the ways to connect to database in ASP.Net is via AJAX using JQUERY by giving an example below which connects to the Northwind database and shows the available customers in dropdownlist of an aspx web page. Upon selecting each customer the Customer details will be displayed. Inorder to make AJAX calls, the latest version of the jQuery library should be referenced which can be downloaded from here.

The design code of Customers.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Customers.aspx.cs" Inherits="Customers" %>

<!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 Info</title>
</head>
<body>
<form id="form1" runat="server">
<div id="SelectCustomers">
        <asp:DropDownList ID="ddlCustomers" runat="server">
        </asp:DropDownList>
    </div>
<div id="CustomerDetails">
    </div>
</form>

</body>
</html>

The codebehind in which data is retrieved from customers table of Northwind database and bound to a dropdownlist showing company name and having CustomerID as value field.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class Customers : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            string strSqlCon = System.Configuration.ConfigurationManager.ConnectionStrings["NorthWind"].ToString();
            // or give your server and authentication details here
            string strSQL = "SELECT CustomerID, CompanyName FROM Customers";
            DataTable dt = new DataTable();
            using (SqlConnection dbCon = new SqlConnection(strSqlCon))
            {
                dbCon.Open();
                using (SqlCommand Cmd = new SqlCommand())
                {
                    Cmd.Connection = dbCon;
                    Cmd.CommandType = CommandType.Text;
                    Cmd.CommandText = strSQL;
                    using (SqlDataAdapter da = new SqlDataAdapter(Cmd))
                    {
                        da.Fill(dt);
                    }
                }
                dbCon.Close();
            }
            ddlCustomers.DataSource = dt;
            ddlCustomers.DataValueField = "CustomerID";
            ddlCustomers.DataTextField = "CompanyName";
            ddlCustomers.DataBind();
            ddlCustomers.Items.Insert(0, "--Customer--");
        }
        catch (Exception ex)
        { throw new Exception("error: " + ex.Message); }
    }
}

Now create another file “FetchCustomer.aspx” with code behind option deselected. The entire FetchCustomer.aspx which generates a response to the Ajax call and creates output onto the caller page itself using Response object from the data retrieved to DataReader object which is from Customer table related to the particular CustomerID passed as Request.QueryString Parameter.

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<script runat="server">
    protected void Page_Load(object sender, EventArgs e)
    {
        string strSqlCon = System.Configuration.ConfigurationManager.ConnectionStrings["NorthWind"].ToString();
        string strSQL = "SELECT CompanyName, Address, City, Region, PostalCode, Country, Phone";
        strSQL += ", Fax FROM Customers WHERE CustomerID = @CustomerID";
        string strCustomerID = Request.QueryString["CustomerID"];
        if (strCustomerID != null &amp;&amp; strCustomerID.Length == 5)
        {
            using (SqlConnection dbCon = new SqlConnection(strSqlCon))
            {
                dbCon.Open();
                using (SqlCommand Cmd = new SqlCommand(strSQL, dbCon))
                {
                    Cmd.Parameters.AddWithValue("CustomerID", strCustomerID);
                    SqlDataReader rdr = Cmd.ExecuteReader();
                    if (rdr.HasRows)
                    {
                        while (rdr.Read())
                        {
                            Response.Write("

");
                            Response.Write("&lt;b&gt;" + rdr["CompanyName"].ToString() + "&lt;/b&gt;");
                            Response.Write("
" + rdr["Address"].ToString());
                            Response.Write("
" + rdr["City"].ToString());
                            Response.Write("
" + rdr["Region"].ToString());
                            Response.Write("
" + rdr["PostalCode"].ToString());
                            Response.Write("
" + rdr["Country"].ToString());
                            Response.Write("
Phone: " + rdr["Phone"].ToString());
                            Response.Write("
Fax: " + rdr["Fax"].ToString() + "

");
                        }
                    }
                }
                dbCon.Close();
            }
        }
        else
        { Response.Write("

No Customer Selected

"); }
        Response.End();
    }
</script>

There are a number of ways jQuery can request the FetchCustomer.aspx page and handle the response. The first way we are going to illustrate is the load() function, which loads html from FetchCustomer.aspx The below code will be added in the head section of the Customers.aspx which will reference the lattest version of the JQuery library and as the page loads, $(document).ready( ) function will be applied to onchange event of the dropdownlist control. This functions gets the id of the div “CustomerDetails” of the page (”Customers.aspx”) within which it resides to load the html returned from the FetchCustomer.aspx page and passes the currently selected dropdownlist value in the querystring.

    <script type="text/javascript" src="script/jquery-1.3.2.min.js" ></script>
    <script type="text/javascript" language="javascript">
        $(document).ready(function() {
            $('#ddlCustomers').change(function() {
                $('#CustomerDetails').load("FetchCustomer.aspx?CustomerID=" + $('#ddlCustomers').val());
            });
        });
</script>

Another way alternative to $.load( ) funtion is the $.get( ) function which does exactly the same except the callback argument specifies what is to be done with the response from the Ajax request. Place this script in the head section of Customers.aspx. In $.get( ) function the query string value is passed along with the querystring name in { } brackets along with the query string name & value seperated by a colon(:). jQuery takes these values and constructs a querystring as part of the HTTP request, so that the page called is FetchCustomer.aspx?CustomerID=strCustomerID.

    <script type="text/javascript" src="script/jquery-1.3.2.min.js" ></script>
    <script type="text/javascript">
      $(document).ready(function() {
        $('#ddlCustomers').change(function() {
          $.get("FetchCustomer.aspx",
            { CustomerID: "" + $('#ddlCustomers').val() + "" },
            function(data) {
              $('#CustomerDetails').html(data);
            });
        });
      });

</script>

The other alternative method is $.ajax(). This is a more feature rich method in that it allows a range of options to be applied to manage different types of call, and error handling. Just place the below script in the head section of the FetchCustomer.aspx page.

    <script type="text/javascript" src="script/jquery-1.3.2.min.js" ></script>
    <script type="text/javascript">
      $(document).ready(function() {
        $('#ddlCustomers').change(function() {
          $.ajax({
            contentType: "text/html; charset=utf-8",
            data: "CustomerID=" + $('#ddlCustomers').val(),
            url: "FetchCustomer.aspx",
            dataType: "html",
            success: function(data) {
              $("#CustomerDetails").html(data);
            }
          });
        });
      });
</script>

Applies To: Microsoft .Net Framework 2.0, 3.5

Leave a Reply Total views: 66 and No data yet! so far Today.

Tags: ASP.Net

Daily News Letter

Twitter

1

Recent Posts

2

Archives

3

Tag Cloud

ASP.Net connect to database via AJAX using JQUERY

Leave a Reply