//20200320 修改了数据库,并且放到了tool和demo里,不再用下面的
//这里Log对应的数据库设计
/*
* CREATE TABLE [dbo].[Log_H](
* [int_index] [int] IDENTITY(1,1) NOT NULL,
* [str_opreater] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
* [str_Type] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
* [str_tablename] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
* [str_Sql] [nvarchar](4000) COLLATE Chinese_PRC_CI_AS NULL,
* [str_Old] [nvarchar](max) COLLATE Chinese_PRC_CI_AS NULL,
* [dat_time] [datetime] NULL,
* CONSTRAINT [PK_Log_H] PRIMARY KEY CLUSTERED
* (
* [int_index] ASC
* )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
* ) ON [PRIMARY]
*/
/// <summary>
/// 生成带本地IP与UUID的sql日志语句 20200323 上面的不带本地ip的用不到了
/// </summary>
/// <param name="username">用户名</param>
/// <param name="sql">sql</param>
/// <returns></returns>
string GetLogSql_IP(string username, string sql)
{
IoRyClass ic = new IoRyClass(this.Path);
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, "新增", sql.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(sqlold);
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, "修改", sql.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(sqlold);
if (oldtable.Rows.Count > 0)
{
//string old = "";
//for (int i = 0; i < oldtable.Columns.Count; i++)
//{
// old = old + " [ " + oldtable.Columns[i].ColumnName + " | " + oldtable.Rows[0][i].ToString() + " ]; ";
//}
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, "删除", sql.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, "查询", sql.Replace("'", "''"), "", "getdate()", tablename, IoRyClass.UUID, IoRyClass.PCIP, Guid.NewGuid()));
}
return("");
}
