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

分享⼀个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)&&rmation.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;
}
}。