CodeSnip: Pivot Tables with ADO.NET and Display in a DataGrid Paged Horizontally
page 3 of 4
by Steve Sharrock
Feedback
Average Rating: 
Views (Total / Last 10 Days): 55446/ 88

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.


View Entire Article

User Comments

Title: Resuelto facil   
Name: Demo Atencia
Date: 2013-01-09 2:41:23 AM
Comment:
Protected Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Dim tabla As DataTable
Dim tablapersonas As DataTable
tablapersonas = objcl.Lista_Personas_Bono(cboProyectos.SelectedValue)
'tabla=objeto datatable
GridView1.DataSource = PivotTable(tabla)
GridView1.DataBind()

End Sub
Public Function PivotTable(ByVal [source] As DataTable) As DataTable
Dim dest As New DataTable("Pivoted" + [source].TableName)

dest.Columns.Add(" ")

Dim r As DataRow
For Each r In [source].Rows
dest.Columns.Add(r(0).ToString())
Next r
Dim i As Integer
For i = 0 To ([source].Columns.Count - 1) - 1
dest.Rows.Add(dest.NewRow())
Next i

For i = 0 To dest.Rows.Count - 1
Dim c As Integer
For c = 0 To dest.Columns.Count - 1
If c = 0 Then
dest.Rows(i)(0) = [source].Columns((i + 1)).ColumnName
Else
dest.Rows(i)(c) = [source].Rows((c - 1))((i + 1))
End If
Next c
Next i
dest.AcceptChanges()
Return dest
End Function 'PivotTable

mas simple ni en la china... saludos
Title: Web Team   
Name: Mani Maran M
Date: 2012-09-24 12:14:49 AM
Comment:
This is really super.Thanks a lot for publishing this article....
Title: Broken source data link   
Name: Sebastian
Date: 2012-06-06 7:09:49 PM
Comment:
Hoping to see an answer could you please upload again and reload the source data link please.
Title: Missing source data   
Name: Sierk
Date: 2012-05-10 4:21:57 PM
Comment:
I know this is old, but the link to the source data is no longer available and I am trying to figure out what the source.TableName from DataTable dest = new DataTable("Pivot" + source.TableName ); is. Could you have the source code restored or add the missing parts?


Thanks
Title: Can U attach source code please   
Name: punk
Date: 2010-07-25 10:09:52 AM
Comment:
Can U attach souce code please, cos, there's an error on Download Code link.
Title: Some extra tools   
Name: redbull
Date: 2010-02-12 10:07:41 PM
Comment:
http://viblend.com/blog/post/2010/01/13/Creating-a-Pivot-Table-in-Silverlight-using-XAML.aspx
Title: Attachable to a queried datasource?   
Name: lduhon@me.com
Date: 2009-10-29 11:13:48 AM
Comment:
Thank for the informative article, though I do have one question. In your example, you've embedded your sample data in the code file. What needs to be changed to make use of this with a SqlDataSource instead of the embedded DataTable.

Thank you again.
Title: Display Coffee Table   
Name: blogcomment1@googlemail.com
Date: 2009-08-11 9:08:46 AM
Comment:
Hello. Thanks for the guide. But why it always say that there's an error?!. Thanks and have a nice day. :)
Title: plz help anyone   
Name: sachin kumar
Date: 2008-09-25 3:17:32 AM
Comment:
Just I want that data come from any data source but my datagrid heading column name will come in xml data file
Title: I cannot be the only one to get this error   
Name: Ralphxyz
Date: 2008-09-11 10:46:02 AM
Comment:
Parser Error Message: Could not load type 'Pivot.Demo'.

Where oh where is "Pivot.Demo"

Certainly not in in the code download!
Title: display image problem   
Name: Rajeev Gangwar
Date: 2008-05-13 4:15:27 AM
Comment:
Please tell me is there any option for binding the image in the datagrid using pivot table. thanks
Title: Asp.Net(C#)   
Name: Rajasekar
Date: 2008-03-12 9:26:40 AM
Comment:
I need to bind the Repeated columns values in Single Columns
Title: Not useful to show images   
Name: garima
Date: 2008-01-28 4:04:44 AM
Comment:
how can we retrieve images from database and display on a grid page with the help of pivot table.
Title: Not Useful in Real World   
Name: Ron Jeremy
Date: 2007-11-27 8:53:25 PM
Comment:
WHo has pivot tables that don't have fields of the same value? You sample doesn't support datafields that have the same values for multiple fields.

The concept is OK, but you need to add some logic.
Title: Problem where the left col has multple of same values   
Name: Lee
Date: 2007-08-24 5:10:45 PM
Comment:
Great for very basic pivots. however i have data like this.
Date Qty failure
2007-04-23 2 Damaged Leads
2007-04-23 120 Improper fillet
2007-04-23 34 Non-wetting
2007-04-23 7 Solder missing
2007-04-23 1 Imp hardware mounting
2007-04-23 3 CONTAM
need to see as
2004-04-23
Damaged Leads 2
Improper fillet 120
Non-wetting 34
Solder missing 7
Imp hardware mounting 1
CONTAM 3
Title: ASP.NET 2.0 Code   
Name: Alloyd
Date: 2007-05-27 7:17:59 PM
Comment:
Were would I get VB.Net version of the code to use on an ASP.NET page. I have used a C to VB conversion utility to convert the code, but I have a type mis-match I can't solve in the BindHorzGrid Sub. Here CodeSnip:

' frist create the "anchored" column[0] -- Product
Dim c As BoundColumn = 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)

The type mis-match error is:

"Value of type 'System.Web.UI.WebControls.BoundColumn' cannot be converted to 'System.Web.UI.WebControls.BataControlField'"
Title: special case   
Name: Ali
Date: 2007-04-18 2:08:24 AM
Comment:
Nice article. But there is a problem when you have repeated columns (repeated products)
Title: CodeSnip: Pivot Tables with ADO.NET and Display in a DataGrid Paged Horizontally   
Name: Donna
Date: 2007-01-23 3:13:43 PM
Comment:
Excellent!! Thanks for the VB.net version, this works great!
Title: CodeSnip: Pivot Tables with ADO.NET and Display in a DataGrid Paged Horizontally   
Name: PJackson
Date: 2006-11-18 9:51:50 AM
Comment:
Thanks. I like the simplicity. PJ
Title: great pivot table   
Name: CSutherland
Date: 2006-11-06 1:34:47 PM
Comment:
Hey thanks for the great article!
I'm trying to do something exactly like this and its a great example you have. Props!
Title: Great   
Name: APetcu
Date: 2006-09-13 3:52:08 AM
Comment:
Plain and simple. Thanks!
Title: ok   
Name: mgv
Date: 2006-09-01 4:28:45 AM
Comment:
not upto the mark try somewhat and get a better ones .
Title: Great article   
Name: Stephan
Date: 2006-07-07 6:09:08 AM
Comment:
Steve, thank you for this article. It was of great help to me. This saved me time to program something simular
Title: hello   
Name: great
Date: 2006-04-04 2:02:11 AM
Comment:
it works!
Title: Indian   
Name: KiranKumar.P
Date: 2006-03-28 12:29:57 PM
Comment:
thank you steve sharrock. Its very useful code for .net developers;
Title: Software Engineer   
Name: John McPherson
Date: 2006-03-06 2:57:09 PM
Comment:
I'm relatively new at vb.net and asp.net but I found what I was looking for...

Thank you.

Regards,

"Sufficiently advanced technology is indistinguishable from magic." - Arthur C. Clark, inventor of the telecommunications satellite
Title: JavaScript Pivot Tables   
Name: Nick
Date: 2006-02-04 7:35:22 PM
Comment:
For an example of how to rearrange multidimensional tables using JavaScript, check out http://www.jnetiq.com
Title: vb.net version of the pivot table function   
Name: Chris
Date: 2005-12-20 11:25:58 AM
Comment:
Here is the vb.net version of the pivot table function:

Public Function PivotTable(ByVal source As DataTable) As DataTable
Dim dest As DataTable = New DataTable("Pivoted" + source.TableName)

' create shchema (string columns) for the destination
' the first column is for the source column name
dest.Columns.Add(" ")

' the remaining dest columns are from each source table row (1st column)
Dim r As DataRow
For Each r In source.Rows
dest.Columns.Add(r(0).ToString())
Next

' now add one row to the dest table for each column in the source, except
' the first which is the Product, in our case
Dim i As Integer
For i = 0 To source.Columns.Count - 1 - 1 Step i + 1
dest.Rows.Add(dest.NewRow())
Next

' now move the source columns to their position in the dest row/cell matrix
' starting down the destination rows, and across the columns
Dim rr As Integer = 0
Dim c As Integer = 0
For rr = 0 To dest.Rows.Count - 1 Step rr + 1
For c = 0 To dest.Columns.Count - 1 Step c + 1
If c = 0 Then
dest.Rows(rr)(0) = source.Columns(rr + 1).ColumnName ' the Product name
Else
dest.Rows(rr)(c) = source.Rows(c - 1)(rr + 1)
End If
Next
c = 0
Next
dest.AcceptChanges()
Return dest
End Function
Title: pivot Table demo   
Name: manish punwani
Date: 2005-12-02 7:50:15 AM
Comment:
this was very helpful
thanks a lot
rgds
manish
Title: database conectivity   
Name: arvind
Date: 2005-03-15 12:29:28 PM
Comment:
how to attach a table of oracle in a aspx page in C#.net project.
Title: CodeSnip: Pivot Tables with ADO.NET and Display in a DataGrid Paged Horizontally   
Name: Alejandro Cuellar Gaxiola
Date: 2005-01-28 7:15:50 AM
Comment:
This is an excellent article showing a very elegant way to pivot a table and how to manage the grid to present the data.
Title: but what about image   
Name: vishal
Date: 2004-12-03 6:42:57 AM
Comment:
but what about images if i wants to display on the first row.
when i tried and , when i wants to add first row than i am unable to retrieve values from database as i store image in byte() format

how can we retrieve images from database and display on a grid page with the help of pivot table.
Title: VB.NET Code   
Name: Steve
Date: 2004-11-09 8:27:32 AM
Comment:
Here are a few links to various tools that will convert C# to VB.

http://authors.aspalliance.com/aldotnet/examples/translate.aspx

http://www.developerfusion.com/utilities/convertcsharptovb.aspx

http://www.kamalpatel.net/

http://w1.311.telia.com/~u31115556/desc/programs.htm#BabbelFisken
Title: VB.NET demo   
Name: Bob
Date: 2004-11-09 7:20:30 AM
Comment:
Could you supply VB.NET demo code also?






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-28 6:10:31 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search