首页 > 代码库 > 过滤表名 & 拼接字符串

过滤表名 & 拼接字符串

 

/// <summary>        /// 分析sql语句中的表名        /// </summary>                        /// <param name="sql">sql语句</param>        /// <returns>表名</returns>        public string AnalyseTableName(string sql)        {            string tblname = "";            try            {                 Regex reg = new Regex(@"(?in)\b(INTO|FROM|Update)\b(?![^\[\]]*\])\s+(?<table>\[[^\[\]]+\]|\S+)");                MatchCollection mc = reg.Matches(sql);                foreach (Match m in mc)                {                    tblname = m.Groups["table"].Value.Replace("[", "").Replace("]", "");                    if (m.Groups["table"].Value.Contains("("))                    {                        tblname = m.Groups["table"].Value.Substring(0, m.Groups["table"].Value.IndexOf("("));                    }                    else                    {                        tblname = m.Groups["table"].Value;                    }                 }             }            catch (Exception ex)            {                if (UseLog())                {                    log.Error(ex.Message.ToString());                }                return "";            }            finally            {                this.Close();            }            return tblname;        }

 

//分析管理员增删改操作         public void RecordOperation(string sql, SqlParameter[] parms, string BaseMsg)        {             string UserId = "";            string UserName = "";            string UserType = "";                        #region 判断操作人身份            if (Session["SysAccountID"] != null)//大后台系统管理员            {                UserId = Session["SysAccountID"].ToString();                UserName = Session["SysAccountName"].ToString();                UserType = "1";            }            else if (Session["UserRight"] != null)            {                string UserRight = Session["UserRight"].ToString();                if (UserRight[0].ToString() == "1")  //单位初始化用户                {                    UserId = Session["DepartID"].ToString();                    UserName = GetDepartName(Session["DepartID"].ToString());                    UserType = "2";                }                else if (UserRight[1].ToString() == "1") //系统管理员                {                    UserId = Session["UserID"].ToString();                    UserName = Session["UserName"].ToString();                    UserType = "3";                }            }            #endregion            if (UserName.Length > 0)            {                string DetailMsg = UserName;                //BaseMsg不为空表示直接传入操作日志,不需要再分析                if (BaseMsg != "")                {                    DetailMsg += BaseMsg;                    InsertRecord(UserId, UserName, DetailMsg, UserType);                }                else                {                    //需要查询数据库分析sql来确定操作日志内容                    string tblname = AnalyseTableName(sql.Trim());                    DataTable dt = GetRecordingMsg(tblname, sql.Trim().Substring(0, 6));                    if (dt != null && dt.Rows.Count > 0)                    {                        BaseMsg = dt.Rows[0]["BaseMsg"].ToString();                        string ColumnName = dt.Rows[0]["ColumnName"].ToString();                        string ColumnValue = http://www.mamicode.com/"";                        if (ColumnName != null && ColumnName != "")                        {                            //如果当前是删除操作                            if (sql.Trim().Substring(0, 6).ToLower() == "delete")                            {                                //查出delete语句中where子句的起始位置                                int strIndex = sql.ToLower().LastIndexOf("where", sql.Length, sql.Length);                                //拼接寻找主要信息的sql                                string tempsql = " select " + ColumnName + " From " + tblname + " " + sql.Substring(strIndex, sql.Length - strIndex);                                ColumnValue = db.QueryValue(tempsql, parms);                            }                            else                            {                                //插入或更新操作                                foreach (SqlParameter p in parms)                                {                                    if (p.ParameterName.ToLower() == "@" + ColumnName.ToLower())                                    {                                        ColumnValue = p.Value.ToString();                                        break;                                    }                                }                            }                        }                        DetailMsg += BaseMsg.Replace("<detail>", ColumnValue); //替换当前更新记录的重点信息                        #region 插入操作日志                        InsertRecord(UserId, UserName, DetailMsg, UserType);                        #endregion                    }                }            }                    }

 

过滤表名 & 拼接字符串