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 Gates.DA
Imports System.Data
Imports System.Data.sqlClient

Namespace DA

Public Class DBTransaction

Dim _DBConnector As DBConnector
Dim _DBTransaction As 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 Sub BeginTransactio()

Try
If _DBConnector Is Nothing Then
_DBConnector.GetDBConnection()
End If

If _DBConnector.DBConnector.State = ConnectionState.Open Then
_DBConnector.OpenDBConnection()

End If
_DBTransaction = _DBConnector.DBConnector.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
Finally
_DBTransaction.Dispose()
_DBConnector.CloseDBConnection()
_isTransaction = False

End Try
End Sub
Public Sub RollbackTransaction()
Try

If Not _DBTransaction Is Nothing Then
_DBTransaction.Rollback()

_DBTransaction.Dispose()

End If
_DBConnector.CloseDBConnection()
_isTransaction = False

Catch ex As Exception

End Try
End Sub

Private Function getCommandTrans(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.DBConnector
If isSP Then
selCommand.CommandType = CommandType.StoredProcedure
Else
selCommand.CommandType = CommandType.Text
End If

selCommand.CommandText = commandText

If _TimeOut > 0 Then
selCommand.CommandTimeout = _TimeOut
End If
If _isTransaction Then
selCommand.Transaction = _DBTransaction
End If

Return selCommand

Catch ex As Exception
RollbackTransaction()

Throw ex
End Try
End Function

Private Function getAdapterTrans(ByVal command As IDbCommand) As IDbDataAdapter

Try

Return New System.Data.sqlClient.sqlDataAdapter(command)

Catch ex As Exception
Throw ex

End Try
End Function

Public Function sqlToDataset(ByVal tableName As String,ByVal strsql As String) As DataSet
Dim reDS As New DataSet
Dim selCommand As IDbCommand
Dim selAdpatee As IDataAdapter

Try

Dim tables() As String
tables = tableName.Split("|")

selCommand = getCommandTrans(strsql)
selAdpatee = getAdapterTrans(selCommand)

selAdpatee.Fill(reDS)

For i As Integer = 0 To tables.Count - 1
reDS.Tables(i).TableName = tables(0)
Next

Return reDS

Catch ex As Exception
Throw ex

End Try

End Function

Public Function Execsql(ByVal strsql As String) As Boolean

Dim selCommand As IDbCommand

Try
selCommand = getCommandTrans(strsql)
selCommand.ExecuteNonQuery()

Return True
Catch ex As Exception
Return False
Throw

End Try

End Function

End Class

End Namespace