ITSourceCode.com

We Exist to Provide 100% Free Source Code and Tutorials

Custom Search

How to Organize the CRUD in VB.Net and MySQL Database

Today, I’m going to teach you how to organize MySQL Connection and CRUD by using a Module in VB.Net. The Module is one of the ways on how to organize your system, reduce the code and it will lessens your work.

Lets’ begin:
First, create a Database and name it “dbuser” in MySQL Database.

  1. CREATE TABLE IF NOT EXISTS `users` (
  2. `name` varchar(30) NOT NULL,
  3. `username` varchar(30) NOT NULL,
  4. `Pass` varchar(60) NOT NULL,
  5. `type` varchar(30) NOT NULL,

Open the Visual Basic and create a new Windows Form Application. After that, in the solution explorer, right click and create “New Folder” , name it “Modules”. This is the way to separate the Module and the Windows Form.
crudform2
In the folder, create a Module and name it “CRUD”.
crudform3
After creating a Module, You have to set the connection string of MySQL Database. Then, Declare all the classes ad variables that are needed.

  1. Imports MySql.Data.MySqlClient
  2. Module strconcrud
  3. 'SETTING UP THE CONNECTION
  4. Public Function strstrconnection() As MySqlConnection
  5. Return New MySqlConnection("server=localhost;user id=root;database=userdb")
  6. End Function
  7. Public strcon As MySqlConnection = strstrconnection()
  8. 'DECLARING CLASSES AND VARIABLE
  9. Public result As String
  10. Public cmd As New MySqlCommand
  11. Public da As New MySqlDataAdapter
  12. Public dt As New DataTable
  13. End Module

After setting up the connection and the declaration. Create the CRUD event.

  1. 'THIS METHOD IS FOR INSERTING DATA IN THE DATABASE
  2. Public Sub create(ByVal sql As String)
  3. Try
  4. strcon.Open()
  5. 'HOLDS THE DATA TO BE EXECUTED
  6. With cmd
  7. .Connection = strcon
  8. .CommandText = sql
  9. 'EXECUTE THE DATA
  10. result = cmd.ExecuteNonQuery
  11. 'CHECKING IF THE DATA HAS EXECUTED OR NOT AND THEN THE POP UP MESSAGE WILL APPEAR
  12. If result = 0 Then
  13. MsgBox("FAILED TO SAVE THE DATA", MsgBoxStyle.Information)
  14. Else
  15. MsgBox("DATA HAS BEEN SAVE IN THE DATABASE")
  16. End If
  17. End With
  18. Catch ex As Exception
  19. MsgBox(ex.Message)
  20. End Try
  21. strcon.Close()
  22. End Sub
  23. 'THIS METHOD IS FOR RETRIEVING DATA IN THE DATABASE
  24. Public Sub reload(ByVal sql As String, ByVal DTG As Object)
  25. Try
  26. dt = New DataTable
  27. strcon.Open()
  28. With cmd
  29. .Connection = strcon
  30. .CommandText = sql
  31. End With
  32. da.SelectCommand = cmd
  33. da.Fill(dt)
  34. dtg.DataSource = dt
  35. Catch ex As Exception
  36. MsgBox(ex.Message)
  37. End Try
  38. strcon.Close()
  39. da.Dispose()
  40. End Sub
  41. 'THIS METHOD IS FOR UPDATING THE DATA IN THE DATABASE.
  42. Public Sub updates(ByVal sql As String)
  43. Try
  44. strcon.Open()
  45. With cmd
  46. .Connection = strcon
  47. .CommandText = sql
  48. result = cmd.ExecuteNonQuery
  49. If result = 0 Then
  50. MsgBox("DATA IS FAILED TO UPPDATE.", MsgBoxStyle.Information)
  51. Else
  52. MsgBox("THE DATA HAS BEEN UPDATED IN THE DATABASE.")
  53. End If
  54. End With
  55.  
  56. Catch ex As Exception
  57. MsgBox(ex.Message)
  58. End Try
  59. strcon.Close()
  60. End Sub
  61. 'THIS METHOD IS FOR DELETING THE DATA IN THE DATABASE
  62. Public Sub delete(ByVal sql As String)
  63. Try
  64. strcon.Open()
  65. With cmd
  66. .Connection = strcon
  67. .CommandText = sql
  68. result = cmd.ExecuteNonQuery
  69. If result = 0 Then
  70. MsgBox("FAILED TO DELETE THE DATA IN THE DATABASE.", MsgBoxStyle.Critical)
  71. Else
  72. MsgBox("DATA HAS BEEN DELETED IN THE DATABASE.")
  73. End If
  74. End With
  75. Catch ex As Exception
  76. MsgBox(ex.Message)
  77. End Try
  78. strcon.Close()
  79. End Sub

Go back to the Form Design and create a User’s Registration Form. It will look like this.
crudform
After setting up the Form, double click the Form and copy this code and put this above the Form1_Load event handler of the Form.

  1. 'THIS PROCEEDURE IS FOR THE CREATE BUTTON
  2. Private Sub CREATE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNCREATE.Click
  3. Try
  4. 'CALL THE METHOD THAT YOU HAVE CREATED
  5. 'AND PUT YOUR QUERY IN THE PARAMETER FOR INSERTING THE DATA IN THE DATABASE
  6. create("INSERT INTO users (`name`, `username`, `Pass`,`type`) VALUES ('" & TXTNAME.Text & "','" _
  7. & TXTUSERNAME.Text & "','" & TXTPASSWORD.Text & "','" & CBOTYPE.Text & "')")
  8. Catch ex As Exception
  9. MsgBox(ex.Message)
  10. End Try
  11. End Sub
  12. 'THIS PROCEEDURE IS FOR THE RELOAD BUTTON
  13. Private Sub RELOAD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNRELOAD.Click
  14. Try
  15. 'CALL THE METHOD THAT YOU HAVE CREATED.
  16. 'PUT YOUR QUERY AND THE NAME OF THE DATAGRIDVIEW IN THE PARAMETERS.
  17. 'THIS IS METHOD IS FOR RETREIVING THE DATA IN THE DATABASE TO THE DATAGRIDVIEW
  18. reload("SELECT * FROM users", DTGLIST)
  19. Catch ex As Exception
  20. MsgBox(ex.Message)
  21. End Try
  22. End Sub
  23. 'THIS PROCEEDURE IS FOR THE UPDATE BUTTON
  24. Private Sub UPDATE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNUPDATE.Click
  25. Try
  26. 'CALL THE METHOD THAT YOU HAVE CREATED
  27. 'AND PUT YOUR QUERY IN THE PARAMETER FOR UPDATING THE DATA IN THE DATABASE
  28. updatess("UPDATE users SET `name` = '" & TXTNAME.Text & "', `username` = '" & TXTUSERNAME.Text _
  29. & "', `Pass` = '" & TXTPASSWORD.Text & "',`type` = '" & CBOTYPE.Text & "' where id = '" & lblid.Text & "'")
  30. Catch ex As Exception
  31. MsgBox(ex.Message)
  32. End Try
  33. End Sub
  34. 'THIS PROCEEDURE IS FOR THE DOUBLE CLICK EVENT OF THE DATAGRID VIEW
  35. Private Sub DataGridView1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles DTGLIST.DoubleClick
  36. 'SET THE RECORDS ON THE LIST OF THE DATAGRIDVIEW TO ITS SPECIFIC FEILDS
  37. lblid.Text = DTGLIST.CurrentRow.Cells(0).Value
  38. TXTNAME.Text = DTGLIST.CurrentRow.Cells(1).Value
  39. TXTUSERNAME.Text = DTGLIST.CurrentRow.Cells(2).Value
  40. TXTPASSWORD.Text = DTGLIST.CurrentRow.Cells(3).Value
  41. CBOTYPE.Text = DTGLIST.CurrentRow.Cells(4).Value
  42. End Sub
  43. 'THIS PROCEEDURE IS FOR THE DELETE BUTTON
  44. Private Sub DELETE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNDELETE.Click
  45. Try
  46. 'CALL THE METHOD THAT YOU HAVE CREATED
  47. 'AND PUT YOUR QUERY IN THE PARAMETER FOR DELETING THE DATA IN THE DATABASE
  48. delete("DELETE FROM users WHERE id = '" & lblid.Text & "'")
  49. Catch ex As Exception
  50. MsgBox(ex.Message)
  51. End Try
  52. End Sub

You can download the complete Source Code and run it on your computer.

Download here.

Facebook Comments

Check Your Domain Ranking

Leave a Reply

Your email address will not be published. Required fields are marked *

ITSourceCode.com © 2016 Frontier Theme

Subscribe For Latest Updates

Signup for our newsletter and get notified when we publish new articles for free!