Wednesday, May 07, 2008

IEnumerable to DataSet Extension Method

I recently had a need to create a DataSet from a List<T>, and found this post that did just that. I was happily using that in C# code, but then had a requirement to use it from a VB.Net app.

I'm not even going to attempt to hide my contempt for VB, and one of the things that I quickly tire of is typing the exact same thing multiple times. The Intellisense in VB also doesn't seem as smart as it is in C#, mainly it doesn't help as much when instantiating objects.

So I wanted a simpler way of calling the code that I had from the post above, and I immediately thought of extension methods. If I could create an extension method that made a DataSet from an IEnumerable then I could call that from the VB app with a minimal of fuss.

Kudos must go to Keith Elder for his original code, but if you want it in extension method form, then here it is:

public static class CollectionExtensions
{
public static DataSet ToDataSet<T>(this
IEnumerable<T> collection, string dataTableName)
{
if (collection == null)
{
throw new ArgumentNullException("collection");
}

if (string.IsNullOrEmpty(dataTableName))
{
throw new ArgumentNullException("dataTableName");
}

DataSet data = new DataSet("NewDataSet");
data.Tables.Add(FillDataTable(dataTableName, collection));
return data;
}

private static DataTable FillDataTable<T>(string tableName,
IEnumerable<T> collection)
{
PropertyInfo[] properties = typeof(T).GetProperties();

DataTable dt = CreateDataTable<T>(tableName,
collection, properties);

IEnumerator<T> enumerator = collection.GetEnumerator();
while (enumerator.MoveNext())
{
dt.Rows.Add(FillDataRow<T>(dt.NewRow(),
enumerator.Current, properties));
}

return dt;
}

private static DataRow FillDataRow<T>(DataRow dataRow,
T item, PropertyInfo[] properties)
{
foreach (PropertyInfo property in properties)
{
dataRow[property.Name.ToString()] = property.GetValue(item, null);
}

return dataRow;
}

private static DataTable CreateDataTable<T>(string tableName,
IEnumerable<T> collection, PropertyInfo[] properties)
{
DataTable dt = new DataTable(tableName);

foreach (PropertyInfo property in properties)
{
dt.Columns.Add(property.Name.ToString());
}

return dt;
}
}

It creates a DataSet with one table that has the name you pass in. In my case I didn't need to name the DataSet explicitly so just used a constant, but the code above could easily be updated to pass in a DataSet name if you need it.

Now you should be able to call ToDataSet on any object that implements the IEnumerable interface.

6 comments:

Param said...

Hey Ian,

Good one and thanks to the original author. solved my problem when i had to bind two columns while displaying in a dropdown list

Anonymous said...

Outstanding! Exactly what I needed. I didn't even have to tweak it any. I am running VS2008 (.NET 3.5) and could not find an easy way to convert the results of a LINQ2SQL list into a dataset - so that I could push the data to a Crystal Report. This worked perfectly. Thank you!

Anonymous said...

Hi Ian, this was very useful.

I was having some date formatting trouble with the DataGridView I was using the dataset to populate. After a while, I tracked it down to none of dataset columns having their types specified. I checked back with the comments at Keith's original post and noticed that someone else spotted this too, although, too late to keep me from spending some time scratching my head.

The solution is easy. Just change the following from:
dt.Columns.Add(property.Name.ToString());

to:
dt.Columns.Add(property.Name, property.propertyType);

then the DataTable will also carry along the type information. This is useful for sorting and formatting. I was only getting a lexical sort based on the DateTime's default ToString() method (so 2/1/2008 would sort *after* 10/1/2008 because of the leading 2) and format specifiers weren't doing anything because the DateTime was being treated as a generic object.

While I'm at it, here is a more compact version of the FillDataTable portion. It gets rid of a couple unnecessary ToString's, the awkward GetEnumerator, and the last two smaller functions that feel a little too broken-out:

DataTable table = new DataTable(tableName);

PropertyInfo[] properties = typeof(T).GetProperties();
foreach (PropertyInfo p in properties) table.Columns.Add(p.Name, p.PropertyType);

foreach (T item in collection)
{
DataRow row = table.NewRow();
foreach (PropertyInfo p in properties) row[p.Name] = p.GetValue(item, null);

table.Rows.Add(row);
}

return table;

The formatting here drops the indents and seems to wrap a little, but copying it out to notepad or something makes it clearer. Hopefully it helps. I mostly wanted to point out the Type being set on columns.

Thanks again!

Mansoor said...

Hey you have done a great work and so did Keith Elder..I really appreciate the hard work done.I have been working on a similar issue with a differnt approach which i think is a no match to wat u guys have done..Great work...Happy Coding..And i am using your piece of code only :)

Mr MacroFace said...

Fabulously useful class. Thanks.

XavierD said...

I agree that this is a very useful class, however I have a question.

In your FillDataRow function where you use the PropertyInfo.GetValue function, how would you handle an item that has an indexer property, such as the Chars property of a string? When I try this on a list of strings I receive a "Parameter count mismatch" exception.

Thank you.