Using AJAX to simply read from and write to a database

• Download source code - 35.3 KB

Introduction

AJAX, the combination of Asynchronous calls with JavaScript and XML, is a cool technology that could be used to accomplish numerous fancy tasks such as dragging and dropping items on a page, navigating through large images using a mouse pointer, and creating chat rooms. But what about accomplishing a task as simple and oftentimes as unexciting as reading from and writing to a database? Should that be accomplished using AJAX? In many instances, the answer is yes.

Before using AJAX in every page in your application, you need to understand what AJAX is good for and when to use it. You also have to be aware of the drawbacks of using AJAX and when to avoid it. Here is a link to a great article on this topic: Responsible Asynchronous Scripting by Shaun Inman.

Why use AJAX to read from a database?

In ASP.NET, a database lookup will require a post back. There are many cases where this is not an option. I worked on an image manipulation project where a post back is very costly in terms of bandwidth and user experience. Database reads and writes needed to happen without posting the entire page back to the server. In such a scenario, the benefit of using AJAX far exceeds the cost.

Components of an "AJAXed" web application

There are three components to a web application using AJAX:

1. AjaxEngine (the forgotten hard worker)

This is an ASPX page with no user interface. It performs all the server side functions such as database lookups and updates.

2. AjaxClient (the middle man)

This is a JavaScript file that makes the asynchronous calls to the AjaxEngine and renders the response back to the AjaxUI.

3. AjaxUI (the sexy model)

This page does nothing but gets all the credit. It performs simple JavaScript tasks such as calling functions from AjaxClient when a button is clicked.

Screenshots

This example uses the Norhtwind database of Microsoft SQL Server to retrieve and update employee data using AJAX. Enter an employee ID between 1 and 9 and click Find to retrieve data. Make changes in the text boxes and click Update Employee to update the database record.

When the "Update Employee" button is clicked, AJAX is used to update the employee record in Northwind and an alert message is displayed.

How about some code? That should please everyone.

AjaxUI

We only need to handle the onClick events of the Find and Update buttons. Here is the code:

<input type="button" name="btnFindEmployee" class="btn" 
       id="" value="Find" onclick="JavaScript:return btnFindEmpoyee_OnClick();">

<INPUT id="" type="button" 
   value="Update Employee" class="btn" name="btnUpdateEmployee" 
   onclick="JavaScript:return btnUpdateEmpoyee_OnClick();">

AjaxClient (JavaScript)

This is the JavaScript file that will act as the middle man. Here are some functions of interest:

//Creating and setting the instance of appropriate
//XMLHTTP Request object to a "XmlHttp" variable

function CreateXMLHTTP()
{
    try
    {
        XMLHTTP = new ActiveXObject("Msxml2.XMLHTTP");
    }

    catch(e)
    {
        try
        {
            XMLHTTP = new ActiveXObject("Microsoft.XMLHTTP");
        }
        catch(oc)
        {
            XMLHTTP = null;
        }
    }

    //Creating object in Mozilla and Safari
    if(!XMLHTTP && typeof XMLHttpRequest != "undefined")
    {
        XMLHTTP = new XMLHttpRequest();
    }
}


function btnFindEmpoyee_OnClick()
{
    //Get Employee ID from text box
    var empID = document.getElementById("txtEmployeeID").value;
    // construct the URL
    var requestUrl =AjaxEnginePage + "?Action=FindEmployee&EmpID=" + 
                    encodeURIComponent(empID);
    CreateXMLHTTP();

    // If browser supports XMLHTTPRequest object
    if(XMLHTTP)
    {
        //Setting the event handler for the response
        XMLHTTP.onreadystatechange = FindEmployee;
        //Initializes the request object with GET (METHOD of posting),
        //Request URL and sets the request as asynchronous.
        XMLHTTP.open("GET", requestUrl, true);
        //Sends the request to server
        XMLHTTP.send(null);
    }
}

function btnUpdateEmpoyee_OnClick()
{
    //Get Employee ID from text box
    var empID = encodeURIComponent(document.getElementById("txtEmployeeID").value);
    var phone = encodeURIComponent(document.getElementById("txtPhone").value);
    var firstName = encodeURIComponent(document.getElementById("txtFirstName").value);
    var lastName = encodeURIComponent(document.getElementById("txtLastName").value);
    var title = encodeURIComponent(document.getElementById("txtTitle").value);
    var birthDate = encodeURIComponent(document.getElementById("txtBirthDate").value);
    var hireDate = encodeURIComponent(document.getElementById("txtHireDate").value);

    // construct the URL
    var requestUrl =AjaxEnginePage + "?Action=UpdateEmployee&EmpID="+ empID + 
                    "&FirstName="+ firstName +"&LastName="+ lastName + 
                    "&Phone="+ phone +"&Title="+ title +"&BirthDate="+ 
                    birthDate +"&HireDate="+ hireDate;
    CreateXMLHTTP();

    // If browser supports XMLHTTPRequest object
    if(XMLHTTP)
    {
        //Setting the event handler for the response
        XMLHTTP.onreadystatechange = ShowSuccessMsg;
        //Initializes the request object with GET (METHOD of posting),
        //Request URL and sets the request as asynchronous.
        XMLHTTP.open("GET", requestUrl, true);
        //Sends the request to server
        XMLHTTP.send(null);
    }
}

function FindEmployee()
{
    // To make sure receiving response data from server is completed
    if(XMLHTTP.readyState == 4)
    {
        //Valid Response is received
        if(XMLHTTP.status == 200)
        {
            SetEmployeeLabels(XMLHTTP.responseXML.documentElement);
        }
        else //something is wrong
        {
            alert("Could not retreive data from the server" );
            document.getElementById("lblFindEmployeeStatus").innerHTML="";
        }
        document.getElementById("btnFindEmployee").disabled=false;
    }
    else
    {
        document.getElementById("btnFindEmployee").disabled=true;
        document.getElementById("lblFindEmployeeStatus").innerHTML= 
           "<img src='Images/ajax-loader.gif'> loading...";
    }
}

AjaxEngine

This is your everyday C# code in an ASP.NET page:

private void Page_Load(object sender, System.EventArgs e)
{
   if(Request["Action"]!=null && Request["Action"].Trim()!="")
   {
       if(Request["Action"]=="FindEmployee" &&(Request["EmpID"]!=null && 
                      Request["EmpID"].Trim()!=""))
           FindEmployee(Convert.ToInt32(Request["EmpID"]));
       else if(Request["Action"]=="UpdateEmployee" &&(Request["EmpID"]!=null 
                  && Request["EmpID"].Trim()!=""))
           UpdateEmployee(Convert.ToInt32(Request["EmpID"]), 
                          Request["FirstName"],Request["LastName"],
       Request["Phone"],Request["Title"],Request["BirthDate"],Request["HireDate"]);
   }
}

public void FindEmployee(int employeeID)
{
    SqlConnection cnn=new SqlConnection(ConfigurationSettings.AppSettings["CnnStr"]);
    cnn.Open();
    SqlDataReader dr;
    SqlCommand cmd=new SqlCommand("SELECT EmployeeID, FirstName, LastName, " + 
                                  "Title, BirthDate, HireDate,HomePhone " +
                                  " FROM Employees WHERE EmployeeID="+ employeeID,cnn);
    dr=cmd.ExecuteReader();
    if(dr.HasRows)
    {
         dr.Read();
         System.Text.StringBuilder sb=new System.Text.StringBuilder("");
         sb.Append("<Employee>");
         sb.Append("<EmployeeID><![CDATA["+ dr["EmployeeID"].ToString()+"]]></EmployeeID>");
         sb.Append("<FirstName><![CDATA["+ dr["FirstName"].ToString()+"]]></FirstName>");
         sb.Append("<LastName><![CDATA["+ dr["LastName"].ToString()+"]]></LastName>");
         sb.Append("<Phone><![CDATA["+ dr["HomePhone"].ToString()+"]]></Phone>");
         sb.Append("<Title><![CDATA["+ dr["Title"].ToString()+ "]]></Title>");
         sb.Append("<BirthDate><![CDATA["+ dr["BirthDate"].ToString()+ "]]></BirthDate>");
         sb.Append("<HireDate><![CDATA["+ dr["HireDate"].ToString() +"]]></HireDate>");
         sb.Append("</Employee>");
         Response.ContentType ="text/xml";
         Response.Write(sb.ToString());
    }
}

private void UpdateEmployee(int employeeID,string firstName, string lastName,
        string phone,string title,string birthDate,string hireDate)
{
    SqlConnection cnn=new SqlConnection(ConfigurationSettings.AppSettings["CnnStr"]);
    cnn.Open();
    SqlCommand cmd=new SqlCommand("UPDATE Employees Set FirstName='"+
                   firstName +"', LastName='"+ lastName +"', HomePhone='"+ phone + 
                   "' ,Title='"+ title +"', BirthDate='"+ birthDate +"', HireDate ='"+ 
                   hireDate +"' WHERE EmployeeID="+ employeeID,cnn);
    cmd.ExecuteNonQuery();
}

Conclusion

There are cases where you need to use AJAX to accomplish tedious tasks such as database lookups and updates. AJAX may not come to mind when you think about those tasks, but it should be used especially when postbacks are very expensive functions in your application.