This small web application has following features: 1. Auto suggest list box [AutoList.js] 2. Auto select text. 3. Scrolling list item either one by one or on the basis of defined page size. 4. Multicolor list item. 5. 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. 6. Client side population of list box from xml. 7. Getting result in xml form by using callback function (AJAX). 8. Client side XML tran
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 (
- 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();
No comments:
Post a Comment