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