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