Tuesday, 8 February 2011

Crosstab, pivot, matrix, WPF DataGrid

When I first started out doing IT professionally (having done it amateurishly for about 15 years at that point), Microsoft Access was my proverbial bread and butter (prior to that I had done a lot of work with accountant-types on Excel and its forebears).  So I was more than a tad familiar with the crosstab query, which has since survived in the Excel Pivot table and the SSRS Matrix/Tablix control (among others).  While I’d love to wax lyrical about the joys of Access, the reason I mention this is because in the last few days I came across a problem that required editing tabular data in a matrix, inside a WPF application.  The problem concerns a security matrix: we have a list of application roles and functions, with a boolean value indicating which roles can carry out which functions.  For example:
  Pending Active Dormant Locked
Monkey X
Organ grinder X X X
Evil Overlord X X X X
As the list of roles and functions may increase or (possibly) decrease dynamically, this data needs to be stored in a database in a format that accommodate this.  Typically:
SystemStatus RoleName CanEdit
Requested Monkey X
Pending Monkey  
Active Monkey  
Dormant Monkey  
Locked Monkey  
Requested Organ Grinder  
Pending Organ Grinder X
Active Organ Grinder X
Dormant Organ Grinder X
Locked Organ Grinder  
Requested Evil Overlord X
Pending Evil Overlord X
Active Evil Overlord X
Dormant Evil Overlord X
Locked Evil Overlord X
So the problem is that we need to be able to get the data from the tabular format into the matrix.  But I don’t want to be rewriting the DataGrid, so here’s where the ADO.NET DataTable comes to the rescue.  In these days of LINQ to SQL, Entity Framework, et al; the DataTable seems a bit of a legacy curiosity and something that I would normally discourage the use of in data-binding scenarios due to its excess of plumbing (and proprietary serialization).  Back in the (.NET 1) day however, it was the preferred means of data access and persistence; supported in Windows Forms data binding controls and even after all these years is natively supported in WPF list controls.  The good thing about the DataTable is that it can be built up on the fly by adding (Data)columns and (Data)rows as needed, then bound to a WPF DataGrid with column auto-generation.
So using a combination of reflection and generics, we can easily build up a pivoted bindable version of an object in a DataTable.  The only thing to be aware of is that DataTables pre-dated nullable types by some years – thus instead of null use DbNull.Value.  So we need some extra code in there to get the underlying primitive from any nullables and set null values appropriately.  Note also that the column pivot values are applied to the DataColumn’s Caption property because the Name property has to follow variable naming conventions (so there’s another method to coerce the column name into a variable-friendly format).
Here’s the pivoting class:
/// <summary>
/// Class for crosstabbing/pivoting a list of objects
/// </summary>
/// <typeparam name="T">The type of object to pivot</typeparam>
public class ObjectPivoter<T>
where T : class, new()
{
#region Private member variables

private PropertyInfo _columnProperty;
private PropertyInfo _valueProperty;
private PropertyInfo _rowProperty;
private string _rowSourceHeader;

#endregion

#region Constructor

/// <summary>
/// Creates a new instance of this class with the given row, column, and value settings
/// </summary>
/// <param name="rowSource">The name of the field in type <typeparamref name="T"/> to use for the row headings in the pivot</param>
/// <param name="columnSource">The name of the field in type <typeparamref name="T"/> to use for the columns headings in the pivot</param>
/// <param name="valueSource">The name of the field in type <typeparamref name="T"/> to use for the values in the pivot</param>
/// <exception cref="System.ArgumentNullException">Thrown if any of the arguments are null, empty, or whitespace only</exception>
/// <exception cref="System.ArgumentOutOfRangeException">Thrown if any of the values used for pivot fields do not exist in type <typeparamref name="T"/></exception>
public ObjectPivoter(string rowSource, string columnSource, string valueSource) :
this(rowSource, rowSource, columnSource, valueSource)
{ }

/// <summary>
/// Creates a new instance of this class with the given row, column, and value settings
/// </summary>
/// <param name="rowSource">The name of the field in type <typeparamref name="T"/> to use for the row headings in the pivot</param>
/// <param name="rowSourceHeader">The caption to use for the row heading of the row pivot</param>
/// <param name="columnSource">The name of the field in type <typeparamref name="T"/> to use for the columns headings in the pivot</param>
/// <param name="valueSource">The name of the field in type <typeparamref name="T"/> to use for the values in the pivot</param>
/// <exception cref="System.ArgumentNullException">Thrown if any of the arguments are null, empty, or whitespace only</exception>
/// <exception cref="System.ArgumentOutOfRangeException">Thrown if any of the values used for pivot fields do not exist in type <typeparamref name="T"/></exception>
public ObjectPivoter(string rowSource, string rowSourceHeader, 
string columnSource, string valueSource)
{
ThrowExceptionIfFieldNullOrEmpty("rowSource", rowSource);
ThrowExceptionIfFieldNullOrEmpty("rowSourceHeader", rowSourceHeader);
ThrowExceptionIfFieldNullOrEmpty("columnSource", columnSource);
ThrowExceptionIfFieldNullOrEmpty("valueSource", valueSource);

_rowSourceHeader = rowSourceHeader;
_columnProperty = typeof(T).GetProperty(columnSource);

if (_columnProperty == null)
{
ThrowExceptionIfFieldNotExists("columnSource", typeof(T), columnSource);
}

_valueProperty = typeof(T).GetProperty(valueSource);

if (_valueProperty == null)
{
ThrowExceptionIfFieldNotExists("valueSource", typeof(T), valueSource);
}

_rowProperty = typeof(T).GetProperty(rowSource);

if (_rowProperty == null)
{
ThrowExceptionIfFieldNotExists("rowSource", typeof(T), rowSource);
}
}

#endregion

#region Public methods

public DataTable CreatePivot(IList<T> source)
{
if (source == null)
{
throw new ArgumentNullException("source");
}

var pivotTable = new DataTable();

CreatePivotColumns(source, pivotTable, _columnProperty, 
_valueProperty, _rowProperty);

AddPivotRows(source, pivotTable, _columnProperty, 
_valueProperty, _rowProperty);

return pivotTable;
}

public ObservableCollection<T> Unpivot(DataTable table)
{
if (table == null)
{
throw new ArgumentNullException("table");
}

var unpivotedData = new ObservableCollection<T>();

foreach (DataRow row in table.Rows)
{
foreach (DataColumn column in table.Columns)
{
if (column.ColumnName != _rowProperty.Name)
{
var dataItem = CreateObjectFromRowColumn(row, column);                    
unpivotedData.Add(dataItem);
}
}
}

return unpivotedData;
}

#endregion

#region Private methods

private void AddPivotRows(IList<T> source, DataTable pivotTable, PropertyInfo columnProperty,
PropertyInfo valueProperty, PropertyInfo rowProperty)
{
var rows = (from s in source
select rowProperty.GetValue(s, null)).Distinct();

foreach (var row in rows)
{
var newRow = pivotTable.NewRow();
newRow[rowProperty.Name] = Convert.ChangeType(row, rowProperty.PropertyType);

foreach (var sourceRow in source)
{
object rowValue = rowProperty.GetValue(sourceRow, null);

if (rowValue != null && rowValue.ToString() == row.ToString())
{
string columnName = columnProperty.GetValue(sourceRow, null).ToString();
columnName = GetCleanColumnName(columnName);

var columnValue = valueProperty.GetValue(sourceRow, null);

if (columnValue == null)
{
columnValue = DBNull.Value;
}

newRow[columnName] = columnValue;
}
}

pivotTable.Rows.Add(newRow);
}
}

private void CreatePivotColumns(IList<T> source, DataTable pivotTable, 
PropertyInfo columnProperty, PropertyInfo valueProperty, PropertyInfo rowProperty)
{
var columns = (from s in source
select columnProperty.GetValue(s, null).ToString()).Distinct();

Type rowColumnType = GetUnderlyingPrimitive(rowProperty.PropertyType);
pivotTable.Columns.Add(rowProperty.Name, rowColumnType);
pivotTable.Columns[rowProperty.Name].Caption = _rowSourceHeader;

foreach (var column in columns)
{
Type columnType = GetUnderlyingPrimitive(valueProperty.PropertyType);

string columnName = GetCleanColumnName(column);
pivotTable.Columns.Add(columnName, columnType);
pivotTable.Columns[columnName].Caption = column;
}
}

private T CreateObjectFromRowColumn(DataRow row, DataColumn column)
{
var dataItem = new T();

_rowProperty.SetValue(dataItem, row[_rowProperty.Name], null);
_columnProperty.SetValue(dataItem, column.Caption, null);

bool isNullable = IsNullable(_valueProperty.PropertyType);

if (row[column.ColumnName] == DBNull.Value && isNullable)
{
_valueProperty.SetValue(dataItem, null, null);
}
else
{
_valueProperty.SetValue(dataItem, row[column.ColumnName], null);
}

return dataItem;
}

private void ThrowExceptionIfFieldNullOrEmpty(string argumentName, string argument)
{
if (string.IsNullOrEmpty(argument) || argument.Trim() == string.Empty)
{
throw new ArgumentNullException(argumentName);
}
}

private void ThrowExceptionIfFieldNotExists(string argumentName, 
Type type, string fieldName)
{
throw new ArgumentOutOfRangeException(argumentName,
string.Format("The field '{0}' does not exist in the '{1}' class",
fieldName, type.Name));
}

private string GetCleanColumnName(string columnName)
{
string cleanColumnName = Regex.Replace(columnName, @"[^\w]", @"_");

if (!Regex.IsMatch(cleanColumnName, @"^[a-zA-Z_]\w*$"))
{
cleanColumnName = string.Concat(@"_", cleanColumnName);
}

return cleanColumnName;
}

private bool IsNullable(Type type)
{
bool isNullable = (type.IsGenericType &&
type.GetGenericTypeDefinition() == typeof(Nullable<>));
return isNullable;
}

private Type GetUnderlyingPrimitive(Type type)
{
if (IsNullable(type))
{
return Nullable.GetUnderlyingType(type);
}

return type;
}

#endregion
}


Usage as follows:


_pivoter = new ObjectPivoter<SecurityMatrixEntry>("RoleName", "Role", "SystemStatus", "CanEdit");
var pivotedData = _pivoter.CreatePivot(dummyData);

dgMatrix.ItemsSource = pivotedData.DefaultView;


Sample (VS2010) project here, which also contains some attached properties for the DataGrid which enable the use of the DataColumn’s Caption property for the column headers as well as locking the first column.

10 comments:

  1. The link to the Sample (VS2010) project appears to be broken.

    ReplyDelete
  2. It appears SkyDrive has moved all my links again a la Hogwarts staircases. I've updated it and it should be valid again.

    ReplyDelete
  3. Very Good Job !!!
    Just a question, It is possible to have the header column with specials caracters like ( & ?

    Rgds

    ReplyDelete
  4. Oups sorry i have forgot this
    p:DataGridPivotHelper.IsFirstColumnLocked="True"
    p:DataGridCellHelper.IsSingleClickInCell="True"
    p:DataGridPivotHelper.UseDataTableCaptionsAsHeaders="True"

    ReplyDelete
  5. download link broken :(

    someone can upload this again ?

    ReplyDelete
    Replies
    1. Sorry about that - the OneDrive/SkyDrive farrago caused my links to go dead. That one's now fixed.

      Delete
  6. Great work; saved me a lot of time. JUST thank you. I used it in my MVVM based application and it working great. I using SQL pivoting which was fine; but was very difficult to make it generic; something I need.

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Hi,

    My original table has a primary key and other columns, where more than one column need to be transposed. I managed to achieve that by using arrays. However, I am struggling to include back the untransposed columns (such as primary key) in the 'unpivoted' table - all relevant entries are set to '0'. Any ideas? much appreciated.

    Thanks.

    ReplyDelete