Creating Business Class for a DataRow with unknown number of columns

Question

I have a stored procedure which returns pivoted columns along with columns from tables. I get the DataTable after executing the stored procedure. Now i want to convert this DataTable to a List<'MyClass'>. The DataTable has some known columns ( coming from tables ) and some unknown number of columns as a result of pivot.

How do i create a Class which really represents one DataRow. The idea i have is following:

public class TableColumns
{
       public int TableColumn1 { get;set; }
       public string TableColumn2 { get;set; }
       public float TableColumn1 { get;set; }
       //additional columns if any
}

public class PivotColumns
{
       public string ColumnName { get;set; }
       public string Value { get;set; }
       //additional columns if any
}

public class MyClass
{
       public TableColumns tableColumns { get;set; }
       public List<PivotColumns> pivotedColumns { get;set; }

       //overload the [] operator with real implementation
       public string this[string pivotedColumnName] { get;set; }
}

and then a helper class to do the conversion:

public static class ConversionHelper
{
       public static MyClass ConvertDataRowToMyClass(DataRow dataRow)
       {
               // some implementation
       }

       public static DataRow ConvertMyClassToDataRow(MyClass myClass)
       {
               // some implementation
       }
}

How good is the approach i mentioned above? Please share ideas / alternates

Thanks

Answer 1

I would have done the below for myself.

public class TableColumns
{
   public int TableColumn1 { get;set; }
   public string TableColumn2 { get;set; }
   public float TableColumn3 { get;set; }
   //additional columns if any
}

public class PivotColumns
{
   public string PivotColumn1 { get;set; }
   public int PivotColumn2 { get;set; }
   public float PivotColumn3 { get;set; }       
   //additional columns if any
}

public class MyClass : TableColumns, PivotColumns{ }




public static class ConversionHelper
{
   public static List<MyClass> ConvertDataRowToMyClass(DataTable dt)
   {
           // some implementation
           List<MyClass> ltMyClass = (from dr in dataTable.AsEnumerable()
                                         select new MyClass
                                         {
                                             TableColumn1 = dr["TableColumn1"] == DBNull.Value || dr["TableColumn1"] == null ? default(int) : dr.Field<int>("TableColumn1"),
                                             PivotColumn2 = dr.Field<int>("PivotColumn2"),
                                             TableColumn2 = dr.Field<string>("TableColumn2")
                                         }).ToList<MyClass>();
   }

   public static DataTable ConvertMyClassToDataRow(List<MyClass> lstMyClass)
   {
           // some implementation
           PropertyDescriptorCollection properties = 
           TypeDescriptor.GetProperties(typeof(MyClass));
           DataTable table = new DataTable();
           foreach (PropertyDescriptor prop in properties)
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
           foreach (T item in data)
           {
               DataRow row = table.NewRow();
               foreach (PropertyDescriptor prop in properties)
                       row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
               table.Rows.Add(row);
           }
           return table;
   }
}

I have copied the list to datatable conversion logic from here.