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.

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>

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.

WPF

Conclusion

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

7 Comments

Join the conversation! Your thoughts help the community grow.

Bonnie Pettis8y ago
Hm, I ran the app from the solution in the zip file and changed the connectionstring to point to mine. I am able to add, update, and delete, but the data is not appearing in the datagrid. It only shows blank rows. When I double click a row in the datagrid, the fields in the upper portion do populate, so the data is there. I thought maybe the text color in the DataGrid was the same as background, but when I tried to highlight to copy, there was nothing there.
paul paul9y ago
It is very nice.;Thanks a lotCan you doing the same thing with C# : WPF CRUD Operations Using C#
- Thiruppathi R9y ago
  Oh yes,why not.Thanks for your comment..
Sarathkumar Palanisamy9y ago
Nice one... keep going on the same way
- Thiruppathi R9y ago
  Thanks so much for your comment!...
Rafnas T P9y ago
Nice begin, keep sharing.....
- Thiruppathi R9y ago
  Thanks for your comment!..