Esempio n. 1
0
        /// <summary>
        /// 生成带本地IP与UUID的执行sp日志语句
        /// </summary>
        /// <param name="username">用户名</param>
        /// <param name="SPName">存储过程名</param>
        /// <param name="DbParameterS">入参</param>
        /// <returns></returns>
        string GetLogSP_IP(string username, string SPName, List <DbParameter> DbParameterS)
        {
            IoRyClass ic = new IoRyClass(this.Path);
            string    ps = "<Parameters><SPName>" + SPName + "</SPName>";

            foreach (var item in DbParameterS)
            {
                string cs = "<ParameterName>" + item.ParameterName + "</ParameterName><Value>" + item.Value.ToString() + "</Value>";
                ps += cs;
            }
            ps += "</Parameters>";
            string sql = string.Format(@";insert into log_data (sopreater_str,type_str,SQL_str,olddata_str,createtime_dt,tablename_str,UUID_GUID_str,IP_str,log_data_GUID) 
values ('{0}','{1}','{2}','{3}',{4},'{5}','{6}','{7}','{8}');", username, "存储过程", ps.Replace("'", "''"), "", "getdate()", SPName, IoRyClass.UUID, IoRyClass.PCIP, Guid.NewGuid());

            return(sql);
        }
Esempio n. 2
0
        string GetLogSql_IP(string username, string sql, List <DbParameter> DbParameterS)
        {
            IoRyClass ic = new IoRyClass(this.Path);
            string    ps = "<Parameters><SQL>" + sql + "</SQL>";

            foreach (var item in DbParameterS)
            {
                string cs = "<ParameterName>" + item.ParameterName + "</ParameterName><Value>" + item.Value.ToString() + "</Value>";
                ps += cs;
            }
            ps += "</Parameters>";
            string tablename = "";

            if (sql.ToLower().Contains("insert"))
            {
                tablename = sql.ToLower().Replace("insert", "").Replace("into", "").Split('(')[0].Trim();
                return(string.Format(@";insert into log_data (sopreater_str,type_str,SQL_str,olddata_str,createtime_dt,tablename_str,UUID_GUID_str,IP_str,log_data_GUID) 
values ('{0}','{1}','{2}','{3}',{4},'{5}','{6}','{7}','{8}');", username, "新增", ps.Replace("'", "''"), "", "getdate()", tablename, IoRyClass.UUID, IoRyClass.PCIP, Guid.NewGuid()));
            }
            if (sql.ToLower().Contains("update"))
            {
                tablename = sql.ToLower().Split(new string[] { "set" }, StringSplitOptions.RemoveEmptyEntries)[0].Replace("update", "").Trim();
                string    sqlold   = "select * from " + tablename + " where " + sql.ToLower().Split(new string[] { "where" }, StringSplitOptions.None)[1];
                DataTable oldtable = ic.GetTable_DbParameter(sqlold, DbParameterS);
                if (oldtable.Rows.Count == 0)
                {
                    return(";");
                }
                else
                {
                    //string old = "";
                    XElement xolddata = new XElement("OldData");
                    for (int j = 0; j < oldtable.Rows.Count; j++)
                    {
                        XElement row = new XElement("Row");
                        for (int i = 0; i < oldtable.Columns.Count; i++)
                        {
                            XElement col = new XElement("Column");
                            col.Add(new XAttribute("colName", oldtable.Columns[i].ColumnName));
                            col.Value = oldtable.Rows[j][i].ToString();
                            row.Add(col);
                            //old = old + " [ " + oldtable.Columns[i].ColumnName + " | " + oldtable.Rows[0][i].ToString() + " ]; ";
                        }
                        xolddata.Add(row);
                    }

                    return(string.Format(@";insert into log_data (sopreater_str,type_str,SQL_str,olddata_str,createtime_dt,tablename_str,UUID_GUID_str,IP_str,log_data_GUID) 
values ('{0}','{1}','{2}','{3}',{4},'{5}','{6}','{7}','{8}');", username, "修改", ps.Replace("'", "''"), xolddata.ToString().Replace("'", "''"), "getdate()", tablename, IoRyClass.UUID, IoRyClass.PCIP, Guid.NewGuid()));
                }
            }
            if (sql.ToLower().Contains("delete"))
            {
                string sqlold = "";
                if (sql.ToLower().Contains("from"))
                {
                    if (sql.ToLower().Contains("where"))
                    {
                        tablename = sql.ToLower().Replace("delete", "").Replace("from", "").Split(new string[] { "where" }, StringSplitOptions.None)[0].Trim();
                    }
                    else
                    {
                        tablename = sql.ToLower().Replace("delete", "").Replace("from", "").Trim();
                    }
                    sqlold = sql.ToLower().Replace("delete", "select * ");
                }
                else
                {
                    if (sql.ToLower().Contains("where"))
                    {
                        tablename = sql.ToLower().Replace("delete", "").Split(new string[] { "where" }, StringSplitOptions.None)[0].Trim();
                    }
                    else
                    {
                        tablename = sql.ToLower().Replace("delete", "").Trim();
                    }
                    sqlold = sql.ToLower().Replace("delete", "select * from ");
                }
                DataTable oldtable = ic.GetTable_DbParameter(sqlold, DbParameterS);
                if (oldtable.Rows.Count > 0)
                {
                    XElement xolddata = new XElement("OldData");
                    for (int j = 0; j < oldtable.Rows.Count; j++)
                    {
                        XElement row = new XElement("Row");
                        for (int i = 0; i < oldtable.Columns.Count; i++)
                        {
                            XElement col = new XElement("Column");
                            col.Add(new XAttribute("colName", oldtable.Columns[i].ColumnName));
                            col.Value = oldtable.Rows[j][i].ToString();
                            row.Add(col);
                        }
                        xolddata.Add(row);
                    }


                    return(string.Format(@";insert into log_data (sopreater_str,type_str,SQL_str,olddata_str,createtime_dt,tablename_str,UUID_GUID_str,IP_str,log_data_GUID)
values ('{0}','{1}','{2}','{3}',{4},'{5}','{6}','{7}','{8}');", username, "删除", ps.Replace("'", "''"), xolddata.ToString().Replace("'", "''"), "getdate()", tablename, IoRyClass.UUID, IoRyClass.PCIP, Guid.NewGuid()));
                }
                else
                {
                    return("");
                }
            }
            if (sql.ToLower().Contains("select"))
            {
                if (sql.ToLower().Contains("where"))
                {
                    tablename = sql.ToLower().Split(new string[] { "where" }, StringSplitOptions.None)[0].Split(new string[] { "from" }, StringSplitOptions.None)[1].Trim();
                }
                else
                {
                    tablename = sql.ToLower().Split(new string[] { "from" }, StringSplitOptions.None)[1].Trim();
                }
                return(string.Format(@";insert into log_data (sopreater_str,type_str,SQL_str,olddata_str,createtime_dt,tablename_str,UUID_GUID_str,IP_str,log_data_GUID)
values ('{0}','{1}','{2}','{3}',{4},'{5}','{6}','{7}','{8}');", username, "查询", ps.Replace("'", "''"), "", "getdate()", tablename, IoRyClass.UUID, IoRyClass.PCIP, Guid.NewGuid()));
            }
            return("");
        }