Using Visual Studio Team System 2008 internal unit testing environment I create the code and the unit test for my pivot table class. Having written this code before without unit testing. I rewrote this with agile development in mind. Basically this code takes a datatable that you want to transform. A key column that defines each unique row. A variable column that will display across the top and a value column representing the data for it.
For this example to work you need to sort the table by that key column. Alternatively, I could add some code to make sure that happens in the view. Will probably update that later.
For those of you that are new to unit testing this was a perfect example of a time when testing came in handy. When I tested the code without variable columns that where of type date the class worked great. As soon as I change the data to a date field the unit test crashed. Below is the result of the modified code by adding ".tostring"
Namespace GEN.DL.Transformation
Public Class PivotTable
Private lSourceTable As DataTable
Private lVariableColumn As DataColumn
Private lAggregateColumn As DataColumn
Private lKeyColumn As DataColumn
Private lDestinationTable As New DataTable
#Region "Public Properties"
''' <summary>
''' Pivot datatable
''' </summary>
Public Sub New(ByVal mSourceTable As DataTable, ByVal mKeyColumn As DataColumn, _
ByVal mVariableColumn As DataColumn, ByVal mValueColumn As DataColumn)
lSourceTable = mSourceTable
lKeyColumn = mKeyColumn
lVariableColumn = mVariableColumn
lAggregateColumn = mValueColumn
End Sub
#End Region
''' <summary>
''' Pivots a datatable
''' </summary>
''' <returns>Returns pivot datatable</returns>
Public Function Transform() As DataTable
Dim mColumn As DataColumn ' Column used in for each to add non essential columns
Dim mSourceDataRow As DataRow ' Source Row
Dim mDestinationDataRow As DataRow = Nothing ' Row to be Added
Dim mCurrentKeyValue As String = "" ' Current Key Name
Dim mCurrentColumn As String = "" ' Current Column Name
' Add All the columns excluding the aggregate and the Variable Column
For Each mColumn In lSourceTable.Columns
If mColumn.Ordinal <> lVariableColumn.Ordinal AndAlso _
mColumn.Ordinal <> lAggregateColumn.Ordinal Then
lDestinationTable.Columns.Add(mColumn.ColumnName, mColumn.DataType)
End If
Next
For Each mSourceDataRow In lSourceTable.Rows ' Main Process to add values to pivot table
' Set the current Column Value
mCurrentColumn = mSourceDataRow(lVariableColumn.Ordinal).ToString
If Not lDestinationTable.Columns.Contains(mCurrentColumn) Then ' Column is new
Dim lDataColumn As DataColumn = lDestinationTable.Columns.Add(mCurrentColumn, lAggregateColumn.DataType)
' set the index so that it is sorted properly:
Dim newOrdinal As Integer = lDataColumn.Ordinal
Dim i As Integer
For i = newOrdinal - 1 To lSourceTable.Columns.Count - 2 Step -1
If lDataColumn.ColumnName.CompareTo(lDestinationTable.Columns(i).ColumnName) < 0 Then
newOrdinal = i
End If
Next
lDataColumn.SetOrdinal(newOrdinal)
End If
If mCurrentKeyValue <> mSourceDataRow(lKeyColumn.Ordinal).ToString Then ' New Row
If Not mCurrentKeyValue = "" Then ' First row
lDestinationTable.Rows.Add(mDestinationDataRow)
End If
mCurrentKeyValue = mSourceDataRow(lKeyColumn.Ordinal).ToString
mDestinationDataRow = lDestinationTable.NewRow()
For Each mColumn In lSourceTable.Columns
If mColumn.Ordinal <> lVariableColumn.Ordinal AndAlso _
mColumn.Ordinal <> lAggregateColumn.Ordinal Then
mDestinationDataRow(mColumn.ColumnName) = mSourceDataRow(mColumn.ColumnName)
End If
Next
End If
mDestinationDataRow(mCurrentColumn) = mSourceDataRow(lAggregateColumn.Ordinal)
Next
' If rows where added, add the last row
If mCurrentKeyValue.Length > 0 Then lDestinationTable.Rows.Add(mDestinationDataRow)
Return lDestinationTable
End Function
End Class
End Namespace