CLASS D'ACCÈS AU BASE DE DONNÉES OLEDB

Imports System.Data.OleDb
Public Class AccessDataBase
    Private ds As DataSet
    Private dv As DataView
    Private dt As DataTable
    Private da As OleDbDataAdapter
    Private das As Dictionary(Of String, OleDbDataAdapter)
    Private con As OleDbConnection
    Private cmd As OleDbCommand
    Private reader As OleDbDataReader
    Private cmdBuilder As OleDbCommandBuilder
    Public Sub New(ByVal strCon As String)

        ds = New DataSet
        dv = New DataView
        da = New OleDbDataAdapter
        das = New Dictionary(Of String, OleDbDataAdapter)
        con = New OleDbConnection(strCon)
        cmd = New OleDbCommand("", con)
        dt = New DataTable
        cmdBuilder = New OleDbCommandBuilder
        

    End Sub
    Public Function getDataSet() As DataSet
        Return ds
    End Function
    Public Function getConnection() As OleDbConnection
        Return con
    End Function
    Public Sub creerTable(ByVal nomTable As String)
        Dim adapter As New OleDbDataAdapter
        executeRequete("select * from " & nomTable, adapter)
        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
        adapter.Fill(ds, nomTable)
        das.Add(nomTable, adapter)
    End Sub
#Region "Méthodes de mise à jour"
    Public Sub ajouter(ByVal nomTable As String, ByVal id As Object(), ByVal params As Object())
        If Not ds.Tables(nomTable).Rows.Contains(id) Then
            ds.Tables(nomTable).Rows.Add(params)
        Else
            MsgBox("Cet enregistrement existe déjà dans la base de données", MsgBoxStyle.Exclamation, "Erreur d'ajour")
        End If
    End Sub
    Public Sub modifier(ByVal nomtable As String, ByVal id As Object(), ByVal params As Object())
        Dim row As DataRow = Nothing
        Dim i As Integer
        If ds.Tables(nomtable).Rows.Contains(id) Then
            For i = 0 To params.Length - 1
                row(i) = params(i)
            Next
        End If
    End Sub
    Public Sub supprimer(ByVal nomTable As String, ByVal id As Object())
        If ds.Tables(nomTable).Rows.Contains(id) Then
            ds.Tables(nomTable).Rows.Find(id).Delete()
        End If
    End Sub
    Public Function rechercher(ByVal nomTable As String, ByVal id As Object) As DataRow
        If ds.Tables(nomTable).Rows.Contains(id) Then
            Return ds.Tables(nomTable).Rows.Find(id)
        Else
            Return Nothing
        End If
    End Function
    Public Function rechercherParFiltration(ByVal nomTable As String, ByVal filtre As Object) As DataRow()
        If ds.Tables(nomTable).Select(filtre).Count <> 0 Then
            Return ds.Tables(nomTable).Select(filtre)
        End If
        Return Nothing
    End Function
    Public Sub enregistrer(ByVal nomTable As String)
        cmdBuilder = New OleDbCommandBuilder(das(nomTable))
        das(nomTable).Update(ds.Tables(nomTable))
    End Sub
 
#End Region
#Region "Méthode de remplissage"
    Public Sub remplir(ByRef grid As DataGrid, ByVal nomTable As String, ByVal filtre As Object, ByVal expressionTri As Object)
        dv = New DataView(ds.Tables(nomTable))
        dv.RowFilter = filtre
        dv.Sort = expressionTri
        grid.DataSource = dv
    End Sub
    Public Sub remplir(ByRef grid As DataGridView, ByVal nomTable As String, ByVal filtre As Object, ByVal expressionTri As Object)
        dv = New DataView(ds.Tables(nomTable))
        dv.RowFilter = filtre
        dv.Sort = expressionTri
        grid.DataSource = dv
    End Sub
    Public Sub remplir(ByRef list As ListControl, ByVal nomTable As String, ByVal filtre As Object, ByVal expressionTri As Object, ByVal indexOfDisplayMember As Integer, ByVal indexOfValueMember As Integer)
        dv = New DataView(ds.Tables(nomTable))
        dv.RowFilter = filtre
        dv.Sort = expressionTri
        list.DataSource = dv
        list.DisplayMember = ds.Tables(nomTable).Columns(indexOfDisplayMember).Caption
        list.ValueMember = ds.Tables(nomTable).Columns(indexOfValueMember).Caption
    End Sub
    Public Sub remplir(ByRef grid As DataGrid, ByVal rqst As String)
        Dim table As New DataTable
        executeRequete(rqst, table)
        grid.DataSource = table
    End Sub
    Public Sub remplir(ByRef list As ListControl, ByVal rqst As String, ByVal indexOfDisplayMember As Integer, ByVal indexOfValueMember As Integer)
        Dim table As New DataTable
        executeRequete(rqst, table)
        list.DataSource = table
        list.DisplayMember = table.Columns(indexOfDisplayMember).Caption
        list.ValueMember = table.Columns(indexOfValueMember).Caption
    End Sub
    Public Sub remplir(ByRef grid As DataGridView, ByVal rqst As String)
        Dim table As New DataTable
        executeRequete(rqst, table)
        grid.DataSource = table
    End Sub
#End Region
#Region "Requête SQL sans paramètres"
    Private Sub ouvrir_con()
        Try
            con.Open()
        Catch ex As Exception
            Throw New Exception
        End Try

    End Sub
    Private Sub fermer_con()
        con.Close()
    End Sub
    Private Function CreerCommande(ByVal rqst As String)
        Return New OleDbCommand(rqst, con)
    End Function

    Public Function executeRequete(ByVal rqst As String) As Object
        Dim commande As New OleDbCommand
        commande = CreerCommande(rqst)
        Try
            ouvrir_con()
            Return commande.ExecuteScalar
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Erreur")
            Return Nothing
        Finally
            fermer_con()
        End Try
    End Function
    Public Sub executeRequete(ByVal rqst As String, ByRef myReader As OleDbDataReader)
        Dim commande As New OleDbCommand
        commande = CreerCommande(rqst)
        Try
            ouvrir_con()
            myReader = commande.ExecuteReader
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Erreur")
        Finally
            fermer_con()
        End Try
    End Sub
    Public Sub executeRequete(ByVal rqst As String, ByRef adapter As OleDbDataAdapter)
        Dim commande As New OleDbCommand
        commande = CreerCommande(rqst)
        Try
            ouvrir_con()
            adapter = New OleDbDataAdapter(commande)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Erreur")
        Finally
            fermer_con()
        End Try
    End Sub
    Public Sub executeRequete(ByVal rqst As String, ByRef table As DataTable)
        Dim adapter As New OleDbDataAdapter
        executeRequete(rqst, adapter)
        Try
            ouvrir_con()
            table = New DataTable
            adapter.Fill(table)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Erreur")
        Finally
            fermer_con()
        End Try
    End Sub
    Public Sub executeRequeteReader(ByVal rqst As String, ByVal table As DataTable)
        Dim row As DataRow
        Try
            ouvrir_con()
            reader = executeRequete(rqst)
            While reader.Read
                row = table.NewRow
                For i As Integer = 0 To reader.FieldCount - 1
                    row(i) = reader.GetValue(i)
                Next
                table.Rows.Add(row)
            End While
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Erreur")
        Finally
            fermer_con()
        End Try
    End Sub
    Public Sub executeRequete(ByVal rqst As String, ByVal dtSet As DataSet)
        Try
            ouvrir_con()
            cmd = CreerCommande(rqst)
            da.SelectCommand = cmd
            da.Fill(dtSet)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Erreur")
        Finally
            fermer_con()
        End Try
    End Sub
#End Region
#Region "Requête SQL avec paramètres"
    Private Function CreerCommande(ByVal rqst As String, ByVal params() As OleDbParameter) As OleDbCommand
        Dim param As OleDbParameter
        Dim commande As New OleDbCommand
        commande.CommandText = rqst
        For Each param In params
            commande.Parameters.Add(param)
        Next
        commande.Connection = con
        Return commande
    End Function
    Public Sub executeRequete(ByVal rqst As String, ByVal params() As OleDbParameter, ByRef myReader As OleDbDataReader)
        Dim commande As New OleDbCommand
        commande = CreerCommande(rqst, params)
        Try
            ouvrir_con()
            myReader = commande.ExecuteReader
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Erreur")
        Finally
            fermer_con()
        End Try
    End Sub
    Public Sub executeRequete(ByVal rqst As String, ByVal params() As OleDbParameter, ByRef adapter As OleDbDataAdapter)
        Dim commande As New OleDbCommand
        commande = CreerCommande(rqst, params)
        Try
            ouvrir_con()
            adapter = New OleDbDataAdapter(commande)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Erreur")
        Finally
            fermer_con()
        End Try
    End Sub
    Public Sub executeRequete(ByVal rqst As String, ByVal params() As OleDbParameter, ByRef table As DataTable)
        Dim adapter As New OleDbDataAdapter
        executeRequete(rqst, params, adapter)
        Try
            ouvrir_con()
            table = New DataTable
            adapter.Fill(table)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Erreur")
        Finally
            fermer_con()
        End Try
    End Sub
    Public Sub executeRequeteReader(ByVal rqst As String, ByVal params() As OleDbParameter, ByVal table As DataTable)
        Dim row As DataRow
        Try
            ouvrir_con()
            reader = executeRequete(rqst, params)
            While reader.Read
                row = table.NewRow
                For i As Integer = 0 To reader.FieldCount - 1
                    row(i) = reader.GetValue(i)
                Next
                table.Rows.Add(row)
            End While
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Erreur")
        Finally
            fermer_con()
        End Try
    End Sub
    Public Sub executeRequete(ByVal rqst As String, ByVal params() As OleDbParameter, ByVal dtSet As DataSet)
        Dim adapter As New OleDbDataAdapter
        executeRequete(rqst, params, adapter)
        Try
            ouvrir_con()
            adapter.Fill(dtSet)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Erreur")
        Finally
            fermer_con()
        End Try
    End Sub
    Public Function executeRequete(ByVal rqst As String, ByVal params() As OleDbParameter) As Object
        cmd = CreerCommande(rqst, params)
        Try
            ouvrir_con()
            Return cmd.ExecuteScalar
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Erreur")
            Return Nothing
        Finally
            fermer_con()
        End Try
    End Function
#End Region


    Public Function getTables(ByVal nomTable As String) As DataTable
        Return ds.Tables(nomTable)
    End Function
    Public Sub etablirRelation(ByVal nomRelation As String, ByVal nomTableParent As String, ByVal nomTableEnfant As String, ByVal nomColParent As String, ByVal nomColEnfant As String)
        Dim colP As New DataColumn
        Dim colE As New DataColumn
        colP = ds.Tables(nomTableParent).Columns(nomColParent)
        colE = ds.Tables(nomTableEnfant).Columns(nomColEnfant)
        ds.Relations.Add(New DataRelation(nomRelation, colP, colE))
    End Sub


End Class