Partial Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Try base.AjouterUser("toto", "password2toto", "Quel age avait Rimbaud ?", "28 ans") base.AjouterUser("fred", "password2fred", "Quel est le double du quart de quatre ?", "deux") Catch ex As Exception MessageBox.Show(ex.Message) End Try End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Try If base.TesterUserExistence("renfield") Then MessageBox.Show("L'utilisateur 'renfield' existe bel et bien.") Else MessageBox.Show("L'utilisateur 'renfield' n'existe pas.") End If If base.TesterUserExistence("fred") Then MessageBox.Show("L'utilisateur 'fred' existe bel et bien.") Else MessageBox.Show("L'utilisateur 'fred' n'existe pas.") End If MessageBox.Show("Le mot de passe de toto est : " & base.LirePassword("toto")) Catch ex As Exception MessageBox.Show(ex.Message) End Try End Sub Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Dim Question As String Dim Reponse As String Try If base.LireQuestionReponse("fred", Question, Reponse) Then MessageBox.Show("La question de fred est '" & Question & "'" & Environment.NewLine & _ "La réponse donnée est '" & Reponse & "'.") End If If base.LireQuestionReponse("renfield", Question, Reponse) Then MessageBox.Show("La question de fred est '" & Question & "'" & Environment.NewLine & _ "La réponse donnée est '" & Reponse & "'.") End If Catch ex As Exception MessageBox.Show(ex.Message) End Try End Sub End Class
Imports System.IO Imports System.Data Imports System.Data.OleDb Imports ADOX Module base Public ReadOnly Property Chemin() As String Get Return Path.Combine(Application.StartupPath, "Base.mdb") End Get End Property Private Function EtablirConnection() As OleDbConnection Dim Cnx As OleDbConnection Dim Cat As Catalog Dim Cmd As OleDbCommand Dim baseACreer As Boolean = False Dim echec As Boolean = False Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;Data Source=" & Chemin & ";" '# Creation du fichier de base de données Try If Not File.Exists(Chemin) Then baseACreer = True Cat = New Catalog '# Ajouter une reference COM à : Microsoft ADO Ext Cat.Create(ConnectionString) End If Catch ex As Exception '# On ne doit pas afficher de MessageBox, elle doit remonter l'info Throw New Exception("Erreur de création de la base de donnée" & Environment.NewLine & ex.Message) End Try '# Ouverture de la connection proprement dite Try Cnx = New OleDbConnection Cnx.ConnectionString = ConnectionString Cnx.Open() '# On a créé le fichier mdb, rest a ajouter la table USERS If baseACreer Then Cmd = New OleDbCommand() Cmd.Connection = Cnx Cmd.CommandType = CommandType.Text Cmd.CommandText = "CREATE TABLE USERS (LOGIN CHAR(8) NOT NULL PRIMARY KEY," & _ "MDP CHAR(16) NOT NULL," & _ "QUESTION VARCHAR(64) NOT NULL," & _ "REPONSE CHAR(8) NOT NULL)" Cmd.ExecuteNonQuery() End If Catch ex As Exception echec = True Throw New Exception("Erreur de création de la table" & Environment.NewLine & ex.Message) Finally If echec Then If Not Nothing Is Cnx Then If Cnx.State = ConnectionState.Open Then Cnx.Close() End If Cnx.Dispose() End If End If End Try Return Cnx End Function Public Function AjouterUser(ByVal login As String, ByVal mdp As String, ByVal question As String, ByVal rep As String) As Boolean Dim Cnx As OleDbConnection = Nothing Dim Cmd As OleDbCommand Try If TesterUserExistence(login) Then Throw New Exception("L'utilisateur " & login & " existe déjà") End If Cnx = EtablirConnection() 'Indiquer le type de commande Cmd = New OleDbCommand Cmd.CommandType = CommandType.Text '# On utilise des requètes parametrées, pas de concaténation aléatoires (risque d'injection SQL) Cmd.CommandText = "INSERT INTO USERS (LOGIN, MDP, QUESTION, REPONSE) VALUES (@login, @mdp, @question, @rep)" Cmd.Parameters.AddWithValue("@login", login) Cmd.Parameters.AddWithValue("@mdp", mdp) Cmd.Parameters.AddWithValue("@question", question) Cmd.Parameters.AddWithValue("@rep", rep) Cmd.Connection = Cnx Cmd.ExecuteNonQuery() Return True Catch ex As Exception Throw New Exception("Erreur dans l'ajout d'un USERS" & Environment.NewLine & ex.Message) Finally If Not Nothing Is Cnx Then Cnx.Close() End If End Try Return True End Function Private Function LireDataRow(ByVal Cmd As OleDbCommand) As DataRow Dim Cnx As OleDbConnection = Nothing Dim dta As OleDbDataAdapter Dim dts As DataSet If Nothing Is Cmd Then Throw New NullReferenceException("Cmd doit être renseigné.") End If Try Cnx = EtablirConnection() Cmd.CommandType = CommandType.Text Cmd.Connection = Cnx dta = New OleDbDataAdapter(Cmd) dts = New DataSet dta.Fill(dts) If dts.Tables(0).Rows.Count = 1 Then Return dts.Tables(0).Rows(0) Else Return Nothing End If Catch ex As Exception Throw New Exception("Erreur lors de la lecture de la table USERS" & Environment.NewLine & ex.Message) Finally '# Un peu de nettoyage... If Not Nothing Is Cnx Then Cnx.Close() End If End Try End Function 'Vérifie si le login existe ou pas Public Function TesterUserExistence(ByVal login As String) As Boolean Dim Cmd As OleDbCommand Dim dtr As DataRow Try Cmd = New OleDbCommand("SELECT COUNT(0) FROM USERS WHERE LOGIN=@login") Cmd.Parameters.AddWithValue("@login", login) dtr = LireDataRow(Cmd) '# Si le Count renvoie 1, on quitte Return CType(dtr.Item(0), Integer) = 1 Catch ex As Exception Throw New Exception("Erreur dans l'ajout d'un USERS" & Environment.NewLine & ex.Message) End Try End Function 'Rempli le combobox de la question et retourne la reponse Public Function LireQuestionReponse(ByVal login As String, ByRef question As String, ByRef reponse As String) As Boolean Dim Cmd As OleDbCommand Dim dtr As DataRow Try question = String.Empty reponse = String.Empty If TesterUserExistence(login) Then Cmd = New OleDbCommand("SELECT QUESTION, REPONSE FROM USERS WHERE LOGIN=@login") Cmd.Parameters.AddWithValue("@login", login) dtr = LireDataRow(Cmd) question = dtr.Item("QUESTION").ToString() reponse = dtr.Item("REPONSE").ToString() Return True End If Catch ex As Exception Throw New Exception("Erreur dans la lecture du mot de passe" & Environment.NewLine & ex.Message) End Try End Function '# Renvoie le mot de passe '#=> Pas courant, on compare en général le hash du mot de passe avec le hash stocké dans la base '# La base ne devrait pas stocker le mot de passe en clair. Public Function LirePassword(ByVal login As String) As String Dim Cmd As OleDbCommand Dim dtr As DataRow Try If TesterUserExistence(login) Then Cmd = New OleDbCommand("SELECT MDP FROM USERS WHERE LOGIN=@login") Cmd.Parameters.AddWithValue("@login", login) dtr = LireDataRow(Cmd) '# Si le Count renvoie 1, on quitte Return dtr.Item(0).ToString() End If Catch ex As Exception Throw New Exception("Erreur dans la lecture du mot de passe" & Environment.NewLine & ex.Message) End Try Return Nothing End Function End Module
Dim Question As String Dim Reponse As String Try If base.LireQuestionReponse("fred", Question, Reponse) Then MessageBox.Show("La question de fred est '" & Question & "'" & Environment.NewLine & _ "La réponse donnée est '" & Reponse & "'.") End If
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionTry If TesterUserExistence(login) Then Throw New Exception("L'utilisateur " & login & " existe déjà") endif Cnx = EtablirConnection() 'Indiquer le type de commande Cmd = New OleDbCommand Cmd.CommandType = CommandType.Text '# On utilise des requètes parametrées, pas de concaténation aléatoires (risque d'injection SQL) Cmd.CommandText = "INSERT INTO USERS (LOGIN, MDP, QUESTION, REPONSE) VALUES (@login, @mdp, @question, @rep)" Cmd.Parameters.AddWithValue("@login", login) Cmd.Parameters.AddWithValue("@mdp", mdp) Cmd.Parameters.AddWithValue("@question", question) Cmd.Parameters.AddWithValue("@rep", rep) MessageBox.Show(Cmd.CommandText) Cmd.Connection = Cnx Cmd.ExecuteNonQuery() Return True Catch ex As Exception Throw New Exception("Erreur dans l'ajout d'un USERS" & Environment.NewLine & ex.Message) Finally If Not Nothing Is Cnx Then Cnx.Close() End If End Try