CodeProject: Using Ajax to simply read from and write to a database. Free source code and programming help

Title

Using Ajax to simply read from and write to a database

Code to Download

Download sourcecode

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 server side functions such as

database look ups 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 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.

Screen Shots

This example uses 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.

Sample screenshot

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

Sample screenshot

How about some code? That should please everyone

AjaxUI

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

Find button

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

Update button

<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...";

    }

}