Convert a DataTable to a list <T>, where the list class 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 ssspAtdDailyattendanceis a dynamic stored procedure in which the data returned by the stored procedure has a variable number of columns.

Now I want to convert the DataTable dtto List<T>, but how dtto return a variable number of columns, so the type T is not fixed.

So my question is: how to convert dtto List?

Answer 1

Dapper, , . Dapper dynamic .

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

SQL .

:

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);

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

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

Answer 2

, T .

, , ? string, int, date .. ( ).

, "T" .

, , mapper, ValueInjector Automapper.

:

, DataTable , ValueInjector. .

, .

Answer 3

@Douglas Jimenez . type, ( , ), , . , factory, , ( DataTable), .

, , :

• DLR dynamic.
  • ExpandoObject , .
• hashmap (, Dictionary<string, object>)
• Emit.
  • CLR. .
  • , .
  • overkill .

. , .

Answer 4

sll -

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

But since you have a requirement that it can contain different columns, you can have a common class with all the properties and place a check before you get the column, for example,

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 can call the as function,

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

Answer 5

I ran into this problem and decided to 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;
  }
}