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

on

🚀 Microsoft Visual Studio 2019 : Visual Basic .NET (VB.NET & PostgreSQL Tutorial).

ðŸ‘Ļ‍ðŸŦ How to Perform SMART CRUD (Create Read Update & Delete) Operations with Search functionality.
✅ https://youtu.be/kqyeaTWol9k

ðŸ‘Ļ‍ðŸŦ How to connect PostgreSQL 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


[FREE SOURCE CODE by iBASSKUNG]

ðŸŽŊ Souce Code: http://bit.ly/39ZUtCY

#BEGIN

Module: PostgreSQL_Connection.vb

Option Explicit On
Option Strict On

Imports Npgsql

Module PostgreSQL_Connection

    Public Function GetConnectionString() As String

        Dim host As String = "Host=localhost;"
        Dim port As String = "Port=5432;"
        Dim db As String = "Database=vb_smart_crud;"
        Dim user As String = "Username=postgres;"
        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 NpgsqlConnection(GetConnectionString())
    Public Cmd As NpgsqlCommand
    Public SQL As String = ""

    Public Function PerformCRUD(Com As NpgsqlCommand) As DataTable

        Dim da As NpgsqlDataAdapter
        Dim dt As New DataTable()

        Try

            da = New NpgsqlDataAdapter
            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 Npgsql

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 NpgsqlCommand(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
            MessageBox.Show("Please input first name and last name.", "Insert Data : iBasskung Tutorial",
                            MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If

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

        Execute(SQL, "Insert")


        MessageBox.Show("The record has been saved.", "Insert Data : iBasskung Tutorial",
                            MessageBoxButtons.OK, MessageBoxIcon.Information)

        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 @keyword::varchar OR TRIM(gender) LIKE @keyword::varchar ORDER BY auto_id ASC"

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

        Cmd = New NpgsqlCommand(SQL, Con)
        Cmd.Parameters.Clear()
        Cmd.Parameters.AddWithValue("keyword", strKeyword)

        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 = 80
            .Columns(1).Width = 170
            .Columns(2).Width = 170
            .Columns(3).Width = 200
            .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
            MessageBox.Show("Please select an item from the list.", "Update Data : iBasskung Tutorial",
                            MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If

        If String.IsNullOrEmpty(FirstNameTextBox.Text.Trim()) Or String.IsNullOrEmpty(LastNameTextBox.Text.Trim()) Then
            MessageBox.Show("Please input first name and last name.", "Update Data : iBasskung Tutorial",
                            MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If

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

        Execute(SQL, "Update")

        MessageBox.Show("The record has been Updated.", "Update Data : iBasskung Tutorial",
                            MessageBoxButtons.OK, MessageBoxIcon.Information)

        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
            MessageBox.Show("Please select an item from the list.", "Update Data : iBasskung Tutorial",
                            MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If

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

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

            Execute(SQL, "Delete")

            MessageBox.Show("The record has been deleted.", "Delete Data : iBasskung Tutorial",
                                MessageBoxButtons.OK, MessageBoxIcon.Information)

            LoadData()

            ResetMe()

        End If

    End Sub

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

        Try

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

            ResetMe()

        Catch ex As Exception
            MsgBox("An error occurred: " & ex.Message, MsgBoxStyle.OkOnly Or MsgBoxStyle.Critical, "Search Error : iBasskung Tutorial")
        End Try

    End Sub

End Class

#END

ðŸŽŊ See more:

ðŸ’Ŋ THANK YOU SO MUCH ðŸ’Ŋ

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 #PostgreSQL #CRUDOperation #Search

Comments

  1. henryMarch 12, 2021 at 8:25 PM

    This is just the information I am finding everywhere. Thanks for your blog, I just subscribe your blog. This is a nice blog.. http://psiprograms.com

    ReplyDelete

Post a Comment