[VB.Net] MySQL connection class [Register/List/Login]

[The-One]

Hey,

I've been working on this a few hours now. I remade my old MySQL connection class. You can do the following things:

• Login
  
• Register a new account
  
• List all the account
  

You can for example add a 'modify' account option or a 'delete' account option which UID count is already build in (meaning you should (never tested) never have two same UID's).




Code and Info:

The class file:

Spoiler (Click to View)

Code:

Imports MySql.Data.MySqlClient
Public Class MySQL_VB
    Private mysql_con As New MySqlConnection
    Private mysql_adapter As New MySqlDataAdapter
    Private mysql_query As String
    Private mysql_command As New MySqlCommand
    Private mysql_bool As Boolean = False
    Private mysql_count As Integer = 0

    Public Sub SetConnectionData(ByVal ip As String, ByVal username As String, ByVal pw As String, ByVal database As String)
        mysql_con.ConnectionString = "server=" & ip & ";" & "user id=" & username & ";" & "password=" & pw & ";" & "database=" & database
    End Sub

    Public Sub Login_(ByVal user As String, ByVal pass As String)
        Try
            mysql_con.Open()
        Catch ex As MySqlException
            MsgBox("Could not connect to the database...")
        End Try
        mysql_query = "SELECT * FROM Users WHERE Username ='" + Replace(user, " ", "") + "' AND Password='" & Replace(pass, " ", "") & "'"
        mysql_command.Connection = mysql_con
        mysql_command.CommandText = mysql_query

        mysql_adapter.SelectCommand = mysql_command
        Dim mysql_data As MySqlDataReader = mysql_command.ExecuteReader()
        If mysql_data.HasRows Then
            MsgBox("Logon sucessful!")
            mysql_con.Close()
            mysql_bool = True
        Else
            MsgBox("Error! Wrong username/password!")
            mysql_bool = False
        End If
    End Sub


    Public Sub Register_(ByVal user As String, ByVal pass As String, ByVal pass2 As String)
        If pass = pass2 Then
            Try
                mysql_con.Open()
            Catch ex As MySqlException
                MsgBox("No connection to the database!")
            End Try

            Count()

            Dim myAdapter As New MySqlDataAdapter
            Dim mysql_Command As New MySqlCommand
            mysql_Command.Connection = mysql_con
            mysql_Command.CommandText = "SELECT * FROM Users WHERE Username='" + user + "'"

            myAdapter.SelectCommand = mysql_Command
            Dim myData As MySqlDataReader
            myData = mysql_Command.ExecuteReader()

            If myData.HasRows = 0 Then
g:
                mysql_con.Close()
                mysql_con.Open()
                mysql_Command.CommandText = "SELECT * FROM Users WHERE Id='" & mysql_count.ToString & "'"

                myAdapter.SelectCommand = mysql_Command
                Dim myDataX As MySqlDataReader
                myDataX = mysql_Command.ExecuteReader()
                If myDataX.HasRows = 0 Then
                    mysql_con.Close()
                    mysql_con.Open()
                    Dim registerfinal As New MySqlDataAdapter
                    mysql_Command.CommandText = "INSERT INTO Users(Id, Username, Password, Date)" _
                     & "VALUES('" & mysql_count & "','" & user & "','" & pass & "','" & My.Computer.Clock.GmtTime & "')"
                    mysql_Command.ExecuteNonQuery()
                    MsgBox("The account with the name: " & user & " was created sucessfully!")
                    mysql_con.Close()
                Else
                    mysql_count += 1
                    GoTo g
                End If
            Else
                MsgBox("This username does already exist")
            End If
        Else
            MsgBox("The passwords did not match!")
        End If
    End Sub

    Public Sub UsersList_(ByVal l As ListView)
        Try
            mysql_con.Open()
        Catch ex As MySqlException
            MsgBox("Could not connect to the database...")
        End Try

        Count()

        Dim myAdapter As New MySqlDataAdapter
        Dim mysql_Command As New MySqlCommand
        mysql_Command.Connection = mysql_con
        mysql_Command.CommandText = "SELECT * FROM Users"

        myAdapter.SelectCommand = mysql_Command
        Dim myData As MySqlDataReader
        myData = mysql_Command.ExecuteReader()

        For i = 0 To mysql_count
            myData.Read()
            Dim lvi As New ListViewItem(New String() {myData.GetString(0), myData.GetString(1), myData.GetString(2), myData.GetString(3)})
            l.Items.Add(lvi)
        Next

        mysql_con.Close()
    End Sub

    Private Sub Count()
        Dim myAdapter As New MySqlDataAdapter
        Dim mysql_Command As New MySqlCommand
        mysql_Command.Connection = mysql_con
        mysql_Command.CommandText = "SELECT * FROM Users"

        myAdapter.SelectCommand = mysql_Command
        Dim myData As MySqlDataReader
        myData = mysql_Command.ExecuteReader()
        Dim L As Integer = -1
g:
        myData.Read()
        If L = myData.GetString(0) Then
        Else
            L = myData.GetString(0)
            GoTo g
        End If

        mysql_count = L
        myData.Close()
    End Sub
End Class

NOTE: to use this you must have MySql.Data.dll included as reference in your project

Usage is now very simple:

List the table:

Code:

Dim m As New MySQL_VB
        m.SetConnectionData("88.198.43.24", "username", "password", "testdba")
        m.UsersList_(ListView1)

Register a new account:

Code:

Dim m As New MySQL_VB
        m.SetConnectionData("88.198.43.24", "username", "password", "testdba")
        m.Register_(TextBox1.Text, TextBox2.Text, TextBox3.Text)

Check if a login exist or is valid:

Code:

Dim m As New MySQL_VB
        m.SetConnectionData("88.198.43.24", "username", "password", "testdba")
        m.Login_(TextBox6.Text, TextBox5.Text)

Downloads:

Whole project (as on the above image YOU MUST DOWNLOAD THE DATABASE FILE BELOW): http://www.mediafire.com/?yu25su89yo79rqb
MySQL.dll (already included on the project): http://www.mediafire.com/?1lk13m1bh8a98p3
Class file: http://www.mediafire.com/?jyu2lunh39jgjjl
Database file: http://www.mediafire.com/?mc8r9c0ob6xp77s