如何追踪每一笔记录的来龙去脉：一个完整的Audit Logging解决方案[下篇]
如何追踪每一笔记录的来龙去脉：一个完整的Audit Logging解决方案[下篇]
蒋金楠
发布于 2022-05-09 12:53:24
发布于 2022-05-09 12:53:24
文章被收录于专栏：大内老A
通过第一部分的介绍，我们对这个Audit Logging解决方案的原理有了较为全面的了解，接下来我们将通过一个简单的Sample来进一步介绍如何在一个具体的应用中如何实现这样一个Audit Logging。
我们沿用在Part I提出的Order management 场景，为了简单起见，我们通过一个Console application来模拟。在这个Application中，你将会看到如何以一种离线的模式操作数据库，如何Log这些操作。
我们首先来介绍Sample程序的结构（如下图）。整个Solution 一共包括两个Project，一个Console application，另一个是用以管理和定义Stored Procedure和Trigger的Database project。我们现在就来一步步实现这样一个Order management的简单的应用。Source Code从Artech.WCFService.zip下载。
一．定义一个轻型的Data access 的Helper class。
在Part I中我提到过这样一个Helper class，虽然这不是本篇所要介绍的重点，但是为了让读者能够较为全面地了解整个处理流程，在这里我对她作一个简单的介绍。
我在前一阵子，写过一篇叫做[原创]我的ORM: 开发自己的Data Access Application Block 的文章，在这片文章中我开发了一个自定义的DAB。我这个Data access helper便是采用里面提出的思想，实现了其中一小部分功能：Data retrieval， Update Dataset和Transaction。力求简洁，我剔出掉其中可配置的data mapping部分采用hard coding的方式实现Dataset和Stored procedure的Mapping。
通过这个Helper class，你可以调用UpdateData方法把对一个Table作的修改向数据库提交。这个方法的思路是这样的：我们对该Table的增加、修改和删除均定义了一个Stored procedure，我们假设Table name和Stored procedure name之间，Stored procedure的每个Parameter 的名称和Table中的Field name和DataRowVersion存在一个Mapping。比如T_ORDER对应的增加、修改和删除stored procedure分别为sp_order_i，sp_order_u和sp_order_d；stored procedure的参数@p_order_id对应的Source column为ORDER_ID, SourceVersion为DataRowVersion.Current，而参数@o_order_id对应的SourceVersion为DataRowVersion.Original。有了这样一个Mapping为前提，相信大家对Helper class的实现原理应该想象得到。当然要使我们的Stored procedure满足这样一个Mapping，靠我们手工的方式来定义每个stored procedure在一个真正的application是不现实的，一般地这些都是通过我们根据具体的Mapping关系开发的生成器生成的。像本Sample的所有stored procedure和trigger也都是通过Generator生成的。
下面是整个Helper class的实现，不算太复杂，有兴趣的话可以看看。否则敬请略过。
using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
namespace Artech.AuditLogging.ConsoleApp
{
public class DataAccessHelper:IDisposable
{
Private Fields#region Private Fields
private bool _isDisposed;
private string _connectionString;
private DbConnection _connection;
private DbTransaction _transaction;
private DbProviderFactory _dbProviderFactory;
private DbDataAdapter _dbDataAdapter;
#endregion
Public Properties#region Public Properties
public DbDataAdapter DbDataAdapter
{
get
{
if (this._dbDataAdapter == null)
{
this._dbDataAdapter = this.DbProviderFactory.CreateDataAdapter();
}
return this._dbDataAdapter;
}
}
public DataAccessHelper()
{
this._dbProviderFactory = DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings[0].ProviderName);
this._connectionString = ConfigurationManager.ConnectionStrings[0].ConnectionString;
}
//Data Access Provider Factory which is responsible for creating provider based ADO.NET conponent.
public DbProviderFactory DbProviderFactory
{
get
{
return this._dbProviderFactory;
}
}
public DbConnection Connection
{
get
{
if (this._connection == null)
{
this._connection = this.DbProviderFactory.CreateConnection();
this._connection.ConnectionString = this._connectionString;
}
if (this._connection.State != ConnectionState.Open)
{
this._connection.Open();
}
return this._connection;
}
}
#endregion
Transaction Operations#region Transaction Operations
public void BeginTransaction()
{
this._transaction = this.Connection.BeginTransaction();
}
public void Rollback()
{
if (this._transaction != null)
{
this._transaction.Rollback();
}
}
public void Commit()
{
if (this._transaction != null)
{
this._transaction.Commit();
}
}
#endregion
Data Mapping#region Data Mapping
private string GetSourceCoulmnName(string patameterName)
{
//@p_abc_def|o_abc_def=>ABC_DEF
return patameterName.Substring(3, patameterName.Length - 3).ToUpper();
}
public DataRowVersion GetSourceVersion(string parameterName)
{
//p_abc_def=>DataRowVersion.Current
//o_abc_default=>DataRowVersion.Original
if (parameterName.StartsWith("@o"))
{
return DataRowVersion.Original;
}
return DataRowVersion.Current;
}
private string GetSelectStoredProcedureName(string tableName)
{
//T_ABC_DEF=>sp_abc_def_s
return string.Format("sp_{0}_s", tableName.Substring(2, tableName.Length - 2).ToLower());
}
private string GetInsertStoredProcedureName(string tableName)
{
//T_ABC_DEF=>sp_abc_def_i
return string.Format("sp_{0}_i", tableName.Substring(2, tableName.Length - 2).ToLower());
}
private string GetModifyStoredProcedureName(string tableName)
{
//T_ABC_DEF=>sp_abc_def_u
return string.Format("sp_{0}_u", tableName.Substring(2, tableName.Length - 2).ToLower());
}
private string GetDeleteStoredProcedureName(string tableName)
{
//T_ABC_DEF=>sp_abc_def_d
return string.Format("sp_{0}_d", tableName.Substring(2, tableName.Length - 2).ToLower());
}
#endregion
Discovery Parameter#region Discovery Parameter
private void DiscoverParameters(DbCommand command)
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
SqlCommandBuilder.DeriveParameters(command as SqlCommand);
}
#endregion
Public Methods#region Public Methods
public void UpdateData(DataTable table)
{
//Create the three commands of the database data adapter.
DbCommand insertCommand = this.Connection.CreateCommand();
DbCommand updateCommand = this.Connection.CreateCommand();
DbCommand deleteCommand = this.Connection.CreateCommand();
//Specify the command type.
insertCommand.CommandType = CommandType.StoredProcedure;
updateCommand.CommandType = CommandType.StoredProcedure;
deleteCommand.CommandType = CommandType.StoredProcedure;
insertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
//Specify the command text.
insertCommand.CommandText = this.GetInsertStoredProcedureName(table.TableName);
updateCommand.CommandText = this.GetModifyStoredProcedureName(table.TableName);
deleteCommand.CommandText = this.GetDeleteStoredProcedureName(table.TableName);
//Set the parameters of the insert command.
if (table.GetChanges(DataRowState.Added) != null)
{
if (this._transaction != null)
{
insertCommand.Transaction = this._transaction;
}
this.DiscoverParameters(insertCommand);
//Specify the Source column and source version for insert command based paramemters.
foreach (DbParameter parameter in insertCommand.Parameters)
{
if (parameter.Direction == ParameterDirection.ReturnValue)
{
continue;
}
parameter.SourceColumn = this.GetSourceCoulmnName(parameter.ParameterName);
parameter.SourceVersion = this.GetSourceVersion(parameter.ParameterName);
}
}
//Set the parameters of the update command.
if (table.GetChanges(DataRowState.Modified) != null)
{
if (this._transaction != null)
{
updateCommand.Transaction = this._transaction;
}
this.DiscoverParameters(updateCommand);
//Specify the Source column and source version for update command based paramemters.
foreach (DbParameter parameter in updateCommand.Parameters)
{
if (parameter.Direction == ParameterDirection.ReturnValue)
{
continue;
}
parameter.SourceColumn = this.GetSourceCoulmnName(parameter.ParameterName);
parameter.SourceVersion = this.GetSourceVersion(parameter.ParameterName);
}
}
//Set the parameters of the delete command.
if (table.GetChanges(DataRowState.Deleted) != null)
{
if (this._transaction != null)
{
deleteCommand.Transaction = this._transaction;
}
this.DiscoverParameters(deleteCommand);
//Specify the Source column and source version for delete command based paramemters.
foreach (DbParameter parameter in deleteCommand.Parameters)
{
if (parameter.Direction == ParameterDirection.ReturnValue)
{
continue;
}
parameter.SourceColumn = this.GetSourceCoulmnName(parameter.ParameterName);
parameter.SourceVersion = this.GetSourceVersion(parameter.ParameterName);
}
}
//Evaluate the commands for the database adapter.
this.DbDataAdapter.InsertCommand = insertCommand;
this.DbDataAdapter.UpdateCommand = updateCommand;
this.DbDataAdapter.DeleteCommand = deleteCommand;
//Execute update.
this.DbDataAdapter.Update(table);
}
public void FillData(DataTable table, CommandType commandType, string commandText, IDictionary<string, object> parameters)
{
DbCommand command = this.Connection.CreateCommand();
command.CommandType = commandType;
command.CommandText = commandText;
foreach (string parameterName in parameters.Keys)
{
DbParameter parameter = this.DbProviderFactory.CreateParameter();
parameter.ParameterName = parameterName;
parameter.Value = parameters[parameterName];
}
this.DbDataAdapter.SelectCommand = command;
this.DbDataAdapter.Fill(table);
}
#endregion
IDisposable Members#region IDisposable Members
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
private void Dispose(bool disposing)
{
if (!this._isDisposed)
{
if (disposing)
{
if (this._connection != null)
{
if (this._connection.State == ConnectionState.Open)
{
this._connection.Close();
}
}
if (this._transaction != null)
{
this._transaction.Dispose();
}
}
}
this._isDisposed = true;
}
#endregion
}
}二．定义Dataset
我们根据数据库中Table的结构定义一个具有相同结构的strongly typed dataset：OrderDataSet.
为了保证数据的完整性，我们必须为两表的relation做出如下的设置：
同时我们为Log的数据定义下面的一个strongly typed dataset：AuditLoggingDataSet。该Dataset中只包含一个Table: T_AUDIT_LOG。我们之所以没有定义T_AUDIT_LOG_DETAIL是因为T_AUDIT_LOG_DETAIL中的数据是通过trigger添加的，我们同过程序只需要在主表中添加总体信息就可以了。
三、定义用于Audit log的helper class：AuditLoggingHelper
下面是所有AuditLoggingHelper所有的Code，很简单。我分别定义了一个public的property：AuditLoggingData。其类型为我们上面定义的strongly typed dataset：AuditLoggingDataSet。还定义了一个Public 的方法AuditLog向AuditLoggingData中添加Log信息，并返回一个Guid用以标识将要执行的transaction。我管这个Guid为Transaction no。
using System;
using System.Collections.Generic;
using System.Text;
namespace Artech.AuditLogging.ConsoleApp
{
public class AuditLoggingHelper
{
private AuditLoggingDataSet _auditLoggingData;
/**//// <summary>
/// A strongly typed dataset to used to store the general auditoing inforamtion.
/// </summary>
public AuditLoggingDataSet AuditLoggingData
{
get { return _auditLoggingData; }
set { _auditLoggingData = value; }
}
/**//// <summary>
/// Log the general auditoing information according with the current transaction.
/// </summary>
/// <returns>A guid which identifies uniquely a transaction</returns>
public Guid AuditLog()
{
if (this._auditLoggingData == null)
{
this._auditLoggingData = new AuditLoggingDataSet();
}
Guid transactionNo = Guid.NewGuid();
AuditLoggingDataSet.T_AUDIT_LOGRow auditRow= this._auditLoggingData.T_AUDIT_LOG.NewT_AUDIT_LOGRow();
auditRow.BeginEdit();
auditRow.TRANSACTION_NO = transactionNo.ToString();
//TODO: The user id is generally the account of the current login user.
auditRow.USER_ID = "testUser";
auditRow.OPERATION_DATE = DateTime.Now;
auditRow.EndEdit();
this._auditLoggingData.T_AUDIT_LOG.AddT_AUDIT_LOGRow(auditRow);
return transactionNo;
}
}
}四、定义stored procedure和trigger
为了较为真实地贴近我们现实的开发， 本Sample的所有Data access操作（除了data retrieval外）均采用stored procedure。通过所有需要进行Log的详细地信息都是通过Trigger来添加的。所有的stored procedure通过这里来查看，所有的trigger通过这里查看。
五：模拟Insert操作
我们先清空所有的Log数据，通过下面的方法添加一个新的Order。
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace Artech.AuditLogging.ConsoleApp
{
class Program
{
static string USER_ID = "testUser";
static void Main(string[] args)
{
AddOrderData();
Console.WriteLine("Operation completes!");
Console.Read();
}
static void UpdateCommonField(DataRow row)
{
row["LAST_UPDATED_BY"] = USER_ID;
row["LAST_UPDATED_ON"] = DateTime.Now;
if (row.RowState == DataRowState.Detached || row.RowState == DataRowState.Added)
{
row["CREATED_BY"] = USER_ID;
row["CREATED_ON"] = DateTime.Now;
}
}
static void AddOrderData()
{
AuditLoggingHelper auditLoggingHelper = new AuditLoggingHelper();
Guid transactionNo = auditLoggingHelper.AuditLog();
//Add an order item
OrderDataSet orderData = new OrderDataSet();
OrderDataSet.T_ORDERRow orderRow = orderData.T_ORDER.NewT_ORDERRow();
orderRow.BeginEdit();
orderRow.ORDER_DATE = DateTime.Today;
orderRow.SUPPLIER = "HP Corporation";
UpdateCommonField(orderRow);
orderRow.TRANSACTION_NO = transactionNo.ToString();
orderRow.EndEdit();
orderData.T_ORDER.AddT_ORDERRow(orderRow);
//Add two order detail items.
OrderDataSet.T_ORDER_DETAILRow orderDetailRow = orderData.T_ORDER_DETAIL.NewT_ORDER_DETAILRow();
orderDetailRow.BeginEdit();
orderDetailRow.ORDER_ID = orderRow.ORDER_ID;
orderDetailRow.PRODUCT_ID = 1;
orderDetailRow.PRODUCT_NAME = "HP Printer";
orderDetailRow.UNIT_PRICE = 3000;
orderDetailRow.QUANTITY = 2;
UpdateCommonField(orderDetailRow);
orderDetailRow.TRANSACTION_NO = transactionNo.ToString();
orderDetailRow.EndEdit();
orderData.T_ORDER_DETAIL.AddT_ORDER_DETAILRow(orderDetailRow);
orderDetailRow = orderData.T_ORDER_DETAIL.NewT_ORDER_DETAILRow();
orderDetailRow.BeginEdit();
orderDetailRow.ORDER_ID = orderRow.ORDER_ID;
orderDetailRow.PRODUCT_ID = 2;
orderDetailRow.PRODUCT_NAME = "HP PC";
orderDetailRow.UNIT_PRICE = 3400;
orderDetailRow.QUANTITY = 22;
UpdateCommonField(orderDetailRow);
orderDetailRow.TRANSACTION_NO = transactionNo.ToString();
orderDetailRow.EndEdit();
orderData.T_ORDER_DETAIL.AddT_ORDER_DETAILRow(orderDetailRow);
using (DataAccessHelper dataAccessHelper = new DataAccessHelper())
{
dataAccessHelper.BeginTransaction();
try
{
dataAccessHelper.UpdateData(auditLoggingHelper.AuditLoggingData.T_AUDIT_LOG);
dataAccessHelper.UpdateData(orderData.T_ORDER);
dataAccessHelper.UpdateData(orderData.T_ORDER_DETAIL);
dataAccessHelper.Commit();
}
catch(Exception ex)
{
dataAccessHelper.Rollback();
Console.WriteLine(ex.Message);
}
}
}
}
}我们来看看T_AUDIT_LOG的记录：
T_AUDIT_LOG_DETAIL的记录
DATA_CHANGE的data分别为下面3段XML。
<dataChange>
<after order_id="33" order_date="Apr 23 2007 12:00AM" supplier="HP Corporation" />
</dataChange><dataChange>
<after order_id="33" product_id="1" product_name="HP Printer" unit_price="3000.00" quantity="2" />
</dataChange><dataChange>
<after order_id="33" product_id="2" product_name="HP PC" unit_price="3400.00" quantity="22" />
</dataChange>六、模拟Update操作
定义新的方法UpdateOrderData修改我们刚刚添加的Order记录：
static void UpdateOrderData()
{
OrderDataSet orderData = GetAllOrderData();
AuditLoggingHelper auditLoggingHelper = new AuditLoggingHelper();
Guid transactionNo = auditLoggingHelper.AuditLog();
OrderDataSet.T_ORDERRow orderRow = orderData.T_ORDER[0];
orderRow.ORDER_DATE = new DateTime(2005, 1, 1);
orderRow.SUPPLIER = "Dell Corporation";
orderRow.TRANSACTION_NO = transactionNo.ToString();
UpdateCommonField(orderRow);
OrderDataSet.T_ORDER_DETAILRow orderDetailRow = orderData.T_ORDER_DETAIL[0];
orderDetailRow.PRODUCT_ID = 3;
orderDetailRow.PRODUCT_NAME = "Workstation";
orderDetailRow.UNIT_PRICE = 10000;
orderDetailRow.QUANTITY = 1;
orderDetailRow.TRANSACTION_NO = transactionNo.ToString();
UpdateCommonField(orderDetailRow);
using (DataAccessHelper dataAccessHelper = new DataAccessHelper())
{
dataAccessHelper.BeginTransaction();
try
{
dataAccessHelper.UpdateData(auditLoggingHelper.AuditLoggingData.T_AUDIT_LOG);
dataAccessHelper.UpdateData(orderData.T_ORDER);
dataAccessHelper.UpdateData(orderData.T_ORDER_DETAIL);
dataAccessHelper.Commit();
}
catch (Exception ex)
{
dataAccessHelper.Rollback();
Console.WriteLine(ex.Message);
}
}
}执行上面的方法，然后查看我们Log表的记录。
两条Update记录对应的DATA_CHANGE字段的XML分别为：
<dataChange>
<before order_id="33" order_date="Apr 23 2007 12:00AM" supplier="HP Corporation" />
<after order_id="33" order_date="Jan 1 2005 12:00AM" supplier="Dell Corporation" />
</dataChange><dataChange>
<before order_id="33" product_id="1" product_name="HP Printer" unit_price="3000.00" quantity="2" />
<after order_id="33" product_id="1" product_name="Workstation" unit_price="10000.00" quantity="1" />
</dataChange>七、模拟Delete操作
定于DeleteOrderData方法delete掉我们添加的Order记录：
static void DeleteOrderData()
{
OrderDataSet orderData = GetAllOrderData();
AuditLoggingHelper auditLoggingHelper = new AuditLoggingHelper();
Guid transactionNo = auditLoggingHelper.AuditLog();
//Delete two order detail items.
OrderDataSet.T_ORDER_DETAILRow orderDetailRow = orderData.T_ORDER_DETAIL[0];
orderDetailRow.TRANSACTION_NO = transactionNo.ToString();
orderDetailRow.AcceptChanges();
orderDetailRow.Delete();
orderDetailRow = orderData.T_ORDER_DETAIL[1];
orderDetailRow.TRANSACTION_NO = transactionNo.ToString();
orderDetailRow.AcceptChanges();
orderDetailRow.Delete();
//Delete the order item.
OrderDataSet.T_ORDERRow orderRow = orderData.T_ORDER[0];
orderRow.TRANSACTION_NO = transactionNo.ToString();
orderRow.AcceptChanges();
orderRow.Delete();
using (DataAccessHelper dataAccessHelper = new DataAccessHelper())
{
dataAccessHelper.BeginTransaction();
try
{
dataAccessHelper.UpdateData(auditLoggingHelper.AuditLoggingData.T_AUDIT_LOG);
dataAccessHelper.UpdateData(orderData.T_ORDER_DETAIL);
dataAccessHelper.UpdateData(orderData.T_ORDER);
dataAccessHelper.Commit();
}
catch (Exception ex)
{
dataAccessHelper.Rollback();
Console.WriteLine(ex.Message);
}
}
}执行上面的方法，然后查看我们Log表的记录。
三条Delete记录对应的DATA_CHANGE字段的XML分别为：
<dataChange>
<before order_id="33" product_id="1" product_name="Workstation" unit_price="10000.00" quantity="1" />
</dataChange><dataChange>
<before order_id="33" product_id="2" product_name="HP PC" unit_price="3400.00" quantity="22" />
</dataChange><dataChange>
<before order_id="33" order_date="Jan 1 2005 12:00AM" supplier="Dell Corporation" />
</dataChange>本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2007-04-23，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
日志服务
日志服务（Cloud Log Service，CLS）是腾讯云提供的一站式日志服务平台，提供了从日志采集、日志存储到日志检索，图表分析、监控告警、日志投递等多项服务，协助用户通过日志来解决业务运维、服务监控、日志审计等场景问题。
腾讯云开发者
