Converting DataTable to List of Generic Type

Question

How do I convert DataTable to List of Generic Type, when Type must be defined at runtime?

I found several example on stackOverflow, but all of these examples use predefined Generic Type to convert to list. In my case I don't have defined type, and Type must be determined at runtime.

In my case Generic Type needs to be Dynamically build with having DataTable columns as each property, and property type will be string.

 string sqlQuery = "SELECT Z.Column2, A.Column1, C.* FROM Z LEFT JOIN A ON A.ID = Z.AId bla bla bla"; 
 //SQL will be very complex due to joins, selectively picked column from different tables.

 con.ConnectionString = "";
 con.Open();

 SqlDataAdapter da = new SqlDataAdapter(sqlQuery , con);
 da.Fill(resultDt);

Now I need to Convert resultDT to List, where Type is determined at run time, and it varies when SQL query varies.

Answer 1

After playing around for couple of hours, I found the way to achieve my goal. This may not be the efficient way of doing but it worked for me.

var properties = new Dictionary<string, string>();
List<dynamic> retList = new List<dynamic>();
foreach (DataRow dr in resultDatatable.Rows)
{
   foreach (DataColumn dc in resultDatatable.Columns)
   {
     string val = (string.IsNullOrEmpty(dr[dc].ToString()) ? "":dr[dc].ToString());
 
     if (!properties.ContainsKey(dc.ColumnName.ToString()))
      properties.Add(dc.ColumnName.ToString(), val);
   }

   var dynamicObject = new ExpandoObject() as IDictionary<string, Object>;
   foreach (var property in properties)
   {
      dynamicObject.Add(property.Key, property.Value);
   }

   retList.Add(dynamicObject);
}

Answer 2

you can use solve problem with Reflection and Execution

 public static List<T> CastToList<T>(this DataTable table) where T : class, new()
{
    var items = table.AsEnumerable().Select(dataRow =>
    {
        T item = new T();
        foreach (var info in item.GetType().GetProperties())
        {
            PropertyInfo pInfo = item.GetType().GetProperty(info.Name);

            if (dataRow[info.Name] is DBNull) continue;
            Object value = Convert.ChangeType(dataRow[info.Name], pInfo.PropertyType);
            pInfo.SetValue(item, value);
        }

        return item;
    }).ToList();

    return items;

}

Call Method

List<Person> data = dataBase.GetData() .CastToList<Person>();

get Data with Ado.net

public DataTable GetData()
{
    DataTable dtPerson = new DataTable();
 using (SqlConnection con = new SqlConnection(connectionString))
 {
    string sqlQuery = "select * from TableName";
    con.Open();
    SqlDataAdapter da = new SqlDataAdapter(sqlQuery, con);
    try
    {
        da.Fill(dtPerson);

    }
    catch (Exception ex)
    {
        con.Close();
    }
}
    return dtPerson;
}