Click here to Skip to main content
14,875,773 members
Articles / Web Development / ASP.NET
Article
Posted 31 Oct 2006

Stats

163.4K views
2.7K downloads
61 bookmarked

Using AJAX to simply read from and write to a database

Rate me:
Please Sign up or sign in to vote.
3.31/5 (7 votes)
15 Oct 2007CPOL2 min read
Use AJAX to retrieve data from a SQL Server database and then update that database using AJAX.

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)
  2. This is an ASPX page with no user interface. It performs all the server side functions such as database lookups and updates.

  3. AjaxClient (the middle man)
  4. This is a JavaScript file that makes the asynchronous calls to the AjaxEngine and renders the response back to the AjaxUI.

  5. AjaxUI (the sexy model)
  6. 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.

Sample screenshot

When the "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 the onClick events of the Find and Update buttons. Here is the code:

Find button:
HTML
<input type="button" name="btnFindEmployee" class="btn" 
       id="" value="Find" onclick="JavaScript:return btnFindEmpoyee_OnClick();">
Update button:
HTML
<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:

JavaScript
//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:

C#
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Dahan Abdo
Web Developer
United States United States
Dahan Abdo is a Software Project Manager. He is an early adapter of the .NET technology. He has been developing Web applications and services since .NET beta1. He enjoys programming in C# and developing web applications. He also enjoys working on large and complex projects using innovative design and cutting edge technologies.
He recently became a Microsoft Certified Applications Developer (MCAD).
Dahan Abdo is also a three time Microsoft Most Valuable Professional (MVP).

Comments and Discussions

 
GeneralMy vote of 5 Pin
Member 875182712-Dec-12 10:51
MemberMember 875182712-Dec-12 10:51 
GeneralGood Article, But i am facing a problem with it Pin
Sumit Prakash Sharma12-Aug-09 23:13
MemberSumit Prakash Sharma12-Aug-09 23:13 
GeneralFacing the problem while fetching the data from database using ajax Pin
sravanthi chowdary30-Oct-08 19:44
Membersravanthi chowdary30-Oct-08 19:44 
Generalajax read and write from database Pin
Lalit Singh (MS CRM)4-Jul-08 3:58
MemberLalit Singh (MS CRM)4-Jul-08 3:58 
QuestionXMLHTTP.responseXML.documentElement returns null value Pin
Hemangajit29-Oct-07 22:43
MemberHemangajit29-Oct-07 22:43 
GeneralDataaccesshelper,dll Pin
bluland9-Aug-07 0:58
Memberbluland9-Aug-07 0:58 
GeneralRe: Dataaccesshelper,dll Pin
Dahan Abdo9-Aug-07 1:51
MemberDahan Abdo9-Aug-07 1:51 
GeneralRe: Dataaccesshelper,dll Pin
bluland9-Aug-07 22:43
Memberbluland9-Aug-07 22:43 
GeneralRe: Dataaccesshelper,dll Pin
bluland10-Aug-07 2:08
Memberbluland10-Aug-07 2:08 
GeneralA Complete Article Pin
ggopis@hotmail.com18-Jul-07 7:45
Memberggopis@hotmail.com18-Jul-07 7:45 
GeneralBest Article Pin
manjots4-Jul-07 21:34
Membermanjots4-Jul-07 21:34 
GeneralGood Article Pin
Bharat Gajjar25-May-07 4:19
MemberBharat Gajjar25-May-07 4:19 
GeneralHelp Pls Pin
sonu22sonu18-Apr-07 2:29
Membersonu22sonu18-Apr-07 2:29 
GeneralRe: Help Pls Pin
Dahan Abdo18-Apr-07 11:23
MemberDahan Abdo18-Apr-07 11:23 
GeneralAtlas Pin
Herre Kuijpers1-Nov-06 21:35
MemberHerre Kuijpers1-Nov-06 21:35 
GeneralRe: Atlas Pin
Fred_Smith2-Nov-06 6:23
MemberFred_Smith2-Nov-06 6:23 
GeneralRe: Atlas Pin
Dahan Abdo2-Nov-06 14:18
MemberDahan Abdo2-Nov-06 14:18 
GeneralRe: Atlas Pin
Secrets6-Nov-06 19:43
MemberSecrets6-Nov-06 19:43 
GeneralRe: Atlas Pin
Mike Lang9-Nov-06 4:10
MemberMike Lang9-Nov-06 4:10 
GeneralRe: Atlas Pin
Dahan Abdo9-Nov-06 17:44
MemberDahan Abdo9-Nov-06 17:44 
GeneralSQL Injection !!! Pin
jesusonline1-Nov-06 21:23
Memberjesusonline1-Nov-06 21:23 
GeneralRe: SQL Injection !!! Pin
Dahan Abdo2-Nov-06 14:09
MemberDahan Abdo2-Nov-06 14:09 
AnswerRe: SQL Injection !!! Pin
jesusonline2-Nov-06 14:39
Memberjesusonline2-Nov-06 14:39 
GeneralRe: SQL Injection !!! Pin
Dahan Abdo2-Nov-06 17:17
MemberDahan Abdo2-Nov-06 17:17 
GeneralAck! Global variable! Pin
/dev/Brian31-Oct-06 14:04
Member/dev/Brian31-Oct-06 14:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.