asp:Feature

LANGUAGES: C#

ASP.NET VERSIONS: 3.5

 

Grid Groupies

ASP.NET 3.5 s ListView and LinqDataSource Controls

 

By Matt Berseth

 

From the Web to the desktop, data grids play an important role in many applications. Fortunately, Microsoft understands this, and with each release of .NET they include new techniques, tools, and UI widgets to help developers build more and more powerful grids. In this article we ll explore ASP.NET 3.5 s ListView and LinqDataSource controls, along with LINQ to SQL, and see how these three components can be combined to build a data grid that supports grouping.

 

Before diving in to the technical details, let s first take a look at the final product. The data grid in Figure 1 displays the Northwind product data grouped by the products assigned category. Each product grouping shows an image button, category name, and the number of products that belong to the category. When the image button is clicked, the product records for the category are displayed. Figure 1 shows how the data grid looks when the Produce and Meat/Poultry categories have been expanded.

 


Figure 1: Products by category.

 

Creating the Product and Category Classes

The first step when building a LINQ to SQL application is to create the object classes that represent the data source. Our grouping grid interacts with the Products and Categories tables, so we can begin by creating LINQ to SQL classes for these items. The 2008 versions of both Visual Studio and Visual Web Developer use the Object Relational Designer (O/R Designer for short) as a design surface for creating LINQ to SQL classes. To get started with the O/R Designer, click the Add New Item button found within Visual Studio s Project menu. This brings up a dialog box with all the available templates; select the LINQ to SQL Classes template and give it a name of NorthwindDataContext.dbml. Once the dialog box is dismissed, the LINQ to SQL designer will open and you can use the Server Explorer to navigate to your SQL Server s Northwind database. After connecting Server Explorer to the database, drag the Categories and Products tables to the design surface. When finished, you ll see a class diagram similar to Figure 2.

 


Figure 2: The Products and Categories object model.

 

As new tables are dropped on the design surface, data type, constraint, and relationship metadata are read from the database and converted into an object model. Tables become classes, columns become properties, and SQL data types like varchar become .NET strings. Even foreign key relationships are translated, allowing data model relationships to be navigated using the object model. Along with classes for the Products and Categories tables, the designer also created the NorthwindDataContext LINQ to SQL class, which acts as the entry point to the Northwind database for all LINQ to SQL operations.

 

The code sample in Figure 3 shows how the newly generated NorthwindDataContext, Products, and Categories classes work together within a LINQ query. The query retrieves all product records belonging to the Produce category. When the query executes, the LINQ to SQL runtime takes care of establishing the database connection, translating the LINQ query into a SQL SELECT statement, and mapping the result to instances of the Products class. The LINQ query is specified in terms of the object model; no special syntax is required to navigate to a product s associated category. Instead, the Products to Categories foreign key relationship is traversed using the Category property of the Products class.

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.Linq;

namespace LINQ_to_SQL_Examples

{

 class Program

 {

   static void Main(string[] args)

   {

     using (NorthwindDataContext ndc = new NorthwindDataContext())

     {

   // Find all product objects that belong to the Produce category

       var products =

         from p in ndc.Products

         where p.Category.CategoryName == "Produce"

         select p;

         foreach (Product p in products)

         {

           Console.WriteLine(string.Format("Product: {0}", p.ProductName));

         }

     }

   }

 }

}

Figure 3: NorthwindDataContext, Products, and Categories classes working together within a LINQ query.

 

Grouping Data with the LinqDataSource

After the LINQ to SQL classes are created, they can be used to configure the LinqDataSource for use by the grouping grid. Unlike ASP.NET s SqlDataSource, the LinqDataSource does not connect directly to a database. Instead, the LinqDataSource s ContextTypeName and TableName attributes are used to tie the LinqDataSource to the LINQ to SQL classes (the LinqDataSource supports connecting to other data sources, as well, but for this example we are focusing only on using it with LINQ to SQL). To bind the LinqDataSource to the LINQ to SQL classes, the ContextTypeName is set to the type name of the DataContext, NorthwindDataContext for the running example, and the TableName attribute to Products.

 

By default, the LinqDataSource retrieves all properties from the data source when selecting data. If all properties are not required, the Select attribute can be used to specify a subset. Like a SQL SELECT statement, properties can be renamed using aliases and new properties can be derived or calculated from other properties. When the Select attribute contains multiple properties or an alias, the contents of the Select attribute must be enclosed within the new(..) method. The new method instructs the LinqDataSource to dynamically create a class containing only the properties specified within the Select attribute. Instances of this dynamic class are returned by the LinqDataSource when the data is selected. The following LinqDataSource demonstrates using the Select attribute to calculate a derived property, as well as rename an existing one:

 

<asp:LinqDataSource

 ID="LinqDataSource1" runat="server"

 ContextTypeName="NorthwindDataContext"

 TableName="Products"

 Select="new(UnitPrice * UnitsInStock As InStockTotalPrice,

              ProductName As Name)"

/>

 

A control binding to this data source can access the product data using the InStockTotalPrice and Name properties of the dynamically created class. When the LinqDataSource is used to group data, two special properties (Key and It) are made available to provide access to the grouped data. The Key property is used to retrieve the grouping values used to consolidate the records. The collection of items belonging to a grouping are referenced using the It property. When the data is grouped, aggregate functions, including Count, Sum, Min, and Max, can be used to perform calculations on the items within each grouping.

 

The following LinqDataSource makes use of the Key and It properties, as well as the Count aggregate function. The records from the Products table are grouped by category by setting the GroupBy attribute to the Products Category.CategoryName property path. For each grouping, the Select attribute uses the Key property to retrieve the grouping value, Count to calculate the number of products belonging to the category, and It to reference the collection of products. The Key and It properties provide a nice way to access the natural master-detail relationship existing between the grouping summary information and the items belonging to each group. In the following sample, the Key and It properties are aliased to make it clear which values these properties hold:

 

<asp:LinqDataSource

 ID="LinqDataSource1" runat="server"

 ContextTypeName="NorthwindDataContext"

 TableName="Products"

 Select="new(Key As CategoryName, Count() As Count,

              It As Products)"

 GroupBy="Category.Categoryname"

/>

 

Creating the ListView

Two ListView controls, one nested inside the other, are used to render the HTML TABLE element for the grouping grid (see Figure 4). The outer ListView binds to the LinqDataSource described previously and sets up the grid s core structure by identifying the columns that make up the grid s header row. The ItemTemplate of the outer ListView uses the CategoryName and Count properties to generate the grouping rows. The inner ListView binds to the Products property and generates a row for each product record.

 

<asp:ListView ID="ListView1" runat="server"

 DataSourceID="LinqDataSource1">

 <LayoutTemplate>

   <table id="products">

     <!-- header row -->

     <tr>

       <th></th>

       <th>Name</th>

       <th>Quantity Per Unit</th>

       <th>Unit Price</th>

       <th>In Stock</th>

       <th>On Order</th>

     </tr>

     <!-- placeholder for databound items -->

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

   </table>

 </LayoutTemplate>

 <ItemTemplate>

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

     <th>

       <!-- image used to expand/collapse product groups -->

       <img src="plus.png" onclick="toggleGroup(this,

        '<%# Eval("Count") %>');" />

     </th>

     <th colspan="5">

        <!-- display the CategoryName and Count -->

       <%# Eval("CategoryName")%>

         (<%# Eval("Count")%> Products)

     </th>

   </tr>

   <!-- bind the inner ListView to Products collection -->

   <asp:ListView ID="ListView2" runat="server"

    DataSource='<%# Eval("Products") %>'>

     <LayoutTemplate>

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

     </LayoutTemplate>

     <ItemTemplate>

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

         <td></td>

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

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

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

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

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

       </tr>

     </ItemTemplate>

   </asp:ListView>

 </ItemTemplate>

</asp:ListView>

Figure 4: Two ListView controls render the HTML TABLE element for the grouping grid.

 

The page hosting the grouping grid contains a JavaScript function that handles toggling the visibility of the groups (see Figure 5). This function is configured to run when a product grouping image button is clicked. The input to this function is a reference to the HTML IMG element that displays the plus or minus image, as well as the number of products belonging to the group. The function first obtains references to the containing TABLE element using the ASP.NET AJAX $get function. Once the TABLE element is retrieved, the number of rows that need to be hidden or displayed is calculated by adding the number of elements within the group to the current offset of the grouping header row within the containing TABLE. Next, the individual rows are looped through and the hidden cascading style sheet class is either added or removed using the ASP.NET AJAX Sys.UI.DomElement.toggleCssClass function. The hidden class uses style sheet rule to prevent the browser from rendering the rows, even though they exist in the TABLE s markup. Finally, the image button is updated to reflect the new state of the group, either expanded or collapsed.

 

function toggleGroup(img, numberOfRows){

 //  get a reference to the row and table

 var tr = img.parentNode.parentNode;

 var table = $get('products');

 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);

 //  add/remove the hidden class from the rows

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

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

 }

 //  toggle the image reference

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

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

 }

 else{

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

 }

 //  update the src with the new value

 img.src = src;

}

Figure 5: Toggling the visibility of the groups.

 

Conclusion

.NET 3.5 is full of new tools, techniques, and UI widgets that can help developers build feature-rich data grids. This article demonstrates how LINQ to SQL can be combined with the ASP.NET 3.5 LinqDataSource and ListView controls to create a grouping grid.

 

The files accompanying this article are available for download.

 

Matt Berseth is a Sr. Software Engineer for a top automotive marketing firm located in Jacksonville, FL. He started his career as a developer at Microsoft and continued it with a large transportation management corporation before moving on to his current position. Matt attended North Dakota State University, where he earned his Master s degree in Software Engineering. In his free time, Matt enjoys reading, writing, hanging out with his friends and family, and watching whatever is on ESPN. You can visit Matt online at http://mattberseth.com or e-mail him at mailto:matt@mattberseth.com.