Spend most of the day today grappling with binding a WPF datagrid to a DataSet loaded from a parameterized MDX query.
The first gotcha was that SSAS expects its parameterized queries to be passed using the ICommandWithParameters interface, however the OleDb provider for .Net doesn’t support named parameters (except for sprocs). This is a ‘fixed’ Connect issue – fixed as in ‘still broken in .Net 4 but marked as fixed because we can’t be bothered’.
So rather than use ado.net parameters, I’m now using string replacement on my source query text. Just great:
// So have to do manual parameterization :-(
query = query
Then of course the WPF data grid wouldn’t show the data (despite the DataSet visualizer working just fine). It bound and showed columns just fine using AutoGenerateColumns:
dataGrid1.ItemsSource = dataSet.Tables.DefaultView;
…but all the rows showed blank!
Eventually I noticed a spew of debug output, listing the binding failures:
System.Windows.Data Error: 17 : Cannot get 'Item' value (type 'Object') from '' (type 'DataRowView'). BindingExpression:Path=[Blah1].[Blah2].[Blah3].[MEMBER_CAPTION]; DataItem='DataRowView' (HashCode=66744534); target element is 'TextBlock' (Name=''); target property is 'Text' (type 'String') TargetInvocationException:'System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: Blah1 is neither a DataColumn nor a DataRelation for table TheTableName.
at System.Data.DataRowView.get_Item(String property)
--- End of inner exception stack trace ---
This all seemed awfully familiar, and fortunately I happened across a helpful blog article (which I wrote!) explaining the problem. This time it is AutoGenerateColumns that’s generated the wrong binding path, causing WPF to try and find ‘deep’ members (attempting to walk multiple indexers) rather than just bind to a column with that name.
The fix is something like this:
// This works
var table = dataSet.Tables;
dataGrid1.AutoGenerateColumns = false;
foreach (DataColumn dataColumn in dataSet.Tables.Columns)
Header = dataColumn.ColumnName,
Binding = new Binding("[" + dataColumn.ColumnName + "]")
dataGrid1.ItemsSource = table.DefaultView;