LINQ -Grid Grouping

Building a Grouping Grid with the ASP.NET 3.5 LinqDataSource and ListView Controls


I haven't played too much with ASP.NET 3.5's new LinqDataSource control yet - so I figured it was about time I did a little research and checked it out.  Typically, I like to document my research by creating a small prototype application focused on a handful of the new features that I am investigating.  I find building a reference application is helpful because it tends to drive out some of those nasty implementation details that somehow remain hidden if I just browse the documentation or read a reference book. 

So for a recent research project, I decided I would look into the LinqDataSource's control GroupBy and OrderGroupBy attributes to see if they could be used to help me build a grid that supports grouping.  Below is the final screen shot for my reference application.  The grid shows the rows in the Northwind Orders table - grouped by the customer that placed the order.  The group of orders for each customer can be expanded/collapsed by clicking on the plus/minus icon (no postback required).  Read on if you are interested in the details.  If you download the code, make sure to update the connection string in the web.config to point to your Northwind database. 

Live Demo (IE6, IE7 and FF) | Download

image

The LinqDataSource Control

Below is what MSDN has to say about the LinqDataSource control.  In a nutshell, the control is a nice way to leverage the Linq features in your web apps.

image

Creating the Northwind DataContext

The LinqDataSource requires a context object that represents the data source.  For my scenario I want to access the rows in the Orders table of the Northwind database, so my first step was to use Visual Studio's designer tools to create the context object for my Northwind database.  To do this I opened the 'Add New Item' dialog and selected the 'LINQ to SQL Classes' template.  I named my template Northwind.dbml and clicked Add.

image

If you connect Visual Studio's Server Explorer to your database, it becomes really easy to create entities from your the tables in your database.  So once the designer was opened for the Northwind.dbml file, I opened the Server Explorer (CTRL + ALT + S), navigated out to my local SQL Server where I have my Northwind database.  I expanded the Tables node in the Northwind tree and dragged the Orders table onto the design surface.

image image

Configuring the LinqDataSource

After the Northwind DataContext was setup, I next moved on to configuring my LinqDataSource that I am using to fill the grid.  I want to display all of the orders grouped by the customer that placed the order.  To help me accomplish this, I configured the LinqDataSource as follows

image

The ContextTypeName points to the Northwind DataContext object that I created in the previous step and the TableName attribute is set to the table within the DataContext that I want to be fetching records from - Orders for this sample.  The Select and GroupBy attributes is where it gets a little more interesting.  Like I mentioned earlier, I was to group the orders by the customer that placed the order.  So I set the GroupBy attribute to the customerid column.  If you look again at the screen shot for my grid, I am also displaying the customerid and the number of orders in the group using the following format: '{0} ({1} Orders)' where {0} is the customerid  and {1} is the number of orders they placed.  I want to use these values in my databinding expressions so I need to make sure to use them in the select statement.  You can reference the grouped by property in the select statement by using the well known Key property.  So when the orders for ALFKI are bound to the grid, Eval('Key') resolves to 'ALFKI' and Eval('Count') resolves to '6'.

The other interesting item in my Select statement is the use of the It keyword.  The It keyword provides you with access to the individual items of the group - so in this scenario the orders for the individual groups.  So for customer ALFKI, here are the values of the Key, Count and Items properties.

Key Count Items
ALFKI 6 Order { ID='10643', OrderedDate='08/25/1997' ... }
Order { ID='10692', OrderedDate='10/31/1997' ... }
Order { ID='10702', OrderedDate='11/24/1997' ... }
Order { ID='10835', OrderedDate='02/12/1998' ... }
Order { ID='10952', OrderedDate='04/27/1998' ... }
Order { ID='11011', OrderedDate='05/07/1998' ... }

Configuring the ListView

After my LinqDataSource was all setup I started working on creating the ListView and its data binding expressions that are used to fill my grid.  I thought the easiest way to produce the markup for the grouping was to use two ListView's, one nested inside the other.  The outer ListView is responsible for rendering the grouping header rows and the nested ListView renders the orders belonging to each of the groups.

So I first created the outer ListView as follows.  I set the DataSourceID to point to the LinqDataSource we configured in the previous section.  Then I used the LayoutTemplate to define the header columns of the table and the ItemTemplate to render the header rows for each of the groups.  As you can see, I am referencing the Key and Count properties of the LinqDataSource using the standard DataBinding syntax. 

 image

Next, I added a nested ListView to the outer ListView for rendering the individual orders.  For the nested ListView, I bound the DataSource property to the Items property of the LinqDataSource and I used regular DataBinding expressions to reference the individual properties of each of the orders.

image 

Expanding and Collapsing the Groups

Finally, to put the finishing touches on the grid, I need a way to expand/collapse the groups by clicking on the plus/minus icon.  To do this, I added a JavaScript function to the page called toggleGroup.  This function applies and removes a css class called hidden the rows to control their visibility. 

image

When the grid first renders, the hidden class is applied to all of the data rows.  When the user clicks the plus icon, the following bit of JavaScript is run and uses the addCssClass/removeCssClass ASP.NET AJAX functions to add or remove the hidden class from the rows.  Finally, the function takes care of updating the plus/minus icon.

image


Code

<%@ Page Language="C#" %>


<!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>Untitled Page</title>

    <script runat="server" type="text/C#">

        /// <summary>

        /// 

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="args"></param>

        protected void PagerCommand(object sender, DataPagerCommandEventArgs e)

        {

            switch (e.CommandName)

            {

                case "Next":

                    //  guard against going off the end of the list

                    e.NewStartRowIndex = Math.Min(e.Item.Pager.StartRowIndex + e.Item.Pager.MaximumRows, e.Item.Pager.TotalRowCount - e.Item.Pager.MaximumRows);

                    e.NewMaximumRows = e.Item.Pager.MaximumRows;

                    break;

                case "Previous":

                    //  guard against going off the begining of the list

                    e.NewStartRowIndex = Math.Max(0, e.Item.Pager.StartRowIndex - e.Item.Pager.MaximumRows);

                    e.NewMaximumRows = e.Item.Pager.MaximumRows;

                    break;

                case "Last":

                    //  the

                    e.NewStartRowIndex = e.Item.Pager.TotalRowCount - e.Item.Pager.MaximumRows;

                    e.NewMaximumRows = e.Item.Pager.MaximumRows;

                    break;

                case "First":

                default:

                    e.NewStartRowIndex = 0;

                    e.NewMaximumRows = e.Item.Pager.MaximumRows;

                    break;

            }


        }


        /// <summary>

        /// 

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="args"></param>

        protected void LvSorting(object sender, ListViewSortEventArgs args)

        {

            this.lds.OrderGroupsBy = string.Format("Key {0}", args.SortDirection);

        }


        /// <summary>

        /// 

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="args"></param>

        protected void LvSorted(object sender, EventArgs args)

        {

            ImageButton btn = this.lv.FindControl("btnSort") as ImageButton;

            btn.ImageUrl = string.Format("~/_assets/img/{0}", this.lv.SortDirection == SortDirection.Ascending ? "asc.png" : "desc.png");

        }

        

    </script>

    <style type="text/css">

        .grid

        {

         border:solid 2px #a5a4bd;        

         font-family:Tahoma;

         font-size:12px;

         width:600px;

        }

        .grid H2

         {

         background:#8988a5 url(_assets/img/title-bg.gif);

         border-bottom:solid 1px #57566f;

         height:28px;

         line-height:28px;

         margin:0px;

         padding:0px 0px 0px 5px;

         color:#fff;

         font-size:15px;

         }

         .grid TABLE

         {

         width:600px;

         }

         

         /* grid headers */

         .grid #orders .head TH

         {

         background:#e9e9eb url(_assets/img/header-bg.gif);

         height:24px;

         line-height:24px;

         padding-left:6px;

         color:#666666;

         text-align:left;

         }

         .grid #orders .head TH.first

         {

         /* get rid of the first slash */

         background:#e9e9eb url(_assets/img/header-bg.gif) -3px;

         width:20px;

         } 

         

         /* group header */

         .grid #orders .group TH

         {

         padding:10px 0px 2px 0px;

         color:#666666;

         text-align:left;

         border-bottom:solid 2px #a5a4bd;

         } 

         .grid #orders .group TH.first

         {

         text-align:center;        

         border-bottom:none;

         }              

         

         /* data items */                                       

         .grid #orders .item TD

         {

         padding:3px 0px 3px 6px;

         border-bottom:solid 1px #eae9e1; 

         color:#222222;      

         } 

         

         /* pager */    

        .grid #pager .pager TD

        {

         height:40px;

        }  

        .grid #pager .pager .commands

        {

         height:40px;

            padding:4px 0px 0px 25px;

        }               

        .grid #pager .pager .info

        {

            padding:4px 25px 0px 0px;

         text-align:right;

        }       

        .hidden

        {

         display:none;

         visibility:hidden;

        }      

    </style>

    <script type="text/javascript">

        function toggleGroup(img, numberOfRows){

            //  get a reference to the row and table

            var tr = img.parentNode.parentNode;

            var table = $get('orders');

            var src = img.src;

            

            //  do some simple math to determine how many

            //  rows we need to hide/show

            var startIndex = tr.rowIndex + 1;

            var stopIndex = startIndex + parseInt(numberOfRows);

            

            //  if the img src ends with plus, then we are expanding the

            //  rows.  go ahead and remove the hidden class from the rows

            //  and update the image src

            if(src.endsWith('plus.png')){

                for(var i = startIndex; i < stopIndex; i++){

                    Sys.UI.DomElement.removeCssClass(table.rows[i], 'hidden');

                }

            

                src = src.replace('plus.png', 'minus.png');

            }

            else{

                for(var i = startIndex; i < stopIndex; i++){

                    Sys.UI.DomElement.addCssClass(table.rows[i], 'hidden');

                }

                            

                src = src.replace('minus.png', 'plus.png');

            }

            

            //  update the src with the new value

            img.src = src;

        }

    </script>

</head>

<body>

    <form id="frm" runat="server">

        <div>

            <asp:ScriptManager ID="scriptManager" runat="server" />

            <asp:LinqDataSource 

                ID="lds" runat="server"

                ContextTypeName="NorthwindDataContext"

                TableName="Orders"  

                Select="new(Key, Count() as Count, It as Items)" GroupBy="customerid"

                AutoPage="true" AutoSort="true"

            />            

            <asp:UpdatePanel ID="upd" runat="server">

                <ContentTemplate>

                    <asp:ListView ID="lv" runat="server" DataSourceID="lds" OnSorting="LvSorting" OnSorted="LvSorted">

                        <LayoutTemplate>

                            <div class="grid">

                                <h2>Orders by Customer <asp:ImageButton ID="btnSort" runat="server" ImageUrl="~/_assets/img/sort_asc.gif" ImageAlign="AbsMiddle" CommandName="Sort"/></h2>

                                <table id="orders" cellpadding="0" cellspacing="0">

                                    <tr class="head">

                                        <th class="first"></th>

                                        <th>ID</th>

                                        <th>Date Ordered</th>

                                        <th>Date Required</th>

                                        <th>Freight</th>

                                        <th>Date Shipped</th>

                                    </tr>

                                    <tr id="itemPlaceholder" runat="server" />                         

                                </table>

                                <table id="pager" cellpadding="0" cellspacing="0">

                                    <tr class="pager">

                                        <asp:DataPager ID="pager" runat="server" PageSize="8">

                                            <Fields>

                                                <asp:TemplatePagerField OnPagerCommand="PagerCommand">

                                                    <PagerTemplate>

                                                        <td class="commands">

                                                            <asp:ImageButton ID="btnFirst" runat="server" CommandName="First" ImageUrl="~/_assets/img/first.gif" AlternateText="First Page" ToolTip="First Page" />

                                                            <asp:ImageButton ID="btnPrevious" runat="server" CommandName="Previous" ImageUrl="~/_assets/img/prev.gif" AlternateText="Previous Page" ToolTip="Previous Page" />    

                                                            <asp:ImageButton ID="btnNext" runat="server" CommandName="Next" ImageUrl="~/_assets/img/next.gif" AlternateText="Next Page" ToolTip="Next Page" />

                                                            <asp:ImageButton ID="btnLast" runat="server" CommandName="Last" ImageUrl="~/_assets/img/last.gif" AlternateText="Last Page" ToolTip="Last Page" />                                                                                                           

                                                        </td>

                                                        <td class="info">

                                                            Page 

                                                            <b>

                                                                <%# Container.TotalRowCount > 0 ? Math.Ceiling(((double)(Container.StartRowIndex + Container.MaximumRows) / Container.MaximumRows)) : 0 %>

                                                            </b>

                                                            of

                                                            <b>

                                                                <%# Math.Ceiling((double)Container.TotalRowCount / Container.MaximumRows)%>

                                                            </b>

                                                            (<%# Container.TotalRowCount %> items)  

                                                        </td>                                                          

                                                    </PagerTemplate>

                                                </asp:TemplatePagerField>

                                            </Fields>

                                        </asp:DataPager>

                                    </tr>                                 

                                </table>

                            </div>

                        </LayoutTemplate>

                        <ItemTemplate>

                            <tr id="row" runat="server" class="group">

                                <th class="first">

                                    <img src="_assets/img/plus.png" alt='Group: <%# Eval("Key")%>' onclick="toggleGroup(this, '<%# Eval("count") %>');" />

                                </th>

                                <th colspan="5"><%# Eval("Key")%> (<%# Eval("Count") %> Orders)</th>

                            </tr>

                            <asp:ListView ID="lvItems" runat="server" DataSource='<%# Eval("Items") %>'>

                                <LayoutTemplate>

                                    <tr runat="server" id="itemPlaceholder" />

                                </LayoutTemplate>

                                <ItemTemplate>

                                    <tr id="row" runat="server" class="item hidden">

                                        <td class="first"></td>

                                        <td><%# Eval("orderid") %></td>

                                        <td><%# Eval("orderdate", "{0:MM/dd/yyyy}")%></td>

                                        <td><%# Eval("requireddate", "{0:MM/dd/yyyy}")%></td>

                                        <td><%# Eval("freight", "{0:c}") %></td>

                                        <td><%# Eval("requireddate", "{0:MM/dd/yyyy}")%></td>

                                    </tr>

                                </ItemTemplate>

                            </asp:ListView>

                        </ItemTemplate>

                    </asp:ListView>

                </ContentTemplate>

            </asp:UpdatePanel>            

        </div>

    </form>

</body>

</html>

That's it.  Enjoy!



Comments