Detailed Explanation of DataTable to Entity Instance in C

Because the query function of Linq is very powerful, I will convert the data obtained from the database into the entity collection List for convenience of processing < T > .

Beginning with the hard-coded way, easy to understand, but the versatility is extremely low, the following is the code in the control desk:

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Demo1
{
  class Program
  {
    static void Main(string[] args)
    {
      DataTable dt = Query();
      List<Usr> usrs = new List<Usr>(dt.Rows.Count);
      // Hard coding is efficient, but not flexible enough. If the entity changes, it needs to modify the code 
      foreach (DataRow dr in dt.Rows)
      {
        Usr usr = new Usr { ID = dr.Field<Int32?>("ID"), Name = dr.Field<String>("Name") };
        usrs.Add(usr);
      }
      usrs.Clear();
    }
    /// <summary>
    ///  Query data 
    /// </summary>
    /// <returns></returns>
    private static DataTable Query()
    {
      DataTable dt = new DataTable();
      dt.Columns.Add("ID", typeof(Int32));
      dt.Columns.Add("Name", typeof(String));
      for (int i = 0; i < 1000000; i++)
      {
        dt.Rows.Add(new Object[] { i, Guid.NewGuid().ToString() });
      }
      return dt;
    }
  }
  class Usr
  {
    public Int32? ID { get; set; }
    public String Name { get; set; }
  }
}

Later, we do this with reflection, and assign values to the attributes of entities with reflection, so that it can be common to all entities, and there is no need to modify the code after adding attributes.

The procedure is as follows:

static class EntityConvert
  {
    /// <summary>
    /// DataTable Convert to List<T>
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="dt"></param>
    /// <returns></returns>
    public static List<T> ToList<T>(this DataTable dt) where T : class, new()
    {
      List<T> colletion = new List<T>();
      PropertyInfo[] pInfos = typeof(T).GetProperties();
      foreach (DataRow dr in dt.Rows)
      {
        T t = new T();
        foreach (PropertyInfo pInfo in pInfos)
        {
          if (!pInfo.CanWrite) continue;
          pInfo.SetValue(t, dr[pInfo.Name]);
        }
        colletion.Add(t);
      }
      return colletion;
    }
  }

Add an extension method to make the program more general. However, the efficiency is not very good. One million rows of data are "only two columns", and the conversion takes 2 seconds

Later, I thought of using delegation as the prototype of delegation as follows

Func<DataRow, Usr> func = dr => new Usr { ID = dr.Field<Int32?>("ID"), Name = dr.Field<String>("Name") };

The code is as follows:

static void Main(string[] args)
    {
      DataTable dt = Query();
      Func<DataRow, Usr> func = dr => new Usr { ID = dr.Field<Int32?>("ID"), Name = dr.Field<String>("Name") };
      List<Usr> usrs = new List<Usr>(dt.Rows.Count);
      Stopwatch sw = Stopwatch.StartNew();
      foreach (DataRow dr in dt.Rows)
      {
        Usr usr = func(dr);
        usrs.Add(usr);
      }
      sw.Stop();
      Console.WriteLine(sw.ElapsedMilliseconds);
      usrs.Clear();
      Console.ReadKey();
    }

The speed is really much faster. I tested it on my computer for 1 time, which takes 0.4 seconds. But the problem comes again. This can only be used for the class Usr. We have to find a way to abstract this class into a generic T, which has both the efficiency of delegation and the generality of generics.

The problem is that the above delegation is generated dynamically. After one afternoon's tossing, the method of generating delegation dynamically is finally tossed out. The dynamic Lambda expression is mainly used

public static class EntityConverter
  {
    /// <summary>
    /// DataTable Generate Entity 
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="dataTable"></param>
    /// <returns></returns>
    public static List<T> ToList<T>(this DataTable dataTable) where T : class, new()
    {
      if (dataTable == null || dataTable.Rows.Count <= 0) throw new ArgumentNullException("dataTable", " The current object is null Unable to build expression tree ");
      Func<DataRow, T> func = dataTable.Rows[0].ToExpression<T>();
      List<T> collection = new List<T>(dataTable.Rows.Count);
      foreach (DataRow dr in dataTable.Rows)
      {
        collection.Add(func(dr));
      }
      return collection;
    }
    /// <summary>
    ///  Generate expression 
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="dataRow"></param>
    /// <returns></returns>
    public static Func<DataRow, T> ToExpression<T>(this DataRow dataRow) where T : class, new()
    {
      if (dataRow == null) throw new ArgumentNullException("dataRow", " The current object is null  Unable to convert to entity ");
      ParameterExpression paramter = Expression.Parameter(typeof(DataRow), "dr");
      List<MemberBinding> binds = new List<MemberBinding>();
      for (int i = 0; i < dataRow.ItemArray.Length; i++)
      {
        String colName = dataRow.Table.Columns[i].ColumnName;
        PropertyInfo pInfo = typeof(T).GetProperty(colName);
        if (pInfo == null) continue;
        MethodInfo mInfo = typeof(DataRowExtensions).GetMethod("Field", new Type[] { typeof(DataRow), typeof(String) }).MakeGenericMethod(pInfo.PropertyType);
        MethodCallExpression call = Expression.Call(mInfo, paramter, Expression.Constant(colName, typeof(String)));
        MemberAssignment bind = Expression.Bind(pInfo, call);
        binds.Add(bind);
      }
      MemberInitExpression init = Expression.MemberInit(Expression.New(typeof(T)), binds.ToArray());
      return Expression.Lambda<Func<DataRow, T>>(init, paramter).Compile();
    }
  }

After testing, it takes 0.47 seconds to convert entities under the same conditions with this method. In addition to the Lambda expression generated by reflection for the first time, the expression used directly for subsequent transformations.