.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。其实完全可以利用反射找出各个对象的属性和值,再一一比较,方法能够更加抽象出来,这 个等以后再说

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。