using Microsoft.CSharp;
using System;
using System.CodeDom.Compiler;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
namespace Utility.Helpers
{
public static class DataTableHelper
{
#region 集合转为对应的DataTable
/// <summary>
///List转换为DataTable
/// </summary>
/// <typeparam name="T">泛型类型</typeparam>
/// <param name="list"></param>
/// <returns></returns>
public static DataTable ToDataTable<T>(this List<T> list) where T : new()
{
DataTable table = new DataTable();
PropertyInfo[] ps = typeof(T).GetProperties();
foreach (PropertyInfo p in ps)
{
if (!p.PropertyType.IsGenericType)
{
table.Columns.Add(ConvertToTableColumnName(p.Name), p.PropertyType);
}
else
{
Type GenericTypeDefinition = p.PropertyType.GetGenericTypeDefinition();
if (GenericTypeDefinition == typeof(Nullable<>))
{
table.Columns.Add(ConvertToTableColumnName(p.Name), Nullable.GetUnderlyingType(p.PropertyType));
}
}
}
foreach (T obj in list)
{
DataRow row = table.NewRow();
foreach (PropertyInfo p in ps)
{
row[ConvertToTableColumnName(p.Name)] = p.GetValue(obj, null);
}
table.Rows.Add(row);
}
return table;
}
#endregion
#region 扩展方法 DataTable转为对应实体的List集合
public static List<T> ToList<T>(this DataTable table) where T : new()
{
List<T> list = new List<T>();
PropertyInfo[] ps = typeof(T).GetProperties();
foreach (DataRow row in table.Rows)
{
T obj = new T();
foreach (DataColumn col in table.Columns)
{
foreach (PropertyInfo p in ps)
{
if (p.Name == ConvertToEntityColumnName(col.ColumnName))
{
if (!p.PropertyType.IsGenericType)
{
p.SetValue(obj,
string.IsNullOrEmpty(row[col.ColumnName].ToString())
? null
: Convert.ChangeType(row[col.ColumnName].ToString(), p.PropertyType), null);
}
else
{
if (p.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
p.SetValue(obj,
string.IsNullOrEmpty(row[col.ColumnName].ToString())
? null
: Convert.ChangeType(row[col.ColumnName],
Nullable.GetUnderlyingType(p.PropertyType)), null);
}
}
}
}
}
list.Add(obj);
}
return list;
}
#endregion
#region 列名转为对应实体类的属性名
public static string ConvertToEntityColumnName(string name)
{
List<string> strList = name.Split('_').ToList();
StringBuilder sb = new StringBuilder();
foreach (string s2 in strList)
{
sb.Append(ReplaceString(s2));
}
return sb.ToString();
}
public static string ReplaceString(string s)
{
return Regex.Replace(s, (string)@"([A-Za-z]{1})([A-Za-z]*)", (MatchEvaluator)MathcEval);
}
private static string MathcEval(Match match)
{
return match.Groups[1].Value.ToUpper() + match.Groups[2].Value.ToLower();
}
#endregion
public static IList ToGenericList(this DataTable dataTable)
{
Type GenericType = DataTableHelper.InitEntityType(dataTable);
Type typeMaster = typeof(List<>);
Type listType = typeMaster.MakeGenericType(GenericType);
IList list = Activator.CreateInstance(listType) as IList;
if (dataTable == null || dataTable.Rows.Count == 0)
return list;
var constructor = GenericType.GetConstructors(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance)
.OrderBy(c => c.GetParameters().Length).First();
var parameters = constructor.GetParameters();
var values = new object[parameters.Length];
foreach (DataRow dr in dataTable.Rows)
{
int index = 0;
foreach (ParameterInfo item in parameters)
{
object itemValue = null;
if (dr[item.Name] != null && dr[item.Name] != DBNull.Value)
{
itemValue = Convert.ChangeType(dr[item.Name], item.ParameterType);
}
values[index++] = itemValue;
}
list.Add(constructor.Invoke(values));
}
return list;
}
#region 属性名转换为数据库对应的列名
//属性名转换为数据库对应的列名
public static string ConvertToTableColumnName(string name)
{
name = Regex.Replace(name, @"([A-Z]{1})([a-z]*)", MatchEval);
name = name.TrimEnd('_');
return name;
}
private static string MatchEval(Match match)
{
return match.Groups[1].Value.ToUpper() + match.Groups[2].Value.ToUpper() + "_";
}
private static Type InitEntityType(DataTable table)
{
CSharpCodeProvider p = new CSharpCodeProvider();
CompilerParameters param = new CompilerParameters();
string s = "namespace __ns" +
"{" +
"public class AAA";
//"{ public AAA(string Name,int ID,string GuidType){this.Name=Name;this.ID=ID;this.GuidType=GuidType;}" +
//" public string Name{ get;set; }" +
//" public int ID{get;set;}" +
//" public string GuidType{get;set;}" +
s += "{ " +
"public AAA(";
for (int i = 0; i < table.Columns.Count; i++)
{
s += "string " + table.Columns[i].ColumnName + ",";
}
s = s.TrimEnd(',') + "){";
for (int i = 0; i < table.Columns.Count; i++)
{
s += "this." + table.Columns[i].ColumnName + "=" + table.Columns[i].ColumnName + ";";
}
s += "}";
for (int i = 0; i < table.Columns.Count; i++)
{
string columnName = table.Columns[i].ColumnName;
s += " public string " + columnName + " {get;set;}\r\n";
}
s += " }}\r\n";
List<object> objs = new List<object>();
for (int i = 0; i < table.Columns.Count; i++)
{
objs.Add("");
}
CompilerResults rel = p.CompileAssemblyFromSource(param, s);
Type t = rel.CompiledAssembly.GetType("__ns.AAA");
object o = t.GetConstructors(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance)
.OrderBy(c => c.GetParameters().Length).First().Invoke(objs.ToArray());
return o.GetType();
}
#endregion
public static List<T> ConvertTomodel<T>(DataTable dt) where T : new()
{
//定义集合
List<T> ts = new List<T>();
//获得此模型的类型
Type type = typeof(T);
//定义一个临时变量
string tempName = "";
//遍历datatable中所有的数据行
foreach (DataRow dr in dt.Rows)
{
T t = new T();
//获得此模型的公共属性
PropertyInfo[] propertys = t.GetType().GetProperties();
//遍历所有属性
foreach (PropertyInfo pi in propertys)
{
//将属性名称赋值给临时变量
tempName = pi.Name;
//检查datatable是否包含此列
if (dt.Columns.Contains(tempName))
{
//判断此属性是否有setter
if (!pi.CanWrite) continue;//改属性不可写，直接跳出
//取值
object value = dr[tempName];
//如果非空，则赋给对象的属性
if (value != DBNull.Value)
{
pi.SetValue(t, value, null);
}
}
}
//对象添加到泛型集合中
ts.Add(t);
}
return ts;
}
}
}