/// <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);
}
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("");
}