Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections

Namespace TKB.VV.DB
	Public Class clsConnectionProvider
		Implements IDisposable

#Region " Class Member Declarations "

		Private		m_scoDBConnection As SqlConnection
		Private		m_bIsTransactionPending, m_bIsDisposed As Boolean
		Private		m_stCurrentTransaction As SqlTransaction
		Private		m_alSavePoints As ArrayList

#End Region


		Public Sub New()
			' // Init the class
			InitClass()
		End Sub


		Overloads Public Sub Dispose() Implements IDisposable.Dispose
			Dispose(True)
			GC.SuppressFinalize(Me)
		End Sub


		Overridable Overloads Protected Sub Dispose(ByVal bIsDisposing As Boolean)
			' // Check to see if Dispose has already been called.
			If Not m_bIsDisposed Then
				If bIsDisposing Then
					' // Dispose managed resources.
					If Not (m_stCurrentTransaction Is Nothing) Then
						m_stCurrentTransaction.Dispose()
						m_stCurrentTransaction = Nothing
					End If
					If Not (m_scoDBConnection Is Nothing) Then
						' // closing the connection will abort (rollback) any pending transactions
						m_scoDBConnection.Close()
						m_scoDBConnection.Dispose()
						m_scoDBConnection = Nothing
					End If
				End If
			End If
			m_bIsDisposed = True
		End Sub


		Private Sub InitClass()
			' // Create all the objects and initialize other members.
			m_scoDBConnection = new SqlConnection()
			m_bIsDisposed = False
			m_stCurrentTransaction = Nothing
			m_bIsTransactionPending = False
			m_alSavePoints = new ArrayList()
		End Sub


		Public Function OpenConnection() As Boolean
			Try
				If (m_scoDBConnection.State And ConnectionState.Open) > 0 Then
					' // It's already open.
					Throw New Exception("OpenConnection::Connection is already open.")
				End If
				m_scoDBConnection.Open()
				m_bIsTransactionPending = False
				m_alSavePoints.Clear()
				Return True
			Catch ex As Exception
				' // bubble exception
				Throw ex
			End Try
		End Function


		Public Function BeginTransaction(sTransactionName As String) As Boolean
			Try
				If m_bIsTransactionPending Then
					' // no nested transactions allowed.
					Throw New Exception("BeginTransaction::Already transaction pending. Nesting not allowed")
				End If
				If (m_scoDBConnection.State And ConnectionState.Open) = 0 Then
					' // no open connection
					Throw New Exception("BeginTransaction::Connection is not open.")
				End If
				' // begin the transaction and store the transaction object.
				m_stCurrentTransaction = m_scoDBConnection.BeginTransaction(IsolationLevel.ReadCommitted, sTransactionName)
				m_bIsTransactionPending = True
				Return True
			Catch ex As Exception
				' // bubble exception
				Throw ex
			End Try
		End Function


		Public Function CommitTransaction() As Boolean
			Try
				If Not m_bIsTransactionPending Then
					' // no transaction pending
					Throw New Exception("CommitTransaction::No transaction pending.")
				End If
				If (m_scoDBConnection.State And ConnectionState.Open) = 0 Then
					' // no open connection
					Throw New Exception("CommitTransaction::Connection is not open.")
				End if
				' // commit the transaction
				m_stCurrentTransaction.Commit()
				m_bIsTransactionPending = False
				m_stCurrentTransaction.Dispose()
				m_stCurrentTransaction = Nothing
				m_alSavePoints.Clear()
				Return True
			Catch ex As Exception
				' // bubble exception
				Throw ex
			End Try
		End Function


		Public Function RollbackTransaction(sTransactionToRollback As String) As Boolean
			Try
				If Not m_bIsTransactionPending Then
					' // no transaction pending
					Throw New Exception("RollbackTransaction::No transaction pending.")
				End If
				If (m_scoDBConnection.State And ConnectionState.Open) = 0 Then
					' // no open connection
					Throw New Exception("RollbackTransaction::Connection is not open.")
				End If
				' // rollback the transaction
				m_stCurrentTransaction.Rollback(sTransactionToRollback)
				' // if this wasn't a savepoint, we've rolled back the complete transaction, so we
				' // can clean it up.
				If Not m_alSavePoints.Contains(sTransactionToRollback) Then
					' // it's not a savepoint
					m_bIsTransactionPending = False
					m_stCurrentTransaction.Dispose()
					m_stCurrentTransaction = Nothing
					m_alSavePoints.Clear()
				End If
				Return True
			Catch ex As Exception
				' // bubble exception
				Throw ex
			End Try
		End Function


		Public Function SaveTransaction(sSavePointName As String) As Boolean
			Try
				If Not m_bIsTransactionPending Then
					' // no transaction pending
					Throw New Exception("SaveTransaction::No transaction pending.")
				End If
				If (m_scoDBConnection.State And ConnectionState.Open) = 0 Then
					' // no open connection
					Throw New Exception("SaveTransaction::Connection is not open.")
				End If
				' // save the transaction
				m_stCurrentTransaction.Save(sSavePointName)
				' // Store the savepoint in the list.
				m_alSavePoints.Add(sSavePointName)
				Return True
			Catch ex As Exception
				' // bubble exception
				Throw ex
			End Try
		End Function


		Public Function CloseConnection(bCommitPendingTransaction As Boolean) As Boolean
			Try
				If (m_scoDBConnection.State And ConnectionState.Open) = 0 Then
					' // No open connection
					Return False
				End If
				If m_bIsTransactionPending Then
					If bCommitPendingTransaction Then
						' // Commit the pending transaction
						m_stCurrentTransaction.Commit()
					Else
						' // Rollback the pending transaction
						m_stCurrentTransaction.Rollback()
					End If
					m_bIsTransactionPending = False
					m_stCurrentTransaction.Dispose()
					m_stCurrentTransaction = Nothing
					m_alSavePoints.Clear()
				End If
				' // close the connection
				m_scoDBConnection.Close()
				Return True
			Catch ex As Exception
				' // bubble exception
				Throw ex
			End Try
		End Function


#Region " Class Property Declarations "

		Public ReadOnly Property stCurrentTransaction() As SqlTransaction
			Get
				Return m_stCurrentTransaction
			End Get
		End Property


		Public ReadOnly Property bIsTransactionPending() As Boolean
			Get
				Return m_bIsTransactionPending
			End Get
		End Property


		Public ReadOnly Property scoDBConnection() As SqlConnection
			Get
				Return m_scoDBConnection
			End Get
		End Property
		Public WriteOnly Property sConnectionString() As String
			Set (ByVal Value As String)
				m_scoDBConnection.ConnectionString = Value
			End Set
		End Property

#End Region

	End Class
End Namespace