• We just launched and are currently in beta. Join us as we build and grow the community.

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

asadsumra99

Data Exfiltration Specialist
A
A Rep
0
0
0
Rep
0
A Vouches
0
0
0
Vouches
0
Posts
89
Likes
38
Bits
0
2 MONTHS
2 2 MONTHS OF SERVICE
LEVEL 1 400 XP
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.

m1.png


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

  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(

    )

  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

  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!"

    )

  16. Else
  17. MsgBox

    (

    "Successfully saved!"

    )

  19. End

    If
  20. End

    With
  21. Catch

    ex As

    Exception
  22. MsgBox

    (

    ex.

    Message

    )

  24. End

    Try
  25. con.

    Close

    (

    )

  27. End

    Sub

  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!"

    )

  39. Else

  41. MsgBox

    (

    "New Data is updated succesfully!"

    )

  43. End

    If
  44. End

    With
  45. Catch

    ex As

    Exception
  46. MsgBox

    (

    ex.

    Message

    )


  49. End

    Try
  50. con.

    Close

    (

    )

  52. End

    Sub

  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!"

    )

  64. Else
  65. MsgBox

    (

    "Data is deleted succesfully!"

    )

  67. End

    If
  68. End

    With
  69. Catch

    ex As

    Exception
  70. MsgBox

    (

    ex.

    Message

    )

  72. End

    Try
  73. con.

    Close

    (

    )

  75. End

    Sub

  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(

    )

  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

  15. Catch

    ex As

    Exception
  16. MsgBox

    (

    ex.

    Message

    )

  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

    (

    )

  32. Catch

    ex As

    Exception
  33. MsgBox

    (

    ex.

    Message

    )

  35. End

    Try

  37. End

    Sub

  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

  3. jokeninsert(

    "INSERT INTO `test`.`users` (`user_id`, (`user_id`, `fullname`, `username`, `password`) "

    &

    _
  4. "VALUES (NULL, '"

    &

    txtfullname.

    Text

    &

    "', '"

    &

    txtusername.

    Text

    &

    "', '"

    &

    txtpassword.

    Text

    &

    "');"

    )

  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

  3. jokendelete(

    "DELETE FROM `test`.`users` WHERE `users`.`user_id` ="

    &

    Val

    (

    lblid.

    Text

    )

    &

    ";"

    )
  4. Call

    btnload_Click(

    sender, e)

  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:

  5. Imports

    MySql.

    Data

    .

    MySqlClient
  6. Public

    Class

    Manage_user

  8. Private

    Sub

    btncreate_Click(

    ByVal

    sender As

    System.

    Object

    , ByVal

    e As

    System.

    EventArgs

    )

    Handles

    btncreate.

    Click

  10. jokeninsert(

    "INSERT INTO `test`.`users` (`user_id`, `fullname`, `username`, `password`) "

    &

    _
  11. "VALUES (NULL, '"

    &

    txtfullname.

    Text

    &

    "', '"

    &

    txtusername.

    Text

    &

    "', '"

    &

    txtpassword.

    Text

    &

    "');"

    )

  13. End

    Sub

  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

  20. Private

    Sub

    btnDelete_Click(

    ByVal

    sender As

    System.

    Object

    , ByVal

    e As

    System.

    EventArgs

    )

    Handles

    btnDelete.

    Click

  22. jokendelete(

    "DELETE FROM `test`.`users` WHERE `users`.`user_id` ="

    &

    Val

    (

    lblid.

    Text

    )

    &

    ";"

    )
  23. Call

    btnload_Click(

    sender, e)

  25. End

    Sub

  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)

  31. End

    Sub

  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

  40. End

    Sub

  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.

 
You must log in or register to reply here.

452,292

324,135

324,143

R rathi
Top