[VB.Net]MySQL connection class [Register/List/Login] - The-One - Jun 13, 2011
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 accounts
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).
[img]http://k.min.us/jdxmsK.png[/img]
Code and Info:
The class file:
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
* - Apple - Jun 13, 2011
Not to thread jack here but you can do everything with this without the limitation that your library provides =P
|
