事务处理在三层架构中的应用

      在三层架构中，事务应该放在那管理，如果放到调用DAL的业务层操作类中，DAL类中操作的Connection通过参数形式传入，那么怎样才能避免在BLL层创建数据库操作类（DBHelper）对象？

      如果在BLL创建DBHelper对象，然后将connection作为参数传回DAL类中，connection的取得和关闭均在BLL。这样BLL与DBHelper的耦合度将加强，没有起到分层的作用。

      最好的办法就是将事务处理独立出来，该类的功能主要是创建数据库连接，开启事务处理，关闭连接。代码如下：

Imports System.Data.Common Public Class TranUtil Private Sub New() End Sub '开启事务 Public Shared Function BeginTransaction() As IDbTransaction Dim conn As DbConnection = DButil.CreateConnection If conn.State = ConnectionState.Closed Then conn.Open() End If Dim tran As IDbTransaction = conn.BeginTransaction() Return tran End Function '关闭连接 Public Shared Sub Close(ByVal tran As IDbTransaction) If tran IsNot Nothing AndAlso tran.Connection.State = ConnectionState.Open Then tran.Connection.Close() tran = Nothing End If End Sub End Class

数据库操作类代码如下：

Imports System.Data.Common Imports System.Configuration Imports System.Reflection.MethodBase Imports MySql.Data.MySqlClient Imports log4net Public Class DButil Private Shared log As ILog = LogManager.GetLogger(GetCurrentMethod().DeclaringType.ToString) Private Sub New() End Sub ' 创建数据库链接 Public Shared Function CreateConnection() As DbConnection log.Info("创建数据库连接") Try '读取app.config中的连接字符串 Dim sConn As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString '创建数据库连接 Dim dbConn As MySqlConnection = New MySqlConnection(sConn) Return dbConn Catch ex As Exception log.Error(ex) Throw ex End Try End Function '取得DbCommand Public Shared Function GetSqlCommand(ByVal connection As DbConnection, ByVal sqlQuery As String) As DbCommand log.Info("sqlQuery：" & sqlQuery) Try Dim dbCommand As MySqlCommand = connection.CreateCommand dbCommand.CommandText = sqlQuery dbCommand.CommandType = CommandType.Text Return dbCommand Catch ex As Exception log.Error(ex) Throw ex End Try End Function ' 给DbCommand对象的参数赋值 Public Overloads Shared Sub AddParameter(ByVal cmd As DbCommand, ByVal parameterName As String, ByVal value As Object) log.Info("parameterName=" & parameterName & "，value=" & value) Dim dbParameter As DbParameter = cmd.CreateParameter() dbParameter.ParameterName = parameterName dbParameter.Value = value dbParameter.Direction = ParameterDirection.Input cmd.Parameters.Add(dbParameter) End Sub ' 查询，传入DbCommand对象与Table名字，返回datatable Public Overloads Shared Function GetDataTable(ByVal cmd As DbCommand, ByVal name As String) As DataTable Try Dim DbDataAdapter As MySqlDataAdapter = New MySqlDataAdapter(cmd.CommandText, cmd.Connection) DbDataAdapter.SelectCommand = cmd Dim dataTable As DataTable = New DataTable() dataTable.TableName = name DbDataAdapter.Fill(dataTable) log.Info(name & " row count：" & dataTable.Rows.Count) Return dataTable Catch ex As Exception log.Error(ex) Throw ex End Try End Function ''新增，删除，修改 Public Shared Function ExecuteNonQuery(ByVal cmd As DbCommand) As Integer Try If cmd.Connection.State = ConnectionState.Closed Then cmd.Connection.Open() End If Dim i As Object = cmd.ExecuteNonQuery log.Info("ExecuteNonQuery Result：" & i) Return i Catch ex As Exception log.Error(ex) Throw ex Finally cmd.Connection.Close() End Try End Function ' 返回单个值,如：单个字段的值或者Count(*)的值 Public Shared Function ExecuteScalar(ByVal cmd As DbCommand) As Object Try If cmd.Connection.State = ConnectionState.Closed Then cmd.Connection.Open() End If Dim obj As Object = cmd.ExecuteScalar log.Info("ExecuteScalar Result：" & obj) Return obj Catch ex As Exception log.Error(ex) Throw ex Finally cmd.Connection.Close() End Try End Function #Region "事务处理" ' 返回单个值,如：单个字段的值或者Count(*)的值，具有事务处理功能 Public Shared Function ExecuteTranScalar(ByVal conn As DbConnection, ByVal cmd As DbCommand) As Object Try If conn.State = ConnectionState.Closed Then conn.Open() End If Dim obj As Object = cmd.ExecuteScalar log.Info("ExecuteScalar Result：" & obj) Return obj Catch ex As Exception log.Error(ex) Throw ex End Try End Function ' 新增，删除，修改，具有事务处理功能 Public Shared Function ExecuteTranNonQuery(ByVal conn As DbConnection, ByVal cmd As DbCommand) As Integer Try If conn.State = ConnectionState.Closed Then conn.Open() End If Dim i As Object = cmd.ExecuteNonQuery log.Info("ExecuteNonQuery Result：" & i) Return i Catch ex As Exception log.Error(ex) Throw ex End Try End Function #End Region End Class

      在DBUtil中，所有具有事务处理的方法都不需要关闭连接，数据库的连接在其开启的地方进行关闭。

      在DAL层中，接收来自BLL层的connection，然后调用DBUtil中的方法进行数据的操作。代码如下：

主档 Public Function InsertOutStockMain(ByVal tran As IDbTransaction, ByVal arl() As String) As Integer Dim sbSql As New StringBuilder sbSql.Append(" insert into outstockmain(stock_no,stock_date,stock_person,buyer_id)") sbSql.Append(" values(?stock_no,?stock_date,?stock_person,?buyer_id)") Try Dim myCmd As MySqlCommand = DButil.GetSqlCommand(tran.Connection, sbSql.ToString) DButil.AddParameter(myCmd, "?stock_no", arl(0)) DButil.AddParameter(myCmd, "?stock_date", arl(1)) DButil.AddParameter(myCmd, "?stock_person", arl(2)) DButil.AddParameter(myCmd, "?buyer_id", arl(3)) Dim iResult As Integer = DButil.ExecuteTranNonQuery(tran.Connection, myCmd) Return iResult Catch ex As Exception Throw ex End Try End Function '明细档 Private Function InsertOutStockDtl(ByVal tran As IDbTransaction, ByVal dr As DataRow) As Integer Dim sbSql As New StringBuilder sbSql.Append(" insert into outstock(seq_no,stock_no,quatity,in_price,out_price)") sbSql.Append(" values(?seq_no,?stock_no,?quatity,?in_price,?out_price)") Dim iResult As Integer = 0 Try Dim myCmd As MySqlCommand = DButil.GetSqlCommand(tran.Connection, sbSql.ToString) DButil.AddParameter(myCmd, "?seq_no", dr.Item("seq_no")) DButil.AddParameter(myCmd, "?stock_no", dr.Item("stock_no")) DButil.AddParameter(myCmd, "?quatity", dr.Item("quatity")) DButil.AddParameter(myCmd, "?in_price", dr.Item("in_price")) DButil.AddParameter(myCmd, "?out_price", dr.Item("out_price")) iResult = DButil.ExecuteTranNonQuery(tran.Connection, myCmd) Return iResult Catch ex As Exception Throw ex End Try End Function

      连接的创建，及事务的开启均在BLL层进行，执行完毕后，连接也在该层关闭。代码如下：

Public Function InsertOut(ByVal arl() As String, ByVal dt As DataTable) As Integer Dim iResult As Integer = 0 Using tran As IDbTransaction = TranUtil.BeginTransaction Try iResult = InsertOutStockMain1(tran, arl) If iResult = 0 Then Throw New BaseException("新增出货主档失败!") End If iResult = 0 For Each dr As DataRow In dt.Rows iResult += InsertOutStockDtl(tran, dr) Next tran.Commit() Return iResult Catch ex As Exception If tran IsNot Nothing Then tran.Rollback() End If Throw ex Finally TranUtil.Close(tran) End Try End Using End Function

      实例代码连接的是MySql数据库，需要在工程中引入Mysql.Data.dll文件。