ListView Grouping On Demand using AJAX and stored-procedure

Matt Berseth has a series of tutorials on building a Grouping Grid with the ASP.NET 3.5 LinqDataSource and ListView Controls. It's quite a good control that able to display the data in master-detail format.

While using LinQ can help you to encapsulate the database layer, it might incur additional workload. And not every coder can write good LinQ query, thus it turns out to be an ugly resource-hunger monster. Personally, i found it's not easy to fine-tune when u need to reduce the page loading time after the data grows huge, if compare with ADO .NET using stored-procedure. At least, you can analyse the execution plan.

You can reduce the page size through paging, but in some scenario, your client might prefer loading all the data in one time (a bit ridiculous though). Then AJAX comes into the picture.


Muhammad Mosa has a series of tutorials on Building a grouping Grid with GridView and ASP.NET AJAX toolkit CollapsiblePanel


By combining the two, we can create Grouping ListView using AJAX and stored procedure.
1) we create the stored procedure to create the grouping data, with the help of GROUPING SETS.

Select a.*, b.OrderDate, b.ShipName, 
c.FirstName + ' ' + c.LastName as fullName
from
(
 SELECT CASE WHEN (GROUPING(CustomerID) = 1) THEN NULL--'...'--'ALL CustomerID'
    ELSE ISNULL(CustomerID, 'UNKNOWN')
     END AS CustomerID,
     CASE WHEN (GROUPING(EmployeeID) = 1) THEN NULL--'...'--'ALL EmployeeID'
    ELSE ISNULL(EmployeeID, 'UNKNOWN')
     END AS EmployeeID,
     CASE WHEN (GROUPING(OrderID) = 1) THEN NULL--'ALL OrderID'
    ELSE ISNULL(OrderID, 'UNKNOWN')
     END AS OrderID,
     COUNT(*) as [itemCount],
     SUM(freight) AS totalFreight
 FROM #temp l
 GROUP BY GROUPING SETS
 (
  (CustomerID, EmployeeID, OrderID),
  (CustomerID, EmployeeID),
  (CustomerID),
  ()
 )
) a

2) Then we need a extended AJAX toolkit CollapsiblePanel from Muhammad Mosa's article.

3) Embed the newly extended CollapsiblePanel in the ItemTemplate of the host listview.

4) Next we need to invoke the nested listview through WebMethod.

[System.Web.Services.WebMethod()]
public static string GetEmployees(string sParams)
{
    Page page = new Page();
    // restart Casini build-in IIS if error 
    COM.UsrCtrl_nestedListView ctl = (COM.UsrCtrl_nestedListView)page.LoadControl(PAGE_EMPLOYEE);
    page.Controls.Add(ctl);
    ctl.Params = sParams;
    System.IO.StringWriter writer = new System.IO.StringWriter();
    HttpContext.Current.Server.Execute(page, writer, false);
    string output = writer.ToString();
    writer.Close();
    return output;
}

5) Eventually we have our final result.

Few things to take note:
i) ToolkitScriptManager has to enable the PageMethods

ii) Since host listView and nested listview are 2 separate html tables, we have to enforce the CSS table-layout Property.

You can get the source-code here (ListViewGrouping_OnDemand.zip).
You can download the Northwind database through Microsoft Download Center.
If you want to customize the CSS of the listView,  this article is a great place to start with.