/// <summary>
/// 比较新/旧数据,如有修改则生成对应的修改日志.
/// </summary>
/// <param name="changes">修改后的数据</param>
/// <param name="tracedFields">跟踪的字段列表</param>
/// <param name="keyField">记录主键,比较新旧数据时用于定位</param>
/// <param name="tableName">表名</param>
/// <returns></returns>
private IList CompareTable(string logID, DataTable originalData, DataTable changes, string[] tracedFields, string keyField, string tableName, bool isMaster)
{
IList logs = new ArrayList();
#region 循环原始数据比较修改后的数据
foreach (DataRow row in originalData.Rows)
{
string keyValue = row[keyField].ToString();//主键值
DataRow[] current = changes.Select(keyField + "='" + keyValue + "'");
if (current.Length == 0)
{
continue; //没有记录,不写日志
}
LogDef log = new LogDef();
log.GUID32 = logID;
log.DocNo = keyValue;
log.KeyFieldName = keyField;
log.LogDate = DateTime.Now;
log.LogUser = _Loginer.Account; //当前登录用户
log.OPType = LogOPType.LogEdit; //新增或修改
log.TableName = tableName;
log.IsMaster = isMaster;
if (current[0].RowState == DataRowState.Deleted)
{
continue; //已经删除,不写入日志
}
foreach (string fieldName in tracedFields)
{
//if (IsNumbericField(row.Table.Columns[fieldName].DataType))
string c1 = ConvertEx.ToString(row[fieldName]); //取原始数据
string c2 = ConvertEx.ToString(current[0][fieldName]); //取最新修改值
//数字类型要特别处理
if (CompareNumbericField(row.Table.Columns[fieldName], c1, c2))
{
log.AppendDetail(tableName, fieldName, c1, c2);//对比有差异\
}
else if (c1 != c2)
{
log.AppendDetail(tableName, fieldName, c1, c2);//对比有差异
}
}
if (log.HasDetail)
{
logs.Add(log); //有修改记录
}
}
#endregion
return(logs);
}
/// <summary>
/// 保存日志
/// </summary>
/// <param name="log"></param>
private void WriteLog(LogDef log)
{
string sql1 = "INSERT INTO tb_Log(GUID32 ,LogUser ,LogDate ,OPType ,DocNo ,TableName ,KeyFieldName,IsMaster) " +
" VALUES (@GUID32,@LogUser,@LogDate,@OPType,@DocNo,@TableName,@KeyFieldName,@IsMaster)";
string sql2 = "INSERT INTO [tb_LogDtl] ([GUID32],[TableName],[FieldName],[OldValue],[NewValue]) VALUES(@GUID32,@TableName,@FieldName,@OldValue,@NewValue)";
SqlConnection conn = DataProvider.Instance.CreateConnection(_Loginer.DBName);
try
{
if (log.IsMaster == true) //注意! 只有主表才写入tb_Log表
{
SqlCommand cmd = new SqlCommand(sql1, conn);
cmd.Parameters.Add("@GUID32", SqlDbType.VarChar, 32, "GUID32").Value = log.GUID32;
cmd.Parameters.Add("@LogUser", SqlDbType.VarChar, 20, "LogUser").Value = log.LogUser;
cmd.Parameters.Add("@LogDate", SqlDbType.DateTime, 8, "LogDate").Value = log.LogDate;
cmd.Parameters.Add("@OPType", SqlDbType.Int, 4, "OPType").Value = (int)log.OPType;
cmd.Parameters.Add("@DocNo", SqlDbType.VarChar, 20, "DocNo").Value = log.DocNo;
cmd.Parameters.Add("@TableName", SqlDbType.VarChar, 30, "TableName").Value = log.TableName;
cmd.Parameters.Add("@KeyFieldName", SqlDbType.VarChar, 30, "KeyFieldName").Value = log.KeyFieldName;
cmd.Parameters.Add("@IsMaster", SqlDbType.VarChar, 1, "IsMaster").Value = log.IsMaster ? "Y" : "N";
cmd.ExecuteNonQuery();
}
//log明细
foreach (LogDefDtl d in log.Details)
{
SqlCommand cmdDtl = new SqlCommand(sql2, conn);
cmdDtl.Parameters.Add("@GUID32", SqlDbType.VarChar, 32, "GUID32").Value = d.GUID32; //明细表外键
cmdDtl.Parameters.Add("@TableName", SqlDbType.VarChar, 50, "TableName").Value = d.TableName;
cmdDtl.Parameters.Add("@FieldName", SqlDbType.VarChar, 50, "FieldName").Value = d.FieldName;
cmdDtl.Parameters.Add("@OldValue", SqlDbType.VarChar, 250, "OldValue").Value = d.OldValue;
cmdDtl.Parameters.Add("@NewValue", SqlDbType.VarChar, 250, "NewValue").Value = d.NewValue;
cmdDtl.ExecuteNonQuery();
}
}
catch
{
}
}
/// <summary>
/// 保存日志
/// </summary>
/// <param name="log"></param>
private void WriteLog(LogDef log)
{
string sql1 = "";
string sql2 = "";
if (_Loginer.DbType == DbAcessTyp.SQLServer)
{
sql1 = "INSERT INTO sys_Log_Table(fid,GUID32 ,LogUser ,LogDate ,OPType ,DocNo ,TableName ,KeyFieldName,IsMaster) " +
" VALUES (@fid,@GUID32,@LogUser,@LogDate,@OPType,@DocNo,@TableName,@KeyFieldName,@IsMaster)";
sql2 = "INSERT INTO sys_Log_TableDtl (fid,GUID32,TableName,FieldName,OldValue,NewValue) VALUES(@fid,@GUID32,@TableName,@FieldName,@OldValue,@NewValue)";
}
else
{
sql1 = "INSERT INTO sys_Log_Table(fid,GUID32 ,LogUser ,LogDate ,OPType ,DocNo ,TableName ,KeyFieldName,IsMaster) " +
" VALUES (:fid,:GUID32,:LogUser,:LogDate,:OPType,:DocNo,:TableName,:KeyFieldName,:IsMaster)";
sql2 = "INSERT INTO sys_Log_TableDtl (fid,GUID32,TableName,FieldName,OldValue,NewValue) VALUES(:fid,:GUID32,:TableName,:FieldName,:OldValue,:NewValue)";
}
IDbConnection conn = DataProvider.Instance.CreateConnection(_Loginer.DBName);
IDbCommand cmd = null;
IDbCommand cmdDtl = null;
try
{
if (_Loginer.DbType == DbAcessTyp.SQLServer)
{
if (log.IsMaster == true) //注意! 只有主表才写入tb_Log表
{
cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql1;
cmd.Connection = conn;
SqlParameter spara = new SqlParameter("@fid", SqlDbType.VarChar);
spara.Value = DocNoTool.GetTableID(_Loginer.DBName, "sys_Log_Table", "fid", _Loginer);
cmd.Parameters.Add(spara);
spara = new SqlParameter("@GUID32", SqlDbType.VarChar);
spara.Value = log.GUID32;
cmd.Parameters.Add(spara);
spara = new SqlParameter("@LogUser", SqlDbType.VarChar);
spara.Value = log.LogUser;
cmd.Parameters.Add(spara);
spara = new SqlParameter("@LogDate", SqlDbType.DateTime);
spara.Value = log.LogDate;
cmd.Parameters.Add(spara);
spara = new SqlParameter("@OPType", SqlDbType.Int);
spara.Value = log.OPType;
cmd.Parameters.Add(spara);
spara = new SqlParameter("@DocNo", SqlDbType.VarChar);
spara.Value = log.DocNo;
cmd.Parameters.Add(spara);
spara = new SqlParameter("@TableName", SqlDbType.VarChar);
spara.Value = log.TableName;
cmd.Parameters.Add(spara);
spara = new SqlParameter("@KeyFieldName", SqlDbType.VarChar);
spara.Value = log.KeyFieldName;
cmd.Parameters.Add(spara);
spara = new SqlParameter("@IsMaster", SqlDbType.VarChar);
spara.Value = log.IsMaster ? "Y" : "N";
cmd.Parameters.Add(spara);
cmd.ExecuteNonQuery();
}
//log明细
foreach (LogDefDtl d in log.Details)
{
cmdDtl = new SqlCommand();;
cmdDtl.CommandType = CommandType.Text;
cmdDtl.CommandText = sql2;
cmdDtl.Connection = conn;
SqlParameter spara = new SqlParameter("@fid", SqlDbType.VarChar);
spara.Value = DocNoTool.GetTableID(_Loginer.DBName, "sys_Log_TableDtl", "fid", _Loginer);
cmdDtl.Parameters.Add(spara);
spara = new SqlParameter("@GUID32", SqlDbType.VarChar);
spara.Value = d.GUID32;
cmdDtl.Parameters.Add(spara);
spara = new SqlParameter("@TableName", SqlDbType.VarChar);
spara.Value = d.TableName;
cmdDtl.Parameters.Add(spara);
spara = new SqlParameter("@FieldName", SqlDbType.VarChar);
spara.Value = d.FieldName;
cmdDtl.Parameters.Add(spara);
spara = new SqlParameter("@OldValue", SqlDbType.VarChar);
spara.Value = d.OldValue;
cmdDtl.Parameters.Add(spara);
spara = new SqlParameter("@NewValue", SqlDbType.VarChar);
spara.Value = d.NewValue;
cmdDtl.Parameters.Add(spara);
cmdDtl.ExecuteNonQuery();
}
}
else
{
if (log.IsMaster == true) //注意! 只有主表才写入tb_Log表
{
cmd = new OracleCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql1;
cmd.Connection = conn;
OleDbParameter spara = new OleDbParameter("fid", OleDbType.VarChar);
spara.Value = DocNoTool.GetTableID(_Loginer.DBName, "sys_Log_Table", "fid", _Loginer);
cmd.Parameters.Add(spara);
spara = new OleDbParameter("GUID32", OleDbType.VarChar);
spara.Value = log.GUID32;
cmd.Parameters.Add(spara);
spara = new OleDbParameter("LogUser", OleDbType.VarChar);
spara.Value = log.LogUser;
cmd.Parameters.Add(spara);
spara = new OleDbParameter("LogDate", OleDbType.Date);
spara.Value = log.LogDate;
cmd.Parameters.Add(spara);
spara = new OleDbParameter("OPType", OleDbType.Integer);
spara.Value = log.OPType;
cmd.Parameters.Add(spara);
spara = new OleDbParameter("DocNo", OleDbType.VarChar);
spara.Value = log.DocNo;
cmd.Parameters.Add(spara);
spara = new OleDbParameter("TableName", OleDbType.VarChar);
spara.Value = log.TableName;
cmd.Parameters.Add(spara);
spara = new OleDbParameter("KeyFieldName", OleDbType.VarChar);
spara.Value = log.KeyFieldName;
cmd.Parameters.Add(spara);
spara = new OleDbParameter("IsMaster", OleDbType.VarChar);
spara.Value = log.IsMaster ? "Y" : "N";
cmd.Parameters.Add(spara);
cmd.ExecuteNonQuery();
}
//log明细
foreach (LogDefDtl d in log.Details)
{
cmdDtl = new SqlCommand();;
cmdDtl.CommandType = CommandType.Text;
cmdDtl.CommandText = sql2;
cmdDtl.Connection = conn;
OleDbParameter spara = new OleDbParameter("fid", OleDbType.VarChar);
spara.Value = DocNoTool.GetTableID(_Loginer.DBName, "sys_Log_TableDtl", "fid", _Loginer);
cmdDtl.Parameters.Add(spara);
spara = new OleDbParameter("GUID32", OleDbType.VarChar);
spara.Value = d.GUID32;
cmdDtl.Parameters.Add(spara);
spara = new OleDbParameter("TableName", OleDbType.VarChar);
spara.Value = d.TableName;
cmdDtl.Parameters.Add(spara);
spara = new OleDbParameter("FieldName", OleDbType.VarChar);
spara.Value = d.FieldName;
cmdDtl.Parameters.Add(spara);
spara = new OleDbParameter("OldValue", OleDbType.VarChar);
spara.Value = d.OldValue;
cmdDtl.Parameters.Add(spara);
spara = new OleDbParameter("NewValue", OleDbType.VarChar);
spara.Value = d.NewValue;
cmdDtl.Parameters.Add(spara);
cmdDtl.ExecuteNonQuery();
}
}
}
catch
{
}
}
