public static Nullable <Guid> GetLastAuditGroupID_Wizard(AuditLog.AuditLogType AuditLogType, string AuditName, string AuditSource, Guid?AuditKey)
{
using (SqlConnection Connection = SQLHelper.GetConnection())
{
SqlTransaction Transaction = Connection.BeginTransaction();
using (SqlCommand cmd = new SqlCommand("", Connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT al.AuditGroupID");
sql.AppendLine("from AuditLog al");
sql.AppendLine("WHERE al.AuditLogType = @AuditLogType");
sql.AppendLine("AND al.AuditSource = @AuditSource");
sql.AppendLine("AND al.AuditKey = @AuditKey");
sql.AppendLine("AND al.AuditName = @AuditName");
sql.AppendLine("ORDER BY al.CreatedDate DESC");
cmd.CommandText = sql.ToString();
cmd.Connection = Connection;
cmd.Transaction = Transaction;
cmd.CommandTimeout = int.MaxValue;
cmd.Parameters.AddWithValue("@AuditLogType", AuditLogType);
cmd.Parameters.AddWithValue("@AuditSource", AuditSource);
cmd.Parameters.AddWithValue("@AuditKey", AuditKey);
cmd.Parameters.AddWithValue("@AuditName", AuditName);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
if (dataTable != null && dataTable.Rows.Count > 0 && !string.IsNullOrEmpty(dataTable.Rows[0][0].ToString()))
{
return(new Guid(dataTable.Rows[0][0].ToString()));
}
else
{
return(null);
}
}
}
}
}
private static void CreateLog_Wizard(SqlConnection Connection, SqlTransaction Transaction, AuditLog.AuditLogType AuditLogType, Guid?AuditKey, string AuditSource, string AuditName, string ReferenceTable, Guid?ReferenceID, DataRow CurrentData, DataRow NewData, Guid?UserID, string UserName)
{
if (CurrentData != null || NewData != null)
{
AuditFieldContainer objContainer = new AuditFieldContainer();
List <AuditField> fieldList = new List <AuditField>();
List <AuditField> fullFieldList = new List <AuditField>();
AuditLog.AuditAction auditAction = default(AuditLog.AuditAction);
Nullable <Guid> auditGroupID = default(Nullable <Guid>);
if (CurrentData == null)
{
auditAction = AuditLog.AuditAction.Insert;
//Add
foreach (DataColumn co in NewData.Table.Columns)
{
AuditField fieldObj = new AuditField();
fieldObj.FieldName = co.ColumnName;
fieldObj.FieldValue = NewData[co.ColumnName].ToString();
fieldList.Add(fieldObj);
}
foreach (DataColumn co in NewData.Table.Columns)
{
AuditField fieldObj = new AuditField();
fieldObj.FieldName = co.ColumnName;
fieldObj.FieldValue = NewData[co.ColumnName].ToString();
fullFieldList.Add(fieldObj);
}
}
else if (NewData == null)
{
//Delete
auditAction = AuditLog.AuditAction.Delete;
auditGroupID = GetLastAuditGroupID_Wizard(Connection, Transaction, AuditLogType, AuditName, AuditSource, AuditKey);
foreach (DataColumn co in CurrentData.Table.Columns)
{
AuditField fieldObj = new AuditField();
fieldObj.FieldName = co.ColumnName;
fieldObj.FieldValue = CurrentData[co.ColumnName].ToString();
fieldList.Add(fieldObj);
}
foreach (DataColumn co in CurrentData.Table.Columns)
{
AuditField fieldObj = new AuditField();
fieldObj.FieldName = co.ColumnName;
fieldObj.FieldValue = CurrentData[co.ColumnName].ToString();
fullFieldList.Add(fieldObj);
}
}
else
{
//Update
auditAction = AuditLog.AuditAction.Update;
auditGroupID = GetLastAuditGroupID_Wizard(Connection, Transaction, AuditLogType, AuditName, AuditSource, AuditKey);
foreach (DataColumn co in CurrentData.Table.Columns)
{
if (CurrentData[co.ColumnName].ToString() != NewData[co.ColumnName].ToString())
{
AuditField fieldObj = new AuditField();
fieldObj.FieldName = co.ColumnName;
fieldObj.FieldValue = NewData[co.ColumnName].ToString();
fieldList.Add(fieldObj);
}
}
foreach (DataColumn co in CurrentData.Table.Columns)
{
AuditField fieldObj = new AuditField();
fieldObj.FieldName = co.ColumnName;
fieldObj.FieldValue = NewData[co.ColumnName].ToString();
fullFieldList.Add(fieldObj);
}
}
if (fieldList.Count > 0)
{
objContainer.AuditFields = fieldList.ToArray();
objContainer.FullAuditFields = fullFieldList.ToArray();
AccelTeam.Utilities.Data Data = new AccelTeam.Utilities.Data(ConfigurationSettings.AppSettings["CRM"].ToString());
Guid auditID = Guid.NewGuid();
string xml = SerializeToXML(objContainer);
if (!auditGroupID.HasValue)
{
auditGroupID = Guid.NewGuid();
}
AuditLog.InsertAuditLog(Connection, Transaction, auditID, AuditSource, AuditKey, AuditName, AuditLogType, auditAction, "", ReferenceTable, ReferenceID, xml, auditGroupID, UserID, UserName);
}
}
}
public static DataTable InsertAuditLog(Guid?
AuditID, string AuditSource, Guid?AuditKey, string AuditName, AuditLog.AuditLogType AuditLogType, AuditLog.AuditAction AuditAction, string AuditContent, string ReferenceTable,
Guid?ReferenceID, string LogXML, Guid?AuditGroupID, Guid?UserID, string Username)
{
using (SqlConnection Connection = SQLHelper.GetConnection())
{
using (SqlCommand cmd = new SqlCommand("", Connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("INSERT INTO AuditLog (AuditID, AuditSource, AuditKey, AuditName, AuditLogType, AuditAction, AuditContent, ReferenceTable, ReferenceID, LogXML, AuditGroupID, CreatedBy, CreatedByName, ModifiedBy, ModifiedByName) ");
sql.AppendLine("VALUES (@AuditID, @AuditSource, @AuditKey, @AuditName, @AuditLogType, @AuditAction, @AuditContent, @ReferenceTable, @ReferenceID, @LogXML, @AuditGroupID, @UserID, @Username, @UserID, @Username) ");
cmd.CommandText = sql.ToString();
cmd.CommandTimeout = int.MaxValue;
cmd.Parameters.AddWithValue("@AuditID", AuditID);
cmd.Parameters.AddWithValue("@AuditSource", AuditSource);
cmd.Parameters.AddWithValue("@AuditKey", AuditKey);
cmd.Parameters.AddWithValue("@AuditName", AuditName);
cmd.Parameters.AddWithValue("@AuditLogType", AuditLogType);
cmd.Parameters.AddWithValue("@AuditAction", AuditAction);
cmd.Parameters.AddWithValue("@AuditContent", AuditContent);
cmd.Parameters.AddWithValue("@ReferenceTable", ConvertToNull(ReferenceTable));
cmd.Parameters.AddWithValue("@ReferenceID", ConvertToNull(ReferenceID));
cmd.Parameters.AddWithValue("@LogXML", LogXML);
cmd.Parameters.AddWithValue("@AuditGroupID", AuditGroupID);
cmd.Parameters.AddWithValue("@UserID", UserID);
cmd.Parameters.AddWithValue("@Username", Username);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
return(dataTable);
}
}
}
}
