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