VB.NET and MySQL / MariaDB Perform SMART CRUD Operations with SEARCH (Windows Forms Source Code)

🚀 Microsoft Visual Studio 2019 : Visual Basic .NET (VB.NET & MySQL / MariaDB Tutorial).

👨‍🏫 How to Perform SMART CRUD (Create Read Update & Delete) Operations with Search functionality.
✅ https://youtu.be/J2e9q9CgxVI

👨‍🏫 How to connect MySQL database using VB.NET

In computer programming, create, read, update, and delete (CRUD) are the four basic functions of persistent storage. Alternate words are sometimes used when defining the four basic functions of CRUD, such as retrieve instead of read, modify instead of update, or destroy instead of delete.

Read more: Wikipedia

Screenshot

YouTube: https://youtu.be/J2e9q9CgxVI

[FREE SOURCE CODE by iBASSKUNG]

🎯 Souce Code: http://bit.ly/2MSaf8I

#BEGIN

Module: MySQL_Connection.vb

Option Explicit On
Option Strict On

Imports System.Data
Imports MySql.Data.MySqlClient

Module MySQL_Connection

    Public Function GetConnectionString() As String

        Dim host As String = "server=localhost;"
        Dim port As String = "port=3306;"
        Dim db As String = "database=vb_smart_crud;"
        Dim user As String = "user=root;"
        Dim pass As String = "password=1234;"

        Dim conString As String = String.Format("{0}{1}{2}{3}{4}", host, port, db, user, pass)

        Return conString

    End Function

    Public Con As New MySqlConnection(GetConnectionString())
    Public Cmd As MySqlCommand
    Public SQL As String = ""

    Public Function PerformCRUD(Com As MySqlCommand) As DataTable

        Dim da As MySqlDataAdapter
        Dim dt As New DataTable()

        Try

            da = New MySqlDataAdapter()
            da.SelectCommand = Com
            da.Fill(dt)

            Return dt

        Catch ex As Exception
            MessageBox.Show("An error occurred: " & ex.Message, "Perform CRUD Operations Failed : iBasskung Tutorial",
                            MessageBoxButtons.OK, MessageBoxIcon.Error)
            dt = Nothing
        End Try

        Return dt

    End Function

End Module

Form: Form1.vb

Option Explicit On
Option Strict On

Imports MySql.Data.MySqlClient

Public Class Form1

    Private ID As String = ""
    Private intRow As Integer = 0

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ResetMe()
        LoadData()
    End Sub

    Private Sub ResetMe()

        Me.ID = ""

        FirstNameTextBox.Text = ""
        LastNameTextBox.Text = ""

        If GenderComboBox.Items.Count > 0 Then
            GenderComboBox.SelectedIndex = 0
        End If

        UpdateButton.Text = "UPDATE ()"
        DeleteButton.Text = "DELETE ()"

        KeywordTextBox.Clear()
        KeywordTextBox.Select()

    End Sub

    Private Sub Execute(MySQL As String, Optional Parameter As String = "")
        Cmd = New MySqlCommand(MySQL, Con)
        AddParameters(Parameter)
        PerformCRUD(Cmd)
    End Sub

    Private Sub AddParameters(str As String)
        Cmd.Parameters.Clear()
        Cmd.Parameters.AddWithValue("FirstName", FirstNameTextBox.Text.Trim())
        Cmd.Parameters.AddWithValue("LastName", LastNameTextBox.Text.Trim())
        Cmd.Parameters.AddWithValue("Gender", GenderComboBox.SelectedItem.ToString())

        If str = "Update" Or str = "Delete" And Not String.IsNullOrEmpty(Me.ID) Then
            Cmd.Parameters.AddWithValue("ID", Me.ID)
        End If

    End Sub

    Private Sub InsertButton_Click(sender As Object, e As EventArgs) Handles InsertButton.Click

        If String.IsNullOrEmpty(FirstNameTextBox.Text.Trim()) Or String.IsNullOrEmpty(LastNameTextBox.Text.Trim()) Then
            MsgBox("Please input first name and last name.", MsgBoxStyle.OkOnly Or MsgBoxStyle.Exclamation,
                    "MySQL : Insert Data : iBasskung Tutorial")
            Exit Sub
        End If

        SQL = "INSERT INTO tbl_smart_crud(first_name, last_name, gender) VALUES(@FirstName, @LastName, @Gender)"

        Execute(SQL, "Insert")

        MsgBox("The record has been saved.", MsgBoxStyle.OkOnly Or MsgBoxStyle.Information,
                    "MySQL : Insert Data : iBasskung Tutorial")

        LoadData()

        ResetMe()

    End Sub

    Private Sub LoadData(Optional keyword As String = "")

        SQL = "SELECT auto_id, first_name, last_name, CONCAT(first_name, ' ', last_name) AS 'full_name', gender FROM tbl_smart_crud " &
              "WHERE CONCAT(first_name, ' ', last_name) LIKE @keyword1 OR gender = @keyword2 ORDER BY auto_id ASC"

        Dim strKeyword As String = String.Format("%{0}%", keyword)

        Cmd = New MySqlCommand(SQL, Con)
        Cmd.Parameters.Clear()
        Cmd.Parameters.AddWithValue("keyword1", strKeyword)
        Cmd.Parameters.AddWithValue("keyword2", keyword)

        Dim dt As DataTable = PerformCRUD(Cmd)

        If dt.Rows.Count > 0 Then
            intRow = convert.ToInt32(dt.Rows.Count.ToString())
        Else
            intRow = 0
        End If

        ToolStripStatusLabel1.Text = "Number of row(s): " & intRow.ToString()

        With DataGridView1

            .MultiSelect = False
            .SelectionMode = DataGridViewSelectionMode.FullRowSelect
            .AutoGenerateColumns = True

            .DataSource = dt

            .Columns(0).HeaderText = "ID"
            .Columns(1).HeaderText = "First Name"
            .Columns(2).HeaderText = "Last Name"
            .Columns(3).HeaderText = "Full Name"
            .Columns(4).HeaderText = "Gender"

            .Columns(0).Width = 75
            .Columns(1).Width = 170
            .Columns(2).Width = 170
            .Columns(3).Width = 220
            .Columns(4).Width = 100

        End With

    End Sub

    Private Sub DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick

        Try

            Dim dgv As DataGridView = DataGridView1

            If e.RowIndex <> -1 Then

                Me.ID = Convert.ToString(dgv.CurrentRow.Cells(0).Value).Trim()
                UpdateButton.Text = "UPDATE (" & Me.ID & ")"
                DeleteButton.Text = "DELETE (" & Me.ID & ")"

                FirstNameTextBox.Text = Convert.ToString(dgv.CurrentRow.Cells(1).Value).Trim()
                LastNameTextBox.Text = Convert.ToString(dgv.CurrentRow.Cells(2).Value).Trim()

                GenderComboBox.SelectedItem = Convert.ToString(dgv.CurrentRow.Cells(4).Value).Trim()

            End If

        Catch ex As Exception

        End Try

    End Sub

    Private Sub UpdateButton_Click(sender As Object, e As EventArgs) Handles UpdateButton.Click

        If DataGridView1.Rows.Count = 0 Then
            Exit Sub
        End If

        If String.IsNullOrEmpty(Me.ID) Then
            MsgBox("Please select an item from the list.", MsgBoxStyle.OkOnly Or MsgBoxStyle.Exclamation,
                    "MySQL : Update Data : iBasskung Tutorial")
            Exit Sub
        End If

        If String.IsNullOrEmpty(FirstNameTextBox.Text.Trim()) Or String.IsNullOrEmpty(LastNameTextBox.Text.Trim()) Then
            MsgBox("Please input first name and last name.", MsgBoxStyle.OkOnly Or MsgBoxStyle.Exclamation,
                    "MySQL : Update Data : iBasskung Tutorial")
            Exit Sub
        End If

        SQL = "UPDATE tbl_smart_crud SET first_name = @FirstName, last_name = @LastName, gender = @Gender WHERE auto_id = @ID"

        Execute(SQL, "Update")

        MsgBox("The record has been updated.", MsgBoxStyle.OkOnly Or MsgBoxStyle.Information,
                    "MySQL : Update Data : iBasskung Tutorial")

        LoadData()

        ResetMe()

    End Sub

    Private Sub DeleteButton_Click(sender As Object, e As EventArgs) Handles DeleteButton.Click

        If DataGridView1.Rows.Count = 0 Then
            Exit Sub
        End If

        If String.IsNullOrEmpty(Me.ID) Then
            MsgBox("Please select an item from the list.", MsgBoxStyle.OkOnly Or MsgBoxStyle.Exclamation,
                    "MySQL : Delete Data : iBasskung Tutorial")
            Exit Sub
        End If

        If MessageBox.Show("Do you want to delete the selected record?", "MySQL : Delete Data : iBasskung Tutorial",
                           MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) = DialogResult.Yes Then

            SQL = "DELETE FROM tbl_smart_crud WHERE auto_id = @ID"

            Execute(SQL, "Delete")

            MsgBox("The record has been deleted.", MsgBoxStyle.OkOnly Or MsgBoxStyle.Information,
                        "MySQL : Delete Data : iBasskung Tutorial")

            LoadData()

            ResetMe()

        End If

    End Sub

    Private Sub SearchButton_Click(sender As Object, e As EventArgs) Handles SearchButton.Click

        If Not String.IsNullOrEmpty(KeywordTextBox.Text.Trim()) Then
            LoadData(KeywordTextBox.Text.Trim())
        Else
            LoadData()
        End If

        ResetMe()

    End Sub

End Class

#END

🎯 See more:

📲 YouTube: https://www.youtube.com/c/iBasskung

📲 Facebook: https://www.facebook.com/CodeAMinute

📲 Udemy: https://www.udemy.com/user/tanin-sangngam

📲 SkillLane: https://www.skilllane.com/instructors/iBasskung

Tags: visual studio 2019, visual basic 2019, vb.net, vb.net tutorial for beginners visual studio 2019, visual basic, vb, visual studio 2019 visual basic tutorial, visual studio 2019 tutorial, how to use visual studio 2019, visual basic tutorial 2019, access, visual studio

#VBDotNET #VisualBasicDotNET #MySQL #MariaDB #CRUDOperation #Search