Voila ma première source, c'est une classe d'accès au base de données OLEDB, elle comporte les méthodes d'ordre général "mise à jour","création des tables","remplissage" et "création des relations".. en utilisant les deux mode de connection, surtout le mode déconnecté... je serai très heureux que qqun puisse se servir de cette classe.
toutes vos remarques ou critiques instructives seront les bienvenues
Source / Exemple :
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
Conclusion :
Ce code est une collection assez complète de méthodes pour gérer l'accès à la BD.
Vous n'êtes pas encore membre ?
inscrivez-vous, c'est gratuit et ça prend moins d'une minute !
Les membres obtiennent plus de réponses que les utilisateurs anonymes.
Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.
Le fait d'être membre vous permet d'avoir des options supplémentaires.