Convert DataTable to List where Class of List is Dynamic

Question

I am executing a stored procedure from C# code

private void DataToGrid()
{
    DataTable dt = new DataTable();
    string[,] aryPara = new string[,] 
    {{ "@pClassId", "10" } ,
    { "@pMediumId", "11" } ,
    { "@pStreamId", "12" } ,
    { "@pSessionId","13" } ,
    { "@pSectionId", "15" } ,
    { "@pShiftId", "16" } ,
    { "@pDateId", "17" } };

    dt = CallStoredProcedureUsingAdapterFillDataTabel("ssspAtdDailyattendance", aryPara);  
    DatagridView1.DataSource = dt;
}

public DataTable CallStoredProcedureUsingAdapterFillDataTabel(string StoredProcedureName, [Optional] string[,] aryParameters)
{
    SqlConnection con = new SqlConnection();
    con.ConnectionString = "Data Source=AIS-OCTACORE\SQLserver2008r2;Initial Catalog= SchoolSoulDataTest; integrated security=SSPI";
    con.open();
    SqlCommand lSQLCmd = new SqlCommand();
    SqlDataAdapter adp = new SqlDataAdapter();
    DataTable dt = new DataTable();
    lSQLCmd.CommandType = CommandType.StoredProcedure;
    lSQLCmd.CommandText = StoredProcedureName;
    lSQLCmd.CommandTimeout = 300;
    try
    {
        for (int i = 0; i < aryParameters.GetLength(0); i++)
        {
            lSQLCmd.Parameters.Add(new SqlParameter(aryParameters[i, 0], aryParameters[i, 1]));
        }
    }
    catch (Exception ex)
    {

    }
    lSQLCmd.Connection = con;
    adp.SelectCommand = lSQLCmd;
    adp.Fill(dt);
    clsConnectionClose();
    return dt;
}

Where ssspAtdDailyattendance is a Dynamic Stored Procedure in which data returned by the stored procedure has variable number of columns.

Now I want to convert DataTable dt to a List<T> but as dt return variable number of columns thus type of T is not Fixed

So my question is how could I convert dt to List?

score 2 · Answer 1 · answered Dec 30 '13 at 10:30

I would recommend you use Dapper for it instead of writing and maintaining the boilerplate code yourself. Dapper supports dynamic objects.

Execute a query and map it to a list of dynamic objects

public static IEnumerable<dynamic> Query (this IDbConnection cnn, string sql, 
object param = null, SqlTransaction transaction = null, bool buffered = true)

This method will execute SQL and return a dynamic list.

Example usage:

var rows = connection.Query("select 1 A, 2 B union all select 3, 4");

((int)rows[0].A).IsEqualTo(1);    
((int)rows[0].B).IsEqualTo(2);    
((int)rows[1].A).IsEqualTo(3);   
((int)rows[1].B).IsEqualTo(4);

Website: http://code.google.com/p/dapper-dot-net/

NuGet: http://www.nuget.org/packages/Dapper/

i didn't get it how colud i do this can you write your code briefly — Amit Bisht, Dec 30 '13 at 10:37
Just use Dapper to execute your sproc instead of using `SqlCommand` directly yourself. — Jakub Konecki, Dec 30 '13 at 10:44

score 2 · Answer 2 · edited May 23 '17 at 11:52

If I understood right your question, T can be more than one type.

So for example, first row could be a Person and second row a Dog? Or did you mean different types as string, int, date and so on (value types).

Regardless you will need to create your "T" entities to represent the data from the datatable and map it.

What you are looking for is potentially a mapper such as ValueInjector or Automapper.

Or even map manually:

This answer here shows how to map a DataTable to a class using ValueInjector. It can be give you a headstart.

I hope this helps.

score 1 · Answer 3 · edited May 23 '17 at 11:44

@Douglas Jimenez has a relevant question. If only the type varies, this can be easily solved with an interface for common fields (or object if no field is common) and different types implementing the other fields. When you build your list, you then use a factory to decide which type to build for the row (or the wholde DataTable), and you build the object using the row.

However, if the type is not known at compile time, there are many solutions:

Use the DLR with the dynamic keyword.
- The ExpandoObject may do what you need.
Use a hashmap (like a Dictionary<string, object>)
Emit the type with the Emit framework.
- This solution is actually complicated and will lead you to advanced parts of the CLR. However, this works really well.
- If you choose this path, this tutorial will help you greatly.
- This may very well be overkill for your needs.

There may be other solutions as well. Good luck with the solution you choose.

score 0 · Answer 4 · edited May 23 '17 at 11:52

According to answer posted by sll you can use something like,

IList<Class1> items = dt.AsEnumerable().Select(row => 
    new Class1
        {
            id = row.Field<string>("id"),
            name = row.Field<string>("name")
        }).ToList();

But since you have requirement that it may contain different columns then you can have a common class with all the properties and place a check before getting column something like,

public class Class1
{
    private int id;
    public string name;

    public Class1(DataRow row)
    {
        id = (int)GetColumnValue(row, "id");
        name = (string)GetColumnValue(row, "name");
    }

    public object GetColumnValue(DataRow row, string column)
    {
        return row.Table.Columns.Contains(column) ? row[column] : null;
    }
}

And then you may invoke function as,

IList<Class1> items = dt.AsEnumerable().Select(row => new Class1(row)).ToList();

score -1 · Answer 5 · edited Jan 18 '16 at 06:30

I faced this issue and soled use this code from

http://musthaan.com/2016/01/18/datatable-to-dynamic-list/

public static class HelperExtensions
{
  public static List<dynamic> ToDynamic(this DataTable dt)
  {
    var dynamicDt = new List<dynamic>();
    foreach (DataRow row in dt.Rows)
    {
      dynamic dyn = new ExpandoObject();
      dynamicDt.Add(dyn);
      foreach (DataColumn column in dt.Columns)
      {
         var dic = (IDictionary<string, object>)dyn;
         dic[column.ColumnName] = row[column];
      }
    }
    return dynamicDt;
  }
}

Convert DataTable to List where Class of List is Dynamic

5 Answers5