ASP.Net connect to database using HttpHandler ASHX file

Another alternative way to connect to database in ASP.Net is using ASHX file. The example we use is the same Customer information application given in my Previous Post 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); }
    }
}

ASHX files are convenient ways to deliver partial content to a web page. They are actually HttpHandlers and responsible for processing incoming HTTP requests and providing the appropriate response. They are often used to deliver binary content such as images or files that are stored in database or outside of the web application file system. For delivering small amounts of html to be plugged into a particular position on a web page, they are extremely useful. Add New Item and choose Generic Handler file type and give the file name as “FetchCustomer.ashx”. A template for a class that inherits from IHttpHandler will be opened which contain one method-ProcessRequest( ) and a property-IsReusable( ). The entire FetchCustomer.aspx which generates a response to the Ajax call and creates output onto the caller page itself using context.Response object from the data retrieved to DataReader object which is from Customer table related to the particular CustomerID passed as Request.QueryString Parameter.

<%@ WebHandler Language="C#" Class="FetchCustomer" %>

using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;

public class FetchCustomer : IHttpHandler {

    public void ProcessRequest (HttpContext context) {
        context.Response.ContentType = "text/html";
        string strSqlCon = System.Configuration.ConfigurationManager.ConnectionStrings["NorthWind"].ToString();
        //edit your own connection string
        string strSQL = "SELECT CompanyName, Address, City, Region, PostalCode, Country, Phone";
        strSQL += ", Fax FROM Customers WHERE CustomerID = @CustomerID";
        string strCustomerId = context.Request.QueryString["CustomerID"];
        if (strCustomerId != null && strCustomerId.Length == 5)
        {
            using (SqlConnection dbCon = new SqlConnection(strSqlCon))
            {
                dbCon.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = dbCon;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = strSQL;
                    cmd.Parameters.AddWithValue("CustomerID", strCustomerId);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows)
                    {
                        while (rdr.Read())
                        {
                            context.Response.Write("

");
                            context.Response.Write("<strong>" + rdr["CompanyName"].ToString() + "</strong>");
                            context.Response.Write("
" + rdr["Address"].ToString());
                            context.Response.Write("
" + rdr["City"].ToString());
                            context.Response.Write("
" + rdr["Region"].ToString());
                            context.Response.Write("
" + rdr["PostalCode"].ToString());
                            context.Response.Write("
" + rdr["Country"].ToString());
                            context.Response.Write("
Phone: " + rdr["Phone"].ToString());
                            context.Response.Write("
Fax: " + rdr["Fax"].ToString() + "

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

No Customer Selected.

");
        }
    }

    public bool IsReusable {
        get {
            return false;
        }
    }

}

Now to call the HttpHandler of ASHX file, use the JQuery load function with QueryString parameter (exactly same as $.load( ) approach which was written in Previous Post) as shown below.

    <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.ashx?CustomerID=" + $('#ddlCustomers').val());
            });
        });
</script>

One Response to “ASP.Net connect to database using HttpHandler ASHX file”

daniel mamann says:

June 2, 2011 at 7:27 pm

Wonderful blog! Do you have any helpful hints for aspiring writers? I’m planning to start my own site soon but I’m a little lost on everything. Would you recommend starting with a free platform like Wordpress or go for a paid option? There are so many choices out there that I’m completely overwhelmed .. Any recommendations? Kudos!