How do I convert a datatable into a POCO object in Asp.Net MVC?

Question

How do I convert a datatable into a POCO object in Asp.Net MVC?

Answer 1

Pass each DataRow into the class constructor (or use getters/setters) and translate each column into the corresponding property. Be careful with nullable columns to extract them properly.

  public class POCO
  {
       public int ID { get; set; }
       public string Name { get; set; }
       public DateTime? Modified { get; set; }
       ...

       public POCO() { }

       public POCO( DataRow row )
       {
            this.ID = (int)row["id"];
            this.Name = (string)row["name"];
            if (!(row["modified"] is DBNull))
            {
                 this.Modified = (DateTime)row["modified"];
            }
            ...
       }
  }

Answer 2

A data table typically holds many rows - do you want to convert each row into an object instance?

In that case, you could e.g. add a constructor to your POCO object that will accept a DataRow as parameter, and then extracts the bits and pieces from that DataRow:

public YourPOCO(DataRow row)
{
   this.Field1 = row["Field1"].ToString();
   ...
   this.FieldN = Convert.ToInt32(row["FieldN"]);
}

and so on, and then call that constructor on each of the rows in the DataTable.Rows collection:

List<YourPOCO> list = new List<YourPOCO>();

foreach(DataRow row in YourDataTable.Rows)
{
   list.Add(new YourPOCO(row));
}

And you could then create a ASP.NET MVC view or partial view based on this "YourPOCO" type and use the "List" template to create a list of "YourPOCO" instances in a list-like display.

Marc

Answer 3

Old question, anyway this can be usefull for somebody:

private static T CreatePocoObject<T>(DataRow dr) where T : class, new()
{
    try
    {
        T oClass = new T();
        Type tClass = typeof (T);
        MemberInfo[] methods = tClass.GetMethods();
        ArrayList aMethods = new ArrayList();
        object[] aoParam = new object[1];

        //Get simple SET methods
        foreach (MethodInfo method in methods)
        {
            if (method.DeclaringType == tClass && method.Name.StartsWith("set_"))
                aMethods.Add(method);
        }

        //Invoke each set method with mapped value
        for (int i = 0; i < aMethods.Count; i++)
        {
            try
            {
                MethodInfo mInvoke = (MethodInfo)aMethods[i];
                //Remove "set_" from method name
                string sColumn = mInvoke.Name.Remove(0, 4);
                //If row contains value for method...
                if (dr.Table.Columns.Contains(sColumn))
                {
                    //Get the parameter (always one for a set property)
                    ParameterInfo[] api = mInvoke.GetParameters();
                    ParameterInfo pi = api[0];

                    //Convert value to parameter type
                    aoParam[0] = Convert.ChangeType(dr[sColumn], pi.ParameterType);

                    //Invoke the method
                    mInvoke.Invoke(oClass, aoParam);
                }
            }
            catch
            {
                System.Diagnostics.Debug.Assert(false, "SetValuesToObject failed to set a value to an object");
            }
        }

        return oClass;
    }
    catch
    {
        System.Diagnostics.Debug.Assert(false, "SetValuesToObject failed to create an object");
    }

    return null;
}

Source is http://blog.developers.ie/cgreen/archive/2007/09/14/using-reflection-to-copy-a-datarow-to-a-class.aspx

Answer 4

I saw your other question about using a datatable in the data access layer. If you return POCO at some point its a good idea to let your DAL return POCO already.

You would use an SqlDataReader to fill the POCO. This is more light weight. Sometimes its easier to use DataSet and DataTable for Lists of entries, but if you tranform the rows into stronly typed POCOS anyway I am pretty shure that this is the way to go.