介绍:List/IEnumerable转换到DataTable/DataView,以及DataTable转换到List
正文:
一、List<T>/IEnumerable转换到DataTable/DataView
方法一:
1 /// <summary> 2 /// Convert a List{T} to a DataTable. 3 /// </summary> 4 private DataTable ToDataTable<T>(List<T> items) 5 { 6 var tb = new DataTable(typeof (T).Name); 7 8 PropertyInfo[] props = typeof (T).GetProperties(BindingFlags.Public | BindingFlags.Instance); 9 10 foreach (PropertyInfo prop in props) 11 { 12 Type t = GetCoreType(prop.PropertyType); 13 tb.Columns.Add(prop.Name, t); 14 } 15 16 foreach (T item in items) 17 { 18 var values = new object[props.Length]; 19 20 for (int i = 0; i < props.Length; i++) 21 { 22 values[i] = props[i].GetValue(item, null); 23 } 24 25 tb.Rows.Add(values); 26 } 27 28 return tb; 29 } 30 31 /// <summary> 32 /// Determine of specified type is nullable 33 /// </summary> 34 public static bool IsNullable(Type t) 35 { 36 return !t.IsValueType || (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>)); 37 } 38 39 /// <summary> 40 /// Return underlying type if type is Nullable otherwise return the type 41 /// </summary> 42 public static Type GetCoreType(Type t) 43 { 44 if (t != null && IsNullable(t)) 45 { 46 if (!t.IsValueType) 47 { 48 return t; 49 } 50 else 51 { 52 return Nullable.GetUnderlyingType(t); 53 } 54 } 55 else 56 { 57 return t; 58 } 59 }
方法二:
1 public static DataTable ToDataTable<T>(IEnumerable<T> collection) 2 { 3 var props = typeof(T).GetProperties(); 4 var dt = new DataTable(); 5 dt.Columns.AddRange(props.Select(p => new DataColumn(p.Name, p.PropertyType)).ToArray()); 6 if (collection.Count() > 0) 7 { 8 for (int i = 0; i < collection.Count(); i++) 9 { 10 ArrayList tempList = new ArrayList(); 11 foreach (PropertyInfo pi in props) 12 { 13 object obj = pi.GetValue(collection.ElementAt(i), null); 14 tempList.Add(obj); 15 } 16 object[] array = tempList.ToArray(); 17 dt.LoadDataRow(array, true); 18 } 19 } 20 return dt; 21 }
二、DataTable转换到List
方法一:
public static IList<T> ConvertTo<T>(DataTable table) { if (table == null) { return null; } List<DataRow> rows = new List<DataRow>(); foreach (DataRow row in table.Rows) { rows.Add(row); } return ConvertTo<T>(rows); } public static IList<T> ConvertTo<T>(IList<DataRow> rows) { IList<T> list = null; if (rows != null) { list = new List<T>(); foreach (DataRow row in rows) { T item = CreateItem<T>(row); list.Add(item); } } return list; } public static T CreateItem<T>(DataRow row) { T obj = default(T); if (row != null) { obj = Activator.CreateInstance<T>(); foreach (DataColumn column in row.Table.Columns) { PropertyInfo prop = obj.GetType().GetProperty(column.ColumnName); try { object value = row[column.ColumnName]; prop.SetValue(obj, value, null); } catch { //You can log something here //throw; } } } return obj; }
方法二:
把查询结果以DataTable返回很方便,但是在检索数据时又很麻烦,没有模型类型检索方便。
所以很多人都是按照以下方式做的:
1 // 获得查询结果 2 DataTable dt = DbHelper.ExecuteDataTable(...); 3 // 把DataTable转换为IList<UserInfo> 4 IList<UserInfo> users = ConvertToUserInfo(dt);
问题:如果此系统有几十上百个模型,那不是每个模型中都要写个把DataTable转换为此模型的方法吗?
解决:能不能写个通用类,可以把DataTable转换为任何模型,呵呵,这就需要利用反射和泛型了
1 using System; 2 using System.Collections.Generic; 3 using System.Text; 4 using System.Data; 5 using System.Reflection; 6 namespace NCL.Data 7 { 8 /// <summary> 9 /// 实体转换辅助类 10 /// </summary> 11 public class ModelConvertHelper<T> where T : new() 12 { 13 public static IList<T> ConvertToModel(DataTable dt) 14 { 15 // 定义集合 16 IList<T> ts = new List<T>(); 17 18 // 获得此模型的类型 19 Type type = typeof(T); 20 string tempName = ""; 21 22 foreach (DataRow dr in dt.Rows) 23 { 24 T t = new T(); 25 // 获得此模型的公共属性 26 PropertyInfo[] propertys = t.GetType().GetProperties(); 27 foreach (PropertyInfo pi in propertys) 28 { 29 tempName = pi.Name; // 检查DataTable是否包含此列 30 31 if (dt.Columns.Contains(tempName)) 32 { 33 // 判断此属性是否有Setter 34 if (!pi.CanWrite) continue; 35 36 object value = dr[tempName]; 37 if (value != DBNull.Value) 38 pi.SetValue(t, value, null); 39 } 40 } 41 ts.Add(t); 42 } 43 return ts; 44 } 45 } 46 }
使用方式:
1 // 获得查询结果 2 DataTable dt = DbHelper.ExecuteDataTable(...); 3 // 把DataTable转换为IList<UserInfo> 4 IList<UserInfo> users = ModelConvertHelper<UserInfo>.ConvertToModel(dt);