In this tutorial, it covers the basics of creating and dropping of MySQL Database with visual Basic. MySQL is a leading open source database management system, multi user and a multithreaded database management system. MySQL database is available on most important OS platforms. It is one part of the very popular LAMP platform. Linux, Apache, MySQL, MySQL, PHP. But in my case, I am using XAMPP.
To begin in this course, open visual basic and create a new project and name it as “ManageDb”. After this, we need to add objects to our form such as: groupbox, textbox, Button, Datagridview and a label. Then set up your object properties and arrange all objects like as shown below.
This time, we’re going to add functionality to our application. To do this, double click our form and add the following code:
This code will simply show all the database on the datagridview.
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'sets the query for shpwing all the tables and store to sql variable
Dim sql As String = "SHOW DATABASES"
Try
'open connection
con.Open()
'bind the connection and query
With cmd
.Connection = con
.CommandText = sql
End With
'Gets or sets a Transact-SQL statement or stored procedure used to select records in the data source.
da.SelectCommand = cmd
da.Fill(publictable)
'get the datasource from publictable and passed to datagridview
DataGridView1.DataSource = publictable
'dispose the dataadapter
da.Dispose()
Catch ex As Exception
End Try
da.Dispose()
End Sub
Next,here’s the code for selecting the specific database name and display it to textbox provided.
Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
txtdbname.Text = DataGridView1.CurrentRow.Cells(0).Value.ToString
End Sub
Then, we’re going to add functionality to our “create” button. To do this, add the following code:
Private Sub btncreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncreate.Click
Dim result As Integer
Dim sql As String = "CREATE DATABASE `" & txtdbname.Text & "`;"
Try
con.Open()
With cmd
.Connection = con
.CommandText = sql
'Executes a Transact-SQL statement against the connection and returns the number of rows affected.
result = cmd.ExecuteNonQuery
If result = 0 Then
MsgBox("No Database created!") Else
MsgBox("Database created Successfully!")
End If
End With
Catch ex As Exception
End Try
Call Form1_Load(sender, e)
End Sub
Next, add the following code for “Drop” button. And here’s the following code:
Private Sub btndropdb_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndropdb.Click
Dim result As Integer
Dim sql As String = "DROP DATABASE `" & txtdbname.Text & "`;"
Try
con.Open()
With cmd
.Connection = con
.CommandText = sql
result = cmd.ExecuteNonQuery
If result = 0 Then
MsgBox("No Database has been Dropped!") Else
MsgBox("Database Successfully dropped!")
End If
End With
Catch ex As Exception
End Try
Call Form1_Load(sender, e)
End Sub
And here’s all the codes for this tutorial:
Imports MySql.Data.MySqlClient
Public Class Form1
'Represents an SQL statement or stored procedure to execute against a data source.
Dim cmd As New MySqlCommand
Dim da As New MySqlDataAdapter
Public total As Integer
Dim publictable As New DataTable
'declare conn as connection and it will now a new connection because
'it is equal to Getconnection Function
Dim con As MySqlConnection = jokenconn()
Public Function jokenconn() As MySqlConnection
Return New MySqlConnection("server=localhost;user id=root;password=;database=")
End Function
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'sets the query for shpwing all the tables and store to sql variable
Dim sql As String = "SHOW DATABASES"
Try
'open connection
con.Open()
'bind the connection and query
With cmd
.Connection = con
.CommandText = sql
End With
'Gets or sets a Transact-SQL statement or stored procedure used to select records in the data source.
da.SelectCommand = cmd
da.Fill(publictable)
'get the datasource from publictable and passed to datagridview
DataGridView1.DataSource = publictable
'dispose the dataadapter
da.Dispose()
Catch ex As Exception
End Try
da.Dispose()
End Sub
Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
txtdbname.Text = DataGridView1.CurrentRow.Cells(0).Value.ToString
End Sub
Private Sub btndropdb_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndropdb.Click
Dim result As Integer
Dim sql As String = "DROP DATABASE `" & txtdbname.Text & "`;"
Try
con.Open()
With cmd
.Connection = con
.CommandText = sql
result = cmd.ExecuteNonQuery
If result = 0 Then
MsgBox("No Database has been Dropped!") Else
MsgBox("Database Successfully dropped!")
End If
End With
Catch ex As Exception
End Try
Call Form1_Load(sender, e)
End Sub
Private Sub btncreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncreate.Click
Dim result As Integer
Dim sql As String = "CREATE DATABASE `" & txtdbname.Text & "`;"
Try
con.Open()
With cmd
.Connection = con
.CommandText = sql
'Executes a Transact-SQL statement against the connection and returns the number of rows affected.
result = cmd.ExecuteNonQuery
If result = 0 Then
MsgBox("No Database created!") Else
MsgBox("Database created Successfully!")
End If
End With
Catch ex As Exception
End Try
Call Form1_Load(sender, e)
End Sub
End Class
This time, you can now test your application by pressing “F5”.
