老项目使用传统简单三层,分为数据访问层(Accessor)+ DBHelper 类,业务层(Business),以及界面项目。随着项目规模的扩大,功能越来越多,随便一个功能就有可能操作好几张表,按以前套路,多表操作的事务管理是在数据访问层里面的,在Accessor这个类库里面使用数据库事务来对多表的写入,更新进行统一事务管理,而且对事务的管理还是处于原始的刀耕火种状态,如下示例代码所示:
/// <summary>
/// 新增发票记录
/// </summary>
/// <param name="model">Invoice实体</param>
/// <param name="log">操作日志对象</param>
/// <returns>返回受影响的行数</returns>
public int Add(Invoice model, Log log)
{
var cmd = db.GetStoredProcCommond(strAdd);
DbTrans t = new Trans(cmd.Connection);
try
{
using (t)
{
int id = Add(model, log, t);
model.InvoiceID = id;
detailsDal.Add(model.List, log, t);
UpdateStatus(model.List, t);
AddSettlement(model, log, t);
t.Commit();
return id;
}
}
catch (SqlException ex)
{
t.RollBack();
return 0;
throw ex;
}
}
上面代码中是对 3 张表进行操作,其中包含了插入操作,更新操作;所以使用了DbTrans对象是对数据库事务的简单封装,从上面代码中可以看出,项目规模越大,老项目写起来越费力费时,全手工打造事务的代码,以及异常处理等等。
聪明的你,或许已经想到了该怎样减少这种重复代码的抒写,那我们就用
(AOP) 面向切面的编程技术来重构上面的代码;在 .net 项目里面,我们可以选择Postsharp、MrAdvice中的任意一个组件来重构,postsharp是需要收费的,并且你要做的工作是将其下载下来进行安装才行,而MrAdvice是开源的aop组件,可以拿来即用,今天我们就使用MrAdvice进行重构。
首先需要告诉你的是,MrAdvice的项目地址是:https://github.com/ArxOne/MrAdvice,可以从这儿下载源码进行查看,你也可以通过NuGet直接在VS 2017里面获得其引用,如下图所示:

添加了引用后,我们编写一个类:TransactionAttribute,这个类你可以放在业务层,因为重构后我们将在业务层进行事务管理,代码如下:
/// <summary>
/// 用于统一在业务层管理数据库事务
/// </summary>
public class TransactionAttribute : Attribute, IMethodAdvice
{
public void Advise(MethodAdviceContext context)
{
TransactionScope TransactionScope= new TransactionScope(TransactionScopeOption.RequiresNew);
try
{
context.Proceed();
TransactionScope.Complete();
TransactionScope.Dispose();
}
catch (Exception ex)
{
Transaction.Current.Rollback();
//在此处你可以记录错误日志
}
}
}
完成上面代码后,我们修改业务层的代码,在多表操作的方法上面打上这个特性[Transaction],如下代码所示:
[Transaction]
public void 多表写入2()
{
var i = dal1.Add("第2班,出错写入的。");
if (i == true)
{
Console.WriteLine("业务发生错误,看事务回滚了没有!");
throw new Exception("出错了。。。。");
}
dal2.Add("张三2", "0002");
}
到这一步,你啥都不用管了,是不是经过重构,代码变得优雅了呢?
如果您还不明白,我在重构过程中,编写了一些测试代码,您可以直接复制到测试工程里面跑一下即知道其作用了,完整代码如下:
–创建数据库语句如下:
CREATE TABLE [dbo].[AClass](
[Class] nvarchar NOT NULL,
[Id] [int] IDENTITY(1,1) NOT NULL
GO
CREATE TABLE [dbo].[AStudent](
[Name12] nvarchar NOT NULL,
[No] nvarchar NOT NULL,
[Id] [int] IDENTITY(1,1) NOT NULL
GO
C#程序代码如下:
public class DbHelper
{
#region 字段
/// <summary>
/// 数据库提供者
/// </summary>
private static string dbProviderName = "System.Data.SqlClient";
/// <summary>
/// 连接字符串
/// </summary>
private static string connectionString = "Data Source=.;Initial Catalog=DEBUG;Integrated Security=True;Max Pool Size=512; Min Pool Size=20;timeout=5000";
/// <summary>
/// 数据库连接
/// </summary>
private DbConnection connection;
#endregion
#region 构造函数
/// <summary>
/// 无参构造函数
/// </summary>
public DbHelper()
{
connection = this.CreateConnection();
}
#endregion
/// <summary>
/// 构造函数
/// </summary>
/// <param name="conString">连接字符串</param>
public DbHelper(string conString)
{
connectionString = conString;
}
/// <summary>
/// 创建数据库连接
/// </summary>
/// <returns>数据库连接</returns>
private DbConnection CreateConnection()
{
DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
DbConnection dbconn = dbfactory.CreateConnection();
dbconn.ConnectionString = connectionString;
return dbconn;
}
/// <summary>
/// 创建数据库命令
/// </summary>
/// <param name="storedProcedure">存储过程名</param>
/// <returns>数据库命令</returns>
public DbCommand GetStoredProcCommond(string storedProcedure)
{
DbCommand dbCommand = connection.CreateCommand();
dbCommand.CommandText = storedProcedure;
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandTimeout = 3600;
if (dbCommand.Connection.State == ConnectionState.Closed)
{
dbCommand.Connection.Open();
}
return dbCommand;
}
/// <summary>
/// 创建数据库命令
/// </summary>
/// <param name="sqlQuery">SQL语句</param>
/// <returns>数据库命令</returns>
public DbCommand GetSqlStringCommond(string sqlQuery)
{
DbCommand dbCommand = connection.CreateCommand();
dbCommand.CommandText = sqlQuery;
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandTimeout = 3600;
if (dbCommand.Connection.State == ConnectionState.Closed)
{
dbCommand.Connection.Open();
}
return dbCommand;
}
/// <summary>
/// 添加输出参数数
/// </summary>
/// <param name="cmd">数据库命令</param>
/// <param name="parameterName">参数名(一般指输出参数名)</param>
/// <param name="dbType">数据类型</param>
/// <param name="size">数据大小</param>
public void AddOutParameter(DbCommand cmd, string parameterName, DbType dbType, int size)
{
DbParameter dbParameter = cmd.CreateParameter();
dbParameter.DbType = dbType;
dbParameter.ParameterName = parameterName;
dbParameter.Size = size;
dbParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(dbParameter);
}
/// <summary>
/// 添加数据库命令参数(存储过程或SQL语句的参数)
/// </summary>
/// <param name="cmd">数据库命令</param>
/// <param name="parameterName">参数名</param>
/// <param name="dbType">数据类型</param>
/// <param name="value">数据值</param>
public void AddInParameter(DbCommand cmd, string parameterName, DbType dbType, object value)
{
DbParameter dbParameter = cmd.CreateParameter();
dbParameter.DbType = dbType;
dbParameter.ParameterName = parameterName;
dbParameter.Value = value;
dbParameter.Direction = ParameterDirection.Input;
cmd.Parameters.Add(dbParameter);
}
/// <summary>
/// 添加返回参数(指执行了数据操作后返回的参数)
/// </summary>
/// <param name="cmd">数据库命令</param>
/// <param name="parameterName">参数名</param>
/// <param name="dbType">数据类型</param>
public void AddReturnParameter(DbCommand cmd, string parameterName, DbType dbType)
{
DbParameter dbParameter = cmd.CreateParameter();
dbParameter.DbType = dbType;
dbParameter.ParameterName = parameterName;
dbParameter.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(dbParameter);
}
/// <summary>
/// 执行数据库命令
/// </summary>
/// <param name="cmd">数据库命令</param>
/// <returns>返回受影响的行数</returns>
public int ExecuteNonQuery(DbCommand cmd)
{
try
{
if (cmd.Connection.State == ConnectionState.Closed)
{
cmd.Connection.Open();
}
int ret = cmd.ExecuteNonQuery();
cmd.Connection.Close();
return ret;
}
catch (SqlException ex)
{
cmd.Connection.Close();
throw ex;
}
}
}
public class StudentDao
{
public bool Add(string name, string no)
{
string sql = string.Format("insert into AStudent (Name12,No) values(@name,@no)");
var db = new DbHelper();
var cmd = db.GetSqlStringCommond(sql);
db.AddInParameter(cmd, "@name", DbType.String, name);
db.AddInParameter(cmd, "@no", DbType.String, no);
return db.ExecuteNonQuery(cmd) > 0;
}
}
public class ClassDao
{
public bool Add(string name)
{
string sql = string.Format("insert into AClass(Class) values(@name)");
var db = new DbHelper();
var cmd = db.GetSqlStringCommond(sql);
db.AddInParameter(cmd, "@name", DbType.String, name);
return db.ExecuteNonQuery(cmd) > 0;
}
}
public class 测试业务层
{
private ClassDao dal1;
private StudentDao dal2;
public 测试业务层()
{
dal1 = new ClassDao();
dal2 = new StudentDao();
}
[Transaction]
public void 多表写入1()
{
dal1.Add("第1班");
dal2.Add("张三1", "0001");
Console.WriteLine("成功写入了两张表的数据!");
}
[Transaction]
public void 多表写入2()
{
var i = dal1.Add("第2班,出错写入的。");
if (i == true)
{
Console.WriteLine("业务发生错误,看事务回滚了没有!");
throw new Exception("出错了。。。。");
}
dal2.Add("张三2", "0002");
}
}
class Program
{
static void Main(string[] args)
{
测试业务层 bll = new 测试业务层();
bll.多表写入1();
bll.多表写入2();
Console.ReadLine();
}
}
/// <summary>
/// 用于统一在业务层管理数据库事务
/// </summary>
public class TransactionAttribute : Attribute, IMethodAdvice
{
public void Advise(MethodAdviceContext context)
{
TransactionScope TransactionScope= new TransactionScope(TransactionScopeOption.RequiresNew);
try
{
context.Proceed();
TransactionScope.Complete();
TransactionScope.Dispose();
}
catch (Exception ex)
{
Transaction.Current.Rollback();
//在此处你可以记录错误日志
}
}
}
User Review
( votes)原创文章,作者:江小编,如若转载,请注明出处:https://jhrs.com/2019/26520.html
扫码关注【江湖人士】公众号,您会获得关于国外被动收入的最新资讯
WA付费会员QQ群:387027533,加这个群需要回答您的WA会员名,待核实后予以通过
普通QQ交流群:178758794,可分享交流建站的各类经验和知识