机房收费系统完美设计——事务处理VS三层架构

页面导航:首页 > 软件编程 > vb.net > 机房收费系统完美设计——事务处理VS三层架构

机房收费系统完美设计——事务处理VS三层架构

来源: 作者: 时间:2016-01-18 16:54 【

上面的博客中只是通过简单的例子介绍事物的应用,但是在三层架构中事物应该怎样使用呢,要弄清楚这一点首先是明白事物、连接、command对象之间的关系connection 对象是数据连接,他

 

上面的博客中只是通过简单的例子介绍事物的应用,但是在三层架构中事物应该怎样使用呢,要弄清楚这一点首先是明白事物、连接、command对象之间的关系

connection 对象是数据连接,他决定你链接什么,数据库在哪里

command 对象是查询执行,由他来完成查询语句或执行语句

事物呢,一个连接可以有多个事物。但一个事物只属于一个连接。连接就好像北京到上海的一条铁路,事物就是铁路上的列出,列车可以只有车头,也可以带有好多车厢,火车包括车头都是要到北京的。但是要是要不整列火车到达北京,要不都没有到达。

上面是我对事物的理解。

说说我的三层架构:

我的三层结构中融入了设计模式,其中U层对B层的操作是通过外观模式来操作的。外观模式的作用就是让B层复杂的逻辑统一到一个外观中来。

以注册新卡为例:

注册新卡需要插入学生信息和卡信息。当然是需要放在事物里的。要不都插入成功,要不都不插入。

通过以上我的解说大家应该能想出,事物的开始和结束都要放在外观层的(后面用Fa层代替)但是事物的创建时要在连接的基础上的。也就是Fa层要获得连接。就要在B、D、Factory、IDAL走一遍。然后获得的Transaction,还要以参数的形式传回D层操作。这个过程走下来,我发现自己要改的东西又有很多了。

让我们先看看怎么实现。

这里我找了一部分别人的代码。其中的Sqlhelper写的比较有代表性。给大家分享。当然方法一定不止是有一个的。大家写好的sqlhelper不一定不好。

将事务处理独立出来,该类的功能主要是创建数据库连接,开启事务处理,关闭连接。代码如下:


[vb]
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   

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 

数据库操作类代码如下:

 

[vb]
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   
#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   

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 
#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中的方法进行数据的操作。代码如下:

[vb] 
主档   
    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   

主档 
    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层进行,执行完毕后,连接也在该层关闭。代码如下:


[vb] 
<SPAN style="FONT-SIZE: 18px">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   
</SPAN> 

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数据库

其中他的 DButil  也就是我说的sqlhelper

 


但是这种方法我也说过需要把Transaction作为参数一层层往下传的。有没有更好的办法呢。一定使用的。方法我们下一篇博客介绍。

 


 


 

Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<