Using MySQL Database with Visual Basic .NET 2010
Target: Target of this tutorial is to establish connection to
MySQLserver Database from Visual Basic .NET 2010.
MySQLserver Database from Visual Basic .NET 2010.
Assumption: I am assuming that you are familiar with the following
1. Creating Database, Table in MySQL
2. Adding data to MySQL database.
3. Creating projects, saving projects, writing codes in VB.net.
1. The MySQL Server
2. The MySQL .NET Connector
3. An IDE for DB Management. Such as SQLyog, PHPmyadmin etc. If you don’t have any, then you can download a free tool.
4. Finally, Visual Basic .NET 2010 [ 2008 will also work ]
Once you have the above things, install all of them.
The Database: For the sake of simplicity, create a database with your IDE or whatever the way you like as “my_test_projects” and a table in it with the name “testing_mysql_vb” with the following structure.
Field Name
|
Data Type
|
Primary
|
Auto_increment
|
id
|
int
|
yes
|
Yes
|
user_name
|
text
|
no
|
no
|
Ok, now open VB.net and create a new project and name it whatever you like and save it. Now do as the following.
Go to menu : Project > Add Reference
Here, select the “Browse” tab and locate the following location
C:\Program Files\MySQL\MySQL Connector Net 6.5.4\Assemblies\v2.0
Note: “C:\Program Files\” may change as per where you have installed MySQL Connector.
Ok, now select “MySQL.Data.dll” as shown in the above screenshot and click “Ok”.
This action will add MySQL Dot Net Connector with our project.
The Form: Design the form as follow,
The above form contains the following controls and settings.
Control
|
Name
|
Value
|
Description
|
Label
|
Label1
|
User Name
|
This is the label for the input field of User Name
|
Textbox
|
text_user_name
|
This will be used to have the user name to be stored
| |
Button
|
btn_save
|
Save
|
This button will save the inputted user name in DB
|
Listbox
|
list_users
|
This will list the saved user names when pulled out from DB
| |
Button
|
btn_get
|
Get Data
|
This button will pull out the user names from DB and will show then in the “list_users” list.
|
Now Double-Click on the form to bring the code editor.
Above everything, write this
Imports MySql.Data.MySqlClient
This will allow us to use various types, methods and classes of the Connector.
Now, just below Public Class your_class_name write the followings
Public dbconn As New MySqlConnection
Public sql As String
Public dbcomm As MySqlCommand
Public dbread As MySqlDataReader
Here we are declaring 4 public objects that we will use.
“dbconn” is the connection to the database.
“sql” is a string, which will actually hold our query.
“dbcomm” is the MySqlCommand that will execute the query.
“dbread” is the one which will hold those data that we will fetch using query from DB.
Ok, Now we are going to make the connection. We will open the connection to the DB in the “load” of our form which now named as “form1” by default. The code looks like the following
dbconn = New MySqlConnection("Data Source=localhost;user id=root;database=my_test_projects;")
Try
dbconn.Open()
Catch ex As Exception
MsgBox("Error in connection, please check Database and connection server.")
End Try
Here, look at the connection string which as follows
dbconn = New MySqlConnection("Data Source=localhost;user id=root;database=my_test_projects;")
Here, “Data Source” means where the database is stored. We have our database in our local machine, thus I have used “localhost”, if you have your DB at some other computer connected by LAN to your machine, you will put the IP address of that machine instead of “localhost”.
“user id” is your ID, you better know what you have used while installing the MySQL Server.
“database” is the name of the database on which we will be working on.
Note: If you have also set a password for your DB Server then you will have put that in your connection string. Then the connection string will look like this
dbconn = New MySqlConnection("Data Source=localhost ; user id=root ; password=your_password_here ; database=my_test_projects;")
After that, in a Try..Catch block, we have opened the connection as
dbconn.Open()
This will open up the connection, if failed, then the message of the catch block will be showed.
MsgBox("Error in connection, please check Database and connection server.")
Run your program to see if you get the Error Message, if don’t then lets go ahead.
Double click the “btn_save” button to write the following codes in its click event.
sql = "INSERT INTO testing_mysql_vb(id,user_name) VALUES(NULL,'" & text_user_name.Text & "')"
Try
dbcomm = New MySqlCommand(sql, dbconn)
dbread = dbcomm.ExecuteReader()
dbread.Close()
Catch ex As Exception
MsgBox("Error in saving to Database. Error is :" & ex.Message)
dbread.Close()
Exit Sub
End Try
MsgBox("The User Name was saved.")
text_user_name.Text = ""
First of all we are writing our SQL Query String as
sql = "INSERT INTO testing_mysql_vb(id,user_name) VALUES(NULL,'" & text_user_name.Text & "')"
This query will insert a “NULL” value for the “id” and the TEXT of the text field text_user_name as the value for the “user_name” field in the table testing_mysql_vb. Well, not now, this is just a string. First we have assigned this sql and our opened connection as a MySQL Command with the following line
dbcomm = New MySqlCommand(sql, dbconn)
Then we have executed the command by
dbread = dbcomm.ExecuteReader()
The “dbread” has nothing to do here as we are inserting data, not fetching. However we will still have to close this by
dbread.Close()
We have kept these lines again in a Try…Catch block, where in the Catch block we showed a message regarding that the data insertion failed and closed the “dbread” and also got out of he sub with “End Sub”. After the Try…Catch block we have plotted another message regarding that the data has been successfully inserted and we have also cleared the text of the “text_user_name” textbox.
So, now you can run your program and try putting something in the “text_user_name” textbox and then clicking on “Save” to see which message appears. If the msgBox("Error in saving to Database. Error is :" & ex.Message) appears, this means the data wasn’t saved, Else if the MsgBox("The User Name was saved.") appears, this means the data has been saved successfully.
To get the saved names back from the DB, double click the “btn_get” button and write the following codes in it
list_users .Items .Clear
sql = "SELECT * FROM testing_mysql_vb"
Try
dbcomm = New MySqlCommand(sql, dbconn)
dbread = dbcomm.ExecuteReader()
While dbread.Read
list_users.Items.Add(dbread("user_name"))
End While
dbread.Close()
Catch ex As Exception
MsgBox("Error in collecting data from Database. Error is :" & ex.Message)
dbread.Close()
Exit Sub
End Try
First of all we are clearing the list_us to list the names that will be pulled out from the DB
list_users .Items .Clear
Then we have written the SQL Query string to pull out everything from the “testing_mysql_db” table. Then we started a Try…Catch block to start processing the query.
dbcomm = New MySqlCommand(sql, dbconn)
dbread = dbcomm.ExecuteReader()
Description of the above two lines are same as it was in the “btn_save” event.
But after that, we have started a while loop as
While dbread.Read
list_users.Items.Add(dbread("user_name"))
End While
Here we made an infinite loop which will run until our reader “dbread” has data. In each loop we add one user name from “dbread” to our list “list_users”.
After that, we have closed the “dbread” in the Catch block of this Try…Catch block we have showed a message regarding the data reading process’s failure though we don’t think that this will come up if you have done what you have been instructed to.
So now, run your program again and click the “Get Data” button and the list on its left will be filled up with the names you have inserted earlier.
And that’s it. The Full code is as follows,
Imports MySql.Data.MySqlClient
Public Class Form1
Public dbconn As New MySqlConnection
Public sql As String
Public dbcomm As MySqlCommand
Public dbread As MySqlDataReader
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
dbconn = New MySqlConnection("Data Source=localhost;user id=root;database=my_test_projects;")
Try
dbconn.Open()
Catch ex As Exception
MsgBox("Error in connection, please check Database and connection server.")
End Try
End Sub
Private Sub btn_save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_save.Click
sql = "INSERT INTO testing_mysql_vb(id,user_name) VALUES(NULL,'" & text_user_name.Text & "')"
Try
dbcomm = New MySqlCommand(sql, dbconn)
dbread = dbcomm.ExecuteReader()
dbread.Close()
Catch ex As Exception
MsgBox("Error in saving to Database. Error is :" & ex.Message)
dbread.Close()
Exit Sub
End Try
MsgBox("The User Name was saved.")
text_user_name.Text = ""
End Sub
Private Sub btn_get_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_get.Click
list_users .Items .Clear
sql = "SELECT * FROM testing_mysql_vb"
Try
dbcomm = New MySqlCommand(sql, dbconn)
dbread = dbcomm.ExecuteReader()
While dbread.Read
list_users.Items.Add(dbread("user_name"))
End While
dbread.Close()
Catch ex As Exception
MsgBox("Error in collecting data from Database. Error is :" & ex.Message)
dbread.Close()
Exit Sub
End Try
End Sub
End Class
資料來源:http://tanmayonrun.blogspot.tw/2013/01/using-mysql-database-with-visual-basic.html
