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.

Wednesday, 2 February 2011

Plumbing-for-free: WCF, MVVM, and the WPF DataGrid

I have come to love MVVM, but every now and then WPF comes back and reminds us that it often wants to be used like Windows Forms with its event-based code-behind model.  Nowhere is this more pronounced than in the WPF DataGrid -which comes with all sorts of helpful plumbing for filtering, sorting, and tracking dirty states.  Unfortunately a lot of this functionality is tied to events on the control, which means that at some point you’ll either need to put View-related code into the ViewModel or else use code-behind.  I hit one such problem today where I wanted to know if an ObservableCollection, that I was using as an ItemSource for a DataGrid, was dirty or not.  By ‘dirty’ I mean that either the contents of the list, or any of the objects in the list, had changed.  However if all changed properties were returned to their original state then I would consider that the data was no longer dirty.

Essentially what I wanted to implement was a ‘trackable’ observable collection similar to Ludwig’s one here.  Unfortunately the requirement to ‘un-dirty’ the collection if data returns to its original state, makes it a bit trickier.  Now this is where using WCF, even for non-distributed systems, can really pay off.  There are two reasons for this:

  1. SvcUtil can be used to automatically implement INotifyPropertyChanged on every public property of a data contract (i.e. model class) when creating proxy classes.  This means that with automated proxy rebuilds (see this post) you get property notifications guaranteed on all data contracts with no extra effort.
  2. WCF ‘model’ objects in SvcUtil generated proxy classes are deep-cloneable by default.  This is because all ‘model’ objects are DataContracts and all fields are DataMembers (otherwise they wouldn’t be picked up from the WSDL by SvcUtil in the first place), so serializing and then de-serializing an object will give you an exact copy that isn’t the same object.  This is a technique used in IDesign’s SerializationUtil helper class.

The net effect is that we have an easy way of being notified when a property has changed, but also of checking whether the object (i.e. data contract) as a whole has changed.  So, taking ‘inspiration’ from Ludwig’s example (i.e. trapping the property change notification per-object), we can include something that also compares objects every time a property on the object changes – but only if the object was in the original collection (because additions/inserts to the list are dirty-by-default).  For this we can use the aforementioned IDesign helper classes, to which we can add a few extra methods for comparing objects.  Rather than attempt to compare byte or ASCII streams, we can compute an MD5 hash of the object and compare that instead.

My solution is simply a leaner modification of the TrackableObservableCollection<T> class.  Internally it wraps every ‘original’ object in the list in a TrackableObject<T> class, which handles the plumbing of notifying the parent list of changes via the OnDirtyStatusChanged event.  The TrackableObservableCollection<T> inherits from ObservableCollection<T> which saves us having to recode a whole raft of plumbing.

Despite what you may think, given the constant serialization/deserialization, the object comparison is very quick (longest time taken is 6ms).  What matters though is that the solution performs well enough.  I’m sure that someone clever has a more efficient way of doing this, but what I like is that it’s all made so easy and plumbing-light by the use of WCF.

To use, simply pass your existing ObservableCollection to the constructor, then bind to the DataGrid:

// get the data
ObservableCollection<TestClass> myCollection = GetData();

// convert to a trackable collection
TrackableObservableCollection<TestClass> myTrackableCollection =
new TrackableObservableCollection<TestClass>(myCollection);

Debug.Assert(!myTrackableCollection.IsDirty);

// store a value
DateTime? originalValue = myCollection[0].DateOfBirth;

// change the value
myCollection[0].DateOfBirth = DateTime.Now;
Debug.Assert(myTrackableCollection.IsDirty);

// change the value back
myCollection[0].DateOfBirth = originalValue;
Debug.Assert(!myTrackableCollection.IsDirty);

Rather than detail every single aspect of the solution, it’s easier just to download the (VS2010) example.

Points to note:

  • For the sake of simplicity I’ve used ‘Add Service Reference’ rather than SvcUtil in the build events.
  • Be careful when using EF-derived data contracts with circular navigation properties as updating a value may not apply the update to the respective navigation property (a problem that can be averted by not using lazy loading needlessly)