Imports System.Data
Imports System.Data.Common
Imports System.Text
Imports System.ComponentModel
Imports System.Threading
Namespace DataLayer

    Public MustInherit Class BaseDataAccessLayer : Inherits Core.ParameterBuilders
        Implements IDisposable

        Private m_Provider As String
        Private m_CnnStr As String

        Private WithEvents m_Cnn As DbConnection
        Private m_Trx As DbTransaction
        Private m_Cmd As DbCommand
        Private WithEvents m_Adapter As DbDataAdapter

#Region " EVENTS SECTION "

        Public Event ConnectionStatus As EventHandler(Of StateChangeEventArgs)

        Public Event FetchDataSuccess As EventHandler(Of FetchDataEventArgs)
        Public Event FetchDataFailed As EventHandler(Of FetchDataEventArgs)

        Public Event TransmitDataSuccess As EventHandler(Of TransmitDataEventArgs)
        Public Event TransmitDataFailed As EventHandler(Of TransmitDataEventArgs)

#End Region

#Region " PROPERTIES SECTION "

        Public ReadOnly Property IsDisposed() As Boolean
            Get
                Return disposedValue
            End Get
        End Property

#End Region

        <DebuggerStepThrough()> _
        Public Sub New(ByVal provider As String, ByVal connectionString As String)
            MyBase.New(provider)
            m_Provider = provider
            m_CnnStr = connectionString
        End Sub

#Region " METHOD & FUNCTION SECTION "

        <DataObjectMethod(DataObjectMethodType.Fill)> _
        Protected Function FetchData(ByVal tableObj As DataTable, _
                                     ByVal sqlStatement As String, _
                                     ByVal params As Core.CSSIDbParameterCollection, _
                                     Optional ByVal cmdType As CommandType = CommandType.Text) _
                                     As DataTable
            Dim table As DataTable = Nothing
            Dim mtx As New Mutex

            mtx.WaitOne()
            Try
                m_Cnn = DbProviderFactories.GetFactory(m_Provider).CreateConnection
                m_Cnn.ConnectionString = m_CnnStr
                m_Cnn.Open()
                If m_Cnn.State = ConnectionState.Open Then
                    m_Cmd = m_Cnn.CreateCommand
                    m_Cmd.CommandText = sqlStatement
                    m_Cmd.CommandType = cmdType
                    If params IsNot Nothing AndAlso params.Count > 0 AndAlso params.ContainsSelectParameters Then
                        For Each param As CSSI.Library.Data.Core.CSSIDbParameter In params.GetSelectParameters
                            m_Cmd.Parameters.Add(param.Parameter(m_Provider))
                        Next
                    End If
                    Try
                        m_Adapter = DbProviderFactories.GetFactory(m_Provider).CreateDataAdapter
                        m_Adapter.SelectCommand = m_Cmd

                        If tableObj IsNot Nothing AndAlso Not tableObj.IsInitialized Then
                            table = Activator.CreateInstance(tableObj.GetType)
                        Else
                            table = tableObj
                        End If
                        table = m_Adapter.FillSchema(table, SchemaType.Mapped)
                        m_Adapter.Fill(table)
                    Catch dbEx As DbException
                        Throw dbEx
                    Catch ex As Exception
                        Throw ex
                    End Try
                End If
            Catch ex As Exception
                Throw ex
            Finally
                If m_Adapter IsNot Nothing Then
                    m_Adapter.Dispose()
                    m_Adapter = Nothing
                End If
                If m_Cmd IsNot Nothing Then
                    m_Cmd.Dispose()
                    m_Cmd = Nothing
                End If
                If m_Cnn IsNot Nothing Then
                    'Place closing in try block as the connection
                    'object may still be disposed.
                    Try
                        m_Cnn.Close()
                        m_Cnn.Dispose()
                    Finally
                        m_Cnn = Nothing
                    End Try
                End If
                mtx.ReleaseMutex()
                mtx.Close()
            End Try
            mtx = Nothing
            Return table
        End Function

        <DataObjectMethod(DataObjectMethodType.Fill)> _
        Protected Function FetchData(ByVal dataSetObj As DataSet, _
                                     ByVal sqlStatement As String, _
                                     ByVal params As Core.CSSIDbParameterCollection, _
                                     Optional ByVal cmdType As CommandType = CommandType.Text) _
                                     As DataSet
            Dim dataSet As DataSet = Nothing
            Dim mtx As New Mutex

            mtx.WaitOne()
            Try
                m_Cnn = DbProviderFactories.GetFactory(m_Provider).CreateConnection
                m_Cnn.ConnectionString = m_CnnStr
                m_Cnn.Open()
                If m_Cnn.State = ConnectionState.Open Then
                    m_Cmd = m_Cnn.CreateCommand
                    m_Cmd.CommandText = sqlStatement
                    m_Cmd.CommandType = cmdType
                    If params IsNot Nothing AndAlso params.Count > 0 AndAlso params.ContainsSelectParameters Then
                        For Each param As CSSI.Library.Data.Core.CSSIDbParameter In params.GetSelectParameters
                            m_Cmd.Parameters.Add(param.Parameter(m_Provider))
                        Next
                    End If
                    Try
                        m_Adapter = DbProviderFactories.GetFactory(m_Provider).CreateDataAdapter
                        m_Adapter.SelectCommand = m_Cmd

                        If dataSet IsNot Nothing AndAlso Not dataSet.IsInitialized Then
                            dataSet = Activator.CreateInstance(dataSet.GetType)
                        Else
                            dataSet = dataSetObj
                        End If
                        m_Adapter.FillSchema(dataSet, SchemaType.Mapped)
                        m_Adapter.Fill(dataSet)
                    Catch dbEx As DbException
                        Throw dbEx
                    Catch ex As Exception
                        Throw ex
                    End Try
                End If
            Catch ex As Exception
                Throw ex
            Finally
                If m_Adapter IsNot Nothing Then
                    m_Adapter.Dispose()
                    m_Adapter = Nothing
                End If
                If m_Cmd IsNot Nothing Then
                    m_Cmd.Dispose()
                    m_Cmd = Nothing
                End If
                If m_Cnn IsNot Nothing Then
                    'Place closing in try block as the connection
                    'object may still be disposed.
                    Try
                        m_Cnn.Close()
                        m_Cnn.Dispose()
                    Finally
                        m_Cnn = Nothing
                    End Try
                End If
                mtx.ReleaseMutex()
                mtx.Close()
            End Try
            mtx = Nothing
            Return dataSet
        End Function

        Protected Function FetchDataObject(ByVal objectToCreate As Type, _
                                           ByVal sqlStatement As String, _
                                           ByVal params As Core.CSSIDbParameterCollection, _
                                           Optional ByVal cmdType As CommandType = CommandType.Text, _
                                           Optional ByVal objectImplementsIFetchDataObject As Boolean = False) _
                                           As Object
            Dim obj As Object = Nothing
            Dim mtx As New Mutex

            mtx.WaitOne()
            Try
                m_Cnn = DbProviderFactories.GetFactory(m_Provider).CreateConnection
                m_Cnn.ConnectionString = m_CnnStr
                m_Cnn.Open()
                If m_Cnn.State = ConnectionState.Open Then
                    m_Cmd = m_Cnn.CreateCommand
                    m_Cmd.CommandText = sqlStatement
                    m_Cmd.CommandType = cmdType
                    If params IsNot Nothing AndAlso params.Count > 0 AndAlso params.ContainsSelectParameters Then
                        For Each param As CSSI.Library.Data.Core.CSSIDbParameter In params.GetSelectParameters
                            m_Cmd.Parameters.Add(param.Parameter(m_Provider))
                        Next
                    End If
                    Try
                        Using dr As New Core.SafeDataReader(m_Cmd.ExecuteReader(CommandBehavior.CloseConnection))
                            If dr.Read Then
                                If objectImplementsIFetchDataObject Then
                                    Dim fetchObj As Core.IFetchDataObject = _
                                        Activator.CreateInstance(objectToCreate)
                                    obj = fetchObj.InitializeObject(dr)
                                Else
                                    obj = Activator.CreateInstance(objectToCreate, New Object() {dr})
                                End If
                            End If
                            dr.Close()
                        End Using
                    Catch dbEx As DbException
                        Throw dbEx
                    Catch ex As Exception
                        Throw ex
                    End Try
                End If
            Catch ex As Exception
                Throw ex
            Finally
                If m_Adapter IsNot Nothing Then
                    m_Adapter.Dispose()
                    m_Adapter = Nothing
                End If
                If m_Cmd IsNot Nothing Then
                    m_Cmd.Dispose()
                    m_Cmd = Nothing
                End If
                If m_Cnn IsNot Nothing Then
                    'Place closing in try block as the connection
                    'object may still be disposed.
                    Try
                        m_Cnn.Close()
                        m_Cnn.Dispose()
                    Finally
                        m_Cnn = Nothing
                    End Try
                End If
                mtx.ReleaseMutex()
                mtx.Close()
            End Try
            mtx = Nothing
            Return obj
        End Function

        Protected Sub FetchDataObject(ByVal objectToLoad As Core.IFetchDataObject, _
                                      ByVal sqlStatement As String, _
                                      ByVal params As Core.CSSIDbParameterCollection, _
                                      Optional ByVal cmdType As CommandType = CommandType.Text)
            Dim mtx As New Mutex

            mtx.WaitOne()
            Try
                m_Cnn = DbProviderFactories.GetFactory(m_Provider).CreateConnection
                m_Cnn.ConnectionString = m_CnnStr
                m_Cnn.Open()
                If m_Cnn.State = ConnectionState.Open Then
                    m_Cmd = m_Cnn.CreateCommand
                    m_Cmd.CommandText = sqlStatement
                    m_Cmd.CommandType = cmdType
                    If params IsNot Nothing AndAlso params.Count > 0 AndAlso params.ContainsSelectParameters Then
                        For Each param As CSSI.Library.Data.Core.CSSIDbParameter In params.GetSelectParameters
                            m_Cmd.Parameters.Add(param.Parameter(m_Provider))
                        Next
                    End If
                    Try
                        Using dr As New Core.SafeDataReader(m_Cmd.ExecuteReader(CommandBehavior.CloseConnection))
                            If dr.Read Then
                                objectToLoad.InitializeObject(dr)
                                'If objectImplementsIFetchDataObject Then
                                '    Dim fetchObj As Core.IFetchDataObject = _
                                '        Activator.CreateInstance(objectToCreate)
                                '    obj = fetchObj.InitializeObject(dr)
                                'Else
                                '    obj = Activator.CreateInstance(objectToCreate, New Object() {dr})
                                'End If
                            End If
                            dr.Close()
                        End Using
                    Catch dbEx As DbException
                        Throw dbEx
                    Catch ex As Exception
                        Throw ex
                    End Try
                End If
            Catch ex As Exception
                Throw ex
            Finally
                If m_Adapter IsNot Nothing Then
                    m_Adapter.Dispose()
                    m_Adapter = Nothing
                End If
                If m_Cmd IsNot Nothing Then
                    m_Cmd.Dispose()
                    m_Cmd = Nothing
                End If
                If m_Cnn IsNot Nothing Then
                    'Place closing in try block as the connection
                    'object may still be disposed.
                    Try
                        m_Cnn.Close()
                    Finally
                        m_Cnn.Dispose()
                        m_Cnn = Nothing
                    End Try
                End If
                mtx.ReleaseMutex()
                mtx.Close()
            End Try
            mtx = Nothing
        End Sub

        Protected Function FetchScalerDataObject(ByVal sqlStatement As String, _
                                                 ByVal params As Core.CSSIDbParameterCollection, _
                                                 Optional ByVal cmdType As CommandType = CommandType.Text) _
                                                 As Object
            Dim mtx As New Mutex
            Dim retval As Object = Nothing
            mtx.WaitOne()
            Try
                m_Cnn = DbProviderFactories.GetFactory(m_Provider).CreateConnection
                m_Cnn.ConnectionString = m_CnnStr
                m_Cnn.Open()
                If m_Cnn.State = ConnectionState.Open Then
                    m_Cmd = m_Cnn.CreateCommand
                    m_Cmd.CommandText = sqlStatement
                    m_Cmd.CommandType = cmdType
                    If params IsNot Nothing AndAlso params.Count > 0 AndAlso params.ContainsSelectParameters Then
                        For Each param As CSSI.Library.Data.Core.CSSIDbParameter In params.GetSelectParameters
                            m_Cmd.Parameters.Add(param.Parameter(m_Provider))
                        Next
                    End If
                    Try
                        retval = m_Cmd.ExecuteScalar
                    Catch dbEx As DbException
                        Throw dbEx
                    Catch ex As Exception
                        Throw ex
                    End Try
                End If
            Catch ex As Exception
                Throw ex
            Finally
                If m_Adapter IsNot Nothing Then
                    m_Adapter.Dispose()
                    m_Adapter = Nothing
                End If
                If m_Cmd IsNot Nothing Then
                    m_Cmd.Dispose()
                    m_Cmd = Nothing
                End If
                If m_Cnn IsNot Nothing Then
                    'Place closing in try block as the connection
                    'object may still be disposed.
                    Try
                        m_Cnn.Close()
                        m_Cnn.Dispose()
                    Finally
                        m_Cnn = Nothing
                    End Try
                End If
                mtx.ReleaseMutex()
                mtx.Close()
            End Try
            mtx = Nothing
            Return retval
        End Function

        Protected Function FetchDataObjects(ByVal objectToCreate As Type, _
                                            ByVal sqlStatement As String, _
                                            ByVal params As Core.CSSIDbParameterCollection, _
                                            Optional ByVal cmdType As CommandType = CommandType.Text, _
                                           Optional ByVal objectImplementsIFetchDataObject As Boolean = False) _
                                            As List(Of Object)
            Dim mtx As New Mutex
            Dim retval As List(Of Object) = Nothing
            mtx.WaitOne()
            Try
                m_Cnn = DbProviderFactories.GetFactory(m_Provider).CreateConnection
                m_Cnn.ConnectionString = m_CnnStr
                m_Cnn.Open()
                If m_Cnn.State = ConnectionState.Open Then
                    m_Cmd = m_Cnn.CreateCommand
                    m_Cmd.CommandText = sqlStatement
                    m_Cmd.CommandType = cmdType
                    If params IsNot Nothing AndAlso params.Count > 0 AndAlso params.ContainsSelectParameters Then
                        For Each param As CSSI.Library.Data.Core.CSSIDbParameter In params.GetSelectParameters
                            m_Cmd.Parameters.Add(param.Parameter(m_Provider))
                        Next
                    End If
                    Try
                        Using dr As New Data.Core.SafeDataReader(m_Cmd.ExecuteReader)
                            Try
                                Do While dr.Read
                                    If retval Is Nothing Then
                                        retval = New List(Of Object)
                                    End If
                                    If objectImplementsIFetchDataObject Then
                                        Dim ifdo As Data.Core.IFetchDataObject = Activator.CreateInstance(objectToCreate)
                                        retval.Add(ifdo.InitializeObject(dr))
                                    Else
                                        retval.Add(Activator.CreateInstance(objectToCreate, New Object() {dr}))
                                    End If
                                Loop
                            Catch dbEx As DbException
                                Throw dbEx
                            Catch ex As Exception
                                Throw ex
                            End Try
                        End Using
                    Catch dbEx As DbException
                        Throw dbEx
                    Catch ex As Exception
                        Throw ex
                    End Try
                End If
            Catch ex As Exception
                Throw ex
            Finally
                If m_Adapter IsNot Nothing Then
                    m_Adapter.Dispose()
                    m_Adapter = Nothing
                End If
                If m_Cmd IsNot Nothing Then
                    m_Cmd.Dispose()
                    m_Cmd = Nothing
                End If
                If m_Cnn IsNot Nothing Then
                    'Place closing in try block as the connection
                    'object may still be disposed.
                    Try
                        m_Cnn.Close()
                        m_Cnn.Dispose()
                    Finally
                        m_Cnn = Nothing
                    End Try
                End If
                mtx.ReleaseMutex()
                mtx.Close()
            End Try
            mtx = Nothing
            Return retval
        End Function

        Protected Function FetchCloneData(ByVal sqlStatement As String, _
                                          ByVal params As Core.CSSIDbParameterCollection, _
                                          Optional ByVal cmdType As CommandType = CommandType.Text) _
                                          As Object

        End Function

        <DataObjectMethod(DataObjectMethodType.Insert Or _
        DataObjectMethodType.Update Or _
        DataObjectMethodType.Delete)> _
        Protected Function TransmitData(ByVal sqlStatement As String, _
                                        ByVal params As Core.CSSIDbParameterCollection, _
                                        Optional ByVal cmdType As CommandType = CommandType.Text, _
                                        Optional ByVal usesParameterReturnValue As Boolean = False, _
                                        Optional ByVal useTransaction As Boolean = False, _
                                        Optional ByRef trans As DbTransaction = Nothing) _
                                        As Object
            Dim retval As Object = Nothing
            Dim mtx As Mutex = Nothing

            mtx = New Mutex
            mtx.WaitOne()
            Try
                If trans Is Nothing Then
                    m_Cnn = DbProviderFactories.GetFactory(m_Provider).CreateConnection
                    m_Cnn.ConnectionString = m_CnnStr
                    m_Cnn.Open()
                Else
                    m_Cnn = trans.Connection
                End If
                If m_Cnn.State = ConnectionState.Open Then

                    If trans Is Nothing Then
                        If Not useTransaction Then
                            m_Trx = m_Cnn.BeginTransaction
                            m_Cmd = m_Cnn.CreateCommand
                            m_Cmd.Transaction = m_Trx
                        Else
                            trans = m_Cnn.BeginTransaction
                            m_Cmd = m_Cnn.CreateCommand
                            m_Cmd.Transaction = trans
                        End If
                    Else
                        m_Cmd = m_Cnn.CreateCommand
                        m_Cmd.Transaction = trans
                    End If

                    m_Cmd.CommandType = cmdType
                    m_Cmd.CommandText = sqlStatement
                    If params IsNot Nothing AndAlso params.Count > 0 Then
                        For Each param As CSSI.Library.Data.Core.CSSIDbParameter In params.GetAllParameters
                            m_Cmd.Parameters.Add(param.Parameter(m_Provider))
                        Next
                    End If

                    If usesParameterReturnValue Then
                        retval = m_Cmd.ExecuteNonQuery
                        retval = _
                            m_Cmd.Parameters(params.GetReturnValueParameter.ParameterName).Value
                    Else
                        retval = m_Cmd.ExecuteNonQuery
                    End If

                    If Not useTransaction Then
                        m_Trx.Commit()
                    End If
                    RaiseEvent TransmitDataSuccess(Me, New TransmitDataEventArgs)
                End If
            Catch ex As Exception
                If trans Is Nothing Then
                    m_Trx.Rollback()
                End If
                RaiseEvent TransmitDataFailed(Me, New TransmitDataEventArgs)
            Finally
                If m_Cmd IsNot Nothing Then
                    m_Cmd.Dispose()
                    m_Cmd = Nothing
                End If
                If Not useTransaction AndAlso m_Cnn IsNot Nothing Then
                    'Place closing in try block as the connection
                    'object may still be disposed.
                    Try
                        m_Cnn.Close()
                        m_Cnn.Dispose()
                    Finally
                        m_Cnn = Nothing
                    End Try
                End If
                mtx.ReleaseMutex()
                mtx.Close()
            End Try

            mtx = Nothing
            Return retval
        End Function

        <DataObjectMethod(DataObjectMethodType.Insert Or _
        DataObjectMethodType.Update Or _
        DataObjectMethodType.Delete)> _
        Protected Function TransmitDataWithoutTransaction( _
            ByVal sqlStatement As String, ByVal params As Core.CSSIDbParameterCollection, _
            Optional ByVal cmdType As CommandType = CommandType.Text) As Object
            '******************************************************************
            Dim retval As Object = Nothing
            Dim mtx As Mutex = Nothing

            mtx = New Mutex
            mtx.WaitOne()
            Try
                m_Cnn = DbProviderFactories.GetFactory(m_Provider).CreateConnection
                m_Cnn.ConnectionString = m_CnnStr
                m_Cnn.Open()
                If m_Cnn.State = ConnectionState.Open Then
                    m_Cmd = m_Cnn.CreateCommand
                    m_Cmd.CommandType = cmdType
                    m_Cmd.CommandText = sqlStatement
                    If params IsNot Nothing AndAlso params.Count > 0 Then
                        For Each param As CSSI.Library.Data.Core.CSSIDbParameter In params.GetAllParameters
                            m_Cmd.Parameters.Add(param.Parameter(m_Provider))
                        Next
                    End If
                    retval = m_Cmd.ExecuteNonQuery
                    RaiseEvent TransmitDataSuccess(Me, New TransmitDataEventArgs)
                End If
            Catch ex As Exception
                RaiseEvent TransmitDataFailed(Me, New TransmitDataEventArgs)
            Finally
                If m_Cmd IsNot Nothing Then
                    m_Cmd.Dispose()
                    m_Cmd = Nothing
                End If
                If m_Cnn IsNot Nothing Then
                    'Place closing in try block as the connection
                    'object may still be disposed.
                    Try
                        m_Cnn.Close()
                        m_Cnn.Dispose()
                    Finally
                        m_Cnn = Nothing
                    End Try
                End If
                mtx.ReleaseMutex()
                mtx.Close()
            End Try

            mtx = Nothing
            Return retval
        End Function

        'Protected Function TransmitData(ByVal dataSet As DataSet, _
        '                                ByVal sqlSelectStatement As String, _
        '                                ByVal params As Core.CSSIDbParameterCollection, _
        '                                Optional ByVal cmdType As CommandType = CommandType.Text, _
        '                                Optional ByVal usesParameterReturnValue As Boolean = False, _
        '                                Optional ByRef trans As DbTransaction = Nothing, _
        '                                Optional ByVal sqlInsertStatement As String = "") _
        '                                As Object
        '    Dim da As DbDataAdapter = Common.DbProviderFactories.GetFactory(m_Provider).CreateDataAdapter

        'End Function

#End Region

        <DebuggerStepThrough()> _
        Private Sub m_Cnn_StateChange(ByVal sender As Object, ByVal e As System.Data.StateChangeEventArgs) Handles m_Cnn.StateChange
            RaiseEvent ConnectionStatus(Me, e)
        End Sub

        Private Sub m_Adapter_FillError(ByVal sender As Object, ByVal e As System.Data.FillErrorEventArgs) Handles m_Adapter.FillError
            RaiseEvent FetchDataFailed(Me, New FetchDataEventArgs)
        End Sub

#Region " DISPOSE SECTION "

        Private disposedValue As Boolean = False        ' To detect redundant calls

        ' IDisposable
        <DebuggerStepThrough()> _
        Protected Overridable Sub Dispose(ByVal disposing As Boolean)
            If Not Me.disposedValue Then
                If disposing Then
                    ' TODO: free managed resources when explicitly called
                    CleanUp()
                End If

                ' TODO: free shared unmanaged resources
            End If
            Me.disposedValue = True
        End Sub

#Region " IDisposable Support "

        ' This code added by Visual Basic to correctly implement the disposable pattern.
        <DebuggerStepThrough()> _
        Public Sub Dispose() Implements IDisposable.Dispose
            ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
            Dispose(True)
            GC.SuppressFinalize(Me)
        End Sub

#End Region

        <DebuggerStepThrough()> _
        Private Sub CleanUp()
            If m_Cmd IsNot Nothing Then
                m_Cmd.Dispose()
                m_Cmd = Nothing
            End If
            If m_Trx IsNot Nothing Then
                m_Trx.Dispose()
                m_Trx = Nothing
            End If
            If m_Cnn IsNot Nothing Then
                m_Cnn.Dispose()
                m_Cnn = Nothing
            End If
        End Sub

#End Region

    End Class

End Namespace