Silverlight / Data Challenging Issue

Question

I'm a little stumped on this one. Anyone have any ideas? I'll try to lay out the example as brief as possible.

Creating Silverlight 3.0 application against SQL 2005 database. Using RIA Services and Entity Framework for data access.

I need to be able to populate a grid against a table. However, my grid UI and my table structure is different. Basically my grid needs to turn rows into columns (like a PIVOT table). Here are my challenges / assumptions

1. I have no idea until runtime which columns I will have on the grid.
2. Silverlight 3 only supports binding to properties
3. Silverlight 3 does not allow you to add a row to the grid and manually populate data.
4. As we all know, Silverlight does not have the System.Data (mainly DataTable) namespace

So, how do I create an object w/ dynamic properties so that I can bind to the grid. Every idea I've had (multi-dimensional arrays, hash tables, etc.) fall apart b/c SL needs a property to bind to, I can't manually add/fill a data row, and I can't figure out a way to add dynamic properties. I've seen an article on a solution involving a linked list but I'm looking for a better alternative. It may come down to making a special "Cody Grid" which will be a bunch of text boxes/labels. Doable for sure but I'll lose some grid functionality that users expect

The ONLY solution I have been able to come up is to create a PIVOT table query in SQL 2005 and use an entity based on that query/view. SQL 2008 would help me with that. I would prefer to do it in Silverlight but if that is the last resort, so be it. If I go the PIVOT route, how do I implement a changing data structure in Entity Framework?

Data Sample.

Table

Name Date Value
Cody 1/1/09 15
Cody 1/2/09 18
Mike 1/1/09 20
Mike 1/8/09 77

Grid UI should look like

Name       1/1/09       1/2/09     1/3/09 ....   1/8/09
Cody           15         18        NULL          NULL
Mike           20        NULL       NULL           77

Cody

Answer 1

My team came up with a good solution. I'm not sure who deserves the credit but it's somewhere in google land. So far it works pretty good.

Essentially the solution comes down to using reflection to build a dynamic object based on this dynamic data. The function takes in a 2-dimensional array and turns it into a List object with properties that can be bound. We put this process in a WCF Service and it seems to do exactly what we need so far.

Here is some of the code that builds the object using Reflection

AppDomain myDomain = AppDomain.CurrentDomain;
            AssemblyName myAsmName = new AssemblyName("MyAssembly");

            AssemblyBuilder myAssembly = myDomain.DefineDynamicAssembly(myAsmName, AssemblyBuilderAccess.Run);
            ModuleBuilder myModule = myAssembly.DefineDynamicModule(myAsmName.Name);

            TypeBuilder myType = myModule.DefineType("DataSource", TypeAttributes.Public);

            string columnName = "whatever";

            for (int j = 0; j <= array.GetUpperBound(1); j++)
            {

                Type properyType = typeof(T);
                FieldBuilder exField = myType.DefineField("_" + "columnName" + counter, properyType, FieldAttributes.Private);

                  //The following line is where I’m passing columnName + counter and getting errors with some strings but not others.
                PropertyBuilder exProperty = myType.DefineProperty(columnName + counter.ToString(), PropertyAttributes.None, properyType, Type.EmptyTypes);
                //Get

                MethodBuilder exGetMethod = myType.DefineMethod("get_" + "columnName" + counter, MethodAttributes.Public, properyType, Type.EmptyTypes); ILGenerator getIlgen = exGetMethod.GetILGenerator();
                //IL for a simple getter:
                //ldarg.0 
                //ldfld int32 SilverlightClassLibrary1.Class1::_Age
                //ret 

                getIlgen.Emit(OpCodes.Ldarg_0);
                getIlgen.Emit(OpCodes.Ldfld, exField);

                getIlgen.Emit(OpCodes.Ret);
                exProperty.SetGetMethod(exGetMethod);

                //Set

                MethodBuilder exSetMethod = myType.DefineMethod("set_" + "columnName" + counter, MethodAttributes.Public, null, new Type[] { properyType }); ILGenerator setIlgen = exSetMethod.GetILGenerator();
                //IL for a simple setter:
                //ldarg.0 
                //ldarg.1 
                //stfld int32 SilverlightClassLibrary1.Class1::_Age
                //ret 

                setIlgen.Emit(OpCodes.Ldarg_0);
                setIlgen.Emit(OpCodes.Ldarg_1);

                setIlgen.Emit(OpCodes.Stfld, exField); setIlgen.Emit(OpCodes.Ret);
                exProperty.SetSetMethod(exSetMethod);

                counter++;

            }
            finished = myType.CreateType();

Answer 2

You can dynamically set columns with their associated bindings (ensuring that AutoGenerateColumns is off):

For instance, the name column:

DataGridTextColumn txtColumn = new DataGridTextColumn(); textColumn.Header = "Name"; textColumn.Binding = new Binding("FirstName"); myDataGrid.Columns.Add(txttColumn);

The ObservableCollection you use to store the data that is queried could possibly be overriden to support pivoting, making sure to change the binding of the DataGrid columns, as shown above.

Note: This is a fair amount of hand waving i'm sure (haven't touched silverlight for over a year); but I hope it's enough to formulate another strategy.

Answer 3

if you are working with two dimensional array then adding columns dynamically as shown above will not work. The problem is with silverlight it cannot understand the binding of columns to a list.

So we have to create list of rows with row convertor that will represent our two dimensional arrays.

this one worked for me

http://www.scottlogic.co.uk/blog/colin/2010/03/binding-a-silverlight-3-datagrid-to-dynamic-data-via-idictionary-updated/