Learn WPF CRUD Operations Using VB.NET

Introduction

This article demonstrates CRUD operations done in Windows Presentation Foundation (WPF) using VB.NET in Visual Studio 2015

In this article, we are going to

• Create database.
• Create stored procedure.
• Create WPF Application in VB.NET.
• Perform CRUD operations.

Create Database

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

USE [master]
GO
/****** Object:  Database [test]    Script Date: 5/7/2017 8:09:18 AM ******/
CREATE DATABASE [test]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ),
 FILEGROUP [DocFiles] CONTAINS FILESTREAM  DEFAULT
( NAME = N'FileStream', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\FileStream' , MAXSIZE = UNLIMITED)
 LOG ON
( NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [test] SET COMPATIBILITY_LEVEL = 130
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [test].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [test] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [test] SET ANSI_NULLS OFF
GO
ALTER DATABASE [test] SET ANSI_PADDING OFF
GO
ALTER DATABASE [test] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [test] SET ARITHABORT OFF
GO
ALTER DATABASE [test] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [test] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [test] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [test] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [test] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [test] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [test] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [test] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [test] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [test] SET  DISABLE_BROKER
GO
ALTER DATABASE [test] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [test] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [test] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [test] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [test] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [test] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [test] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [test] SET RECOVERY FULL
GO
ALTER DATABASE [test] SET  MULTI_USER
GO
ALTER DATABASE [test] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [test] SET DB_CHAINING OFF
GO
ALTER DATABASE [test] SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'DocFileDirctory' )
GO
ALTER DATABASE [test] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [test] SET DELAYED_DURABILITY = DISABLED
GO
EXEC sys.sp_db_vardecimal_storage_format N'test', N'ON'
GO
ALTER DATABASE [test] SET QUERY_STORE = OFF
GO

I have created database named “Test”. Now, let's create a new table.

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]
GO

Create Store procedure

Probably, we have 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 WPF Application in VB.NET

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

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

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>

CRUD Operations

Create “Model” folder in Solution Explorer and create 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

Imports namespaces in your mainwindow.xaml.vb file.

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

Declare global variable and connection string in the class.

Dim connectionString As String = "Data Source=XXX;Initial Catalog=test;uid=sa;pwd=XXXX;"
    Dim SqlCon As SqlConnection
    Dim SqlCmd As New SqlCommand
    Dim SqlDa As SqlDataAdapter
    Dim Dt As DataTable
    Dim Query As String
    Dim ID As String

You can validate the Textbox and Rich Textbox Controls in 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 "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 and pass different mode to SP for each event (Update and 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” call 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 which is used “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.

Conclusion

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