Learn WPF CRUD Operations Using VB.NET

Contents

Create a Database

Create Stored Procedures

Create a WPF Application in VB.NET

Perform CRUD Operations

Declare global variables and connection strings in the class.

You can validate the textbox and rich textbox controls and add, update, and Delete events.

In DataGrid, by using the mousedoubleclick event for editing the recorders, data can be retrieved by employee ID.

After completing the above steps, run the application.

Conclusion

Happy Coding!

In this article, we are going to:

Create a database.
Create stored procedures.
Create a WPF application in VB.NET.
Perform CRUD operations.

Create a Database

Open SQL Server 2016. Then, click the New Query window and run the below query.

USE [test]
GO
/****** Object:  Table [dbo].[EmployeeMaster]    Script Date: 5/7/2017 8:07:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeeMaster](
	[Id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[EmployeeCode] [bigint] NULL,
	[EmployeeName] [varchar](150) NULL,
	[EmployeeDob] [datetime] NULL,
	[EmployeeAddress] [varchar](500) NULL
) ON [PRIMARY]

Create Stored Procedures

You’ve probably used Entity Framework, but I have written the stored procedure for my data operations, so run the below SP.

CREATE PROCEDURE [dbo].[EmpMaster_SP]
@ID		NUMERIC(18,0)=NULL,
@EmpCode BIGINT=NULL,
@EmpName VARCHAR(150)=NULL,
@DOB	 DATETIME=NULL,
@Address VARCHAR(500)=NULL,
@Mode	 VARCHAR(10)
AS
BEGIN
	SET NOCOUNT ON;
	IF (@Mode='ADD')
	BEGIN
		INSERT INTO EmployeeMaster (EmployeeCode,EmployeeName,EmployeeDob,EmployeeAddress)
			VALUES(@EmpCode,@EmpName,@DOB,@Address)
	END
	IF (@Mode='EDIT')
	BEGIN
		UPDATE EmployeeMaster SET EmployeeCode=@EmpCode,EmployeeName=@EmpName,EmployeeDob=@DOB,EmployeeAddress=@Address WHERE ID=@ID
			
	END

	IF (@Mode='DELETE')
	BEGIN
		DELETE FROM EmployeeMaster WHERE ID=@ID
			
	END
	IF (@Mode='GET')
	BEGIN
		SELECT Id,EmployeeCode,EmployeeName,CONVERT(VARCHAR(10), EmployeeDob)EmployeeDob,EmployeeAddress FROM EmployeeMaster
			
	END
	IF (@Mode='GETID')
	BEGIN
		SELECT Id,EmployeeCode,EmployeeName, EmployeeDob,EmployeeAddress FROM EmployeeMaster WHERE ID=@ID
			
	END
 Create a WPF Application in VB.NET

Open Visual Studio 2015. Go to New Project > Visual Basic (under templates) > WPF Application.

WPF

After creating the application, open the Solution Explorer, which appears like the below image. Now, we are ready to create our design screen.

WPF

Here, I am using simple WPF controls:

Textbox.
Rich textbox.
Button.
Datagrid.
Label.
Date picker.

Then, write the following XAML code in MainWindow.xaml file.

<Window x:Class="MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:CURD_Gridvb"
        mc:Ignorable="d"
        Title="CURD" Height="700" Width="900" Background="DarkGray">
    <Grid Background="#FF474747">
        <Rectangle Fill="#FF66512F" HorizontalAlignment="Left" Height="165" Margin="76,40,0,0" Stroke="Black" VerticalAlignment="Top" Width="779"/>
        <Label x:Name="label" Content="Employee Code" HorizontalAlignment="Left" Margin="90,81,0,0" VerticalAlignment="Top" Foreground="#FFEADCDC" FontWeight="Bold" FontFamily="Tahoma"/>
        <TextBox x:Name="txtCode" HorizontalAlignment="Left" Height="30" Margin="202,75,0,0" TextWrapping="Wrap" Text="" VerticalAlignment="Top" Width="170" FontSize="14"/>
        <Label x:Name="label_Copy" Content="Employee Name" HorizontalAlignment="Left" Margin="417,81,0,0" VerticalAlignment="Top" RenderTransformOrigin="3.602,0.615" Foreground="#FFEADCDC" FontWeight="Bold" FontFamily="Tahoma"/>
        <TextBox x:Name="txtName" HorizontalAlignment="Left" Height="30" Margin="550,75,0,0" TextWrapping="Wrap" Text="" VerticalAlignment="Top" Width="235" FontSize="14"/>
        <Label x:Name="label_Copy1" Content="DOB" HorizontalAlignment="Left" Margin="90,134,0,0" VerticalAlignment="Top" Foreground="#FFEADCDC" FontWeight="Bold" FontFamily="Tahoma"/>
        <DatePicker x:Name="txtDate" HorizontalAlignment="Left" Margin="202,139,0,0" VerticalAlignment="Top" Width="170" Height="30" FontSize="14"/>
        <Label x:Name="label_Copy2" Content="Employee Address" HorizontalAlignment="Left" Margin="417,134,0,0" VerticalAlignment="Top" RenderTransformOrigin="3.602,0.615" Foreground="#FFEADCDC" FontWeight="Bold" FontFamily="Tahoma"/>
        <RichTextBox x:Name="rtxtAddress" HorizontalAlignment="Left" Height="75" Margin="550,117,0,0" VerticalAlignment="Top" Width="235" FontSize="14">
            <FlowDocument>
                <Paragraph>
                    <Run Text=""/>
                </Paragraph>
            </FlowDocument>
        </RichTextBox>
        <Rectangle Fill="#FF472828" HorizontalAlignment="Left" Height="55" Margin="76,220,0,0" Stroke="Black" VerticalAlignment="Top" Width="755"/>
        <Button x:Name="btnAdd" Content="Add" HorizontalAlignment="Left" Margin="119,230,0,0" VerticalAlignment="Top" Width="166" RenderTransformOrigin="-0.053,0" Height="35" Foreground="#FF0C0A0A" FontWeight="Bold" BorderBrush="#FFFFF4F4"
                />
        <Button x:Name="btnUpdate" Content="Update" HorizontalAlignment="Left" Margin="339,230,0,0" VerticalAlignment="Top" Width="175" RenderTransformOrigin="-0.053,0" Height="35" Foreground="Black" BorderBrush="#FFF7F6F5"
                />
        <Button x:Name="btnDelete" Content="Delete" HorizontalAlignment="Left" Margin="550,230,0,0" VerticalAlignment="Top" Width="170" RenderTransformOrigin="-0.003,0" Height="35" Foreground="#FF111010"
                />
        <Rectangle Fill="#FF0E2727" HorizontalAlignment="Left" Height="270" Margin="76,300,0,0" Stroke="Black" VerticalAlignment="Top" Width="755"/>

        <DataGrid   x:Name="dgEmp" Height="270" AutoGenerateColumns="False" RowHeight="25" 
                    GridLinesVisibility="Vertical" HeadersVisibility="All" RowBackground="WhiteSmoke" 
                    AlternatingRowBackground="LightGray" IsReadOnly="True" Margin="76,300,61,99" >
            <DataGrid.Columns>
                <DataGridTextColumn Binding="{Binding Id}" Width="130" Header="Employee Id"/>
                <DataGridTextColumn Binding="{Binding EmployeeCode}" Width="130" Header="Employee Code"/>
                <DataGridTextColumn Binding="{Binding EmployeeName}" Width="200" Header="Employee Name"/>
                <DataGridTextColumn Binding="{Binding EmployeeDob}" Width="100" Header="DOB"/>
                <DataGridTextColumn Binding="{Binding EmployeeAddress}" Width="200" Header="Employee Address"/>
            </DataGrid.Columns>
        </DataGrid>
        <Label x:Name="label_Copy3" Content="Employee Id" HorizontalAlignment="Left" Margin="90,45,0,0" VerticalAlignment="Top" Foreground="#FFEADCDC" FontWeight="Bold" FontFamily="Tahoma"/>
        <Label x:Name="lblEmpId" Content="" HorizontalAlignment="Left" Margin="202,45,0,0" VerticalAlignment="Top" Foreground="#FFEADCDC" FontWeight="Bold" FontFamily="Tahoma"/>

    </Grid>
</Window>
 Perform CRUD Operations

Create a Model folder in Solution Explorer and create a new VB.Class there.

Public Class Employee
    Public Property EmployeeCode As Int32
    Public Property EmployeeName As String
    Public Property DOB As Date
    Public Property Address As String
End Class

Declare global variables and connection strings in the class.

Imports System.Data
Imports System.Data.SqlClient
Imports CURD_Gridvb.Employee

You can validate the textbox and rich textbox controls and add, update, and Delete events.

If (txtCode.Text = String.Empty) Then
            MessageBox.Show("Enter the Employee Code")
            Return
        End If

        If (txtName.Text = String.Empty) Then
            MessageBox.Show("Enter the Employee Name")
            Return
        End If

        If (txtDate.Text = String.Empty) Then
            MessageBox.Show("Enter the Employee Name")
            Return
        End If

        Dim EmpAddress As String
        EmpAddress = New TextRange(rtxtAddress.Document.ContentStart, rtxtAddress.Document.ContentEnd).Text.ToString()
        If (EmpAddress = String.Empty) Then
            MessageBox.Show("Enter the Employee Name")
            Return
        End If

Copy and paste the below code in the “add” button event.

Try
            Dim Emp As New Employee
            Emp.EmployeeCode = Convert.ToInt32(txtCode.Text)
            Emp.EmployeeName = UCase(txtName.Text.Trim())
            Emp.DOB = Convert.ToDateTime(txtDate.Text)
            Emp.Address = EmpAddress
            SqlCon = New SqlConnection(connectionString)
            SqlCmd.Connection = SqlCon
            SqlCmd.CommandText = "EmpMaster_SP"
            SqlCmd.CommandType = CommandType.StoredProcedure
            SqlCmd.Parameters.AddWithValue("Mode", " ADD")
            SqlCmd.Parameters.AddWithValue("EmpCode", Emp.EmployeeCode)
            SqlCmd.Parameters.AddWithValue("EmpName", Emp.EmployeeName)
            SqlCmd.Parameters.AddWithValue("DOB", Emp.DOB)
            SqlCmd.Parameters.AddWithValue("Address", Emp.Address)
            SqlCon.Open()
            SqlCmd.ExecuteNonQuery()
            SqlCmd.Parameters.Clear()
            SqlCon.Close()
            Load_Grid()
            MessageBox.Show("Updated Successfully")

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
        End Try

I will reuse the same method & passing different mode to SP for each event (Update & Delete).

SqlCmd.Parameters.AddWithValue("Mode", "EDIT") OR SqlCmd.Parameters.AddWithValue("Mode", "DELETE")
SqlCmd.Parameters.AddWithValue("ID", Convert.ToInt32(lblEmpId.Content))

Let’s retrieve the data from database using DataGrid. Load_Grid calls to all the events.

Public Sub Load_Grid()
        Try
            SqlCon = New SqlConnection(connectionString)
            SqlCmd.Connection = SqlCon
            SqlCmd.CommandText = "EmpMaster_SP"
            SqlCmd.CommandType = CommandType.StoredProcedure
            SqlCmd.Parameters.AddWithValue("Mode", "GET")
            SqlCon.Open()
            SqlDa = New SqlDataAdapter(SqlCmd)
            Dt = New DataTable("Employee")
            SqlDa.Fill(Dt)
            dgEmp.ItemsSource = Dt.DefaultView
            SqlCmd.Parameters.Clear()
            SqlCon.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
        End Try

    End Sub

You must use Binding="{Binding XXX}" in VB.Net WPF DataGrid control.

<DataGrid.Columns>
                <DataGridTextColumn Binding="{Binding Id}" Width="130" Header="Employee Id"/>
                <DataGridTextColumn Binding="{Binding EmployeeCode}" Width="130" Header="Employee Code"/>
                <DataGridTextColumn Binding="{Binding EmployeeName}" Width="200" Header="Employee Name"/>
                <DataGridTextColumn Binding="{Binding EmployeeDob}" Width="100" Header="DOB"/>
                <DataGridTextColumn Binding="{Binding EmployeeAddress}" Width="200" Header="Employee Address"/>
            </DataGrid.Columns>
In DataGrid, by using the mousedoubleclick event for editing the recorders, data can be retrieved by employee ID.

Try
            SqlCon = New SqlConnection(connectionString)
            Dim Drv As DataRowView = DirectCast(dgEmp.SelectedItem, DataRowView)
            Dim Fd As New FlowDocument
            Dim Pg As New Paragraph

            SqlCmd.Connection = SqlCon
            SqlCmd.CommandText = "EmpMaster_SP"
            SqlCmd.CommandType = CommandType.StoredProcedure
            SqlCmd.Parameters.AddWithValue("Mode", "GETID")
            SqlCmd.Parameters.AddWithValue("ID", Convert.ToInt32(Drv("ID")))
            SqlCon.Open()


            Dim sqlReader As SqlDataReader = SqlCmd.ExecuteReader()
            If sqlReader.HasRows Then
                While (sqlReader.Read())
                    lblEmpId.Content = sqlReader.GetValue(0).ToString()
                    txtCode.Text = sqlReader.GetValue(1)
                    txtName.Text = sqlReader.GetString(2)
                    txtDate.Text = sqlReader.GetDateTime(3)
                    Pg.Inlines.Add(New Run(sqlReader.GetString(4).ToString()))
                    Fd.Blocks.Add(Pg)
                    rtxtAddress.Document = Fd

                End While

            End If

            SqlCmd.Parameters.Clear()
            SqlCon.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
        End Try
After completing the above steps, run the application.

WPF

Conclusion

In this article, we saw how to perform WPF CRUD operations using VB.NET. If you have any queries, please comment below.