views:

1195

answers:

3

Given a list of objects, I am needing to transform it into a dataset where each item in the list is represented by a row and each property is a column in the row. This DataSet will then be passed to an Aspose.Cells function in order to create an Excel document as a report.

Say I have the following:

public class Record
{
   public int ID { get; set; }
   public bool Status { get; set; }
   public string Message { get; set; }
}

Given a List records, how do I transform it into a DataSet as follows:

ID Status Message
1  true   "message" 
2  false  "message2" 
3  true   "message3" 
...

At the moment the only thing I can think of is as follows:

DataSet ds = new DataSet
ds.Tables.Add();
ds.Tables[0].Add("ID", typeof(int));    
ds.Tables[0].Add("Status", typeof(bool));
ds.Tables[0].Add("Message", typeof(string));

foreach(Record record in records)
{
    ds.Tables[0].Rows.Add(record.ID, record.Status, record.Message);
}

But this way leaves me thinking there must be a better way since at the very least if new properties are added to Record then they won't show up in the DataSet...but at the same time it allows me to control the order each property is added to the row.

Does anyone know of a better way to do this?

+1  A: 

Apart from additionally using Reflection to determine the properties of class Record to take care of adding new properties, that's pretty much it.

Mitch Wheat
You're probably right...even though its not what I wanted to hear. I guess either I need to improve my knowlege of DataSets or someone at Microsoft needs to think of a better way.
mezoid
+5  A: 

You can do it through reflection and generics, inspecting the properties of the underlying type.

Consider this extension method that I use:

    public static DataTable ToDataTable<T>(this IEnumerable<T> collection)
    {
        DataTable dt = new DataTable("DataTable");
        Type t = typeof(T);
        PropertyInfo[] pia = t.GetProperties();

        //Inspect the properties and create the columns in the DataTable
        foreach (PropertyInfo pi in pia)
        {
            Type ColumnType = pi.PropertyType;
            if ((ColumnType.IsGenericType))
            {
                ColumnType = ColumnType.GetGenericArguments()[0];
            }
            dt.Columns.Add(pi.Name, ColumnType);
        }

        //Populate the data table
        foreach (T item in collection)
        {
            DataRow dr = dt.NewRow();
            dr.BeginEdit();
            foreach (PropertyInfo pi in pia)
            {
                if (pi.GetValue(item, null) != null)
                {
                    dr[pi.Name] = pi.GetValue(item, null);
                }
            }
            dr.EndEdit();
            dt.Rows.Add(dr);
        }
        return dt;
    }
CMS
Well, the community has spoken so I'll vote this one the answer even though I won't be able to use it for my purposes since I want to be able control the order of the parameters. But I'll definitely keep this solution in mind...
mezoid
Hey, I've just tested your extension and have found that if you want to control the order the columns appear in the datatable then you need to declare them in the order you want them in the object of type T that you pass to the extension. That's awesome!
mezoid
A: 

I've written a small library myself to accomplish this task. It uses reflection only for the first time an object type is to be translated to a datatable. It emits a method that will do all the work translating an object type.

Its blazing fast. You can find it here: ModelShredder on GoogleCode

Johannes Rudolph