|
Imports System.Data
Imports System.Data.SqlClient
Namespace DA
Public Class DBConnector
Dim _connectionString As String
Dim _DBConnection As System.Data.SqlClient.SqlConnection
Public Sub New(ByVal connectionString As String)
_connectionString = connectionString
End Sub
Property DBConnection() As IDbConnection
Get
Return _DBConnection
End Get
Set(ByVal value As IDbConnection)
_DBConnection = value
End Set
End Property
Public Sub GetDBConnection()
Try
If _DBConnection Is Nothing Then
_DBConnection = New System.Data.SqlClient.SqlConnection(_connectionString)
End If
Catch ex As Exception
Throw ex
End Try
End Sub
Public Sub OpenDBConnection()
Try
If _DBConnection Is Nothing Then
GetDBConnection()
End If
If _DBConnection.State <> ConnectionState.Open Then
_DBConnection.Open()
End If
Catch ex As Exception
_DBConnection.Close()
Throw ex
End Try
End Sub
Public Sub CloseDBConnection()
Try
If Not _DBConnection Is Nothing Then
If _DBConnection.State = ConnectionState.Open Then
_DBConnection.Close()
End If
End If
Catch ex As Exception
End Try
End Sub
End Class
End Namespace
Imports System.Data
Imports System.Data.SqlClient
Imports GatesU.MES.DA
Namespace DA
Public Class DBTransaction
Dim _DBConnector As GatesU.MES.DA.DBConnector
Dim _DBTransaction As System.Data.IDbTransaction
Dim _TimeOut As Integer = 0
Dim _isTransaction As Boolean = False
Public Sub New(ByVal connectionString As String)
_DBConnector = New DBConnector(connectionString)
End Sub
Public Property DBTransaction() As IDbTransaction
Get
Return _DBTransaction
End Get
Set(ByVal value As IDbTransaction)
_DBTransaction = value
End Set
End Property
Public Property TimeOut() As Integer
Get
Return _TimeOut
End Get
Set(ByVal value As Integer)
_TimeOut = value
End Set
End Property
Public Sub BeginTransaction()
Try
If _DBConnector.DBConnection Is Nothing Then
_DBConnector.GetDBConnection()
End If
If _DBConnector.DBConnection.State <> ConnectionState.Open Then
_DBConnector.OpenDBConnection()
End If
_DBTransaction = _DBConnector.DBConnection.BeginTransaction(IsolationLevel.ReadUncommitted)
_isTransaction = True
Catch ex As Exception
_DBConnector.CloseDBConnection()
_isTransaction = False
End Try
End Sub
Public Sub CommitTransaction()
Try
_DBTransaction.Commit()
Catch ex As Exception
_DBTransaction.Rollback()
Finally
_DBConnector.CloseDBConnection()
_DBTransaction.Dispose()
_isTransaction = False
End Try
End Sub
Public Sub RollbackTransaction()
If Not _DBTransaction Is Nothing Then
_DBTransaction.Rollback()
_DBTransaction.Dispose()
End If
_DBConnector.CloseDBConnection()
_isTransaction = False
End Sub
Private Function getDBCommandTrans(ByVal commandText As String,Optional ByVal isSP As Boolean = False) As IDbCommand
Dim selCommand As New System.Data.SqlClient.SqlCommand
Try
_DBConnector.OpenDBConnection()
selCommand.Connection = _DBConnector.DBConnection
If isSP Then
selCommand.CommandType = CommandType.StoredProcedure
Else
selCommand.CommandType = CommandType.Text
End If
selCommand.CommandText = commandText
If _isTransaction Then
selCommand.Transaction = _DBTransaction
End If
If _TimeOut > 0 Then
selCommand.CommandTimeout = _TimeOut
End If
Return selCommand
Catch ex As Exception
RollbackTransaction()
Throw ex
End Try
End Function
Private Function getDBAdapter(ByVal DBCommand As IDbCommand) As IDbDataAdapter
Try
Return New System.Data.SqlClient.SqlDataAdapter(DBCommand)
Catch ex As Exception
RollbackTransaction()
Throw ex
End Try
End Function
Public Function ExecSQL(ByVal strSQL As String) As Boolean
Dim selCommand As IDbCommand
Try
selCommand = getDBCommandTrans(strSQL)
selCommand.ExecuteNonQuery()
Return True
Catch ex As Exception
Return False
End Try
End Function
Public Function SQLToDataset(ByVal tableName As String,ByVal strSQL As String) As DataSet
Dim selCommand As IDbCommand
Dim selAdapter As IDataAdapter
Dim reDS As New DataSet
Try
Dim tables() As String
tables = tableName.Split("|")
selCommand = getDBCommandTrans(strSQL)
selAdapter = getDBAdapter(selCommand)
If selCommand Is Nothing Or selAdapter Is Nothing Then
Throw New Exception("err")
End If
selAdapter.Fill(reDS)
If reDS.Tables.Count <> tables.Count Then
Throw New Exception("e")
End If
For i As Integer = 0 To tables.Count - 1
reDS.Tables(i).TableName = tables(i)
Next
Return reDS
Catch ex As Exception
Throw ex
End Try
End Function
End Class
End Namespace （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
