Converting Generic Lists or Collections to a DataSet
Posted by Keith Elder | Posted in .Net, Programming | Posted on 10-03-2006
Today I had a situation where I was calling a web service that returned an array of items. Here is a sample of the XML the web service was returning.
<?
xml version=“1.0“ encoding=“utf-8“?>
<ItemCollection>
<item>
<FirstName>Keith</FirstName>
<LastName>Elder</LastName>
<FavoriteColor>Blue</FavoriteColor>
</item>
<item>
<FirstName>Chris</FirstName>
<LastName>Risner</LastName>
<FavoriteColor>Pink</FavoriteColor>
</item>
<item>
<FirstName>David</FirstName>
<LastName>Little</LastName>
<FavoriteColor>Carnation Pink</FavoriteColor>
</item>
</ItemCollection>
The web service returned the data as expected but I needed to bind this to a DataGridView in the user interface. For anyone that has used VS2005 you know this is pretty simple. I added a new data source of type “Object” and selected my ItemCollection object that was generated from the WSDL of the web service. I dropped the ItemCollection onto the designer and I had a DataGridView with all of my columns. So far, I’ve spent about 30 seconds on this and I’ve connected to a web service and bound data to the UI. I was going to be done with this in a few minutes. I proceeded to take out the columns that I didn’t want showing and built the solution and everything looked great.
I then referred back to the spec and noticed something that I missed. The columns need to be sortable in the UI. This poses a problem because the data that is coming back from the web service gets converted into an array of items (items[]). Although we can bind an array to a DataGridView there isn’t an easy way to make the DataGridView sort this information based on each column.
I stared at my screen for a few minutes and came to the conclusion that I was going to need to convert the items[] to a DataSet since when bound to a DataGridView it would provide automatic sorting as well as filtering down the road in case someone decided they wanted to search this data (which they typically do). I started then writing a CollectionToDataSet object that would take a collection of items and convert the collection to a DataSet. When you decide to go down this path to make this type of library you really hate building something that only works for one type of object. I first got the new class working but it only worked with the type of object I was working with. During my playing around with the new class a light bulb went off and it hit me this would be a good place to implement a Generic.
Here is the end result of the class which uses a Generic to figure out which type of object is being passed in. The only thing it must do is implement the ICollection interface and things work. Here is what I came up with.
/// <summary>
/// This will take anything that implements the ICollection interface and convert
/// it to a DataSet.
/// </summary>
/// <example>
/// CollectiontoDataSet converter = new CollectionToDataSet<Letters[]>(letters);
/// DataSet ds = converter.CreateDataSet();
/// </example>
/// <typeparam name=”T”></typeparam>
public class CollectionToDataSet<T> where T : System.Collections.ICollection
{
T _collection;
public CollectionToDataSet(T list)
{
_collection = list;
}
private PropertyInfo[] _propertyCollection = null;
private PropertyInfo[] PropertyCollection
{
get
{
if (_propertyCollection == null)
{
_propertyCollection = GetPropertyCollection();
}
return _propertyCollection;
}
}
private PropertyInfo[] GetPropertyCollection()
{
if (_collection.Count > 0)
{
IEnumerator enumerator = _collection.GetEnumerator();
enumerator.MoveNext();
return enumerator.Current.GetType().GetProperties();
}
return null;
}
public DataSet CreateDataSet()
{
DataSet ds = new DataSet(“GridDataSet”);
ds.Tables.Add(FillDataTable());
return ds;
}
private DataTable FillDataTable()
{
IEnumerator enumerator = _collection.GetEnumerator();
DataTable dt = CreateDataTable();
while (enumerator.MoveNext())
{
dt.Rows.Add(FillDataRow(dt.NewRow(),enumerator.Current));
}
return dt;
}
private DataRow FillDataRow(DataRow dataRow, object p)
{
foreach (PropertyInfo property in PropertyCollection)
{
dataRow[property.Name.ToString()] = property.GetValue(p, null);
}
return dataRow;
}
private DataTable CreateDataTable()
{
DataTable dt = new DataTable(“GridDataTable”);
foreach (PropertyInfo property in PropertyCollection)
{
dt.Columns.Add(property.Name.ToString());
}
return dt;
}
}
There you go! A class to take any type of object that implements the ICollection interface and convert it to a DataSet.
As I was writing this article I thought of another way this could be done. Since the items[] is serializable to XML and a DataSet can take XML and convert that to a DataSet through the ReadXml() method, what I could have done is convert the object to XML, then have a DataSet just read in the XML. Just food for thought. I may play with that later on to see which one is “faster”. I can tell you that serializing the object and then converting it would be a lot less code to write initially, however, this class will probably come in handy more than not.
Let me know what you think about the class or if you have any ideas on how to improve it or ways you would have done it differently. I’ve attached the class file to this post for those that want to download it and give it whirl.
excellent article, thanks!
The idea presented at the end is what I have been using until I encountered a problem. An instance of a complex type containing an array or a generic collection of a type is returned. I serialized the object to xml string then read it back into DataSet. It appeared to be working except that all data types become System.String though original data type may be float, double and etc.
Here is the code snippet:
string xml = XmlUtils.Serialize(value);
XmlTextReader xtr = new XmlTextReader(xml, XmlNodeType.Element, null);
DataSet ds = new DataSet();
ds.ReadXml(xtr);
Another question (and hoping that someone is still watching this series), I implemented Ian’s extension method. I have two issues:
GridView1.datasource=mylist.ToDataSet(); //ian’s method
GridView1.AllowSorting=true;
//bind
clicked a column to sort, and it doesn’t sort. just errors out (event call not specified)
the other error, converted the Datatable to use the properties as described above, unfortunatley most of the properties are nullable (don’t ask) and Datasets don’t support System.Nullable. work around?
thanks!
I got no responses to my question but i just find myself the solution. Thanks anyway.
objPds.DataSource = MyDataset.Tables[0].DefaultView;
I have a dataset and want to “put it” in a PagedDataSource. Just like this
“MyPagedDataSource.DataSource = MyDataSet;” but i get the error “Cannot implicitly convert type ‘System.Data.DataTable’ to ‘System.Collections.IEnumerable”. Anyone can help me Please.
Sorry about my poor english and my basic knowledge about c# asp.net.
(argh!)
CollectionToDataSet<List> converter = new CollectionToDataSet<List>(list);
nevermind. The implementation example in the comment code above is wrong.
it should read: CollectionToDataSet
converter = new CollectionToDataSet
(list);
At least, that’s what worked for me.
(note, for some reason the > and < characters are being removed.
Error 1 Using the generic type ‘MCS.DAL.CollectionToDataSet‘ requires ‘1’ type argument
I have both using statements
using System.Collections
using MCS.DAL (the namespace for the class)
Suggestions?
here’s the call
CollectionToDataSet stlds = new CollectionToDataSet(stl);
Hi Ellie,
You can use the extension method that Ian created: iandykes.blogspot.com/…/…extension-method.html
For example, prdData.ToDataSet(“TableName”)
Mighty Mao
Hi There
Thanks for the code. I have got an issue with that. I wonder how to use this code.
In my program I have got a generic list as below:
List prdData = ic_ProductData.GetByPrdDefIdInsId();
All I need is to convert the prdData to either dataset or datatable
I tried to do it like:
CollectiontoDataSet converter = new CollectionToDataSet>(prdData );
but I will get compile error in the first line at CollectiontoDataSet converter=…
Could U please tell me how can I use the provided class?
Regards
Ellie
@ A. H.
Technically you have two objects, cars and then tires. Tires would be a relational table in the true dataset way, which is REALLY overkill to just do that.
I would not at all recommend converting this stuff into a Dataset. Keep them objects and bind the objects. Look at CSLA.Net for examples of how to do this.
-Keith
What if the objects of whose type your generic list is has yet other custom data objects as properties, and you need to display the properties of these properties?
For example, you could have a list of “cars”, with the property “tires”, but you didn’t want to display “tires” – you wanted to display “tires.size”, which is a property of “tires”.
Any suggestions?
Many Thanks for your code! It spent me a lot of time because I needed a code just like this. The only amendments I did it to specify column types for the data set according to fileds type with code like this:
dt.Columns.Add(property.Name.ToString(),property.PropertyType);
Kind Regards
I’ve been to this site a few times Keith and I always find something interesting. Here I found a great List<T> to DataSet conversion and a killer extenstion created by Ian.
Nice, very nice.
thank youuu
Thank you
@Gerard
Look at the comments in the code above…. he gives you an example
Like Mike’s comment at the top, I don’t really get how to implement this..
I have a generic list of houses declared by
List<house> houses;
how do I convert this into a DataSet ?
The answer to “Error1-Using the generic type ‘System.Collections.Generic.IEnumerator<T>’ requires ‘1’ type arguments” is to include the following statement:
using System.Collections;
Very cool Mike. A great implementation for today’s new features of the platform.
Hi Keith, Great article on converting collections to a dataset. I can’t seem to find the class file in this post anywhere and when I try to compile the code snippet I get the error “Error1-Using the generic type ‘System.Collections.Generic.IEnumerator<T>’ requires ‘1’ type arguments”. Can you send me the class file or point me in the right direction? Thanks a million!
I converted this code over to VB.net – hope this helps somebody! Please excuse the formatting…
Imports System.Data
Imports System.Reflection
Public Class CollectionToDataSet
Private _collection As ICollection
Private _propertyCollection() As PropertyInfo = Nothing
Private ReadOnly Property PropertyCollection() As PropertyInfo()
Get
If _propertyCollection Is Nothing Then
_propertyCollection = GetPropertyCollection()
End If
Return _propertyCollection
End Get
End Property
Private Function GetPropertyCollection() As PropertyInfo()
If (_collection.Count > 0) Then
Dim enumerator As IEnumerator = _collection.GetEnumerator()
enumerator.MoveNext()
Return enumerator.Current.GetType().GetProperties()
Else
Return Nothing
End If
End Function
Public Function CreateDataSet() As DataSet
Dim ds As DataSet = New DataSet(“GridDataSet”)
ds.Tables.Add(FillDataTable())
Return ds
End Function
Private Function FillDataTable() As DataTable
Dim enumerator As IEnumerator = _collection.GetEnumerator()
Dim dt As DataTable = CreateDataTable()
While enumerator.MoveNext()
dt.Rows.Add(FillDataRow(dt.NewRow(), enumerator.Current))
End While
Return dt
End Function
Private Function FillDataRow(ByRef dataRow As DataRow, ByRef p As Object) As DataRow
For Each pi As PropertyInfo In PropertyCollection
dataRow(pi.Name.ToString()) = pi.GetValue(p, Nothing)
Next
Return dataRow
End Function
Private Function CreateDataTable() As DataTable
Dim dt As DataTable = New DataTable(“GridDataTable”)
For Each pi As PropertyInfo In PropertyCollection
dt.Columns.Add(pi.Name.ToString())
Next
Return dt
End Function
Public Sub New(ByRef coll As ICollection)
_collection = coll
End Sub
End Class
I’m not understanding the question, which class are you referring to?
How do you create an instance of the class?