This tutorial is all about Setting Up the Database Connection Between MySQL and Visual Basic.
In this tutorial, I will teach you how to set up the database connection in the project. I’m going to use a module for setting up the connection of the database, declaring classes, variables and creating a method for “saving, retrieving, updating and deleting” the data in the MySQL Database. So that, it will reduce the bunch of codes and it will lessen your work.
I’m going to use the Visual Studio 2008 in creating this project.
Let’s begin:
Open the Visual Studio 2008 and create a new Windows Form Application. After that, you have to add the MySQL.Data.dll for your references so that, it won’t have encounter any errors when you are dealing with MySQL.
Now, go to the solution explorer and right click the file. After that, hit the “add reference” and the reference form will be pop-up. Go to browse panel, locate the MySQL.Data.dll and hit ok.
After adding the reference, you have to create a Module. To do that, go to the solution explorer and right click the file. Hit the “add” and select the “Module” to create a new item. After that, the form of “add new item” will appear and name the Module “dbconnection”. Then, hit add.
Now, in the Module, set a connection of the database, declare all the classes, variables and create a method in saving, retrieving, updating and deleting data in the database.
[vbnet]
‘SET YOUR IMPORTS
Imports MySql.Data.MySqlClient
Module dbconnection
‘SET UP THE DATABASE CONNECTION
Private Function mysqlcon() As MySqlConnection
Return New MySqlConnection(“server=localhost; user id=root; database=dbinformation”)
End Function
Public con As MySqlConnection = mysqlcon()
‘A SET OF COMMAND IN MYSQL
Dim cmd As New MySqlCommand
‘SET A CLASS THAT SERVE THE BRIDGE BETWEEN A DATASET AND DATABASE FOR SAVING AND RETRIEVING DATA.
Dim da As New MySqlDataAdapter
Private dt As New DataTable
Public query As String
Private result As Integer
#Region “CRUD”
‘A SUB PRUCEDURE FOR INSERTING DATA IN THE DATABASE
Public Sub mysqlCreate(ByVal query As String)
Try
‘OPENING THE CONNECTION
con.Open()
‘SET YOUR COMMANDS TO PROVIDE A TEXT-BASE INTERFACE INTO THE MYSQL DATABASE SERVER.
‘AND ONCE IT’S CONNECTED, YOU CAN MAKE QUERY OR MANY OTHER OPERATION.
With cmd
.Connection = con
.CommandText = query
End With
‘IT EXECUTE THE DATA
result = cmd.ExecuteNonQuery
‘IT NOTIFY IF THE DATA HAS EXECUTED OR NOT.
If result > 0 Then
MsgBox(“The data has been saved in the database.”)
Else
MsgBox(“The data failed to save in the database.”)
End If
‘CLOSING THE CONNECTION
con.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
‘A SUB PRUCEDURE FOR INSERTING,UPDATING AND DELETE DATA IN THE DATABASE AND IT NO MESSAGE WILL BE POPUP
Public Sub mysqlCUDNoMessage(ByVal query As String)
Try
‘OPENING THE CONNECTION
con.Open()
‘SET YOUR COMMANDS TO PROVIDE A TEXT-BASE INTERFACE INTO THE MYSQL DATABASE SERVER.
‘AND ONCE IT’S CONNECTED, YOU CAN MAKE QUERY OR MANY OTHER OPERATION.
With cmd
.Connection = con
.CommandText = query
End With
‘IT EXECUTE THE DATA
result = cmd.ExecuteNonQuery
‘CLOSING THE CONNECTION
con.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
‘A SUB PRUCEDURE FOR RETRIEVING DATA IN THE DATABASE
Public Sub mysqlRetrieve(ByVal query As String, ByVal obj As Object)
Try
‘OPENING THE CONNECTION
con.Open()
‘SET YOUR COMMANDS TO PROVIDE A TEXT-BASE INTERFACE INTO THE MYSQL DATABASE SERVER.
‘AND ONCE IT’S CONNECTED, YOU CAN MAKE QUERY OR MANY OTHER OPERATION.
With cmd
.Connection = con
.CommandText = query
End With
da = New MySqlDataAdapter
‘SET THIS STORED PROCEDURE TO SELECT THE RECORD IN THE DATASOURCE
da.SelectCommand = cmd
dt = New DataTable
da.Fill(dt)
obj.datasource = dt
‘CLOSING THE CONNECTION
con.Close()
da.Dispose()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
‘A SUB PRUCEDURE FOR UPDATING DATA IN THE DATABASE
Public Sub mysqlUpdate(ByVal query As String)
Try
‘OPENING THE CONNECTION
con.Open()
‘SET YOUR COMMANDS TO PROVIDE A TEXT-BASE INTERFACE INTO THE MYSQL DATABASE SERVER.
‘AND ONCE IT’S CONNECTED, YOU CAN MAKE QUERY OR MANY OTHER OPERATION.
With cmd
.Connection = con
.CommandText = query
End With
‘IT EXECUTE THE DATA
result = cmd.ExecuteNonQuery
‘IT NOTIFY IF THE DATA HAS EXECUTED OR NOT.
If result > 0 Then
MsgBox(“The data has been updated in the database.”)
Else
MsgBox(“The data failed to update in the database.”)
End If
‘CLOSING THE CONNECTION
con.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
‘A SUB PRUCEDURE FOR DELETING DATA IN THE DATABASE
Public Sub mysqlDelete(ByVal query As String)
Try
‘OPENING THE CONNECTION
con.Open()
‘SET YOUR COMMANDS TO PROVIDE A TEXT-BASE INTERFACE INTO THE MYSQL DATABASE SERVER.
‘AND ONCE IT’S CONNECTED, YOU CAN MAKE QUERY OR MANY OTHER OPERATION.
With cmd
.Connection = con
.CommandText = query
End With
‘IT EXECUTE THE DATA
result = cmd.ExecuteNonQuery
‘IT NOTIFY IF THE DATA HAS EXECUTED OR NOT.
If result > 0 Then
MsgBox(“The data has been deleted in the database.”)
Else
MsgBox(“The data failed to delete in the database.”)
End If
‘CLOSING THE CONNECTION
con.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
#End Region
End Module
[/vbnet]
Readers might read also: