A lot of people have asked this question. The answer is that there is no direct way.
Current Silverlight does not support DataSet object. I was hoping to see DataSet support in beta 2, but according to people from Microsoft (http://silverlight.net/forums/p/16297/54531.aspx#54531)
that DataSet support is not likely even in version 2 final release.
So what should we do? In our application we hold our Data in DataSet from the Data tier to the UI tier. We don't want to covert it to a list of object with pre-defined properties in WCF before we pass it to Silverlight. In our case that is not even possible.
In our application almost every SQL is dynamically generated.
One solution is to pass the DataSet Column Information and DataSet XML to the silverlight. On the Silverlight side we build a Dynamic Data Object based on those data with functions provided by System.Reflection.Emit namespace. Then we bind the List of dynamically
build DataObject to the DataGrid. The Dynamic Data Object will have one property for each column in the DataSet with the same DataType.
Because we have the Column Information besides the Data, binding to the DataGrid can be very flexible. I can set AutoGeneratedColumn = true so it display all the Data in the DataSet or I can dynamically generate columns I want to display.
3) Find GetData.cs in DataSetInDataGrid.Silverlight project and change the Database connection string to a connection string that connects to your SQL server database.
4) Set start page to Default.html in DataSetInDataGrid.Silverlight_Web project. Build and run the application.
5) Type in any SQL statement to do a query to your database. The returning data should be displayed in the DataGrid automatically.
6) Implement your UpdateDataSet function in the GetData.cs to update the Data in the DataSet to DB (such as build Update sql statement based on the current data in the DataSet).
Let me know if you see any problems.
Sally Xu
Software Engineer
Aprimo, Inc
Please remember to mark the replies as answers if they answered your question
wow looks like alot of deep heavy stuff to get this working, but i'm glad u have a solution. i converted it over to beta2 with a few tweaks and works great, but only problem is i can't get it to sort by clicking on the columns. beta2 supports
CanUserReorderColumns property for datagrid but when i click on it, it gives me an error. "SortDescription's property name is invalid"
it would be so awesome if i can get sorting too, any idea?
What you want is to be able to dynamically build a DataGrid in SilverLight with ANY data you may deliver from the server. The data could be an Array of records, a DataSet, XML, or weakly typed JSON, it doesn't matter. Here is the code I use to do that,
and also dynamically create the columns. I found this code somewhere, but I don't remember where, but it works great.
Here is the code that creates the DataGrid:
public partial class DynamicGrid : UserControl
{
public DynamicGrid()
{
InitializeComponent();
Canvas.SetLeft(butt, 500);
this.theGrid.Columns.Add(
new DataGridTextColumn
{
Header = "ID",
DisplayMemberBinding = new Binding("ID")
});
this.theGrid.Columns.Add(
new DataGridTextColumn
{
Header = "Name",
DisplayMemberBinding = new Binding("Name")
});
this.theGrid.Columns.Add(
new DataGridTextColumn
{
Header = "Index",
DisplayMemberBinding = new Binding("Index")
});
this.theGrid.Columns.Add(
new DataGridCheckBoxColumn
{
Header = "Is Even",
DisplayMemberBinding = new Binding("IsEven")
});
this.theGrid.ItemsSource = GenerateData().ToDataSource();
}
public IEnumerable<IDictionary> GenerateData()
{
for (var i = 0; i < 1000; i++)
{
var dict = new Dictionary<string, object>();
dict.Add("ID", Guid.NewGuid());
dict.Add("Name", "Name_" + i);
dict.Add("Index", i);
dict.Add("IsEven", i % 2 == 0);
yield return dict;
}
}
}
To make this work, you need the following Extension Method: public static class DataSourceCreator
{
private static readonly Regex PropertNameRegex =
new Regex(@"^[A-Za-z]+[A-Za-z1-9_]*$", RegexOptions.Singleline);
public static object[] ToDataSource(this IEnumerable<IDictionary> list)
{
IDictionary firstDict = null;
bool hasData = false;
foreach (IDictionary currentDict in list)
{
hasData = true;
firstDict = currentDict;
break;
}
if (!hasData)
{
return new object[] { };
}
if (firstDict == null)
{
throw new ArgumentException("IDictionary entry cannot be null");
}
wow looks like alot of deep heavy stuff to get this working, but i'm glad u have a solution. i converted it over to beta2 with a few tweaks and works great, but only problem is i can't get it to sort by clicking on the columns. beta2 supports
CanUserReorderColumns property for datagrid but when i click on it, it gives me an error. "SortDescription's property name is invalid"
it would be so awesome if i can get sorting too, any idea?
Because I have not put any sort code in that sample. Also how to do sort really depend on how you want it. Do you want to go back to the database to re-run the query so your sort really means against all the data or you just want to sort with the current
dataset. You can implement that part yourself to fit your need. It should not be that difficult.
Sally Xu
Software Engineer
Aprimo, Inc
Please remember to mark the replies as answers if they answered your question
This was indeed an impressive solution with great performance.
While you're there, he also has a sweet wrapper on the DispatcherTimer, to create a setTimeout function in SL, like javascript. The direct link is below.
As your instruction, I found this post and I tried to download your demo from your link.
When I open it I got some errors like the following...
1. I cannot find the GetData.cs in DataSetInDataGrid.Silverlight project.
2. In the reference of SL project, there are warning icons on System.Windows.Controls.Data, System.Windows.Controls.Data. So I tried to remove and add reference again. But I got the errors:
I have many small projects in Beta 1 and now I don't have enought time to convert (also I must look in detail of Beta 2 to convert...).
If you can give me the version in Beta 1, that'll be very good. But I don't want you to cost much time to find back so it's ok. Maybe I will try another way.
For converting from beta 1 to beta 2, it will take some effort. It took me a few days to fully convert my projects and have every issues resolved. But issues mainly come from a few areas. MouseDownEvent break for many controls is one, WCF Service cross-domain
file change is another. Other than that, I just need to fix some Xaml and code to use the new syntax.
But you have to do it anyway. Because now all the code samples are in beta2. All the demo sites are updated to beta2. Most discussions here are about beta2. You have no other choice.
Sally Xu
Software Engineer
Aprimo, Inc
Please remember to mark the replies as answers if they answered your question
sladapter
All-Star
43609 Points
7910 Posts
How to Display DataSet in Silverlight DataGrid
May 27, 2008 05:25 PM | LINK
A lot of people have asked this question. The answer is that there is no direct way.
Current Silverlight does not support DataSet object. I was hoping to see DataSet support in beta 2, but according to people from Microsoft (http://silverlight.net/forums/p/16297/54531.aspx#54531) that DataSet support is not likely even in version 2 final release.
So what should we do? In our application we hold our Data in DataSet from the Data tier to the UI tier. We don't want to covert it to a list of object with pre-defined properties in WCF before we pass it to Silverlight. In our case that is not even possible. In our application almost every SQL is dynamically generated.
One solution is to pass the DataSet Column Information and DataSet XML to the silverlight. On the Silverlight side we build a Dynamic Data Object based on those data with functions provided by System.Reflection.Emit namespace. Then we bind the List of dynamically build DataObject to the DataGrid. The Dynamic Data Object will have one property for each column in the DataSet with the same DataType.
Because we have the Column Information besides the Data, binding to the DataGrid can be very flexible. I can set AutoGeneratedColumn = true so it display all the Data in the DataSet or I can dynamically generate columns I want to display.
Here is my code in case anybody wants to try:
Steps to Test:
1) Download the Source from http://cid-9cffd385fd75195b.skydrive.live.com/self.aspx/Silverlight/DataSetInDataGrid.zip
2) Open the solution file in VS 2008
3) Find GetData.cs in DataSetInDataGrid.Silverlight project and change the Database connection string to a connection string that connects to your SQL server database.
private const string connectionString = @"Data Source=LOCALHOST;Initial Catalog=Northwind;Integrated Security=True;"; // change this
4) Set start page to Default.html in DataSetInDataGrid.Silverlight_Web project. Build and run the application.
5) Type in any SQL statement to do a query to your database. The returning data should be displayed in the DataGrid automatically.
6) Implement your UpdateDataSet function in the GetData.cs to update the Data in the DataSet to DB (such as build Update sql statement based on the current data in the DataSet).
Let me know if you see any problems.
Software Engineer
Aprimo, Inc
Please remember to mark the replies as answers if they answered your question
slhungry
Member
44 Points
30 Posts
Re: How to Display DataSet in Silverlight DataGrid
Jun 10, 2008 04:21 AM | LINK
wow looks like alot of deep heavy stuff to get this working, but i'm glad u have a solution. i converted it over to beta2 with a few tweaks and works great, but only problem is i can't get it to sort by clicking on the columns. beta2 supports CanUserReorderColumns property for datagrid but when i click on it, it gives me an error. "SortDescription's property name is invalid"
it would be so awesome if i can get sorting too, any idea?
samcov
Participant
1673 Points
719 Posts
Re: How to Display DataSet in Silverlight DataGrid
Jun 10, 2008 07:00 AM | LINK
What you want is to be able to dynamically build a DataGrid in SilverLight with ANY data you may deliver from the server. The data could be an Array of records, a DataSet, XML, or weakly typed JSON, it doesn't matter. Here is the code I use to do that, and also dynamically create the columns. I found this code somewhere, but I don't remember where, but it works great.
Here is the code that creates the DataGrid:
public partial class DynamicGrid : UserControl
{
public DynamicGrid()
{
InitializeComponent();
Canvas.SetLeft(butt, 500);
this.theGrid.Columns.Add(
new DataGridTextColumn
{
Header = "ID",
DisplayMemberBinding = new Binding("ID")
});
this.theGrid.Columns.Add(
new DataGridTextColumn
{
Header = "Name",
DisplayMemberBinding = new Binding("Name")
});
this.theGrid.Columns.Add(
new DataGridTextColumn
{
Header = "Index",
DisplayMemberBinding = new Binding("Index")
});
this.theGrid.Columns.Add(
new DataGridCheckBoxColumn
{
Header = "Is Even",
DisplayMemberBinding = new Binding("IsEven")
});
this.theGrid.ItemsSource = GenerateData().ToDataSource();
}
public IEnumerable<IDictionary> GenerateData()
{
for (var i = 0; i < 1000; i++)
{
var dict = new Dictionary<string, object>();
dict.Add("ID", Guid.NewGuid());
dict.Add("Name", "Name_" + i);
dict.Add("Index", i);
dict.Add("IsEven", i % 2 == 0);
yield return dict;
}
}
}
To make this work, you need the following Extension Method:
public static class DataSourceCreator
{
private static readonly Regex PropertNameRegex =
new Regex(@"^[A-Za-z]+[A-Za-z1-9_]*$", RegexOptions.Singleline);
public static object[] ToDataSource(this IEnumerable<IDictionary> list)
{
IDictionary firstDict = null;
bool hasData = false;
foreach (IDictionary currentDict in list)
{
hasData = true;
firstDict = currentDict;
break;
}
if (!hasData)
{
return new object[] { };
}
if (firstDict == null)
{
throw new ArgumentException("IDictionary entry cannot be null");
}
Type objectType = null;
TypeBuilder tb = GetTypeBuilder(list.GetHashCode());
ConstructorBuilder constructor =
tb.DefineDefaultConstructor(
MethodAttributes.Public |
MethodAttributes.SpecialName |
MethodAttributes.RTSpecialName);
foreach (DictionaryEntry pair in firstDict)
{
if (PropertNameRegex.IsMatch(Convert.ToString(pair.Key), 0))
{
CreateProperty(tb,
Convert.ToString(pair.Key),
pair.Value == null ?
typeof(object) :
pair.Value.GetType());
}
else
{
throw new ArgumentException(
@"Each key of IDictionary must be
alphanumeric and start with character.");
}
}
objectType = tb.CreateType();
return GenerateArray(objectType, list, firstDict);
}
private static object[] GenerateArray(Type objectType, IEnumerable<IDictionary> list, IDictionary firstDict)
{
var itemsSource = new List<object>();
foreach (var currentDict in list)
{
if (currentDict == null)
{
throw new ArgumentException("IDictionary entry cannot be null");
}
object row = Activator.CreateInstance(objectType);
foreach (DictionaryEntry pair in firstDict)
{
if (currentDict.Contains(pair.Key))
{
PropertyInfo property =
objectType.GetProperty(Convert.ToString(pair.Key));
property.SetValue(
row,
Convert.ChangeType(
currentDict[pair.Key],
property.PropertyType,
null),
null);
}
}
itemsSource.Add(row);
}
return itemsSource.ToArray();
}
private static TypeBuilder GetTypeBuilder(int code)
{
AssemblyName an = new AssemblyName("TempAssembly" + code);
AssemblyBuilder assemblyBuilder =
AppDomain.CurrentDomain.DefineDynamicAssembly(
an, AssemblyBuilderAccess.Run);
ModuleBuilder moduleBuilder = assemblyBuilder.DefineDynamicModule("MainModule");
TypeBuilder tb = moduleBuilder.DefineType("TempType" + code
, TypeAttributes.Public |
TypeAttributes.Class |
TypeAttributes.AutoClass |
TypeAttributes.AnsiClass |
TypeAttributes.BeforeFieldInit |
TypeAttributes.AutoLayout
, typeof(object));
return tb;
}
private static void CreateProperty(TypeBuilder tb, string propertyName, Type propertyType)
{
FieldBuilder fieldBuilder = tb.DefineField("_" + propertyName,
propertyType,
FieldAttributes.Private);
PropertyBuilder propertyBuilder =
tb.DefineProperty(
propertyName, PropertyAttributes.HasDefault, propertyType, null);
MethodBuilder getPropMthdBldr =
tb.DefineMethod("get_" + propertyName,
MethodAttributes.Public |
MethodAttributes.SpecialName |
MethodAttributes.HideBySig,
propertyType, Type.EmptyTypes);
ILGenerator getIL = getPropMthdBldr.GetILGenerator();
getIL.Emit(OpCodes.Ldarg_0);
getIL.Emit(OpCodes.Ldfld, fieldBuilder);
getIL.Emit(OpCodes.Ret);
MethodBuilder setPropMthdBldr =
tb.DefineMethod("set_" + propertyName,
MethodAttributes.Public |
MethodAttributes.SpecialName |
MethodAttributes.HideBySig,
null, new Type[] { propertyType });
ILGenerator setIL = setPropMthdBldr.GetILGenerator();
setIL.Emit(OpCodes.Ldarg_0);
setIL.Emit(OpCodes.Ldarg_1);
setIL.Emit(OpCodes.Stfld, fieldBuilder);
setIL.Emit(OpCodes.Ret);
propertyBuilder.SetGetMethod(getPropMthdBldr);
propertyBuilder.SetSetMethod(setPropMthdBldr);
}
}
This will yield the following Grid:
samcov
Participant
1673 Points
719 Posts
Re: How to Display DataSet in Silverlight DataGrid
Jun 10, 2008 07:09 AM | LINK
I posted all that, and then later saw your link, which is a very similar solution.
I wish I could remember where I got that code, but it works, and is quite fast.
Sam...
sladapter
All-Star
43609 Points
7910 Posts
Re: How to Display DataSet in Silverlight DataGrid
Jun 10, 2008 01:28 PM | LINK
Because I have not put any sort code in that sample. Also how to do sort really depend on how you want it. Do you want to go back to the database to re-run the query so your sort really means against all the data or you just want to sort with the current dataset. You can implement that part yourself to fit your need. It should not be that difficult.
Software Engineer
Aprimo, Inc
Please remember to mark the replies as answers if they answered your question
samcov
Participant
1673 Points
719 Posts
Re: How to Display DataSet in Silverlight DataGrid
Jun 11, 2008 08:21 AM | LINK
Well, I can stop wishing. Here is the article I got this from.
batmansilver
Member
61 Points
58 Posts
Re: How to Display DataSet in Silverlight DataGrid
Jun 20, 2008 01:40 AM | LINK
Hello Sladaper,
As your instruction, I found this post and I tried to download your demo from your link.
When I open it I got some errors like the following...
1. I cannot find the GetData.cs in DataSetInDataGrid.Silverlight project.
2. In the reference of SL project, there are warning icons on System.Windows.Controls.Data, System.Windows.Controls.Data. So I tried to remove and add reference again. But I got the errors:
Library project file cannot specify ApplicationDefinition element.
The project file contains a property value that is not valid
I'm using Silverlight 2 Beta 1. Is this the Silverlight 2 Beta 2 project? if so how can I use it in Beta 1?
Thank you.
sladapter
All-Star
43609 Points
7910 Posts
Re: How to Display DataSet in Silverlight DataGrid
Jun 20, 2008 02:01 AM | LINK
It used to be beta1 project. But I updated it after beta2 was released. I don't know if I can still find the original zip file.
Why are you still using beta1?
Software Engineer
Aprimo, Inc
Please remember to mark the replies as answers if they answered your question
batmansilver
Member
61 Points
58 Posts
Re: How to Display DataSet in Silverlight DataGrid
Jun 20, 2008 02:12 AM | LINK
I have many small projects in Beta 1 and now I don't have enought time to convert (also I must look in detail of Beta 2 to convert...).
If you can give me the version in Beta 1, that'll be very good. But I don't want you to cost much time to find back so it's ok. Maybe I will try another way.
Thank you.
sladapter
All-Star
43609 Points
7910 Posts
Re: How to Display DataSet in Silverlight DataGrid
Jun 20, 2008 02:41 AM | LINK
Sorry, I do not have the original file any more.
For converting from beta 1 to beta 2, it will take some effort. It took me a few days to fully convert my projects and have every issues resolved. But issues mainly come from a few areas. MouseDownEvent break for many controls is one, WCF Service cross-domain file change is another. Other than that, I just need to fix some Xaml and code to use the new syntax.
But you have to do it anyway. Because now all the code samples are in beta2. All the demo sites are updated to beta2. Most discussions here are about beta2. You have no other choice.
Software Engineer
Aprimo, Inc
Please remember to mark the replies as answers if they answered your question