DataTable转换为实体集合
1 using System; 2 using System.Collections; 3 using System.Collections.Generic; 4 using System.Data; 5 using System.Linq; 6 using System.Reflection; 7 using System.Text; 8 9 namespace TransformDTToModel 10 { 11 public class TransformUtil 12 { 13 /// <summary> 14 /// 将DB中改动的内容同步到泛型集合中 15 /// </summary> 16 /// <typeparam name="T">类型</typeparam> 17 /// <param name="source">dt源</param> 18 /// <param name="destinationArray">目标Model集合</param> 19 /// <returns></returns> 20 public static bool ConvertDataTableToModel<T>(DataTable source, List<T> destinationArray) 21 where T : class 22 { 23 if (source == null || destinationArray == null || source.PrimaryKey == null || source.PrimaryKey.Count() <= 0) 24 return false; 25 26 DataTable dtChange = source.GetChanges(); 27 if (dtChange == null) 28 return false; 29 30 List<string> keys = new List<string>(); 31 foreach (var item in source.PrimaryKey) 32 { 33 keys.Add(item.ColumnName); 34 } 35 36 return ConvertDataTableToModel(source, destinationArray, keys); 37 } 38 39 /// <summary> 40 /// 同步table里改动的数据到泛型集合里去（新增，修改，删除） 41 /// </summary> 42 /// <typeparam name="T">类型</typeparam> 43 /// <param name="source">dt源</param> 44 /// <param name="destinationArray">目标Model集合</param> 45 /// <param name="keyColumnArray">主键集合</param> 46 /// <returns></returns> 47 public static bool ConvertDataTableToModel<T>(DataTable source, List<T> destinationArray, List<string> keyColumnArray) 48 where T : class 49 { 50 if (source == null || destinationArray == null || source.Rows.Count == 0 || keyColumnArray == null || keyColumnArray.Count == 0) 51 return false; 52 53 Type modeType = destinationArray.GetType().GetGenericArguments()[0];//模型类型 54 PropertyInfo[] ppInfoArray = modeType.GetProperties();//公共属性集合 55 List<PropertyInfo> listPPInfo = ppInfoArray.ToList();//方便查询 56 //关键列 57 List<PropertyInfo> keyPIArray = listPPInfo.FindAll(x => keyColumnArray.Contains(x.Name)); 58 59 List<T> listToDelete = new List<T>(); 60 //新增的数据 61 DataRow[] drAddArray = source.Select("", "", DataViewRowState.Added); 62 63 object objItem = modeType.Assembly.CreateInstance(modeType.FullName); 64 foreach (DataRow dr in drAddArray) 65 { 66 destinationArray.Add((T)objItem); 67 foreach (System.Reflection.PropertyInfo pi in listPPInfo) 68 { 69 pi.SetValue(destinationArray[destinationArray.Count - 1], dr[pi.Name], null); 70 } 71 } 72 //修改和删除的数据 73 DataView dvForOP = new DataView(source); 74 dvForOP.RowStateFilter = DataViewRowState.Deleted | DataViewRowState.ModifiedCurrent; 75 76 foreach (DataRowView drv in dvForOP) 77 { 78 for (int i = 0; i < destinationArray.Count; i++) 79 { 80 bool blIsTheRow = true; 81 //找出关键列对应的行 82 foreach (System.Reflection.PropertyInfo pInfo in keyPIArray) 83 { 84 object okey = pInfo.GetValue(destinationArray[i], null); 85 if (okey == null) 86 continue; 87 if (drv[pInfo.Name].ToString() != okey.ToString()) 88 { 89 blIsTheRow = false; 90 break; 91 } 92 } 93 if (!blIsTheRow)//非本行 94 continue; 95 //根据行状态同步赋值 96 switch (drv.Row.RowState) 97 { 98 case DataRowState.Modified: 99 { 100 foreach (System.Reflection.PropertyInfo pi in listPPInfo) 101 { 102 if (keyPIArray.Contains(pi))//主键列不更新 103 continue; 104 pi.SetValue(destinationArray[i], drv[pi.Name], null); 105 } 106 } break; 107 case DataRowState.Deleted: 108 { 109 listToDelete.Add(destinationArray[i]); 110 } break; 111 } 112 } 113 } 114 115 for (int i = 0; i < listToDelete.Count; i++) 116 { 117 destinationArray.Remove(listToDelete[i]); 118 } 119 120 return true; 121 } 122 123 /// <summary> 124 /// 将视图转换成泛型集合 125 /// </summary> 126 /// <typeparam name="T">类型</typeparam> 127 /// <param name="dataView">视图</param> 128 /// <param name="model">泛型实例</param> 129 /// <returns></returns> 130 public static List<T> ConvertDataViewToModel<T>(DataView dataView, T model) 131 where T:class 132 { 133 List<T> listReturn = new List<T>(); 134 Type modelType = model.GetType(); 135 DataTable dt = dataView.Table; 136 //获取model所有类型 137 PropertyInfo[] modelProperties = modelType.GetProperties(); 138 139 //遍历所有行，逐行添加对象 140 for (int i = 0; i < dt.Rows.Count; i++) 141 { 142 object obj = modelType.Assembly.CreateInstance(modelType.FullName); 143 listReturn.Add((T)obj); 144 //遍历model所有属性 145 foreach (PropertyInfo pi in modelProperties) 146 { 147 //遍历所有列 148 foreach (DataColumn col in dt.Columns) 149 { 150 //如果列数据类型与model的数据类型相同、名称相同 151 if (col.DataType == pi.PropertyType 152 && col.ColumnName == pi.Name) 153 { 154 pi.SetValue(obj, dt.Rows[i][col.ColumnName], null); 155 } 156 } 157 } 158 } 159 160 return listReturn; 161 } 162 163 /// <summary> 164 /// 将泛型集合类转换成DataTable 165 /// </summary> 166 /// <typeparam name="T">集合项类型</typeparam> 167 /// <param name="sourceArray">集合</param> 168 /// <param name="propertyNameArray">需要返回的列的列名，如需返回所有列，此参数传入null值</param> 169 /// <returns>数据集(表)</returns> 170 public static DataTable ConvertModelToDataTable<T>(IList<T> sourceArray, params string[] propertyNameArray) 171 where T:class 172 { 173 List<string> propertyNameList = new List<string>(); 174 if (propertyNameArray != null) 175 propertyNameList.AddRange(propertyNameArray); 176 177 DataTable result = new DataTable(); 178 //获取结构 179 Type[] typeArr = sourceArray.GetType().GetGenericArguments(); 180 if (typeArr.Length == 0) 181 return result; 182 183 PropertyInfo[] propertys = typeArr[0].GetProperties(); 184 foreach (PropertyInfo pi in propertys) 185 { 186 if (propertyNameList.Count == 0) 187 { 188 result.Columns.Add(pi.Name, pi.PropertyType); 189 } 190 else 191 { 192 if (propertyNameList.Contains(pi.Name)) 193 result.Columns.Add(pi.Name, pi.PropertyType); 194 } 195 } 196 for (int i = 0; i < sourceArray.Count; i++) 197 { 198 ArrayList tempList = new ArrayList(); 199 foreach (PropertyInfo pi in propertys) 200 { 201 if (propertyNameList.Count == 0) 202 { 203 object obj = pi.GetValue(sourceArray[i], null); 204 tempList.Add(obj); 205 } 206 else 207 { 208 if (propertyNameList.Contains(pi.Name)) 209 { 210 object obj = pi.GetValue(sourceArray[i], null); 211 tempList.Add(obj); 212 } 213 } 214 } 215 object[] array = tempList.ToArray(); 216 result.LoadDataRow(array, true); 217 } 218 219 return result; 220 } 221 222 } 223 }
public class People
{
public string Id { get; set; }
public string Name { get; set; }
public string Address { get; set; }
}
单元测试
[TestClass]
public class UnitTestForTransformDTAndModel
{
[TestMethod]
public void TestConvertDataTableToModel()
{
DataTable dt = new DataTable();
dt.Columns.Add("Id", typeof(string));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Address", typeof(string));
dt.PrimaryKey = new DataColumn[] { dt.Columns[0] };
dt.Rows.Add("0001", "张三", "武汉市");
dt.Rows.Add("0002", "李四", "北京市");
dt.AcceptChanges();
dt.Rows.Add("0003", "王五", "深圳市");
List<People> allPeople = new List<People>();
TransformUtil.ConvertDataTableToModel<People>(dt, allPeople);
//断言是不是只有一个数据，平且是只是修改状态的王五这个人
Assert.AreEqual(allPeople.Count, 1);
Assert.AreEqual(allPeople[0].Name, "王五");
}
[TestMethod]
public void TestConvertModelToDataTable()
{
List<People> allPeople = new List<People>()
{
new People(){ Id="0001", Name="张三", Address ="武汉市"},
new People(){ Id="0002", Name="李四", Address ="北京市"},
new People(){ Id="0003", Name="王五", Address ="深圳市"}
};
DataTable dt = TransformUtil.ConvertModelToDataTable<People>(allPeople, null);
//断言是不是有3行数据，数据的列有3列,第1列是不是Id,第一行第二列是不是张三
Assert.AreEqual(dt.Rows.Count, 3);
Assert.AreEqual(dt.Columns.Count, 3);
Assert.AreEqual(dt.Columns[0].ColumnName, "Id");
Assert.AreEqual(dt.Rows[0][1], "张三");
}
}
