CodeSnip: Pivot Tables with ADO.NET and Display in a DataGrid Paged Horizontally: ASP Alliance


CodeSnip: Pivot Tables with ADO.NET and Display in a DataGrid Paged Horizontally

page

by Steve Sharrock
Feedback

Average Rating:

Views (Total / Last 10 Days): 54759/ 82

Overview

[Download Code]
I was recently asked by a client to prototype a DataGrid showing a feature comparison of several products. The underlying database table contained a row for each product with columns that describe the product name and the various attributes and features of the product.

Like many online comparison grids, the client wanted the products listed across the top horizontally, with the features and attributes listed vertically with their description down the left side. Since there were a large number of products, the desire was to navigate horizontally to each subset of products while keeping the feature names anchored in the first column of the DataGrid. For consistency within the site, the client also wanted the selection of Products to use paging, rather than horizontal scrolling.

The first step is to pivot the table such that each column represents the product name found in the first column of each row of the input table, and each row represents the individual features of all products from the original table's columns. One solution might be to implement this transform as a stored procedure on the database server, and indeed there are several references available for this. However, I was using a Web Service to obtain the product data in the form of a DataSet, so my solution simply required me to pivot the appropriate table from the DataSet. This is a fairly straight-forward transform detailed in the next page of this article.

The next step is to create a DataGrid that navigates horizontally to the selected set of products, while anchoring the first column that contains the description of each feature. The choice was to either prune the pivoted table of all unwanted columns for each page change letting the DataGrid bind all columns or dynamically create the DataGrid for each page specifying the appropriate BoundColumns. The latter approach is outlined in the third page of this article.

A link is provided at the top of each page to download the source code for the demo of these techniques. Use the link below to view a very simple demonstration version of this prototype that shows the raw input table, the complete pivot table, and the final paged comparison DataGrid.

View Pivot Table DataGrid Demo

The next page outlines the steps I used to pivot the DataTable containing the Product rows in preparation for binding the DataGrid.

Pivot DataTable

[Download Code]
The output of the pivot method is a new DataTable with one row for each column of the input table, and one column for each Product represented by the rows of the input table. To simplify this example, we assume that the "Product" column is the first column of each row (Column[0]), and the remaining columns represent the features.

The first step is to create the new DataTable and its schema from the rows of the input table. The first column is for the Feature description, and the remaining columns are the Product names found in each row of the input. In the following code snippet, source refers to the input table and dest is the new pivoted table.

 
DataTable dest = new DataTable("Pivot" + source.TableName ); 
// 1st column is for the feature descriptions 
dest.Columns.Add( "Features" );
// the remaining columns are Product Description 
// from each source table row (1st column) 
foreach( DataRow r in source.Rows ) 
dest.Columns.Add( r[0].ToString() ); 
// assign each row the Product name

Now that we have the appropriate columns defined in the schema of the destination table, the next step is to create a DataRow for each feature defined in the columns of the source table, excluding the first column representing the Product name.

 
for( int i = 0; i < source.Columns.Count - 1; i++ ) 
dest.Rows.Add( dest.NewRow() );

The final step is the actual transform of the columns from the source rows into the destination table. Since each table now represents a two dimensional array of its rows and columns, a very simple transform of all columns and rows could be performed by the following nested for loop.

for( int r = 0; r < dest.Rows.Count; r++ ) 
for( int c = 0; c < source.Columns.Count; c++ ) 
dest.Rows[r][c] = source.Rows[c][r];

However, this simple loop doesn't account for the first column of the destination representing the Feature description from the source columns. So we modify the code slightly as shown in this code snippet.

for( int r = 0; r < dest.Rows.Count; r++ ) 
for( int c = 0; c < source.Columns.Count; c++ ) 
if ( c == 0 ) dest.Rows[r][0] = source.Columns[r + 1].ColumnName; 
else dest.Rows[r][c] = source.Rows[c - 1][r + 1];

Now that we have our pivoted DataTable ready to display, we could simply bind it to a DataGrid; however, our client wants the DataGrid to page, rather than scroll horizontally to show a limited number of products on each page. The technique used to implement this feature is illustrated on the next page.

Create the DataGrid

[Download Code]
The client wants to navigate thru the DataGrid Products horizontally and anchor the first column containing the Feature descriptions. We could implement horizontal scrolling using a DIV tag, but anchoring the first column becomes more problematic. Since our client wants to Page thru the Products, we can simply bind the appropriate columns to the DataGrid based on the DataGrids's current PageIndex. Since the DataGrid doesn't really support this type of paging, we'll implement our own Page navigation buttons, and maintain the current PageIndex in the ViewState.

In the code snippets below, assume there is a "Previous Page" button named prevBtn, and a "Next Page" button named nextBtn. There is also a property named PageIndex that maintains the currently selected page index and is set when the previous and next buttons are clicked. When the page loads the first time, the PageIndex is set to zero and the BindHorzGrrd method is called. In subsequent PostBacks, the nextBtn and prevBtn modify the current PageIndex and then call the BindHorzGrid method.

The first part of the BindHorzGrid method simply sets up some values that we'll need to decide which columns should be displayed for the current PageIndex. We also enable/disable our navigation buttons depending on the presence of either previous pages or next pages.

private void BindHorzGrid() 
{ 
const int pageSize = 3; 
// constant number of columns displayed in each page 
int colCount = pivotTbl.Columns.Count - 1; 
int pageMax = colCount / pageSize; 
if ( PageIndex >= pageMax ) PageIndex = pageMax; 
else if ( PageIndex < 0 ) PageIndex = 0; 
prevBtn.Enabled = ( PageIndex > 0 ); 
nextBtn.Enabled = ( PageIndex < pageMax );

The remainder of this method creates the DataGrid's BoundColumns that reflect the correct columns from our pivoted DataTable. We first clear the Columns collection and then add the first column representing the Features. The remaining columns are added starting with the (zero based) PageIndex * pageSize (number of columns per page). The final step is to supply the DataSource and then DataBind the grid and our pivot table.

pageGrid.Columns.Clear(); 
// frist create the "anchored" column[0] -- Product 
BoundColumn c = new BoundColumn(); 
c.HeaderText = pivotTbl.Columns[0].ColumnName; 
c.HeaderStyle.Font.Bold = true; 
c.DataField = pivotTbl.Columns[0].ColumnName; 
c.ItemStyle.Font.Bold = true; 
pageGrid.Columns.Add( c ); 
// now create the bound columns for this page's set of columns 
int count = 0; 
for( int i = PageIndex * pageSize; count < pageSize && i < colCount; i++, count++ )
 { 
c = new BoundColumn(); 
c.HeaderStyle.Width = Unit.Pixel( 100 ); 
c.ItemStyle.Width = Unit.Pixel( 100 ); 
c.HeaderText = pivotTbl.Columns[ i + 1 ].ColumnName; 
c.DataField = pivotTbl.Columns[ i + 1].ColumnName; 
pageGrid.Columns.Add( c ); 
} 
// now bind the grid to our new bound columns 
pageGrid.DataSource = pivotTbl; 
pageGrid.DataBind(); 
}

If you download the source code, you'll see that I depend on formatting the columns using the DataGrid's ItemDataBound event handler. Since all of the pivot table's DataColumns are of type String, I may need to parse the data value depending on the Column being bound.

Summary

[Download Code]
This is only one of many approaches to solve this problem. I like this approach because it is quick and easy to implement. My client was able to integrate the prototype demo using their live data with very little effort. Perhaps in the future I may generalize this into a User Control or Custom Control.

Use the link below to view a simple demonstration version of the Pivot Table and Horizontal Paged DataGrid.

View Pivot Table DataGrid Demo