Public Class DatabaseClass
        Dim db As Database
        Dim dataSet As DataSet

        Dim fact As DbProviderFactory
        Dim _conn As DbConnection
        Dim _trans As DbTransaction

        'Connection Property
        Public Property Conn() As DbConnection
            Get
                Return _conn
            End Get
            Set(ByVal value As DbConnection)
                _conn = value
            End Set
        End Property

        'Transaction Property
        Public Property Trans() As DbTransaction
            Get
                Return _trans
            End Get
            Set(ByVal Value As DbTransaction)
                _trans = Value
            End Set
        End Property

        Public Sub New()
            Try
                db = DatabaseFactory.CreateDatabase(NAPResource.Connection)
                Conn = db.CreateConnection
                fact = db.DbProviderFactory
            Catch ex As ConfigurationException
                Logger.LogInfo(ex)
            Catch ex As TargetInvocationException
                Logger.LogInfo(ex)
            Catch ex As Exception
            End Try
        End Sub

        Public Function SelectMethod(ByVal query As String, ByVal param() As Object) As DataSet
            Try
                'Executing stored procedure with parameter passing then return the dataset
                dataSet = db.ExecuteDataSet(query, param)

            Catch ex As SqlClient.SqlException
                Throw
            Catch ex As DbException
                Throw
            End Try
            Return dataSet
        End Function

        Public Function SelectMethod(ByVal query As String) As DataSet
            Try
                'Executing stored procedure without parameter passing then return the dataset 
                dataSet = db.ExecuteDataSet(CommandType.StoredProcedure, query)
            Catch ex As SqlClient.SqlException
                Throw
            Catch ex As DbException
                Throw
            End Try
            Return dataSet
        End Function

        Public Function SelectMethod(ByVal query As String, ByVal bool As Boolean) As DataSet
            Try
                'Executing query then return the dataset 
                dataSet = db.ExecuteDataSet(CommandType.Text, query)
            Catch ex As SqlClient.SqlException
                Throw
            Catch ex As DbException
                Throw
            End Try
            Return dataSet
        End Function

        Public Function InsertMethod(ByVal query As String, ByVal param() As Object) As Integer
            Dim saved As Integer = 0
            'Executing Insert/Delete/Update stored procedures with parameters then return integer value
            Try
                LogParams(param, query)
                saved = db.ExecuteNonQuery(query, param)
                Return saved
            Catch ex As SqlClient.SqlException
                Throw
            Catch ex As DbException
                Throw
            End Try
            Return saved
        End Function

       
        Public Function InsertMethod(ByVal query As String) As Integer
            Dim saved As Integer = 0
            Logger.LogInfo(query)
            Try
                'Executing Insert/Delete/Update stored procedures without parameters then return integer value
                saved = db.ExecuteNonQuery(CommandType.Text, query)
                Return saved
            Catch ex As SqlClient.SqlException
                Throw
            Catch ex As DbException
                Throw
            End Try
            Return saved
        End Function

        Public Function UpdateDataSet(ByVal dataSet As DataSet, ByVal tableName As String) As Integer
            Dim saved As Integer = 0
            Try
                'Inserting many rows at once
                Dim fact As DbProviderFactory = db.DbProviderFactory
                Dim cnn As DbConnection = db.CreateConnection()
                cnn.Open()

                'Transaction assigned for update table
                Dim dbTrans As DbTransaction = cnn.BeginTransaction()
                Dim adp As DbDataAdapter = fact.CreateDataAdapter()
                Dim cmd As DbCommand = cnn.CreateCommand()
                cmd.Transaction = dbTrans
                cmd.CommandText = NAPResource.SelectQuery & tableName
                adp.SelectCommand = cmd
                Dim bld As DbCommandBuilder = fact.CreateCommandBuilder()
                bld.DataAdapter = adp
                adp.InsertCommand = CType((CType(bld.GetInsertCommand(), ICloneable)).Clone(), DbCommand)
                adp.Update(dataSet, tableName)
                dbTrans.Commit()
                Return saved

            Catch ex As SqlClient.SqlException
                Throw
            Catch ex As DbException
                Throw
            End Try
            Return saved
        End Function

        Public Function UpdateDataSet(ByVal dataSet As DataSet, ByVal tableName As ArrayList) As Integer
            Dim saved As Integer = 0

            Try
                'Inserting many rows at once
                If _conn.State = ConnectionState.Closed Then
                    _conn.Open()
                End If
                Dim cmd As DbCommand = _conn.CreateCommand()
                If _trans Is Nothing Then
                    doBeginTrans()
                End If
                cmd.Transaction = _trans
                Dim adp As DbDataAdapter = fact.CreateDataAdapter()
                For Each table As String In tableName
                    cmd.CommandText = NAPResource.SelectQuery & table
                    adp.SelectCommand = cmd
                    Dim bld As DbCommandBuilder = fact.CreateCommandBuilder()
                    bld.DataAdapter = adp
                    adp.InsertCommand = CType((CType(bld.GetInsertCommand(), ICloneable)).Clone(), DbCommand)
                    adp.Update(dataSet, table)
                Next
                Return saved
            Catch ex As SqlClient.SqlException
                Throw
            Catch ex As DbException
                Throw
            End Try
            Return saved
        End Function

        Public Function InsertMethodTran(ByVal name As String, ByVal params() As Object) As Integer

            Try
                'This method is for inserting new agency details with transaction
                LogParams(params, name)
                If _conn.State = ConnectionState.Closed Then
                    _conn.Open()
                End If
                Dim cmd As DbCommand = _conn.CreateCommand()
                If _trans Is Nothing Then
                    doBeginTrans()
                End If
                cmd.Transaction = _trans
                cmd.CommandText = name
                cmd.CommandType = CommandType.StoredProcedure
                db.DiscoverParameters(cmd)
                Dim paramCount As Integer = cmd.Parameters.Count
                Dim Array As ArrayList = New ArrayList()

                Dim iCount As Integer = 0
                For iCount = 0 To paramCount - 1
                    Array.Add(cmd.Parameters.Item(iCount).ParameterName)
                Next

                'Adding parameters to command to execute
                Dim iCnt As Integer = 0
                For iCount = 0 To paramCount - 1
                    If cmd.Parameters(iCount).Direction.ToString().Equals(NAPResource.Input) Then
                        cmd.Parameters(iCount).Value = params(iCnt)
                        iCnt = iCnt + 1
                    End If
                Next
                Return Convert.ToInt32(cmd.ExecuteScalar())
            Catch ex As Exception
                Throw
            End Try
        End Function

        Public Sub doBeginTrans()
            'Begin transaction
            Try
                If _conn.State = ConnectionState.Closed Then
                    _conn.Open()
                    _trans = _conn.BeginTransaction()
                End If
            Catch ex As Exception
                Throw
            End Try
        End Sub

        Public Sub doCommit()
            'Commit the transaction if no errors  
            Try
                If Not _trans Is Nothing Then
                    If _conn.State = ConnectionState.Closed Then
                        _conn.Open()
                        _trans.Commit()
                        _trans = Nothing
                    Else
                        _trans.Commit()
                        _trans = Nothing
                    End If
                End If
            Catch ex As Exception
                Throw
            End Try
        End Sub

        Public Sub doRollback()
            'Rollback the transaction if there is error
            Try
                If Not _trans Is Nothing Then
                    _trans.Rollback()
                    _trans = Nothing
                End If
            Catch ex As Exception
                Throw
            End Try
        End Sub
    End Class