Your Ad Here
Google

Monday, March 05, 2007

 

Using ajax to simply read from and write to a database - Use ajax to retrieve data from a SQL Server database and then update that database using ajax




Title


Using Ajax to simply read from and write to a database


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?


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

 


 


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("<?xml version=\"1.0\"?>");
           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 look ups 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


 



Labels:


# posted by Amit Patel @ 10:03 AM
Comments: Post a Comment



<< Home

This page is powered by Blogger. Isn't yours?