This small web application has following features:
- Auto suggest list box [AutoList.js]
- Auto select text.
- Scrolling list item either one by one or on the basis of defined page size.
- Multicolor list item.
- Getting result in the xml form directly from SQL Server using ‘FOR XML’ and to process it to populate list box or to send back to client.
- Client side population of list box from xml.
- Getting result in xml form by using callback function (AJAX).
- Client side XML transformation using XSLT.
- Client side wait/process message while processing data [ProcessMonitor.js].
Stored Procedures :
(a) sp_GetAllCategories
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE sp_GetAllCategories
AS
SET NOCOUNT ON
--- get all categories
Select
CategoryId,
CategoryName
From
Categories
Order By
CategoryName
For XML Auto
--- get all products with related category
Select
CategoryId,
ProductId,
ProductName
From
Products
Order By
ProductName
For Xml Auto
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
(b) GetProductOrdersDetail
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE GetProductOrdersDetail @ProductID int
AS
SELECT
Orders.OrderId,
Convert(varchar,Orders.OrderDate,103) AS OrderDate,
Customers.CompanyName,
Customers.Country,
Convert(varchar,Orders.RequiredDate,103) AS ReqDate,
Od.Quantity,
Od.UnitPrice,
Amount = (Od.Quantity * Od.UnitPrice),
DiscountPerc = (100*convert(money,Od.Discount)),
Discount = (Od.Quantity * Od.UnitPrice)* convert(money,Od.Discount),
NetAmount=(Od.Quantity * Od.UnitPrice) - (Od.Quantity * Od.UnitPrice)* convert(money,Od.Discount)
FROM Orders
Inner Join
Customers
On
Customers.CustomerId = Orders.CustomerId
Inner Join
[Order Details] Od
On
Od.OrderID = Orders.OrderID
Where
Od.ProductID = @ProductID
FOR XML AUTO
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Function to read xml result using ExecuteXmlReader :
public void ExecuteReaderGetXmlDoc(string p_strSPName , SqlParameter[]
p_arrSqlParams, ref XmlDocument p_xmlDoc )
{
SqlCommand l_objCommand;
StringBuilder l_objStrBuilder;
XmlReader l_objXmlReader;
try
{
if (this.OpenConnection())
{
l_objCommand = new SqlCommand();
l_objCommand.Connection = _objConnection;
l_objCommand.CommandType = CommandType.StoredProcedure;
l_objCommand.CommandText = p_strSPName;
if (p_arrSqlParams != null)
{
this.AddCommandParameter(p_arrSqlParams, ref l_objCommand);
}
l_objXmlReader =(XmlReader) l_objCommand.ExecuteXmlReader();
l_objXmlReader.Read();
l_objStrBuilder = new StringBuilder();
// add root node
l_objStrBuilder.Append("<Root>");
while(l_objXmlReader.ReadState != ReadState.EndOfFile)
{
// append xmlNode
l_objStrBuilder.Append(l_objXmlReader.ReadOuterXml());
}
// close root node
l_objStrBuilder.Append("</Root>");
p_xmlDoc.LoadXml(l_objStrBuilder.ToString());
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
l_objCommand = null;
this.CloseConnection();
this._objConnection = null;
}
}
Function to populate list from xml [ Code behind ] :
private void PopulateCategoriesList(XmlNodeList
p_xmlCategoryNodeList,XmlDocument p_xmlDoc)
{
ListItem l_objListItem;
int i = 0;
try
{
foreach(XmlNode categoryNode in p_xmlCategoryNodeList)
{
l_objListItem = new ListItem();
l_objListItem.Value = categoryNode.Attributes.Item(0).Value ;
l_objListItem.Text = categoryNode.Attributes.Item(1).Value ;
// set item color
if (i % 2 == 0)
{
l_objListItem.Attributes.Add("class","ListItem"); }
else
{
l_objListItem.Attributes.Add("class","ListAltItem"); }
lstCategory.Items.Add(l_objListItem);
i += 1;
// remove categoryNode
p_xmlDoc.DocumentElement.RemoveChild(categoryNode);
}
}
catch(Exception ex)
{
throw ex;
}
}
Function to populate list from xml [ Client Side ] :
if(this.XmlNodeList == null)
{
alert("NodeList not assigned");
return false;
}
var nodeListLength = this.XmlNodeList.length;
this.ListCtrl.options.length = 0;
for(var i=0; i<nodeListLength; i++)
{
var objListItem = document.createElement("option") ;
objListItem.value = this.XmlNodeList.item(i).attributes(1).text;
objListItem.text = this.XmlNodeList.item(i).attributes(2).text;
// set item color
if (i%2 == 0)
{
objListItem.style.background = this.ITEM_COLOR;
}
else
{
objListItem.style.background = this.ALT_ITEM_COLOR; }
this.ListCtrl.add(objListItem);
}
this.ListCtrl.selectedIndex = -1;
Getting result from code behind function using AJAX :
function getProductOrders(p_intProductId)
{
try
{
if(p_intProductId != "")
{
// call displayProcess() before calling send() of XmlHTTP
Message.displayProcess("Searching orders ");
var strURL = "Demo.aspx?IsCallback=1&ProductId=" +
p_intProductId;
this.xmlhttp.Open("POST",strURL, false);
this.xmlhttp.onreadystatechange = process_OrdersResult; this.xmlhttp.send(null);
}
else
{
Message.display("Please select product !");
return false;
}
}
catch(e)
{
alert("Error in getProductOrders() : " + e.message);
}
}
Transform xml data using XSLT :
function transformXmlDoc()
{
try
{
document.getElementById("tdOrderList").innerHTML = this.xmlProductOrderDoc.transformNode(this.xslOrderDoc);
}
catch(e)
{
alert("Error in transformXmlDoc() : " + e.message);
}
}
Checking callback on Pageload :
if (!Page.IsPostBack && !IsCallback )
{
GetAllCategories();
}
else if (IsCallback)
{
int productId = Convert.ToInt32(Request.QueryString["ProductId"]);
GetProductOrdersDetail(productId);
}
Return result back to the client in xml form :
Response.Clear();
Response.ContentType = "text/xml";
if (l_objXmlDoc.SelectNodes("//Orders").Count > 0 )
{
Response.Write(l_objXmlDoc.OuterXml);
}
else
{
Response.Write("<Root></Root>");
}
Response.End();