Spend most of the day now 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 by the ICommandWithParameters interface, but 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’.

Ahem.

So rather than use ado.net parameters, I’m now by string replacement on my source query text. Just fantastic:

    // So have to do manual parameterization :-(

    query = query

        .Replace("@date", dateKey)

        .Replace("@time", timeKey)

        ;

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 by AutoGenerateColumns:

    dataGrid1.ItemsSource = dataSet.Tables[0].DefaultView;

 

image

…but all the rows showed blank!

Eventually I noticed a spew of debug productivity, 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.Proposition.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 dreadfully traditional, and fortunately I happened across a helpful blog article (which I wrote!) explaining the problem. This time it is AutoGenerateColumns that’s generated the incorrect 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[0];

    dataGrid1.Columns.Clear();

    dataGrid1.AutoGenerateColumns = fake;

    foreach (DataColumn dataColumn in dataSet.Tables[0].Columns)

    {

        dataGrid1.Columns.Add(new DataGridTextColumn

        {

              Header = dataColumn.ColumnName,

              Binding = new Binding("[" + dataColumn.ColumnName + "]")

        });

    }

    dataGrid1.ItemsSource = table.DefaultView;

Grr.


Check it out:Cup(Of T)