.net对各表的操作详细到字段的更改记录的日志

存入数据库中，目前的字段包括操作人，操作时间，sql语句，被修改的字段，字段原值,操作人的身份。

  /// <summary>
        /// 添加操作日志
        /// </summary>
        /// <param name="strsql">执行的sql语句</param>
        /// <param name="username">执行人</param>
        /// <param name="dentity">执行人身份</param>
        /// <param name="cmdparms">参数</param>
        public static void AddLog(StringBuilder strsql,string username,string dentity,params SqlParameter[] cmdparms)
        {
            Graduate.Model.OperateLog model = new Graduate.Model.OperateLog();
            model.dentity = dentity;
            model.operatUser = username;
            model.operatTime = DateTime.Now.ToString();
            string content = strsql.ToString().Substring(0, strsql.ToString().IndexOf("values"));
            StringBuilder sblog = new StringBuilder();
            sblog.Append(content + " values ");
            sblog.Append(" ( ");
            foreach (SqlParameter sp in cmdparms)
            {
                sblog.Append("'" + sp.Value + "',");
            }
            sblog.ToString().TrimEnd(',');
            sblog.Insert(sblog.Length - 1, ')');
            model.operatContent = sblog.ToString().TrimEnd(',');
            model.PrevValue = "";
            model.NewValue = "";
            Graduate.DAL.OperateLog dal = new OperateLog();
            dal.Add(model);
        }

        /// <summary>
        /// 删除操作日志
        /// </summary>
        /// <param name="strsql">sql语句</param>
        /// <param name="username">执行人</param>
        /// <param name="dentity">执行人身份</param>
        /// <param name="id">关键字段</param>
        /// <param name="hash">参数(Hasttable)</param>
        public static void DeleteLog(StringBuilder strsql, string username, string dentity, string id,Hashtable hash)
        {
            Graduate.Model.OperateLog logmodel = new Graduate.Model.OperateLog();
            logmodel.dentity = dentity;
            logmodel.operatUser = username;
            StringBuilder sblog = new StringBuilder();
            string conten = strsql.ToString().Substring(0, strsql.ToString().IndexOf('='));
            sblog.Append(conten + " = ");
            sblog.Append(id.ToString());
            logmodel.operatContent = sblog.ToString();
            logmodel.operatTime = DateTime.Now.ToString();
            string prevval = "";
            foreach (DictionaryEntry de in hash)
            {
                prevval += de.Key + "=" + de.Value + ",";
            }
            prevval = prevval.TrimEnd(',');
            logmodel.PrevValue = prevval;
            logmodel.NewValue = "";
            Graduate.DAL.OperateLog logdal = new OperateLog();
            logdal.Add(logmodel);
        }

        /// <summary>
        /// 更新操作日志
        /// </summary>
        /// <param name="obj1">原对象</param>
        /// <param name="parmhash">更新字段集(Hashtable)</param>
        /// <param name="username">执行人</param>
        /// <param name="dentity">执行人身份</param>
        /// <param name="tableName">表名</param>
        /// <param name="keytable">存储条件和值(Hashtable)</param>
        public static void UpdateLog(object obj1,Hashtable parmhash,string username,string dentity,string tableName,Hashtable keytable)
        {
            PropertyInfo[] properties = obj1.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
           
            StringBuilder consb = new StringBuilder();
            Hashtable prevhash = new Hashtable();
            Hashtable newhash = new Hashtable();
            consb.Append(" update " + tableName + " set ");
            foreach (PropertyInfo pi in properties)
            {
                string piname = pi.Name;
                string pivalue = Convert.ToString(pi.GetValue(obj1, null));
                foreach (DictionaryEntry de in parmhash)
                {
                    if (piname == de.Key.ToString())
                    {
                        if (pivalue != de.Value.ToString())
                        {
                            if (!keytable.Contains(de.Key))
                            {
                                consb.Append(de.Key + "='" + de.Value + "'" + ",");
                            }
                            prevhash.Add(piname, pivalue);
                            newhash.Add(piname, de.Value);
                        }
                    }
                }
            }
            consb.Remove(consb.Length - 1, 1);
            consb.Append(" where ");
            foreach (DictionaryEntry de in keytable)
            {
                consb.Append(de.Key + "='" + de.Value + "'" + " and ");
            }
            consb.Remove(consb.Length - 4, 3); //去掉"and "
            string prevval = "";
            foreach (DictionaryEntry de in prevhash)
            {
                prevval += de.Key + "=" + de.Value + ",";
            }
            prevval = prevval.TrimEnd(',');
            string newval = "";
            foreach (DictionaryEntry de in newhash)
            {
                newval += de.Key + "=" + de.Value + ",";
            }
            newval = newval.TrimEnd(',');
            Graduate.Model.OperateLog logmodel = new Graduate.Model.OperateLog();
            logmodel.dentity = dentity;
            logmodel.NewValue = newval;
            logmodel.operatContent = consb.ToString();
            logmodel.operatTime = DateTime.Now.ToString();
            logmodel.operatUser = username;
            logmodel.PrevValue = prevval;
            Graduate.DAL.OperateLog logdal = new OperateLog();
            logdal.Add(logmodel);
        }

    由于使用的是sqlhelper类，所以一般sql语句类似"insert into A (field) values (@field)",如果传入了参数，sql语句并不改变，因为使用的是StringBuilder连接字符串，而我需要具体到保存入数据库的sql也能 执行的地步，所以需要做各种拼接转换工作.

     有一个地方结果是正确的，我却不明白为什么.看“添加操作”的 “ model.operatContent = sblog.ToString().TrimEnd(',');”；

拼接完insert语句后，总是在后面跟了个“，”还不知道是怎么产生的。需要trimEnd去掉","才算正确，想不明白

   另外一个需要说的是，之所以放在DAL层，是因为传入了"执行人"和"执行人身份"这个两个字段，那么就需要为各个类的增改删方法添加这个方法了，这个工 作量就比较大，是很不好的，主要是因为不想修改sqlhelper。其实完全可以利用反射找出各个对象的属性和值，再一一比较，方法能够更加抽象出来，这 个等以后再说