分享一个DataTable转List强类型的类库

类库扩展自Datatable,可以直接用Datatable.ToList<T>()进行转换。为了方便把DataReader装入Datatable,开扩展了一个LoadForReader(this DataTable dt, IDataReader reder),用法很简单。看例子。

public override void Execute()
        {
            using (var conn = DbConnection)
            {
                conn.Open();
                MySqlCommand Command = new MySqlCommand(QuerySql, conn);
                var data = new DataTable().LoadForReader(Command.ExecuteReader());
                foreach (var item in data.ToList<Admin>(new List<ConvertMapping> { ConvertMapping.Convert("userId", "Id") }))
                {
                    AddEntityToContext(item);
                }
            }
            SavaChangs();
        }

DataTable.ToList<T>()有两个可选参数,第一个是“映射关系集合”,另外一个是“自定义转换器”。下面分别说下有什么作用。

1：映射关系集合

     假设现在数据库一张Student表,Student表拥有Id,StudentName,StudetnAge,同样,我们在项目中有一个Student类,Student类有Id,Name,Age字段。在进行转换的时候,会自动找同名的属性进行映射复制,Id可以进行赋值,但是StudentName和Age却不行。所以我们要把StudentName和Name建立映射关系。

           var mappings = new List<ConvertMapping>{
                    ConvertMapping.Convert("StudentName","Name"),
                    ConvertMapping.Convert("StudentAge","Age")
            };

2:自定义转换器

     假设我们在转换的时候,需要把数据库里面StudentAge字段小于18的在转换的时候全部改成18,就需要用到”自定义转换器”,用法如下:

new DataTable().ToList<Admin>(null, (c, r) =>
            {
                if (r["StudentAge"] != DBNull.Value && Convert.ToInt32(r["StudentAge"]) < 18)
                {
                    c.Age = 18;
                }
                else
                {
                    c.Age = Convert.ToInt32(r["StudentAge"]);
                }
            });

          下面贴上代码:

public static class DatatableExtend
    {
        /// <summary>
        /// 表格转换成List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="table"></param>
        /// <param name="mappings"></param>
        /// <returns></returns>
        public static List<T> ToList<T>(this DataTable table, IList<ConvertMapping> mappings = null, Action<T, DataRow> convetAc = null) where T : class, new()
        {
            List<T> result = new List<T>();
            if (table == null || table.Rows.Count == 0)
            {
                return result;
            }
            ConvertMapping mappingResult = null;
            foreach (DataRow row in table.Rows)
            {
                T tResult = new T();
                foreach (DataColumn column in table.Columns)
                {
                    if (mappings != null)
                    {
                        mappingResult = mappings.Where(c => c.SourceColumnName.Equals(column.ColumnName, StringComparison.CurrentCultureIgnoreCase)).SingleOrDefault();
                    }
                    if (mappingResult == null)
                    {
                        mappingResult = ConvertMapping.Convert(column.ColumnName, column.ColumnName);
                    }
                    SetPropertyValue(tResult, mappingResult.NewSourceColumnName, row[mappingResult.SourceColumnName].ToString());
                }
                convetAc?.Invoke(tResult, row);
                result.Add(tResult);
            }
            return result;
        }

        public static DataTable LoadForReader(this DataTable dt, IDataReader reder)
        {
            using (reder)
            {
                dt.Load(reder);
            }
            return dt;
        }

        private static void SetPropertyValue<T>(T otype, string propertyName, object value)
        {
            if (otype == null)
            {
                throw new ArgumentNullException(nameof(otype));
            }
            var proInfo = otype.GetType().GetProperty(propertyName, BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public | BindingFlags.IgnoreCase);
            if (proInfo != null)
            {
                if (proInfo.PropertyType == typeof(bool)&&Microsoft.VisualBasic.Information.IsNumeric(value))
                {
                    value = Convert.ToInt32(value);
                }
                proInfo.SetValue(otype, Convert.ChangeType(value, proInfo.PropertyType));
            }
        }
    }

    public class ConvertMapping
    {
        /// <summary>
        /// 源字段
        /// </summary>
        public string SourceColumnName { get; private set; }

        /// <summary>
        /// 新字段
        /// </summary>
        public string NewSourceColumnName { get; private set; }

        private ConvertMapping() { }
        public static ConvertMapping Convert<TType, TNewType>(Expression<Func<TType, string>> sourece, Expression<Func<TNewType, string>> newSource)
        {
            ConvertMapping mapping = new ConvertMapping();
            mapping.SourceColumnName = sourece.Parameters.SingleOrDefault()?.Name;
            mapping.NewSourceColumnName = newSource.Parameters.SingleOrDefault()?.Name;
            return mapping;
        }

        public static ConvertMapping Convert(string sourece, string newSource)
        {
            ConvertMapping mapping = new ConvertMapping();
            mapping.SourceColumnName = sourece;
            mapping.NewSourceColumnName = newSource;
            return mapping;
        }
    }