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

0
4510

This tutorial is all about 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.
[mysql] CREATE TABLE IF NOT EXISTS users (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(30) NOT NULL,
username varchar(30) NOT NULL,
Pass varchar(60) NOT NULL,
type varchar(30) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
[/mysql] 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.
[vbnet] Imports MySql.Data.MySqlClient
Module strconcrud
‘SETTING UP THE CONNECTION
Public Function strstrconnection() As MySqlConnection
Return New MySqlConnection(“server=localhost;user id=root;database=userdb”)
End Function
Public strcon As MySqlConnection = strstrconnection()
‘DECLARING CLASSES AND VARIABLE
Public result As String
Public cmd As New MySqlCommand
Public da As New MySqlDataAdapter
Public dt As New DataTable
End Module
[/vbnet] After setting up the connection and the declaration. Create the CRUD event.
[vbnet] ‘THIS METHOD IS FOR INSERTING DATA IN THE DATABASE
Public Sub create(ByVal sql As String)
Try
strcon.Open()
‘HOLDS THE DATA TO BE EXECUTED
With cmd
.Connection = strcon
.CommandText = sql
‘EXECUTE THE DATA
result = cmd.ExecuteNonQuery
‘CHECKING IF THE DATA HAS EXECUTED OR NOT AND THEN THE POP UP MESSAGE WILL APPEAR
If result = 0 Then
MsgBox(“FAILED TO SAVE THE DATA”, MsgBoxStyle.Information)
Else
MsgBox(“DATA HAS BEEN SAVE IN THE DATABASE”)
End If
End With
Catch ex As Exception
MsgBox(ex.Message)
End Try
strcon.Close()
End Sub
‘THIS METHOD IS FOR RETRIEVING DATA IN THE DATABASE
Public Sub reload(ByVal sql As String, ByVal DTG As Object)
Try
dt = New DataTable
strcon.Open()
With cmd
.Connection = strcon
.CommandText = sql
End With
da.SelectCommand = cmd
da.Fill(dt)
dtg.DataSource = dt
Catch ex As Exception
MsgBox(ex.Message)
End Try
strcon.Close()
da.Dispose()
End Sub
‘THIS METHOD IS FOR UPDATING THE DATA IN THE DATABASE.
Public Sub updates(ByVal sql As String)
Try
strcon.Open()
With cmd
.Connection = strcon
.CommandText = sql
result = cmd.ExecuteNonQuery
If result = 0 Then
MsgBox(“DATA IS FAILED TO UPPDATE.”, MsgBoxStyle.Information)
Else
MsgBox(“THE DATA HAS BEEN UPDATED IN THE DATABASE.”)
End If
End With

Catch ex As Exception
MsgBox(ex.Message)
End Try
strcon.Close()
End Sub
‘THIS METHOD IS FOR DELETING THE DATA IN THE DATABASE
Public Sub delete(ByVal sql As String)
Try
strcon.Open()
With cmd
.Connection = strcon
.CommandText = sql
result = cmd.ExecuteNonQuery
If result = 0 Then
MsgBox(“FAILED TO DELETE THE DATA IN THE DATABASE.”, MsgBoxStyle.Critical)
Else
MsgBox(“DATA HAS BEEN DELETED IN THE DATABASE.”)
End If
End With
Catch ex As Exception
MsgBox(ex.Message)
End Try
strcon.Close()
End Sub
[/vbnet] 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.
[vbnet] ‘THIS PROCEEDURE IS FOR THE CREATE BUTTON
Private Sub CREATE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNCREATE.Click
Try
‘CALL THE METHOD THAT YOU HAVE CREATED
‘AND PUT YOUR QUERY IN THE PARAMETER FOR INSERTING THE DATA IN THE DATABASE
create(“INSERT INTO users (name, username, Pass,type) VALUES (‘” & TXTNAME.Text & “‘,'” _
& TXTUSERNAME.Text & “‘,'” & TXTPASSWORD.Text & “‘,'” & CBOTYPE.Text & “‘)”)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
‘THIS PROCEEDURE IS FOR THE RELOAD BUTTON
Private Sub RELOAD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNRELOAD.Click
Try
‘CALL THE METHOD THAT YOU HAVE CREATED.
‘PUT YOUR QUERY AND THE NAME OF THE DATAGRIDVIEW IN THE PARAMETERS.
‘THIS IS METHOD IS FOR RETREIVING THE DATA IN THE DATABASE TO THE DATAGRIDVIEW
reload(“SELECT * FROM users”, DTGLIST)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
‘THIS PROCEEDURE IS FOR THE UPDATE BUTTON
Private Sub UPDATE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNUPDATE.Click
Try
‘CALL THE METHOD THAT YOU HAVE CREATED
‘AND PUT YOUR QUERY IN THE PARAMETER FOR UPDATING THE DATA IN THE DATABASE
updatess(“UPDATE users SET name = ‘” & TXTNAME.Text & “‘, username = ‘” & TXTUSERNAME.Text _
& “‘, Pass = ‘” & TXTPASSWORD.Text & “‘,type = ‘” & CBOTYPE.Text & “‘ where id = ‘” & lblid.Text & “‘”)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
‘THIS PROCEEDURE IS FOR THE DOUBLE CLICK EVENT OF THE DATAGRID VIEW
Private Sub DataGridView1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles DTGLIST.DoubleClick
‘SET THE RECORDS ON THE LIST OF THE DATAGRIDVIEW TO ITS SPECIFIC FEILDS
lblid.Text = DTGLIST.CurrentRow.Cells(0).Value
TXTNAME.Text = DTGLIST.CurrentRow.Cells(1).Value
TXTUSERNAME.Text = DTGLIST.CurrentRow.Cells(2).Value
TXTPASSWORD.Text = DTGLIST.CurrentRow.Cells(3).Value
CBOTYPE.Text = DTGLIST.CurrentRow.Cells(4).Value
End Sub
‘THIS PROCEEDURE IS FOR THE DELETE BUTTON
Private Sub DELETE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNDELETE.Click
Try
‘CALL THE METHOD THAT YOU HAVE CREATED
‘AND PUT YOUR QUERY IN THE PARAMETER FOR DELETING THE DATA IN THE DATABASE
delete(“DELETE FROM users WHERE id = ‘” & lblid.Text & “‘”)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
[/vbnet]

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

Download here.

 

Readers might read also:

 

 

Facebook Comments

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.