Imports System.Data
Imports System.Data.OleDb
Imports System.Configuration.ConfigurationManager
Public Class AccessDB
    Implements IDisposable
    'This class is used to Manipulated the MSSQL Database
    Private Function GetConnectionString() As String

        Dim str As String '= AppSettings("fs10ConnectionString").ToString       
        Dim filepath As String = AppDomain.CurrentDomain.BaseDirectory & "\rcmb.dat"
        str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filepath & ";User Id=admin;Password=;"
        Return str

    End Function

    'FillDataSet(spName)
    'Purpose : Connects the database,executes the query and returns datatable
    'Inputs  : Stored Procedure Name and TableName (optional)
    'Returns : DataTable
    Public Overloads Function FillDataSet(ByVal Query As String) As DataTable
        Dim myConnection As OleDbConnection = New OleDbConnection(GetConnectionString)
        Dim myAdapter As OleDbDataAdapter = New OleDbDataAdapter(Query, myConnection)
        Try
            myAdapter.SelectCommand.CommandType = CommandType.Text
            Dim DS As New DataSet
            myConnection.Open()
            myAdapter.Fill(DS, "Table1")
            Return DS.Tables("Table1")
        Catch ex As Exception
            Throw
        Finally
            If Not myConnection Is Nothing Then
                myConnection.Close()
                myConnection.Dispose()
            End If
        End Try

    End Function

    'FillDataSet(spName,TableName)
    'Purpose : Connects the database,executes the query and returns datatable
    'Inputs  : Query String and TableName (optional)
    'Returns : DataTable
    Public Overloads Function FillDataSet(ByVal Query As String, ByVal tableName As String) As DataTable
        Try
            Dim myConnection As OleDbConnection = New OleDbConnection(GetConnectionString)
            Dim myAdapter As OleDbDataAdapter = New OleDbDataAdapter(Query, myConnection)
            myAdapter.SelectCommand.CommandType = CommandType.Text
            Dim DS As New DataSet
            myConnection.Open()
            myAdapter.Fill(DS, tableName)
            myConnection.Close()
            myConnection.Dispose()
            Return DS.Tables(tableName)
        Catch ex As Exception
            Throw
        End Try
    End Function

    'ExecuteNQ(Query)
    'Purpose : Connects the database,executes non query
    'Inputs  : Query
    'Returns : Return Nothing

    Public Function ExecuteNQ(ByVal Query As String) As Object

        Dim myConnection As OleDbConnection = New OleDbConnection(GetConnectionString)
        Dim myCommand As OleDbCommand = New OleDbCommand(Query, myConnection)
        Try
            myConnection.Open()
            Return myCommand.ExecuteNonQuery()
        Catch ex As Exception
            Throw
        Finally
            If Not myConnection Is Nothing Then
                myConnection.Close()
                myConnection.Dispose()
            End If
        End Try
    End Function

    'ExecuteScalar(Query)
    'Purpose : Connects the database,executes scalar
    'Inputs  : Query
    'Returns : The first column of the first row in the result set,
    '           or a null reference if the result set is empty.
    Public Overloads Function ExecuteScalar(ByVal Query As String) As Object

        Dim myConnection As OleDbConnection = New OleDbConnection(GetConnectionString)
        Dim myCommand As OleDbCommand = New OleDbCommand(Query, myConnection)
        Try
            myConnection.Open()
            Return myCommand.ExecuteScalar()
        Catch ex As Exception
            Throw
        Finally
            If Not myConnection Is Nothing Then
                myConnection.Close()
                myConnection.Dispose()
            End If
        End Try

    End Function

    Private disposedValue As Boolean = False        ' To detect redundant calls

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                ' TODO: free unmanaged resources when explicitly called
            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.
    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

End Class