基于Ado.Net返回DataTable转换为动态匿名集合。

获取测试数据：

DataTable dt1 = new DataTable();//创建Table1

dt1.Columns.Add("ID", typeof(string));

dt1.Columns.Add("NAME", typeof(string));

dt1.Columns.Add("AGE", typeof(int));

dt1.Columns.Add("SEX", typeof(string));


dt1.Columns.Add("Money", typeof(decimal));


dt1.PrimaryKey = new DataColumn[] { dt1.Columns["ID"] };

for (int i = 0; i < 3; i++)
{

    DataRow dr = dt1.NewRow();

    dr["ID"] = "00" + i.ToString();

    dr["NAME"] = "00-" + i.ToString();

    dr["AGE"] = 15 + i;

    dr["SEX"] = null;//"M";

    dr["Money"] = DBNull.Value;

    dt1.Rows.Add(dr);

}

获取DataTable中所有ColumnsName的集合：

static List<(string,Type)> GetColumnsByDataTable(DataTable dt)
{
    List<(string, Type)> strColumns = new();
    if (dt.Columns.Count > 0)
    {
        int columnNum = 0;
        columnNum = dt.Columns.Count;
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            Type type = dt.Columns[i].DataType;
            strColumns.Add(new (dt.Columns[i].ColumnName, type));
        }
    }
    return strColumns;
}

正文现在开始，进行转化动态匿名集合：

// 获取DataTable中所有ColumnsName的集合
var ColumnsList = GetColumnsByDataTable(dt1);
List<dynamic> dynamics = new();
foreach (DataRow dr in dt1.Rows)
{
    dynamic dobj = new System.Dynamic.ExpandoObject();
    var dic = (IDictionary<string, object>)dobj;
    foreach (var item in ColumnsList)
    {
        if(dr[item.Item1] != DBNull.Value && dr[item.Item1] != null)
        {
            dic[item.Item1] = dr[item.Item1];
        }
        else
        {
            Type type = item.Item2;
            switch (type.Name)
            {
                case "Int32":  dic[item.Item1] = 0;break;
                case "String": dic[item.Item1] = ""; break;
                case "Double":
                case "Float":
                case "Decimal": dic[item.Item1] = 0.00;break;
                case "Boolean": dic[item.Item1] = false; break;
            }
        }
    }
    dynamics.Add(dobj);
}
// 转换JSON输出一下
string json = JsonConvert.SerializeObject(dynamics);

Console.WriteLine(json);

 最后结果：