WPF

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. 

  1. USE [master]  
  2. GO  
  3. /****** Object:  Database [test]    Script Date: 5/7/2017 8:09:18 AM ******/  
  4. CREATE DATABASE [test]  
  5.  CONTAINMENT = NONE  
  6.  ON  PRIMARY   
  7. ( NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ),   
  8.  FILEGROUP [DocFiles] CONTAINS FILESTREAM  DEFAULT  
  9. ( NAME = N'FileStream', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\FileStream' , MAXSIZE = UNLIMITED)  
  10.  LOG ON   
  11. ( 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 )  
  12. GO  
  13. ALTER DATABASE [test] SET COMPATIBILITY_LEVEL = 130  
  14. GO  
  15. IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))  
  16. begin  
  17. EXEC [test].[dbo].[sp_fulltext_database] @action = 'enable'  
  18. end  
  19. GO  
  20. ALTER DATABASE [test] SET ANSI_NULL_DEFAULT OFF   
  21. GO  
  22. ALTER DATABASE [test] SET ANSI_NULLS OFF   
  23. GO  
  24. ALTER DATABASE [test] SET ANSI_PADDING OFF   
  25. GO  
  26. ALTER DATABASE [test] SET ANSI_WARNINGS OFF   
  27. GO  
  28. ALTER DATABASE [test] SET ARITHABORT OFF   
  29. GO  
  30. ALTER DATABASE [test] SET AUTO_CLOSE OFF   
  31. GO  
  32. ALTER DATABASE [test] SET AUTO_SHRINK OFF   
  33. GO  
  34. ALTER DATABASE [test] SET AUTO_UPDATE_STATISTICS ON   
  35. GO  
  36. ALTER DATABASE [test] SET CURSOR_CLOSE_ON_COMMIT OFF   
  37. GO  
  38. ALTER DATABASE [test] SET CURSOR_DEFAULT  GLOBAL   
  39. GO  
  40. ALTER DATABASE [test] SET CONCAT_NULL_YIELDS_NULL OFF   
  41. GO  
  42. ALTER DATABASE [test] SET NUMERIC_ROUNDABORT OFF   
  43. GO  
  44. ALTER DATABASE [test] SET QUOTED_IDENTIFIER OFF   
  45. GO  
  46. ALTER DATABASE [test] SET RECURSIVE_TRIGGERS OFF   
  47. GO  
  48. ALTER DATABASE [test] SET  DISABLE_BROKER   
  49. GO  
  50. ALTER DATABASE [test] SET AUTO_UPDATE_STATISTICS_ASYNC OFF   
  51. GO  
  52. ALTER DATABASE [test] SET DATE_CORRELATION_OPTIMIZATION OFF   
  53. GO  
  54. ALTER DATABASE [test] SET TRUSTWORTHY OFF   
  55. GO  
  56. ALTER DATABASE [test] SET ALLOW_SNAPSHOT_ISOLATION OFF   
  57. GO  
  58. ALTER DATABASE [test] SET PARAMETERIZATION SIMPLE   
  59. GO  
  60. ALTER DATABASE [test] SET READ_COMMITTED_SNAPSHOT OFF   
  61. GO  
  62. ALTER DATABASE [test] SET HONOR_BROKER_PRIORITY OFF   
  63. GO  
  64. ALTER DATABASE [test] SET RECOVERY FULL   
  65. GO  
  66. ALTER DATABASE [test] SET  MULTI_USER   
  67. GO  
  68. ALTER DATABASE [test] SET PAGE_VERIFY CHECKSUM    
  69. GO  
  70. ALTER DATABASE [test] SET DB_CHAINING OFF   
  71. GO  
  72. ALTER DATABASE [test] SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'DocFileDirctory' )   
  73. GO  
  74. ALTER DATABASE [test] SET TARGET_RECOVERY_TIME = 60 SECONDS   
  75. GO  
  76. ALTER DATABASE [test] SET DELAYED_DURABILITY = DISABLED   
  77. GO  
  78. EXEC sys.sp_db_vardecimal_storage_format N'test', N'ON'  
  79. GO  
  80. ALTER DATABASE [test] SET QUERY_STORE = OFF  
  81. GO   

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

  1. USE [test]  
  2. GO  
  3. /****** Object:  Table [dbo].[EmployeeMaster]    Script Date: 5/7/2017 8:07:35 AM ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. CREATE TABLE [dbo].[EmployeeMaster](  
  9.     [Id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,  
  10.     [EmployeeCode] [bigint] NULL,  
  11.     [EmployeeName] [varchar](150) NULL,  
  12.     [EmployeeDob] [datetime] NULL,  
  13.     [EmployeeAddress] [varchar](500) NULL  
  14. ) ON [PRIMARY]  
  15. 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.

  1. CREATE PROCEDURE [dbo].[EmpMaster_SP]  
  2. @ID     NUMERIC(18,0)=NULL,  
  3. @EmpCode BIGINT=NULL,  
  4. @EmpName VARCHAR(150)=NULL,  
  5. @DOB     DATETIME=NULL,  
  6. @Address VARCHAR(500)=NULL,  
  7. @Mode    VARCHAR(10)  
  8. AS  
  9. BEGIN  
  10.     SET NOCOUNT ON;  
  11.   
  12.     IF (@Mode='ADD')  
  13.     BEGIN  
  14.         INSERT INTO EmployeeMaster (EmployeeCode,EmployeeName,EmployeeDob,EmployeeAddress)  
  15.             VALUES(@EmpCode,@EmpName,@DOB,@Address)  
  16.     END  
  17.   
  18.     IF (@Mode='EDIT')  
  19.     BEGIN  
  20.         UPDATE EmployeeMaster SET EmployeeCode=@EmpCode,EmployeeName=@EmpName,EmployeeDob=@DOB,EmployeeAddress=@Address WHERE ID=@ID  
  21.               
  22.     END  
  23.   
  24.     IF (@Mode='DELETE')  
  25.     BEGIN  
  26.         DELETE FROM EmployeeMaster WHERE ID=@ID  
  27.               
  28.     END  
  29.   
  30.     IF (@Mode='GET')  
  31.     BEGIN  
  32.         SELECT Id,EmployeeCode,EmployeeName,CONVERT(VARCHAR(10), EmployeeDob)EmployeeDob,EmployeeAddress FROM EmployeeMaster  
  33.               
  34.     END  
  35.   
  36.     IF (@Mode='GETID')  
  37.     BEGIN  
  38.         SELECT Id,EmployeeCode,EmployeeName, EmployeeDob,EmployeeAddress FROM EmployeeMaster WHERE ID=@ID  
  39.               
  40.     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. 

  1. <Window x:Class="MainWindow"  
  2.         xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"  
  3.         xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"  
  4.         xmlns:d="http://schemas.microsoft.com/expression/blend/2008"  
  5.         xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"  
  6.         xmlns:local="clr-namespace:CURD_Gridvb"  
  7.         mc:Ignorable="d"  
  8.         Title="CURD" Height="700" Width="900" Background="DarkGray">  
  9.     <Grid Background="#FF474747">  
  10.         <Rectangle Fill="#FF66512F" HorizontalAlignment="Left" Height="165" Margin="76,40,0,0" Stroke="Black" VerticalAlignment="Top" Width="779"/>  
  11.         <Label x:Name="label" Content="Employee Code" HorizontalAlignment="Left" Margin="90,81,0,0" VerticalAlignment="Top" Foreground="#FFEADCDC" FontWeight="Bold" FontFamily="Tahoma"/>  
  12.         <TextBox x:Name="txtCode" HorizontalAlignment="Left" Height="30" Margin="202,75,0,0" TextWrapping="Wrap" Text="" VerticalAlignment="Top" Width="170" FontSize="14"/>  
  13.         <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"/>  
  14.         <TextBox x:Name="txtName" HorizontalAlignment="Left" Height="30" Margin="550,75,0,0" TextWrapping="Wrap" Text="" VerticalAlignment="Top" Width="235" FontSize="14"/>  
  15.         <Label x:Name="label_Copy1" Content="DOB" HorizontalAlignment="Left" Margin="90,134,0,0" VerticalAlignment="Top" Foreground="#FFEADCDC" FontWeight="Bold" FontFamily="Tahoma"/>  
  16.         <DatePicker x:Name="txtDate" HorizontalAlignment="Left" Margin="202,139,0,0" VerticalAlignment="Top" Width="170" Height="30" FontSize="14"/>  
  17.         <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"/>  
  18.         <RichTextBox x:Name="rtxtAddress" HorizontalAlignment="Left" Height="75" Margin="550,117,0,0" VerticalAlignment="Top" Width="235" FontSize="14">  
  19.             <FlowDocument>  
  20.                 <Paragraph>  
  21.                     <Run Text=""/>  
  22.                 </Paragraph>  
  23.             </FlowDocument>  
  24.         </RichTextBox>  
  25.         <Rectangle Fill="#FF472828" HorizontalAlignment="Left" Height="55" Margin="76,220,0,0" Stroke="Black" VerticalAlignment="Top" Width="755"/>  
  26.         <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"  
  27.                 />  
  28.         <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"  
  29.                 />  
  30.         <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"  
  31.                 />  
  32.         <Rectangle Fill="#FF0E2727" HorizontalAlignment="Left" Height="270" Margin="76,300,0,0" Stroke="Black" VerticalAlignment="Top" Width="755"/>  
  33.   
  34.         <DataGrid   x:Name="dgEmp" Height="270" AutoGenerateColumns="False" RowHeight="25"   
  35.                     GridLinesVisibility="Vertical" HeadersVisibility="All" RowBackground="WhiteSmoke"   
  36.                     AlternatingRowBackground="LightGray" IsReadOnly="True" Margin="76,300,61,99" >  
  37.             <DataGrid.Columns>  
  38.                 <DataGridTextColumn Binding="{Binding Id}" Width="130" Header="Employee Id"/>  
  39.                 <DataGridTextColumn Binding="{Binding EmployeeCode}" Width="130" Header="Employee Code"/>  
  40.                 <DataGridTextColumn Binding="{Binding EmployeeName}" Width="200" Header="Employee Name"/>  
  41.                 <DataGridTextColumn Binding="{Binding EmployeeDob}" Width="100" Header="DOB"/>  
  42.                 <DataGridTextColumn Binding="{Binding EmployeeAddress}" Width="200" Header="Employee Address"/>  
  43.             </DataGrid.Columns>  
  44.         </DataGrid>  
  45.         <Label x:Name="label_Copy3" Content="Employee Id" HorizontalAlignment="Left" Margin="90,45,0,0" VerticalAlignment="Top" Foreground="#FFEADCDC" FontWeight="Bold" FontFamily="Tahoma"/>  
  46.         <Label x:Name="lblEmpId" Content="" HorizontalAlignment="Left" Margin="202,45,0,0" VerticalAlignment="Top" Foreground="#FFEADCDC" FontWeight="Bold" FontFamily="Tahoma"/>  
  47.   
  48.     </Grid>  
  49. </Window>   

CRUD Operations

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

  1. Public Class Employee  
  2.     Public Property EmployeeCode As Int32  
  3.     Public Property EmployeeName As String  
  4.     Public Property DOB As Date  
  5.     Public Property Address As String  
  6. End Class   

Imports namespaces in your mainwindow.xaml.vb file.

  1. Imports System.Data  
  2. Imports System.Data.SqlClient  
  3. Imports CURD_Gridvb.Employee   

Declare global variable and connection string in the class.

  1. Dim connectionString As String = "Data Source=XXX;Initial Catalog=test;uid=sa;pwd=XXXX;"  
  2.     Dim SqlCon As SqlConnection  
  3.     Dim SqlCmd As New SqlCommand  
  4.     Dim SqlDa As SqlDataAdapter  
  5.     Dim Dt As DataTable  
  6.     Dim Query As String  
  7.     Dim ID As String   

You can validate the Textbox and Rich Textbox Controls in Add, Update, and Delete events. 

  1. If (txtCode.Text = String.Empty) Then  
  2.             MessageBox.Show("Enter the Employee Code")  
  3.             Return  
  4.         End If  
  5.   
  6.         If (txtName.Text = String.Empty) Then  
  7.             MessageBox.Show("Enter the Employee Name")  
  8.             Return  
  9.         End If  
  10.   
  11.         If (txtDate.Text = String.Empty) Then  
  12.             MessageBox.Show("Enter the Employee Name")  
  13.             Return  
  14.         End If  
  15.   
  16.         Dim EmpAddress As String  
  17.         EmpAddress = New TextRange(rtxtAddress.Document.ContentStart, rtxtAddress.Document.ContentEnd).Text.ToString()  
  18.         If (EmpAddress = String.Empty) Then  
  19.             MessageBox.Show("Enter the Employee Name")  
  20.             Return  
  21.         End If    

Copy and paste the below code in "Add" button event. 

  1. Try  
  2.             Dim Emp As New Employee  
  3.             Emp.EmployeeCode = Convert.ToInt32(txtCode.Text)  
  4.             Emp.EmployeeName = UCase(txtName.Text.Trim())  
  5.             Emp.DOB = Convert.ToDateTime(txtDate.Text)  
  6.             Emp.Address = EmpAddress  
  7.             SqlCon = New SqlConnection(connectionString)  
  8.             SqlCmd.Connection = SqlCon  
  9.             SqlCmd.CommandText = "EmpMaster_SP"  
  10.             SqlCmd.CommandType = CommandType.StoredProcedure  
  11.             SqlCmd.Parameters.AddWithValue("Mode", " ADD")  
  12.             SqlCmd.Parameters.AddWithValue("EmpCode", Emp.EmployeeCode)  
  13.             SqlCmd.Parameters.AddWithValue("EmpName", Emp.EmployeeName)  
  14.             SqlCmd.Parameters.AddWithValue("DOB", Emp.DOB)  
  15.             SqlCmd.Parameters.AddWithValue("Address", Emp.Address)  
  16.             SqlCon.Open()  
  17.             SqlCmd.ExecuteNonQuery()  
  18.             SqlCmd.Parameters.Clear()  
  19.             SqlCon.Close()  
  20.             Load_Grid()  
  21.             MessageBox.Show("Updated Successfully")  
  22.   
  23.         Catch ex As Exception  
  24.             MessageBox.Show(ex.Message.ToString())  
  25.         End Try   

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

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

Let's retrieve the data from database using DataGrid. “Load_Grid” call to all the events. 

  1. Public Sub Load_Grid()  
  2.         Try  
  3.             SqlCon = New SqlConnection(connectionString)  
  4.             SqlCmd.Connection = SqlCon  
  5.             SqlCmd.CommandText = "EmpMaster_SP"  
  6.             SqlCmd.CommandType = CommandType.StoredProcedure  
  7.             SqlCmd.Parameters.AddWithValue("Mode", "GET")  
  8.             SqlCon.Open()  
  9.             SqlDa = New SqlDataAdapter(SqlCmd)  
  10.             Dt = New DataTable("Employee")  
  11.             SqlDa.Fill(Dt)  
  12.             dgEmp.ItemsSource = Dt.DefaultView  
  13.             SqlCmd.Parameters.Clear()  
  14.             SqlCon.Close()  
  15.         Catch ex As Exception  
  16.             MessageBox.Show(ex.Message.ToString())  
  17.         End Try  
  18.   
  19.     End Sub   

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

  1. <DataGrid.Columns>  
  2.                 <DataGridTextColumn Binding="{Binding Id}" Width="130" Header="Employee Id"/>  
  3.                 <DataGridTextColumn Binding="{Binding EmployeeCode}" Width="130" Header="Employee Code"/>  
  4.                 <DataGridTextColumn Binding="{Binding EmployeeName}" Width="200" Header="Employee Name"/>  
  5.                 <DataGridTextColumn Binding="{Binding EmployeeDob}" Width="100" Header="DOB"/>  
  6.                 <DataGridTextColumn Binding="{Binding EmployeeAddress}" Width="200" Header="Employee Address"/>  
  7.             </DataGrid.Columns>   

In DataGrid which is used “mousedoubleclick” event for Editing the recorders, data can be retrieved by employee Id. 

  1. Try  
  2.             SqlCon = New SqlConnection(connectionString)  
  3.             Dim Drv As DataRowView = DirectCast(dgEmp.SelectedItem, DataRowView)  
  4.             Dim Fd As New FlowDocument  
  5.             Dim Pg As New Paragraph  
  6.   
  7.             SqlCmd.Connection = SqlCon  
  8.             SqlCmd.CommandText = "EmpMaster_SP"  
  9.             SqlCmd.CommandType = CommandType.StoredProcedure  
  10.             SqlCmd.Parameters.AddWithValue("Mode", "GETID")  
  11.             SqlCmd.Parameters.AddWithValue("ID", Convert.ToInt32(Drv("ID")))  
  12.             SqlCon.Open()  
  13.   
  14.   
  15.             Dim sqlReader As SqlDataReader = SqlCmd.ExecuteReader()  
  16.             If sqlReader.HasRows Then  
  17.                 While (sqlReader.Read())  
  18.                     lblEmpId.Content = sqlReader.GetValue(0).ToString()  
  19.                     txtCode.Text = sqlReader.GetValue(1)  
  20.                     txtName.Text = sqlReader.GetString(2)  
  21.                     txtDate.Text = sqlReader.GetDateTime(3)  
  22.                     Pg.Inlines.Add(New Run(sqlReader.GetString(4).ToString()))  
  23.                     Fd.Blocks.Add(Pg)  
  24.                     rtxtAddress.Document = Fd  
  25.   
  26.                 End While  
  27.   
  28.             End If  
  29.   
  30.             SqlCmd.Parameters.Clear()  
  31.             SqlCon.Close()  
  32.         Catch ex As Exception  
  33.             MessageBox.Show(ex.Message.ToString())  
  34.         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.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Membership not found