十年河东，十年河西，莫欺少年穷！
学无止境，精益求精！
有这样一个需求，要求操作数据库时‘留痕’，具体需求如下：
假设操作如下一张表：
create table Student ( StudentId varchar(100) primary key not null, StudentName nvarchar(20),--姓名 StudentNo varchar(10),--学号 SutdentSex varchar(1),--性别 ProvinceFrom nvarchar(50),--省份 CityFrom nvarchar(50),--城市 CreateDate datetime default(getdate()), )
需求如下：当更新学生表时，要求记录如下信息：
create table ChangeHistory ( HistoryId varchar(100) primary key not null, ChangeField nvarchar(50),--所修改字段的中文含义 BeforeChangeValue nvarchar(100),--修改前的值 AfterChangeValue nvarchar(100),--修改后的值 ChangeBy nvarchar(20),--修改人 ChangeTime datetime,--修改时间 )
看到上述两张表，你会不会感觉很简单呢？
可能会有人立马想到一个笨方法，如下：
List<StudentModel> DataBaseStudentList = new List<StudentModel>();//用于模拟数据库学生表中的4条记录 protected void Page_Load(object sender, EventArgs e) { //学生一 StudentModel M1 = new StudentModel() { StudentId = "01", CityFrom = "苏州", ProvinceFrom = "江苏", StudentName = "啊龙", StudentNo = "081309201", SutdentSex = "男" }; //学生二 StudentModel M2 = new StudentModel() { StudentId = "02", CityFrom = "苏州", ProvinceFrom = "江苏", StudentName = "阿狗", StudentNo = "081309202", SutdentSex = "男" }; //学生三 StudentModel M3 = new StudentModel() { StudentId = "03", CityFrom = "苏州", ProvinceFrom = "江苏", StudentName = "阿猫", StudentNo = "081309203", SutdentSex = "女" }; //学生四 StudentModel M4 = new StudentModel() { StudentId = "04", CityFrom = "苏州", ProvinceFrom = "江苏", StudentName = "阿猪", StudentNo = "081309204", SutdentSex = "女" }; DataBaseStudentList.Add(M1); DataBaseStudentList.Add(M2); DataBaseStudentList.Add(M3); DataBaseStudentList.Add(M4); //数据库填充完后，我们修改学生表 string StudentId = "03"; StudentModel ef = DataBaseStudentList.Where(A => A.StudentId == StudentId).FirstOrDefault(); if (ef != null) { ef.StudentName = "阿狸猫";//修改了姓名 } //调用历史变更的方法。 UpdateStudent(ef, StudentId); } public void UpdateStudent(StudentModel newModel, string StudentId) { List<ChangeHistoryModel> ChangeHistoryList = new List<ChangeHistoryModel>(); var oldef = DataBaseStudentList.Where(A => A.StudentId == StudentId).FirstOrDefault(); if(oldef.CityFrom!= newModel.CityFrom) { ChangeHistoryModel M = new ChangeHistoryModel() { HistoryId = Guid.NewGuid().ToString(), ChangeField = "学生所属城市", BeforeChangeValue = oldef.CityFrom, AfterChangeValue = newModel.CityFrom, ChangeBy = "修改人姓名", ChangeTime = DateTime.Now }; } if (oldef.ProvinceFrom != newModel.ProvinceFrom) { ChangeHistoryModel M = new ChangeHistoryModel() { HistoryId = Guid.NewGuid().ToString(), ChangeField = "学生所属省份", BeforeChangeValue = oldef.ProvinceFrom, AfterChangeValue = newModel.ProvinceFrom, ChangeBy = "修改人姓名", ChangeTime = DateTime.Now }; } if (oldef.StudentName != newModel.StudentName) { ChangeHistoryModel M = new ChangeHistoryModel() { HistoryId = Guid.NewGuid().ToString(), ChangeField = "学生姓名", BeforeChangeValue = oldef.StudentName, AfterChangeValue = newModel.StudentName, ChangeBy = "修改人姓名", ChangeTime = DateTime.Now }; } /* ... ... 等等其他字段的修改 ... */ //最后将历史变更插入数据库 }
上述的方法是通过每个字段作比对，如果不一致，则生成变更记录，这种方法实现了上述要求，但如果有上百个字段，那么...
OK，下面我们用反射结合.config文件来实现！
1、新建SelectItem.config文件，用来将数据表字段对应中文含义，如下：
<?xml version="1.0" encoding="utf-8"?> <config> <!--学生信息解读 反射案例 历史变更记录--> <StudentInfo value="StudentName">学生姓名</StudentInfo> <StudentInfo value="StudentNo">学号</StudentInfo> <StudentInfo value="SutdentSex">性别</StudentInfo> <StudentInfo value="ProvinceFrom">省份</StudentInfo> <StudentInfo value="CityFrom">城市</StudentInfo> </config>
2、创建两个Model，一个OldModel，代表没修改之前的Model，一个NewModel，代表修改后的Model，如下：
public class StudentOldModel { public string StudentId { get; set; } public string StudentName { get; set; } public string StudentNo { get; set; } public string SutdentSex { get; set; } public string ProvinceFrom { get; set; } public string CityFrom { get; set; } public string CreateDate { get; set; } } public class StudentNewModel { public string StudentId { get; set; } public string StudentName { get; set; } public string StudentNo { get; set; } public string SutdentSex { get; set; } public string ProvinceFrom { get; set; } public string CityFrom { get; set; } public string CreateDate { get; set; } }
3、构造老的OldModel，和新的NewModel，如下：
//来自数据库 StudentOldModel M1 = new StudentOldModel() { StudentId = "01", CityFrom = "苏州", ProvinceFrom = "江苏", StudentName = "啊龙", StudentNo = "081309201", SutdentSex = "男" }; //用户修改后 StudentNewModel M2 = new StudentNewModel() { StudentId = "01", CityFrom = "郑州", ProvinceFrom = "河南", StudentName = "啊龙", StudentNo = "081309201", SutdentSex = "男" };
4、利用反射结合.config文件记录变更如下：
public static void UpdateHistory(StudentNewModel newModel, StudentOldModel oldModel, string modifyUserName, string modifyWeb) { List<ChangeHistoryModel> ChangeHistoryModelList = new List<ChangeHistoryModel>(); Type t = newModel.GetType(); PropertyInfo[] PropertyList = t.GetProperties(); // Type tSource = oldModel.GetType(); PropertyInfo[] SourcePropertyList = tSource.GetProperties(); var ls = PropertyList.GroupBy(a => a.PropertyType.Name).Select(g => (new { a = g.Key, count = g.Count() })).ToList(); foreach (PropertyInfo item in PropertyList) { string name = item.Name; string Filed =SelectHelper. GetFiled(name); object value = item.GetValue(newModel, null); PropertyInfo ef = SourcePropertyList.Where(A => A.Name.ToLower() == name.ToLower()).FirstOrDefault(); string PropertyType = item.PropertyType.Name; if (PropertyType == "String") { string ToValue = value == null ? "" : value.ToString(); if (ef != null) { object Value2 = ef.GetValue(oldModel, null); string ScoureValue = Value2 == null ? "" : Value2.ToString(); if ((ToValue != ScoureValue)) { if (!string.IsNullOrEmpty(Filed)) { ChangeHistoryModel CM = new ChangeHistoryModel() { ChangeField = Filed, BeforeChangeValue = ScoureValue, AfterChangeValue = ToValue, HistoryId = Guid.NewGuid().ToString(), ChangeBy = "修改人姓名", ChangeTime = DateTime.Now }; ChangeHistoryModelList.Add(CM); } } } } } }
上述就可以动态完成变更记录的生成了！需要说明的是：不管数据库字段是什么数据类型，我们要把所有字段转化为Model中的字符串类型String,
需要辅助类如下：
public class SelectHelper { private static SelectItemConfig configManager; public static Dictionary<string, string> GetConfigByKey(string key, string name = "SelectItem.config") { string mapPath = HttpContext.Current.Server.MapPath("~/"); configManager = new SelectItemConfig(mapPath, name); Dictionary<string, string> dic = configManager.getItemListByKey(key); return dic.OrderBy(p => p.Value).ToDictionary(o => o.Key, p => p.Value); ; } public static Dictionary<string, string> GetConfigByKeyForFields(string key, string name = "SelectItem.config") { string mapPath = HttpContext.Current.Server.MapPath("~/"); configManager = new SelectItemConfig(mapPath, name); Dictionary<string, string> dic = configManager.getItemListByKey(key); return dic; } /// <summary> /// 获取XML文件中某一节点的值 /// </summary> /// <param name="name"></param> /// <returns></returns> public static string GetFiled(string name) { Dictionary<string, string> dic = SelectHelper.GetConfigByKey("StudentInfo"); string Filed = string.Empty; foreach (var item in dic) { if (item.Key.ToLower() == name.ToLower()) { Filed = item.Value; break; } } return Filed; } } public class SelectItemConfig { private XmlDocument _doc; private XmlNode _rootNode; public SelectItemConfig(String mapPath, string name) { _doc = new XmlDocument(); _doc.Load(mapPath + name); _rootNode = _doc.DocumentElement; } public void setConfig(String key, String value) { if (value == null || (value != null && value.Length == 0)) { return; } if (_rootNode.SelectSingleNode(key) == null || (_rootNode.SelectSingleNode(key) != null && _rootNode.SelectSingleNode(key).ChildNodes.Count == 0)) { XmlElement newNode = _doc.CreateElement(key); newNode.InnerText = value; _rootNode.AppendChild(newNode); } else { _rootNode.SelectSingleNode(key).ChildNodes[0].InnerText = value; } } /// <summary> /// 获取单个节点的值 /// </summary> /// <param name="key"></param> /// <returns></returns> public String getConfig(string key) { if (_rootNode.SelectSingleNode(key) != null) if (_rootNode.SelectSingleNode(key).ChildNodes.Count > 0) return _rootNode.SelectSingleNode(key).ChildNodes[0].Value; else return ""; else return null; } /// <summary> /// 获取key值对应的多个节点的值的集合 /// </summary> /// <param name="key"></param> /// <returns></returns> public Dictionary<string, string> getItemListByKey(string key) { Dictionary<string, string> dic = new Dictionary<string, string>() { }; if (_rootNode.SelectSingleNode(key) != null) { XmlNodeList xmlNode = _rootNode.SelectNodes(key); int nodeCount = _rootNode.SelectNodes(key).Count; if (nodeCount > 0) { for (int i = 0; i < nodeCount; i++) { var item = _rootNode.SelectNodes(key).Item(i); dic.Add(item.Attributes["value"].Value, item.InnerText);//Select 下拉框 Value属性 } } } return dic; } public Boolean SaveConfig(String mapPath) { try { _doc.Save(mapPath + @"FlexPaper.config"); return true; } catch { return false; } } }
@陈卧龙的博客