How can I convert a DataTable into a Dynamic object?
Solution 1
How about with DynamicObject
:
public static class DataTableX
{
public static IEnumerable<dynamic> AsDynamicEnumerable(this DataTable table)
{
// Validate argument here..
return table.AsEnumerable().Select(row => new DynamicRow(row));
}
private sealed class DynamicRow : DynamicObject
{
private readonly DataRow _row;
internal DynamicRow(DataRow row) { _row = row; }
// Interprets a member-access as an indexer-access on the
// contained DataRow.
public override bool TryGetMember(GetMemberBinder binder, out object result)
{
var retVal = _row.Table.Columns.Contains(binder.Name);
result = retVal ? _row[binder.Name] : null;
return retVal;
}
}
}
You could also try overriding TrySetMember
if you wish to make the dynamic-row writable.
Usage:
DataTable table = ...
var dynamicTable = table.AsDynamicEnumerable();
var firstRowsNameField = dynamicTable.First().Name;
Solution 2
class Program
{
static void Main()
{
var dt = new DataTable();
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Rows.Add(1, "x");
dt.Rows.Add(2, "y");
List<dynamic> dynamicDt = dt.ToDynamic();
Console.WriteLine(dynamicDt.First().ID);
Console.WriteLine(dynamicDt.First().Name);
}
}
public static class DataTableExtensions
{
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;
}
}
Solution 3
Previous suneelsarraf codes only generated runtime dynamic object with property as string. following update will generate each property based on DataTable column's data type.
public static class DataTableExtension
{
/// <summary>
/// Convert a database data table to a runtime dynamic definied type collection (dynamic class' name as table name).
/// </summary>
/// <param name="dt"></param>
/// <param name="className"></param>
/// <returns></returns>
public static List<dynamic> ToDynamicList(DataTable dt, string className)
{
return ToDynamicList(ToDictionary(dt), getNewObject(dt.Columns, className));
}
private static List<Dictionary<string, object>> ToDictionary(DataTable dt)
{
var columns = dt.Columns.Cast<DataColumn>();
var Temp = dt.AsEnumerable().Select(dataRow => columns.Select(column =>
new { Column = column.ColumnName, Value = dataRow[column] })
.ToDictionary(data => data.Column, data => data.Value)).ToList();
return Temp.ToList();
}
private static List<dynamic> ToDynamicList(List<Dictionary<string, object>> list, Type TypeObj)
{
dynamic temp = new List<dynamic>();
foreach (Dictionary<string, object> step in list)
{
object Obj = Activator.CreateInstance(TypeObj);
PropertyInfo[] properties = Obj.GetType().GetProperties();
Dictionary<string, object> DictList = (Dictionary<string, object>)step;
foreach (KeyValuePair<string, object> keyValuePair in DictList)
{
foreach (PropertyInfo property in properties)
{
if (property.Name == keyValuePair.Key)
{
if (keyValuePair.Value != null && keyValuePair.Value.GetType() != typeof(System.DBNull))
{
if (keyValuePair.Value.GetType() == typeof(System.Guid))
{
property.SetValue(Obj, keyValuePair.Value, null);
}
else
{
property.SetValue(Obj, keyValuePair.Value, null);
}
}
break;
}
}
}
temp.Add(Obj);
}
return temp;
}
private static Type getNewObject(DataColumnCollection columns, string className)
{
AssemblyName assemblyName = new AssemblyName();
assemblyName.Name = "YourAssembly";
System.Reflection.Emit.AssemblyBuilder assemblyBuilder = Thread.GetDomain().DefineDynamicAssembly(assemblyName, AssemblyBuilderAccess.Run);
ModuleBuilder module = assemblyBuilder.DefineDynamicModule("YourDynamicModule");
TypeBuilder typeBuilder = module.DefineType(className, TypeAttributes.Public);
foreach (DataColumn column in columns)
{
string propertyName = column.ColumnName;
FieldBuilder field = typeBuilder.DefineField(propertyName, column.DataType, FieldAttributes.Public);
PropertyBuilder property = typeBuilder.DefineProperty(propertyName, System.Reflection.PropertyAttributes.None, column.DataType, new Type[] { column.DataType });
MethodAttributes GetSetAttr = MethodAttributes.Public | MethodAttributes.HideBySig;
MethodBuilder currGetPropMthdBldr = typeBuilder.DefineMethod("get_value", GetSetAttr, column.DataType, new Type[] { column.DataType }); // Type.EmptyTypes);
ILGenerator currGetIL = currGetPropMthdBldr.GetILGenerator();
currGetIL.Emit(OpCodes.Ldarg_0);
currGetIL.Emit(OpCodes.Ldfld, field);
currGetIL.Emit(OpCodes.Ret);
MethodBuilder currSetPropMthdBldr = typeBuilder.DefineMethod("set_value", GetSetAttr, null, new Type[] { column.DataType });
ILGenerator currSetIL = currSetPropMthdBldr.GetILGenerator();
currSetIL.Emit(OpCodes.Ldarg_0);
currSetIL.Emit(OpCodes.Ldarg_1);
currSetIL.Emit(OpCodes.Stfld, field);
currSetIL.Emit(OpCodes.Ret);
property.SetGetMethod(currGetPropMthdBldr);
property.SetSetMethod(currSetPropMthdBldr);
}
Type obj = typeBuilder.CreateType();
return obj;
}
}
Solution 4
There are some ORMs that can read directly from DB to dynamic ExpandoObject
. For example petapoco (and read here the example)
Or you could try something similar:
var dt = new DataTable();
var dns = new List<dynamic>();
foreach (var item in dt.AsEnumerable())
{
// Expando objects are IDictionary<string, object>
IDictionary<string, object> dn = new ExpandoObject();
foreach (var column in dt.Columns.Cast<DataColumn>())
{
dn[column.ColumnName] = item[column];
}
dns.Add(dn);
}
// Now you can do something like dns[0].MyColumnName
// or recast to IDictionary<string, object> and do
// something like casted["MyColumnName"]
Solution 5
You can use an extension like this:
using System;
using System.Collections.Generic;
using System.Data;
using System.Dynamic;
public static class DataTableExtensions
{
public static IEnumerable<dynamic> AsDynamicEnumerable(this DataTable table)
{
if (table == null)
{
yield break;
}
foreach (DataRow row in table.Rows)
{
IDictionary<string, object> dRow = new ExpandoObject();
foreach (DataColumn column in table.Columns)
{
var value = row[column.ColumnName];
dRow[column.ColumnName] = Convert.IsDBNull(value) ? null : value;
}
yield return dRow;
}
}
}
Usage:
var dataTable = GetDataTableFromSomewhere();
var dynamicTable = dataTable.AsDynamicEnumerable();
var firstRowIDColumn = dynamicTable.First().ID;
var lastRowIDColumn = dynamicTable.Last()["ID"];
But I prefered an IDataReader approach if possible.
Related videos on Youtube
BrunoLM
I'm a Developer for Fun! Things I like Code Play games Anime / Manga Contact information [email protected] LinkedIn Facebook Site - https://brunolm.github.io/ Blog - http://blog.codingwise.com/
Updated on December 19, 2020Comments
-
BrunoLM over 3 years
How can I convert a
DataTable
inIEnumerable<dynamicObject>
?For example, I want to convert any
DataTable
ID | Name DI | emaN --------- or --------- 1 | x 2 | x 2 | y 1 | y
In a list of objects
// list 1 (ex 1) // list 2 (ex 2) { { { ID = 1, Name = "x" } { DI = 2, emaN = "x" } { ID = 2, Name = "y" } { DI = 1, emaN = "y" } } }
So
list1.First().ID // 1 list2.First().emaN // "x"
How can I do it?
-
BrunoLM over 12 yearsI am thinking in something more generic.
-
Yahia over 12 yearsyou mean - without knowledge of the fields ?
-
BrunoLM over 12 yearsYes, exactly. New fields can appear or disappear, or it might be completely different fields.....
-
Yahia over 12 yearsthen your best option is to use some ORM like petapoco or Dapper - see the answer from xanatos
-
Amr Badawy about 11 yearsyou can't apply indexing with [] to an ExpandoObject, replace code with one existing here stackoverflow.com/a/7794962/84216
-
Crystian Leão over 9 yearsThere is a little big mistake man! The line
dynamicDt.Add(dyn);
Should be outside the inner loop. -
kinnu almost 7 yearsThis solution worked perfectly for me as I just want to bind to datagrid from datatable.
-
Hypenate about 5 yearsTo my .NET Core project I had to add the NuGet Microsoft.CSharp.