AJAX Web Service Driven Customers Table With Customer Details

This article will show you how to develop a fully AJAX Web Service driven table that displays a list of customers from the SQL database as well as allowing you to review each customer's orders.

Introduction

This article will show you how to create a
n ajax table which displays filtered customer list based on the input by the user as well as allows you to select each customer and view their orders in a modal window. The structure I used is rather simple and shouldn't take a lot of time to figure out. I have 2 classes which represent a customer and an order object. This is what I used to parse the data. Now let's take a look at my folder structure:



As you can see the structure is quite simple. I have a Default.aspx page, a WebService file (WSTable.asmx), a css folder with a default.css file inside of it (we'll talk about it later on), an App_Code folder where my classes are
stored as well as my WebService codebehind file (App_Code/WSTable.cs). Now first let's see what we're trying to achieve:


As you can see
we have a simple input box on top of the page which allows us to enter a couple of letters and see all the relevant companies in the database. In the picture above the search term I entered is "Gour" which returned all the companies whose names contain "gour" (Gourmanes, Gourmet, etc).

App_Code Classes

We have 2 main classes in our App_Code
, the Customer class and the Order class. As their name suggest they will be used to represent an instance of either a customer or an order. We also have 2 more folders (BAL and DAL) which is what I usually name my App_Code folders so it is easier to find the class that I may need at some moment. In these folders I have 2 more classes: CustomerBAL.cs (which represents the Business layer of my application) and CustomerDAL (which represents the Data Access layer and performs all the Customer SQL related operations). Now let's take a look at the Customer class and what it consists off:

using System;
using System.Collections.Generic;
using System.Web;

/// <summary>
/// Summary description for Customer
/// </summary>
public class Customer
{
#region Private and Public Properties

private string _customerID = string.Empty;
public string CustomerID
{
get {
return _customerID; }
set { _customerID = value; }
}
private string _companyName = string.Empty;
public string CompanyName
{
get {
return _companyName; }
set { _companyName = value; }
}
private string _contactName = string.Empty;
public string ContactName
{
get {
return _contactName; }
set { _contactName = value; }
}
private string _contactTitle = string.Empty;
public string ContactTitle
{
get {
return _contactTitle; }
set { _contactTitle = value; }
}
private string _address = string.Empty;
public string Address
{
get {
return _address; }
set { _address = value; }
}
private string _city = string.Empty;
public string City
{
get {
return _city; }
set { _city = value; }
}
private string _region = string.Empty;
public string Region
{
get {
return _region; }
set { _region = value; }
}
private string _postalCode = string.Empty;
public string PostalCode
{
get {
return _postalCode; }
set { _postalCode = value; }
}
private string _country = string.Empty;
public string Country
{
get {
return _country; }
set { _country = value; }
}
private string _phone = string.Empty;
public string Phone
{
get {
return _phone; }
set { _phone = value; }
}
private string _fax = string.Empty;
public string Fax
{
get {
return _fax; }
set { _fax = value; }
}

#endregion

public Customer()
{
//
// TODO: Add constructor logic here
//
}

public static List<Customer> GetCustomersByCompanyName(string companyNamePrefix)
{
return CustomerBAL.GetCustomersByCompanyName(companyNamePrefix);
}

public static List<Order> GetCustomerOrders(string customerID)
{
return CustomerBAL.GetCustomerOrders(customerID);
}
}

As you can see, other than a bunch of properties this class only has 2 methods which I will explain later. Now let's take a look at the
Order class:

using System;
using System.Collections.Generic;
using System.Web;

/// <summary>
/// Summary description for Order
/// </summary>
public class Order
{
#region Private and Public properties

private int _orderID = 0;
public int OrderID
{
get {
return _orderID; }
set { _orderID = value; }
}
private string _customerID = string.Empty;
public string CustomerID
{
get {
return _customerID; }
set { _customerID = value; }
}
private int _employeeID = 0;
public int EmployeeID
{
get {
return _employeeID; }
set { _employeeID = value; }
}
private DateTime _orderDate;
public DateTime OrderDate
{
get {
return _orderDate; }
set { _orderDate = value; }
}
private DateTime _requiredDate;
public DateTime RequiredDate
{
get {
return _requiredDate; }
set { _requiredDate = value; }
}
private DateTime _shippedDate;
public DateTime ShippedDate
{
get {
return _shippedDate; }
set { _shippedDate = value; }
}
private int _shipVia = 0;
public int ShipVia
{
get {
return _shipVia; }
set { _shipVia = value; }
}
private double _freight = 0;
public double Freight
{
get {
return _freight; }
set { _freight = value; }
}
private string _shipName = string.Empty;
public string ShipName
{
get {
return _shipName; }
set { _shipName = value; }
}
private string _shipAddress = string.Empty;
public string ShipAddress
{
get {
return _shipAddress; }
set { _shipAddress = value; }
}
private string _shipCity = string.Empty;
public string ShipCity
{
get {
return _shipCity; }
set { _shipCity = value; }
}
private string _shipRegion = string.Empty;
public string ShipRegion
{
get {
return _shipRegion; }
set { _shipRegion = value; }
}
private string _shipPostalCode = string.Empty;
public string ShipPostalCode
{
get {
return _shipPostalCode; }
set { _shipPostalCode = value; }
}
private string _shipCountry = string.Empty;
public string ShipCountry
{
get {
return _shipCountry; }
set { _shipCountry = value; }
}

#endregion

public Order()
{
//
// TODO: Add constructor logic here
//
}
}

As you can see this class is even simpler and contains no methods at all. Only a bunch of properties. However it doesn't really need any methods since I will call any meth
ods related to Customer orders from the Customer class itself. Now let's move on to the Web Service code that is the App_Code/WSTable.cs file:

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.Services;
using System.Text;

/// <summary>
/// Summary description for WSTable
/// </summary>
[WebService(
Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
public class WSTable : System.Web.Services.WebService {

public WSTable () {

//Uncomment the following line if using designed components
//InitializeComponent();
}

[WebMethod]
public string GetTable(string prefix) {
List<Customer> customers = Customer.GetCustomersByCompanyName(prefix);
StringBuilder builder =
new StringBuilder();

if (customers != null)
{
builder.Append(
"<table>");

builder.Append(
"<tr><thead>");
builder.AppendFormat(
"<th scope=\"col\">{0}</th>", "Customer ID");
builder.AppendFormat(
"<th scope=\"col\">{0}</th>", "Company Name");
builder.AppendFormat(
"<th scope=\"col\">{0}</th>", "Contact Name");
builder.AppendFormat(
"<th scope=\"col\">{0}</th>", "Contact Title");
builder.AppendFormat(
"<th scope=\"col\">{0}</th>", "Address");
builder.AppendFormat(
"<th scope=\"col\">{0}</th>", "City");
builder.AppendFormat(
"<th scope=\"col\">{0}</th>", "Region");
builder.AppendFormat(
"<th scope=\"col\">{0}</th>", "Postal Code");
builder.AppendFormat(
"<th scope=\"col\">{0}</th>", "Country");
builder.AppendFormat(
"<th scope=\"col\">{0}</th>", "Phone");
builder.AppendFormat(
"<th scope=\"col\">{0}</th>", "Fax");
builder.Append(
"</thead></tr>");

foreach (Customer customer
in customers)
{
builder.Append(
"<tr>");

builder.AppendFormat(
"<td>{0}</td>", customer.CustomerID);
builder.AppendFormat(
"<td>{0}</td>", customer.CompanyName);
builder.AppendFormat(
"<td>{0}</td>", customer.ContactName);
builder.AppendFormat(
"<td>{0}</td>", customer.ContactTitle);
builder.AppendFormat(
"<td>{0}</td>", customer.Address);
builder.AppendFormat(
"<td>{0}</td>", customer.City);
builder.AppendFormat(
"<td>{0}</td>", customer.Region);
builder.AppendFormat(
"<td>{0}</td>", customer.PostalCode);
builder.AppendFormat(
"<td>{0}</td>", customer.Country);
builder.AppendFormat(
"<td>{0}</td>", customer.Phone);
builder.AppendFormat(
"<td>{0}</td>", customer.Fax);
string linkText = String.Format(
"<a class=\"detailsLink\" href=\"javascript:showCustomerDetails('{0}');\">Details</a>", customer.CustomerID);
builder.AppendFormat(
"<td>{0}</td>", linkText);

builder.Append(
"</tr>");
}

builder.Append(
"<tr><tfoot>");

builder.AppendFormat(
"<th><em>Total: {0} {1} found</em></th>", customers.Count, customers.Count == 1 ? "result" : "results");

builder.Append(
"</tfoot></tr>");

builder.Append(
"</table>");
}
else
{

}

return builder.ToString();
}

[WebMethod]
public string GetCustomerOrders(string customerID)
{
List<Order> orders = Customer.GetCustomerOrders(customerID);
StringBuilder builder =
new StringBuilder();

if (orders != null && orders.Count > 0)
{
builder.Append(
"<table>");

builder.Append(
"<tr><thead>");

builder.AppendFormat(
"<th>{0}</th>", "OrderID");
builder.AppendFormat(
"<th>{0}</th>", "Freight Charge");
builder.AppendFormat(
"<th>{0}</th>", "Country");

builder.Append(
"</thead></tr>");

foreach (Order order
in orders)
{
builder.Append(
"<tr>");

builder.AppendFormat(
"<td>{0}</td>", order.OrderID);
builder.AppendFormat(
"<td>{0} €</td>", order.Freight);
builder.AppendFormat(
"<td>{0}</td>", order.ShipCountry);

builder.Append(
"</tr>");
}
builder.Append(
"</table>");
}
else
{

}

return builder.ToString();
}
}

Now the WSTable.cs has got a bit more code but nothing really serious and complicated. This web service contains 2 WebMethods: the GetTable method and the GetCustomerOrders method. I believe the names of methods are self-explanitory but I will explain them in detail further on. Now let's take a look at the class the WebService actually calls to query the data and display the results:

using System;
using System.Collections.Generic;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for CustomerDAL
/// </summary>
public class CustomerDAL
{
public CustomerDAL()
{
//
// TODO: Add constructor logic here
//
}

public static List<Customer> GetCustomersByCompanyName(string companyNamePrefix)
{
using (SqlConnection conn = new SqlConnection(ServiceConfig.ConnectionString))
{
string queryString = String.Format(
"SELECT * FROM Customers WHERE CompanyName LIKE '%' + {0} + '%'", "@companyName");
SqlCommand comm =
new SqlCommand(queryString, conn);
comm.Parameters.AddWithValue(
"companyName", companyNamePrefix);
SqlDataAdapter da =
new SqlDataAdapter(comm);
DataTable ds =
new DataTable();
da.
Fill(ds);

if (ds != null && ds.Rows.Count > 0)
{
List<Customer> customers = new List<Customer>();

foreach (DataRow row
in ds.Rows)
{
Customer customer =
new Customer();
customer.CustomerID = row[
"CustomerID"].ToString();
customer.CompanyName = row[
"CompanyName"].ToString();
customer.ContactName = row[
"ContactName"].ToString();
customer.ContactTitle = row[
"ContactTitle"].ToString();
customer.Address = row[
"Address"].ToString();
customer.City = row[
"City"].ToString();
customer.Region = row[
"Region"].ToString();
customer.PostalCode = row[
"PostalCode"].ToString();
customer.Country = row[
"Country"].ToString();
customer.Phone = row[
"Phone"].ToString();
customer.Fax = row[
"Fax"].ToString();

customers.
Add(customer);
}

return customers;
}

return null;
}
}

public static List<Order> GetCustomerOrders(string customerID)
{
using (SqlConnection conn = new SqlConnection(ServiceConfig.ConnectionString))
{
string queryString = String.Format(
"SELECT * FROM Orders WHERE CustomerID=@CustomerID");
SqlCommand comm =
new SqlCommand(queryString, conn);
comm.Parameters.AddWithValue(
"CustomerID", customerID);
SqlDataAdapter da =
new SqlDataAdapter(comm);
DataTable ds =
new DataTable();
da.
Fill(ds);

if (ds != null && ds.Rows.Count > 0)
{
List<Order> orders = new List<Order>();

foreach (DataRow row
in ds.Rows)
{
Order order =
new Order();
order.OrderID =
Convert.ToInt32(row["OrderID"].ToString());
order.CustomerID = row[
"CustomerID"].ToString();
order.EmployeeID =
Convert.ToInt32(row["EmployeeID"].ToString());
order.OrderDate =
Convert.ToDateTime(row["OrderDate"].ToString());
order.RequiredDate =
Convert.ToDateTime(row["RequiredDate"].ToString());
order.ShippedDate =
Convert.ToDateTime(row["ShippedDate"].ToString());
order.ShipVia =
Convert.ToInt32(row["ShipVia"].ToString());
order.Freight =
Convert.ToDouble(row["Freight"].ToString());
order.ShipName = row[
"ShipName"].ToString();
order.ShipAddress = row[
"ShipAddress"].ToString();
order.ShipCity = row[
"ShipCity"].ToString();
order.ShipRegion = row[
"ShipRegion"].ToString();
order.ShipPostalCode = row[
"ShipPostalCode"].ToString();
order.ShipCountry = row[
"ShipCountry"].ToString();

orders.
Add(order);
}

return orders;
}

return null;
}
}
}

Now let's take a look at the code above. As you can see we have 2 public static methods in the Customer
DAL.cs. The methods are static so that I don't have to instantiate the CustomerDAL class before calling the method. If they weren't static you would have to execute the method the following way:

CustomerDAL
custDAL = new CustomerDAL();
List<Customer> customers = custDAL.GetCustomersByCompanyName("sometexthere");

But in case when the method is static you can call it by directly accessing the CustomerDAL class like this:

List<Customer> customers = CustomerDAL.GetCustomersByCompanyName("sometexthere");
(that seems easier doesn't it?)

Anyways, let's move on. You maybe have noticed that there's one more class in my App_Code folder named ServiceConfig. This is a simple helper class which I've developed and which
I use in pretty much every project to read common data like web.config Connection strings, AppSettings etc, etc. So in this project this class contains nothing more than a ConnectionString which you can see being used in the above code. The last class to review is the CustomerBAL class. This class represents the Business Layer of the application and in my case its usually just a communicator between the interface and the data layer (it passes the data and requests/responses between them). Note though that some people tend to do their data parsing exactly in this layer.

using System;
using System.Collections.Generic;
using System.Web;

/// <summary>
/// Summary description for CustomerBAL
/// </summary>
public class CustomerBAL
{
public CustomerBAL()
{
//
// TODO: Add constructor logic here
//
}

public static List<Customer> GetCustomersByCompanyName(string companyNamePrefix)
{
return CustomerDAL.GetCustomersByCompanyName(companyNamePrefix);
}

public static List<Order> GetCustomerOrders(string customerID)
{
return CustomerDAL.GetCustomerOrders(customerID);
}
}

As you can see it's a really simple class which only accepts a request and forwards it to the DataAccess layer returning any response back if needed.

Page markup and Javascript

I've kept the page as simple as possible so it's easier for you to understand how everything works. Let's take a look at the markup of the page:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!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>WebService Table Example</title>
<link href="css/default.css" rel="stylesheet" type="text/css" />
<script type="text/javascript">
function applyFilter() {
document.getElementById("loaderImg").style.display = "block";
var prefix = document.getElementById("txtSearch").value;

// allow filtering only when text length bigger than 2
if (prefix.length > 2) {
WSTable.GetTable(prefix, onWSTableComplete);
}
else {
document.getElementById("contentWrapper").innerHTML = "";
}
}

function onWSTableComplete(result) {
document.getElementById("contentWrapper").innerHTML = result;
document.getElementById("loaderImg").style.display = "none";
}

function showCustomerDetails(customerID) {
document.getElementById("modalDiv").style.display = "block";
WSTable.GetCustomerOrders(customerID, onCustomerDetailsComplete);
}

function onCustomerDetailsComplete(result) {
document.getElementById("modalContent").innerHTML = result;
}

function closeModal() {
document.getElementById("modalDiv").style.display = "none";
}

function setFocus(controlName) {
document.getElementById(controlName).focus();
}
</script>
</head>
<body onload="setFocus('txtSearch');">
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager" runat="server">
<Services>
<asp:ServiceReference Path="WSTable.asmx" />
</Services>
</asp:ScriptManager>

<div id="searchWrapper">
Search term:
<asp:TextBox ID="txtSearch" onkeyup="applyFilter();" runat="server" />
<br /><br />
<img id="loaderImg" src="images/loader.gif" alt="" style="display:none;margin:0 auto;" />
</div>
<div id="contentWrapper">

</div>

<div id="modalDiv" style="display:none;">
<h2>Customer Orders</h2>
<div class="modalContentWrapper">
<div id="modalContent">

</div>
</div>
<a class="closeLink" href="javascript:closeModal();">Close</a>
</div>
</form>
</body>
</html>

The first thing to pay attention to is the fact that I have a Script
Manager control on the page. Notice the Services tag and the <asp:ServiceReference tag. This tag points to the WSTable.asmx file we created. This is needed if you want to be able to access the methods and the WebService itself. Notice also that I wrote all the Javascript directly into the head tags of the page. This is so that it would be easier to track everything that I'm talking about rather than complicating things by putting everything in separate files like it should be done for a live application. However I did separate the *.css file since it's not important for the tutorial itself and it would create too much spam in the page creating the opposite effect than the one I intended initially. Hope everything is clear now. Let's analyze what's going on here:

As you can see we have a searchWrapper div which is a wrapper around t
he TextBox control and a loader image. Underneath the searchWrapper div is the contentWrapper div. This is where the results from the search will be inserted. Further down the page i have a modalDiv which is hidden and which will be used to populate Order details for a selected customer. We have a h2 title inside the modalDiv as well as modalContentWrapper. The role of modalContentWrapper is to wrap around the orders table having fixed height and overflow-y:auto; so in case the orders table expands more than the height of the wrapper a vertical scrollbar will appear and allow you to scroll up and down. the #modalContent div will be used to insert the orders table.

Now
, when you download the sample from the bottom of the page (make sure you have the Northwind sample database installed on your SQL Server) and run it locally what happens is that the moment the text you enter in the textbox exceeds 2 characters the web service is called and the current text is passed as a parameter to the web service method which than takes this parameter and calls the CustomerDAL.GetCustomersByCustomerName method. Then it loops through the results and generates the HTML content to display using a StringBuilder object. After all of this has been done it returns the result as a string and a javascript callback function which was referenced in the webservice call is fired. This callback function takes a parameter (which in my case i named result) which represents the result sent back from the WebService. I then set the innerHTML of the #contentWrapper to the result object. How it looks like you can see on the picture at the beginning of tutorial as well.

Javascript code

If you take a look at the txtSearch TextBox you will see it has a javascript onkeyup property set to call a applyFilter Javascript function. Why did I use onKeyUp? Because this way you prevent unnecessary spamming to the applyFilter call. If use hold the button down it won't call the function for each letter enter as it would onchange and it won't call the applyFilter as long as you hold the button down. It will only call the applyFilter function once you release the keyboard key. Now let's take a look at applyFilter function:

function applyFilter() {
document.getElementById("loaderImg").style.display = "block";
var prefix = document.getElementById("txtSearch").value;

// allow filtering only when text length bigger than 2
if (prefix.length > 2) {
WSTable.GetTable(prefix, onWSTableComplete);
}
else {
document.getElementById("contentWrapper").innerHTML = "";
}
}

function onWSTableComplete(result) {
document.getElementById("contentWrapper").innerHTML = result;
document.getElementById("loaderImg").style.display = "none";
}

first thing you can see is that i'm enable the display of my loader img (which is hidden initially) so show the user something's going on. Then, i store the current value of the txtSearch to a variable named prefix. I then check if the prefix has more than 2 characters (its waste of resources doing it for anything less than 2 chars, you might just as well show them everything in that case without any filter). If the prefix is larger than 2 characters I'm initiating a call to my WSTable Web Service and I'm calling it's GetTable method and passing it 2 parameters: prefix and what 2nd parameter represents is "What function should I call when I'm done doing your instruction?" and in there I'm telling it to call the onWSTableComplete function when its complete. Now let's examine that one as well:

function onWSTableComplete(result) {
document.getElementById("contentWrapper").innerHTML = result;
document.getElementById("loaderImg").style.display = "none";
}

As you can see this function takes 1 parameter as input. I named it result because it is actually the result of our webservice (a string in this case but it can also be a boolean or something else). You can name it whatever you feel like. Let's see what goes on in here:

First I'm populating the contentWrapper div with the result I got from the Web Service and then I'm hiding the loader image to announce that the action is complete. Now if you take a look at the HTML generated by the Web Service you will see that among the customer details a new column is added as well which houses a Customer Details link. Hover your mouse and you'll see this link has been dynamically given a href which calls a Javascript function named showCustomerDetails and it passes the CustomerID as well. Now what happened initially in GetTable function is exactly what happens here only this time we are not checking any prefixes but simply calling the WebService's GetCustomerOrders method and passing it a CustomerID and a callback function as parameters. Before actually calling the Web Service we're setting the display of the modal div to visible because this is were the data will be presented to the user. The div itself is positioned by using CSS and that is something outside of scope of this article but the css file itself is included in the source code which you can download below so feel free to open it and see how the centering and overlay divs were achieved.

Notes and Source code

Remember to add the ScriptManager control on your page and to reference the WebService file. Otherwise you won't be able to call the WebService using Javascript. Also another important thing to notice is that when you create a WebService file initially by default the [System.Web.Script.Services.ScriptService] on the top of the class file is commented out. Make sure you uncomment it or once again you won't be able to call any of the methods from Javascript. Last but not least make sure that each method you create in a WebService has to have a [WebMethod] tag above the method declaration or..you guessed already, you won't be able to call that method otherwise. In general I hope you liked the article and that it will be of some use for you. Feel free to comment and leave opinions or ask further questions regarding the article. I will do my best to explain everything to you.

Here is the source code to the application so you can test it locally. Included is the SQL CREATE Northwind database file which you will run to create the copy of Northwind that I'm using. Why so? Well obviously Microsoft decided to move their SQL sample databases to codeplex and there's still no official release of Northwind there. I don't want to link to other websites so I've decided to do it like this. Download the source, unpack it using Winrar or some similiar program, create the Northwind database, open up the project (don't forget to change the connection string inside the web.config file to point to your SQL Server and use your username and password.

Don't forget to change the connection string in web.config or otherwise the sample will throw an error.

http://nullskull.com/FileUpload/-1672755420_AjaxWSTable.zip

Happy coding!
Sasha Kotlo.

By Sasha Kotlo   Popularity  (3210 Views)
Picture
Biography - Sasha Kotlo
- C#.NET Developement - SQL Server Database Applications - N-Tier Architecture Developement - Object Oriented Programming - XHTML/CSS - Javascript, jQuery - Photoshop, Illustrator, After Effects
My Website