ASP.Net : AutoList

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 transformation using XSLT.
9. 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();