Part IV: Improving of Code using Modules in Visual Basic.Net

Submitted by joken on Thursday, September 5, 2013 - 11:57.
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.
  1. Imports MySql.Data.MySqlClient
  2. Module dbcon
  3.  
  4.     'we declare con as our mysqlconnection because we wanted this connection will be available in all parts of our project
  5.     Public con As MySqlConnection = jokenconn()
  6.  
  7.     'This time our mysqlconnection is place inside the a function name jokenconn()
  8.     'the purpose of of this is that it will always return the mysqlconnection as new mysqlconnection wherever we want to use this connection
  9.     Public Function jokenconn() As MySqlConnection
  10.         Return New MySqlConnection("server=localhost;user id=root;password=;database=test")
  11.     End Function
  12.  
  13. 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.
  1. Imports MySql.Data.MySqlClient
  2. Module dbcrud
  3.     Dim result As Integer
  4.     Dim cmd As New MySqlCommand
  5.     Public con As MySqlConnection = jokenconn()
  6.     Public Sub jokeninsert(ByVal sql As String)
  7.         Try
  8.             con.Open()
  9.             With cmd
  10.                 .Connection = con
  11.                 .CommandText = sql
  12.                 result = cmd.ExecuteNonQuery
  13.                 If result = 0 Then
  14.                     MsgBox("Data has been Inserted!")
  15.  
  16.                 Else
  17.                     MsgBox("Successfully saved!")
  18.  
  19.                 End If
  20.             End With
  21.         Catch ex As Exception
  22.             MsgBox(ex.Message)
  23.  
  24.         End Try
  25.         con.Close()
  26.  
  27.     End Sub
  28.  
  29.     Public Sub jokenupdate(ByVal sql As String)
  30.         Try
  31.             con.Open()
  32.             With cmd
  33.                 .Connection = con
  34.                 .CommandText = sql
  35.                 result = cmd.ExecuteNonQuery
  36.                 If result = 0 Then
  37.                     MsgBox("No Data has been Updated!")
  38.  
  39.                 Else
  40.  
  41.                     MsgBox("New Data is updated succesfully!")
  42.  
  43.                 End If
  44.             End With
  45.         Catch ex As Exception
  46.             MsgBox(ex.Message)
  47.  
  48.  
  49.         End Try
  50.         con.Close()
  51.  
  52.     End Sub
  53.  
  54.     Public Sub jokendelete(ByVal sql As String)
  55.         Try
  56.             con.Open()
  57.             With cmd
  58.                 .Connection = con
  59.                 .CommandText = sql
  60.                 result = cmd.ExecuteNonQuery
  61.                 If result = 0 Then
  62.                     MsgBox("No Data has been deleted!")
  63.  
  64.                 Else
  65.                     MsgBox("Data is deleted succesfully!")
  66.  
  67.                 End If
  68.             End With
  69.         Catch ex As Exception
  70.             MsgBox(ex.Message)
  71.  
  72.         End Try
  73.         con.Close()
  74.  
  75.     End Sub
  76.  
  77. End Module
Code for dbselect module.
  1. Imports MySql.Data.MySqlClient
  2. Module dbselect
  3.     Dim cmd As New MySqlCommand
  4.     Dim da As New MySqlDataAdapter
  5.     Public con As MySqlConnection = jokenconn()
  6.  
  7.     Public Sub findThis(ByVal sql As String)
  8.         Try
  9.             con.Open()
  10.             With cmd
  11.                 .Connection = con
  12.                 .CommandText = sql
  13.             End With
  14.  
  15.         Catch ex As Exception
  16.             MsgBox(ex.Message)
  17.  
  18.         End Try
  19.         con.Close()
  20.         da.Dispose()
  21.     End Sub
  22.     Public Sub filltable(ByVal dtgrd As Object)
  23.         Dim publictable As New DataTable
  24.         Try
  25.             da.SelectCommand = cmd
  26.             da.Fill(publictable)
  27.             dtgrd.DataSource = publictable
  28.             dtgrd.Columns(0).Visible = False
  29.             ' dtgrd.Columns(1).Visible = False
  30.             da.Dispose()
  31.  
  32.         Catch ex As Exception
  33.             MsgBox(ex.Message)
  34.  
  35.         End Try
  36.  
  37.     End Sub
  38.  
  39. 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.
  1.   Private Sub btncreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncreate.Click
  2.    
  3.         jokeninsert("INSERT INTO `test`.`users` (`user_id`,  (`user_id`, `fullname`, `username`, `password`) " & _
  4.                     "VALUES (NULL, '" & txtfullname.Text & "', '" & txtusername.Text & "', '" & txtpassword.Text & "');")
  5.  
  6.     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.
  1.           findThis("Select * from users")
  2.           filltable(dtguser)
and for “Update User” here’s the code.
  1.   Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click
  2.         jokenupdate("UPDATE  `test`.`users` SET  `fullname` =  '" & txtfullname.Text & "',`username` =  '" & txtusername.Text & "',`password` =  '" & txtpassword.Text & "' WHERE  `users`.`user_id` =" & Val(lblid.Text) & ";")
  3.         Call btnload_Click(sender, e)
  4.     End Sub
And for “Delete User” here’s the code.
  1. Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
  2.  
  3.         jokendelete("DELETE FROM `test`.`users` WHERE `users`.`user_id` =" & Val(lblid.Text) & ";")
  4.         Call btnload_Click(sender, e)
  5.  
  6.     End Sub
And now this is our code for “Manage_user” form.
  1. 'Description: This program can able to insert and read user form the MySQL Database using Visual Basic.
  2. 'Author:      Joken Villanueva
  3. 'Date Created:
  4.  
  5. Imports MySql.Data.MySqlClient
  6. Public Class Manage_user
  7.  
  8.     Private Sub btncreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncreate.Click
  9.    
  10.         jokeninsert("INSERT INTO `test`.`users` (`user_id`, `fullname`, `username`, `password`) " & _
  11.                     "VALUES (NULL, '" & txtfullname.Text & "', '" & txtusername.Text & "', '" & txtpassword.Text & "');")
  12.  
  13.     End Sub
  14.  
  15.     Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click
  16.         jokenupdate("UPDATE  `test`.`users` SET  `fullname` =  '" & txtfullname.Text & "',`username` =  '" & txtusername.Text & "',`password` =  '" & txtpassword.Text & "' WHERE  `users`.`user_id` =" & Val(lblid.Text) & ";")
  17.         Call btnload_Click(sender, e)
  18.     End Sub
  19.  
  20.     Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
  21.  
  22.         jokendelete("DELETE FROM `test`.`users` WHERE `users`.`user_id` =" & Val(lblid.Text) & ";")
  23.         Call btnload_Click(sender, e)
  24.  
  25.     End Sub
  26.  
  27.     Private Sub btnload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnload.Click
  28.         findThis("Select * from users")
  29.         filltable(dtguser)
  30.  
  31.     End Sub
  32.  
  33.     Private Sub dtguser_CellMouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles dtguser.CellMouseClick
  34.         'this code will simply pass the value from the specific row selected by the user
  35.         lblid.Text = dtguser.CurrentRow.Cells(0).Value
  36.         txtfullname.Text = dtguser.CurrentRow.Cells(1).Value
  37.         txtusername.Text = dtguser.CurrentRow.Cells(2).Value
  38.         txtpassword.Text = dtguser.CurrentRow.Cells(3).Value
  39.  
  40.     End Sub
  41.  
  42. 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.
Tags
visual basic.net
MYSQL
Modules
Refactoring

Add new comment

About text formats
  • 39 views