This tutorial is a continuation of
Part III: Update and Delete Specific Data in MySQL Database using Visual Basic. Net. But this time we're going to focus on how we can improve the design of our existing code using Code Refactoring. Refactoring is the process of changing a software system in such a way that it hasn't altered the external behavior of the code, yet improves its internal structure.
To start with, we need to open our last project from part three the project is so called “VBMYSQL”. Then after this on the solution explorer right click the project name->Select Add->New Item. In the Add New Item form Select “Module” and name it as “dbcon” and finally click “Add” button and this look like as shown below.
And we're going to do it again for two times and name it as “dbselect” and “dbcrud”. After clicking the Add button, it will automatically show in the Solution Explorer and double click this “dbcon” module so that we can add our code to set for Database connection. And this bit of code.
Imports MySql.Data.MySqlClient
Module dbcon
'we declare con as our mysqlconnection because we wanted this connection will be available in all parts of our project
Public con As MySqlConnection = jokenconn()
'This time our mysqlconnection is place inside the a function name jokenconn()
'the purpose of of this is that it will always return the mysqlconnection as new mysqlconnection wherever we want to use this connection
Public Function jokenconn() As MySqlConnection
Return New MySqlConnection("server=localhost;user id=root;password=;database=test")
End Function
End Module
Next we’re now going to set also our module for Creating, Updating and deleting of Data. To do this just simply double click the “dbcrud” module and add this code.
Imports MySql.Data.MySqlClient
Module dbcrud
Dim result As Integer
Dim cmd As New MySqlCommand
Public con As MySqlConnection = jokenconn()
Public Sub jokeninsert(ByVal sql As String)
Try
con.Open()
With cmd
.Connection = con
.CommandText = sql
result = cmd.ExecuteNonQuery
If result = 0 Then
MsgBox("Data has been Inserted!")
Else
End If
End With
Catch ex As Exception
End Try
End Sub
Public Sub jokenupdate(ByVal sql As String)
Try
con.Open()
With cmd
.Connection = con
.CommandText = sql
result = cmd.ExecuteNonQuery
If result = 0 Then
MsgBox("No Data has been Updated!")
Else
MsgBox("New Data is updated succesfully!")
End If
End With
Catch ex As Exception
End Try
End Sub
Public Sub jokendelete(ByVal sql As String)
Try
con.Open()
With cmd
.Connection = con
.CommandText = sql
result = cmd.ExecuteNonQuery
If result = 0 Then
MsgBox("No Data has been deleted!")
Else
MsgBox("Data is deleted succesfully!")
End If
End With
Catch ex As Exception
End Try
End Sub
End Module
Code for dbselect module.
Imports MySql.Data.MySqlClient
Module dbselect
Dim cmd As New MySqlCommand
Dim da As New MySqlDataAdapter
Public con As MySqlConnection = jokenconn()
Public Sub findThis(ByVal sql As String)
Try
con.Open()
With cmd
.Connection = con
.CommandText = sql
End With
Catch ex As Exception
End Try
da.Dispose()
End Sub
Public Sub filltable(ByVal dtgrd As Object)
Dim publictable As New DataTable
Try
da.SelectCommand = cmd
da.Fill(publictable)
dtgrd.DataSource = publictable
dtgrd.Columns(0).Visible = False
' dtgrd.Columns(1).Visible = False
da.Dispose()
Catch ex As Exception
End Try
End Sub
End Module
After setting the three modules were now going to use this module. Let's start with “Create User” button to do this double click it deletes all the codes in there except the “INSERT INTO” statement because this will be using it for our code.
And the “Create User” button will now look like as shown below.
Private Sub btncreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncreate.Click
jokeninsert("INSERT INTO `test`.`users` (`user_id`, (`user_id`, `fullname`, `username`, `password`) " & _
"VALUES (NULL, '" & txtfullname.Text & "', '" & txtusername.Text & "', '" & txtpassword.Text & "');")
End Sub
And you try this now by running your program. Then after this we will do now our Reading of Data to do this just add this code.
findThis("Select * from users")
filltable(dtguser)
and for “Update User” here’s the code.
Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click
jokenupdate
("UPDATE `test`.`users` SET `fullname` = '" & txtfullname
.Text & "',`username` = '" & txtusername
.Text & "',`password` = '" & txtpassword
.Text & "' WHERE `users`.`user_id` =" & Val(lblid
.Text) & ";") Call btnload_Click(sender, e)
End Sub
And for “Delete User” here’s the code.
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
jokendelete
("DELETE FROM `test`.`users` WHERE `users`.`user_id` =" & Val(lblid
.Text) & ";") Call btnload_Click(sender, e)
End Sub
And now this is our code for “Manage_user” form.
'Description: This program can able to insert and read user form the MySQL Database using Visual Basic.
'Author: Joken Villanueva
'Date Created:
Imports MySql.Data.MySqlClient
Public Class Manage_user
Private Sub btncreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncreate.Click
jokeninsert("INSERT INTO `test`.`users` (`user_id`, `fullname`, `username`, `password`) " & _
"VALUES (NULL, '" & txtfullname.Text & "', '" & txtusername.Text & "', '" & txtpassword.Text & "');")
End Sub
Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click
jokenupdate
("UPDATE `test`.`users` SET `fullname` = '" & txtfullname
.Text & "',`username` = '" & txtusername
.Text & "',`password` = '" & txtpassword
.Text & "' WHERE `users`.`user_id` =" & Val(lblid
.Text) & ";") Call btnload_Click(sender, e)
End Sub
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
jokendelete
("DELETE FROM `test`.`users` WHERE `users`.`user_id` =" & Val(lblid
.Text) & ";") Call btnload_Click(sender, e)
End Sub
Private Sub btnload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnload.Click
findThis("Select * from users")
filltable(dtguser)
End Sub
Private Sub dtguser_CellMouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles dtguser.CellMouseClick
'this code will simply pass the value from the specific row selected by the user
lblid.Text = dtguser.CurrentRow.Cells(0).Value
txtfullname.Text = dtguser.CurrentRow.Cells(1).Value
txtusername.Text = dtguser.CurrentRow.Cells(2).Value
txtpassword.Text = dtguser.CurrentRow.Cells(3).Value
End Sub
End Class
As we can observe there is really big different of using modules compare to use of traditional coding style. With the help of this module we can now able to do our coding 10x faster compare before. Meaning with the use of this technique we can create a system very soon and with less of bugs. Hope it can help you to improve your productivity in programming using visual Basic Modules.