Windows Forms CRUD (Create/Update/Delete) Application In .NET Core And Visual Basic

Hello,

Here's a Visual Basic tutorial on how to create a CRUD (Create/Update/Delete) application using Windows Forms In .NET Core. The same concept is applied to a Windows Forms application in .NET Framework. Most of the concepts and logic were borrowed from the C# version here except that a different programming language is used. First is to create a Students table on your SQL Server Database using the script below.

USE [TestDatabase]
GO

/****** Object:  Table [dbo].[Students]    Script Date: 03/31/2014 14:11:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Students](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NULL,
	[Age] [int] NULL,
	[Address] [varchar](50) NULL,
	[Contact] [varchar](50) NULL,
 CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Create a Windows Forms App called "CRUDApplicationWindowsFormsVB" using latest Visual Studio IDE (Visual Studio 2022) and set the target framework to .NET 8.

Windows Forms CRUD (Create/Update/Delete) Application In .NET Core And Visual Basic

Next is to setup the project structure by adding two folders specifically Model and DataAccess. Create a Student class inside the Model folder. And then DALHelper and StudentService classes in the DataAccess. Also add an App.config file for the connectionString. Rename the default form to FStudents.

Update your App.config file by setting the connectionString node that points to the database as to where the table is created.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="TestDatabase" connectionString="Data Source=.;Initial Catalog=TestDatabase;Integrated Security=True;" providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

In your Student.vb class, add properties that holds information for a particular student like ID, Name, Age, and etc..

Public Class Student
    Public Property ID As Integer = 0
    Public Property Name As String = String.Empty
    Public Property Age As Integer = 0
    Public Property Address As String = String.Empty
    Public Property Contact As String = String.Empty
End Class

For the DALHelper class, these are methods that communicates to the database that performs specific actions such as inserting, retrieving, deleting or updating records. What each method needs is the query passed from the caller into the the argument of the function.

Option Infer On
Imports System.Configuration
Imports System.Data.SqlClient

Public Class DALHelper
    Private myAdapter As SqlDataAdapter
    Private myConn As SqlConnection
    Private connectionString As String

    Public Sub New()
        connectionString = ConfigurationManager.ConnectionStrings("TestDatabase").ConnectionString
        myAdapter = New SqlDataAdapter()
        myConn = New SqlConnection(connectionString)
    End Sub

    Private Function OpenConnection() As SqlConnection
        If myConn.State = ConnectionState.Closed OrElse myConn.State = ConnectionState.Broken Then
            myConn.Open()
        End If

        Return myConn
    End Function

    Private Function CloseConnection() As SqlConnection
        If myConn.State = ConnectionState.Open Or myConn.State = ConnectionState.Broken Then
            myConn.Close()
        End If

        Return myConn
    End Function

    Public Function ExecuteScalar(ByVal _query As String) As Integer
        Dim id As Integer = -1
        Dim command As SqlCommand = myConn.CreateCommand()

        Try
            command.Connection = OpenConnection()
            command.CommandText = _query
            Dim objId As Object = command.ExecuteScalar()

            If objId <> DBNull.Value AndAlso objId IsNot Nothing Then
                id = CInt(objId)
            End If

        Catch ex As Exception
        Finally
            CloseConnection()
            command.Dispose()
            myConn.Dispose()
        End Try

        Return id
    End Function

    Public Function ExecuteScalar(ByVal _query As String, ByVal sqlParameter As SqlParameter()) As Integer
        Dim id As Integer = -1
        Dim command As SqlCommand = myConn.CreateCommand()

        Try
            command.Connection = OpenConnection()
            command.Parameters.AddRange(sqlParameter)
            command.CommandText = _query
            Dim objId As Object = command.ExecuteScalar()

            If objId <> DBNull.Value And objId IsNot Nothing Then
                id = CInt(objId)
            End If

        Catch ex As Exception
        Finally
            CloseConnection()
            command.Dispose()
            myConn.Dispose()
        End Try

        Return id
    End Function

    Public Function BoolExecuteScalar(ByVal _query As String, ByVal sqlParameter As SqlParameter()) As Boolean
        Dim isExisting As Boolean = False
        Dim command As SqlCommand = myConn.CreateCommand()

        Try
            command.Connection = OpenConnection()
            command.Parameters.AddRange(sqlParameter)
            command.CommandText = _query
            Dim objId As Object = command.ExecuteScalar()

            If objId <> DBNull.Value And objId IsNot Nothing Then
                isExisting = True
            End If

        Catch ex As Exception
        Finally
            CloseConnection()
            command.Dispose()
            myConn.Dispose()
        End Try

        Return isExisting
    End Function

    Public Function ExecuteSelectQuery(ByVal _query As String) As DataSet
        Dim ds As DataSet = New DataSet()
        Dim command As SqlCommand = myConn.CreateCommand()

        Try
            command.Connection = OpenConnection()
            command.CommandText = _query
            myAdapter.SelectCommand = command
            myAdapter.Fill(ds)
        Catch ex As Exception
        Finally
            CloseConnection()
            command.Dispose()
            myConn.Dispose()
        End Try

        Return ds
    End Function

    Public Function ExecuteSelectQueryDataTable(ByVal _query As String) As DataTable
        Dim dt As DataTable = New DataTable()
        Dim command As SqlCommand = myConn.CreateCommand()

        Try
            command.Connection = OpenConnection()
            command.CommandText = _query
            myAdapter.SelectCommand = command
            myAdapter.Fill(dt)
        Catch ex As Exception
        Finally
            CloseConnection()
            command.Dispose()
            myConn.Dispose()
        End Try

        Return dt
    End Function

    Public Function ExecuteSelectQueryDataTable(ByVal _query As String, ByVal sqlParameter As SqlParameter()) As DataTable
        Dim dt As DataTable = New DataTable()
        Dim command As SqlCommand = myConn.CreateCommand()

        Try
            command.Connection = OpenConnection()
            command.Parameters.AddRange(sqlParameter)
            command.CommandText = _query
            myAdapter.SelectCommand = command
            myAdapter.Fill(dt)
        Catch ex As Exception
        Finally
            CloseConnection()
            command.Dispose()
            myConn.Dispose()
        End Try

        Return dt
    End Function

    Public Function ExecuteSelectQuery(ByVal _query As String, ByVal sqlParameter As SqlParameter()) As DataSet
        Dim ds As DataSet = New DataSet()
        Dim command As SqlCommand = myConn.CreateCommand()

        Try
            command.Connection = OpenConnection()
            command.Parameters.AddRange(sqlParameter)
            command.CommandText = _query
            myAdapter.SelectCommand = command
            myAdapter.Fill(ds)
        Catch ex As Exception
        Finally
            CloseConnection()
            command.Dispose()
            myConn.Dispose()
        End Try

        Return ds
    End Function

    Public Sub ExecuteNonQuery(ByVal query As String)
        Dim command As SqlCommand = myConn.CreateCommand()

        Try
            command.Connection = OpenConnection()
            command.CommandText = query
            command.CommandTimeout = 0
            command.ExecuteNonQuery()
        Catch e As Exception
        Finally
            CloseConnection()
            command.Dispose()
            myConn.Dispose()
        End Try
    End Sub

    Public Sub ExecuteNonQuery(ByVal query As String, ByVal sqlParameters As SqlParameter())
        Dim command As SqlCommand = myConn.CreateCommand()

        Try
            command.Connection = OpenConnection()
            command.Parameters.AddRange(sqlParameters)
            command.CommandText = query
            command.CommandTimeout = 0
            command.ExecuteNonQuery()
        Catch e As Exception
        Finally
            CloseConnection()
            command.Dispose()
            myConn.Dispose()
        End Try
    End Sub
End Class

The StudentService.vb class sets the query with the model values and then calls the methods of the DALHelper class passing in the query string.

Option Infer On
Imports System.Data.SqlClient

Public Class StudentService
    Private helper As DALHelper
    Private dtStudent As DataTable
    Private parameter As SqlParameter()
    Private query As String

    Public Sub SaveStudent(ByVal student As Student)
        helper = New DALHelper()
        query = "Insert Into Students(Name,Age,Address,Contact) " & " Values (@Name,@Age, @Address,@Contact)"
        parameter = New SqlParameter(3) {}
        parameter(0) = New SqlParameter("@Name", SqlDbType.VarChar) With {
            .Value = student.Name
        }
        parameter(1) = New SqlParameter("@Age", SqlDbType.Int) With {
            .Value = student.Age
        }
        parameter(2) = New SqlParameter("@Address", SqlDbType.VarChar) With {
            .Value = student.Address
        }
        parameter(3) = New SqlParameter("@Contact", SqlDbType.VarChar) With {
            .Value = student.Contact
        }
        helper.ExecuteNonQuery(query, parameter)
    End Sub

    Public Sub UpdateStudent(ByVal student As Student)
        helper = New DALHelper()
        query = "Update Students set Name=@Name,Age=@Age,Address=@Address,Contact=@Contact Where ID=@ID"
        parameter = New SqlParameter(4) {}
        parameter(0) = New SqlParameter("@Name", SqlDbType.VarChar) With {
            .Value = student.Name
        }
        parameter(1) = New SqlParameter("@Age", SqlDbType.Int) With {
            .Value = student.Age
        }
        parameter(2) = New SqlParameter("@Address", SqlDbType.VarChar) With {
            .Value = student.Address
        }
        parameter(3) = New SqlParameter("@Contact", SqlDbType.VarChar) With {
            .Value = student.Contact
        }
        parameter(4) = New SqlParameter("@ID", SqlDbType.Int) With {
            .Value = student.ID
        }
        helper.ExecuteNonQuery(query, parameter)
    End Sub

    Public Sub DeleteStudent(ByVal student As Student)
        helper = New DALHelper()
        query = "DELETE FROM Students WHERE ID=@id"
        parameter = New SqlParameter(0) {}
        parameter(0) = New SqlParameter("@id", SqlDbType.Int) With {
            .Value = student.ID
        }
        helper.ExecuteNonQuery(query, parameter)
    End Sub

    Public Function Fetch() As DataTable
        helper = New DALHelper()
        dtStudent = New DataTable()
        query = "Select * from Students"
        dtStudent = helper.ExecuteSelectQueryDataTable(query)
        Return dtStudent
    End Function
End Class

To proceed with the UI, add four Labels, four Textboxes (txtName,txtAge,txtAddress and txtContact), two Buttons (btnSave & btnCancel) and a DataGridView control (dgvStudents).The TextBoxes and the Label controls are inside a GroupBox control.

Add multiple columns to the DataGridView controls and set their corresponding DataPropertyName property. This will bind the field of the model to it's corresponding column.
ID - DataPropertyName (ID)
Name - DataPropertyName (Name)
Age - DataPropertyName (Age)
Address - DataPropertyName (Address)
Contact - DataPropertyName (Contact)
Optionally, you may set other properties such as Width, ReadOnly and other properties depending on the project requirements.

As for the code-behind implementation, it contains methods that fetches data from the database, saving and cancelling events, and DataGridView CellContent click event. Each event calls a specific service method and pass the model object to that method. Referring to the CellContext click event, this method does two things: deleting data from the database if the user wish to proceed and selection of records that will be modified.

Option Infer On
Option Strict On

Public Class FStudents
    Private student As Student
    Private service As StudentService
    Private Property ID As Integer

    Public Sub New()
        InitializeComponent()
    End Sub

    Private Sub FStudents_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        FetchData()
    End Sub

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        student = New Student()

        If ID <= 0 Then
            If (txtName.Text <> String.Empty) Then
                student.Name = txtName.Text
                student.Address = txtAddress.Text
                student.Age = If(String.IsNullOrEmpty(txtAge.Text), 0, Convert.ToInt32(txtAge.Text))
                student.Contact = txtContact.Text
                service.SaveStudent(student)
            Else
                MessageBox.Show("Add at least a value to name!", "Add value", MessageBoxButtons.OK, MessageBoxIcon.[Error])
            End If
        Else
            student.ID = ID
            student.Name = txtName.Text
            student.Address = txtAddress.Text
            student.Age = If(String.IsNullOrEmpty(txtAge.Text), 0, Convert.ToInt32(txtAge.Text))
            student.Contact = txtContact.Text
            service.UpdateStudent(student)
        End If

        ClearControls()
        ResetID()
        FetchData()
    End Sub

    Private Sub btnCancel_Click(sender As Object, e As EventArgs) Handles btnCancel.Click
        ClearControls()
        ResetID()
    End Sub

    Private Sub FetchData()
        service = New StudentService()
        dgvStudents.DataSource = service.Fetch()
    End Sub

    Private Sub ResetID()
        ID = -1
    End Sub

    Private Sub ClearControls()
        txtAddress.Text = String.Empty
        txtAge.Text = String.Empty
        txtContact.Text = String.Empty
        txtName.Text = String.Empty
    End Sub

    Private Sub dgvStudents_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgvStudents.CellContentClick
        If e.RowIndex >= 0 AndAlso (CType(sender, DataGridView)).Columns(e.ColumnIndex).[GetType]() = GetType(DataGridViewButtonColumn) AndAlso (e.ColumnIndex = 1) Then
            If MessageBox.Show("You sure about this? Student will be deleted from database!", "Remove?", MessageBoxButtons.OKCancel) = DialogResult.OK Then

                If (CType(sender, DataGridView)).CurrentRow.Cells(2).Value IsNot Nothing Then
                    student = New Student()
                    student.ID = Convert.ToInt32((CType(sender, DataGridView)).CurrentRow.Cells(2).Value)
                    service.DeleteStudent(student)
                    FetchData()
                End If
            End If
        Else

            If e.RowIndex >= 0 AndAlso (CType(sender, DataGridView)).Columns(e.ColumnIndex).[GetType]() = GetType(DataGridViewButtonColumn) AndAlso (e.ColumnIndex = 0) Then
                ID = Convert.ToInt32((CType(sender, DataGridView)).CurrentRow.Cells(2).Value)
                txtAddress.Text = (CType(sender, DataGridView)).CurrentRow.Cells(5).Value.ToString()
                txtAge.Text = (CType(sender, DataGridView)).CurrentRow.Cells(4).Value.ToString()
                txtContact.Text = (CType(sender, DataGridView)).CurrentRow.Cells(6).Value.ToString()
                txtName.Text = (CType(sender, DataGridView)).CurrentRow.Cells(3).Value.ToString()
            End If
        End If
    End Sub
End Class

Source Code: Github

Cheers!

Search This Blog

.NET GENES

Donate

Windows Forms CRUD (Create/Update/Delete) Application In .NET Core And Visual Basic

Comments

Post a Comment

Donate

Popular Posts From This Blog

WPF CRUD Application Using DataGrid, MVVM Pattern, Entity Framework, And C#.NET

How To Insert Or Add Emojis In Microsoft Teams Status Message

Bootstrap Modal In ASP.NET MVC With CRUD Operations