如何追踪每一笔记录的来龙去脉：一个完整的Audit Logging解决方

发布时间：2020-12-16 09:06:36 所属栏目：asp.Net 来源：网络整理

导读：通过第一部分的介绍，我们对这个Audit Logging解决方案的原理有了较为全面的了解，接下来我们将通过一个简单的Sample来进一步介绍如何在一个具体的应用中如何实现这样一个Audit Logging。我们沿用在Part I提出的Order management 场景，为了简单起见，我们

通过第一部分的介绍，我们对这个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;

?System.Collections.Generic;

?System.Text;

?System.Configuration;

?System.Data;

?System.Data.Common;

?System.Data.SqlClient;

namespace?Artech.AuditLogging.ConsoleApp

{

????public?class?DataAccessHelper:IDisposable

????

{

????????Private?Fields

????????Public?Properties?Public?Properties

?????????DbDataAdapter?DbDataAdapter

????????

{

????????????get

????????????

{

????????????????if?(this._dbDataAdapter?==null)

????????????????

{

????????????????????=.DbProviderFactory.CreateDataAdapter();

????????????????}

????????????????return._dbDataAdapter;

????????????}

????????}?DataAccessHelper()

????????

._dbProviderFactory??DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings[0].ProviderName);

????????????._connectionString??ConfigurationManager.ConnectionStrings[].ConnectionString;

????????}//Data?Access?Provider?Factory?which?is?responsible?for?creating?provider?based?ADO.NET?conponent.

?????????DbProviderFactory?DbProviderFactory

????????

????????????

._dbProviderFactory;

????????????}??

????????}?DbConnection?Connection

????????

????????????

._connection?)

????????????????

.DbProviderFactory.CreateConnection();

????????????????????._connection.ConnectionString?._connectionString;

????????????????}._connection.State?!=?ConnectionState.Open)

????????????????

._connection.Open();

????????????????}._connection;

????????????}

????????

????????Transaction?Operations?Transaction?Operationsvoid?BeginTransaction()

????????

._transaction?.Connection.BeginTransaction();

????????}?Rollback()

????????

)

????????????

._transaction.Rollback();

????????????}?Commit()

????????

)

????????????

????????????????._transaction.Commit();

????????????}

????????Data?Mapping?Data?Mapping?GetSourceCoulmnName(?patameterName)

????????

@p_abc_def|o_abc_def=>ABC_DEF?????????????patameterName.Substring(3,?patameterName.Length?-).ToUpper();

????????}??DataRowVersion?GetSourceVersion(?parameterName)

????????

p_abc_def=>DataRowVersion.Current

????????????o_abc_default=>DataRowVersion.Original?(parameterName.StartsWith("@o))

????????????

?DataRowVersion.Original;

????????????}

?????????????DataRowVersion.Current;

????????}?GetSelectStoredProcedureName(?tableName)

????????

T_ABC_DEF=>sp_abc_def_s.Format(sp_{0}_s2).ToLower());

????????}?GetInsertStoredProcedureName(?tableName)

????????

T_ABC_DEF=>sp_abc_def_isp_{0}_i?GetModifyStoredProcedureName(?tableName)

????????

T_ABC_DEF=>sp_abc_def_usp_{0}_u?GetDeleteStoredProcedureName(?tableName)

????????

T_ABC_DEF=>sp_abc_def_dsp_{0}_d

????????Discovery?Parameter?Discovery?Parameter?DiscoverParameters(DbCommand?command)

????????

?(command.Connection.State??ConnectionState.Open)

????????????

{

????????????????command.Connection.Open();

????????????}

????????????SqlCommandBuilder.DeriveParameters(command?as?SqlCommand);

????????}

????????

????????Public?Methods?Public?Methods?UpdateData(DataTable?table)

????????

{

????????????

????????????Create?the?three?commands?of?the?database?data?adapter.????????????DbCommand?insertCommand?.Connection.CreateCommand();

????????????DbCommand?updateCommand?.Connection.CreateCommand();

????????????DbCommand?deleteCommand?.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?.GetInsertStoredProcedureName(table.TableName);

????????????updateCommand.CommandText?.GetModifyStoredProcedureName(table.TableName);

????????????deleteCommand.CommandText?.GetDeleteStoredProcedureName(table.TableName);

???????????

????????????Set?the?parameters?of?the?insert?command.?(table.GetChanges(DataRowState.Added)?)

????????????

)

????????????????

{

????????????????????insertCommand.Transaction?._transaction;

????????????????}

??????????????.DiscoverParameters(insertCommand);

??????????????Specify?the?Source?column?and?source?version?for?insert?command?based?paramemters.??????????????foreach?(DbParameter?parameter?in?insertCommand.Parameters)

??????????????

{

???????????????????(parameter.Direction??ParameterDirection.ReturnValue)

??????????????????

{

??????????????????????continue;

??????????????????}

??????????????????parameter.SourceColumn?.GetSourceCoulmnName(parameter.ParameterName);

??????????????????parameter.SourceVersion?.GetSourceVersion(parameter.ParameterName);

??????????????}

????????????}

????????????Set?the?parameters?of?the?update?command.?(table.GetChanges(DataRowState.Modified)?)

????????????

)

????????????????

{

????????????????????updateCommand.Transaction?.DiscoverParameters(updateCommand);

????????????????Specify?the?Source?column?and?source?version?for?update?command?based?paramemters.?????????????????updateCommand.Parameters)

????????????????

?ParameterDirection.ReturnValue)

????????????????????

{

????????????????????????;

????????????????????}

????????????????????parameter.SourceColumn?.GetSourceCoulmnName(parameter.ParameterName);

????????????????????parameter.SourceVersion?.GetSourceVersion(parameter.ParameterName);

????????????????}??????????????

????????????}Set?the?parameters?of?the?delete?command.?(table.GetChanges(DataRowState.Deleted)?)

????????????

)

????????????????

{

????????????????????deleteCommand.Transaction?

????????????????.DiscoverParameters(deleteCommand);

????????????????Specify?the?Source?column?and?source?version?for?delete?command?based?paramemters.?deleteCommand.Parameters)

????????????????

?ParameterDirection.ReturnValue)

????????????????????

????

????????????}??????????????

????????????Evaluate?the?commands?for?the?database?adapter..DbDataAdapter.InsertCommand??insertCommand;

????????????.DbDataAdapter.UpdateCommand??updateCommand;

????????????.DbDataAdapter.DeleteCommand??deleteCommand;

????????????Execute?update..DbDataAdapter.Update(table);????????????

????????}?FillData(DataTable?table,?CommandType?commandType,??commandText,?IDictionary<object>?parameters)

????????

{

????????????DbCommand?command?.Connection.CreateCommand();

????????????command.CommandType??commandType;

????????????command.CommandText??commandText;

?????????????parameterName??parameters.Keys)

????????????

{

????????????????DbParameter?parameter?.DbProviderFactory.CreateParameter();

????????????????parameter.ParameterName??parameterName;

????????????????parameter.Value??parameters[parameterName];

????????????}.DbDataAdapter.SelectCommand??command;

????????????.DbDataAdapter.Fill(table);

????????}

????????IDisposable?Members

二．定义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。

?System.Text;

?Artech.AuditLogging.ConsoleApp

?AuditLoggingHelper

????

{

?????????AuditLoggingDataSet?_auditLoggingData;

????????/**////?<summary>

?????????A?strongly?typed?dataset?to?used?to?store?the?general?auditoing?inforamtion.?

????????</summary>

?AuditLoggingDataSet?AuditLoggingData

????????

{

?????????????

{??_auditLoggingData;?}

????????????set?

{?_auditLoggingData??value;?}

????????/**/?Log?the?general?auditoing?information?according?with?the?current?transaction.

????????</summary>

????????<returns>A?guid?which?identifies?uniquely?a?transaction</returns>

?Guid?AuditLog()

????????

._auditLoggingData?)

????????????

new?AuditLoggingDataSet();

????????????}

????????????Guid?transactionNo??Guid.NewGuid();

????????????AuditLoggingDataSet.T_AUDIT_LOGRow?auditRow._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();

????????????._auditLoggingData.T_AUDIT_LOG.AddT_AUDIT_LOGRow(auditRow);

?????????????transactionNo;

????????}

四、定义stored procedure和trigger

为了较为真实地贴近我们现实的开发，本Sample的所有Data access操作（除了data retrieval外）均采用stored procedure。通过所有需要进行Log的详细地信息都是通过Trigger来添加的。所有的stored procedure通过这里来查看，所有的trigger通过这里查看。

五：模拟Insert操作

我们先清空所有的Log数据，通过下面的方法添加一个新的Order。

?System.Data;

?Artech.AuditLogging.ConsoleApp

?Program

????

static?USER_ID?;

?????????Main([]?args)

????????

{

????????????AddOrderData();

????????????Console.WriteLine(Operation?completes!);

????????????Console.Read();

????????}?UpdateCommonField(DataRow?row)

????????

{

????????????row[LAST_UPDATED_BY]??USER_ID;

????????????row[LAST_UPDATED_ON?DateTime.Now;

?????????????(row.RowState??DataRowState.Detached?||?row.RowState??DataRowState.Added)

????????????

{

????????????????row[CREATED_BY?USER_ID;

????????????????row[CREATED_ON?DateTime.Now;

????????????}??

????????

?????????AddOrderData()

????????

{

????????????AuditLoggingHelper?auditLoggingHelper??AuditLoggingHelper();

????????????Guid?transactionNo??auditLoggingHelper.AuditLog();

????????????Add?an?order?item????????????OrderDataSet?orderData??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?;

????????????UpdateCommonField(orderDetailRow);

????????????orderDetailRow.TRANSACTION_NO??transactionNo.ToString();

????????????orderDetailRow.EndEdit();

????????????orderData.T_ORDER_DETAIL.AddT_ORDER_DETAILRow(orderDetailRow);

????????????orderDetailRow?HP?PC340022?transactionNo.ToString();

????????????orderDetailRow.EndEdit();

????????????orderData.T_ORDER_DETAIL.AddT_ORDER_DETAILRow(orderDetailRow);

?????????????(DataAccessHelper?dataAccessHelper??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"/>

?product_id="1"?product_name="HP?Printer"?unit_price="3000.00"?quantity="2"="HP?PC"="3400.00"="22"

六、模拟Update操作

定义新的方法UpdateOrderData修改我们刚刚添加的Order记录：

?????UpdateOrderData()

????????

{

????????????OrderDataSet?orderData??GetAllOrderData();

????????????AuditLoggingHelper?auditLoggingHelper??auditLoggingHelper.AuditLog();

????????????OrderDataSet.T_ORDERRow?orderRow??orderData.T_ORDER[];

????????????orderRow.ORDER_DATE??DateTime(2005);

????????????orderRow.SUPPLIER?Dell?Corporation;

????????????orderRow.TRANSACTION_NO???transactionNo.ToString();

????????????UpdateCommonField(orderRow);

????????????OrderDataSet.T_ORDER_DETAILRow?orderDetailRow??orderData.T_ORDER_DETAIL[];

????????????orderDetailRow.PRODUCT_ID?Workstation10000;

????????????orderDetailRow.TRANSACTION_NO???transactionNo.ToString();

????????????UpdateCommonField(orderDetailRow);

?????????????DataAccessHelper())

????????????

{????????????????

????????????????dataAccessHelper.BeginTransaction();

????????????????

?(Exception?ex)

????????????????

????????????

????????}

执行上面的方法，然后查看我们Log表的记录。

两条Update记录对应的DATA_CHANGE字段的XML分别为：

before?="Jan??1?2005?12:00AM"="Dell?Corporation"="Workstation"="10000.00"

七、模拟Delete操作

定于DeleteOrderData方法delete掉我们添加的Order记录：

?DeleteOrderData()

????????

Delete?two?order?detail?items.];

????????????orderDetailRow.TRANSACTION_NO??transactionNo.ToString();

????????????orderDetailRow.AcceptChanges();

????????????orderDetailRow.Delete();

????????????orderDetailRow??transactionNo.ToString();

????????????orderDetailRow.AcceptChanges();

????????????orderDetailRow.Delete();

????????????Delete?the?order?item.????????????OrderDataSet.T_ORDERRow?orderRow?];

????????????orderRow.TRANSACTION_NO??transactionNo.ToString();

????????????orderRow.AcceptChanges();

????????????orderRow.Delete();

?????????????DataAccessHelper())

????????????

????????????????

{

????????????????????dataAccessHelper.UpdateData(auditLoggingHelper.AuditLoggingData.T_AUDIT_LOG);

????????????????????dataAccessHelper.UpdateData(orderData.T_ORDER_DETAIL);

????????????????????dataAccessHelper.UpdateData(orderData.T_ORDER);

????????????????????dataAccessHelper.Commit();

????????????????}?(Exception?ex)

????????????????

????????}

执行上面的方法，然后查看我们Log表的记录。

三条Delete记录对应的DATA_CHANGE字段的XML分别为：

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!