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] [bigintNULL,
  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.     IF (@Mode=‘ADD’)
  12.     BEGIN
  13.         INSERT INTO EmployeeMaster (EmployeeCode,EmployeeName,EmployeeDob,EmployeeAddress)
  14.             VALUES(@EmpCode,@EmpName,@DOB,@Address)
  15.     END
  16.     IF (@Mode=‘EDIT’)
  17.     BEGIN
  18.         UPDATE EmployeeMaster SET EmployeeCode=@EmpCode,EmployeeName=@EmpName,EmployeeDob=@DOB,EmployeeAddress=@Address WHERE ID=@ID
  19.     END
  20.     IF (@Mode=‘DELETE’)
  21.     BEGIN
  22.         DELETE FROM EmployeeMaster WHERE ID=@ID
  23.     END
  24.     IF (@Mode=‘GET’)
  25.     BEGIN
  26.         SELECT Id,EmployeeCode,EmployeeName,CONVERT(VARCHAR(10), EmployeeDob)EmployeeDob,EmployeeAddress FROM EmployeeMaster
  27.     END
  28.     IF (@Mode=‘GETID’)
  29.     BEGIN
  30.         SELECT Id,EmployeeCode,EmployeeName, EmployeeDob,EmployeeAddress FROM EmployeeMaster WHERE ID=@ID
  31.     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&#8221;
  3.         xmlns:x=http://schemas.microsoft.com/winfx/2006/xaml&#8221;
  4.         xmlns:d=http://schemas.microsoft.com/expression/blend/2008&#8221;
  5.         xmlns:mc=http://schemas.openxmlformats.org/markup-compatibility/2006&#8221;
  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.         <DataGrid   x:Name=“dgEmp” Height=“270” AutoGenerateColumns=“False” RowHeight=“25”
  34.                     GridLinesVisibility=“Vertical” HeadersVisibility=“All” RowBackground=“WhiteSmoke”
  35.                     AlternatingRowBackground=“LightGray” IsReadOnly=“True” Margin=“76,300,61,99” >
  36.             <DataGrid.Columns>
  37.                 <DataGridTextColumn Binding=“{Binding Id}” Width=“130” Header=“Employee Id”/>
  38.                 <DataGridTextColumn Binding=“{Binding EmployeeCode}” Width=“130” Header=“Employee Code”/>
  39.                 <DataGridTextColumn Binding=“{Binding EmployeeName}” Width=“200” Header=“Employee Name”/>
  40.                 <DataGridTextColumn Binding=“{Binding EmployeeDob}” Width=“100” Header=“DOB”/>
  41.                 <DataGridTextColumn Binding=“{Binding EmployeeAddress}” Width=“200” Header=“Employee Address”/>
  42.             </DataGrid.Columns>
  43.         </DataGrid>
  44.         <Label x:Name=“label_Copy3” Content=“Employee Id” HorizontalAlignment=“Left” Margin=“90,45,0,0” VerticalAlignment=“Top” Foreground=“#FFEADCDC” FontWeight=“Bold” FontFamily=“Tahoma”/>
  45.         <Label x:Name=“lblEmpId” Content=“” HorizontalAlignment=“Left” Margin=“202,45,0,0” VerticalAlignment=“Top” Foreground=“#FFEADCDC” FontWeight=“Bold” FontFamily=“Tahoma”/>
  46.     </Grid>
  47. </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.         If (txtName.Text = String.Empty) Then
  6.             MessageBox.Show(“Enter the Employee Name”)
  7.             Return
  8.         End If
  9.         If (txtDate.Text = String.Empty) Then
  10.             MessageBox.Show(“Enter the Employee Name”)
  11.             Return
  12.         End If
  13.         Dim EmpAddress As String
  14.         EmpAddress = New TextRange(rtxtAddress.Document.ContentStart, rtxtAddress.Document.ContentEnd).Text.ToString()
  15.         If (EmpAddress = String.Empty) Then
  16.             MessageBox.Show(“Enter the Employee Name”)
  17.             Return
  18.         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.         Catch ex As Exception
  23.             MessageBox.Show(ex.Message.ToString())
  24.         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. 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.     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.             SqlCmd.Connection = SqlCon
  7.             SqlCmd.CommandText = “EmpMaster_SP”
  8.             SqlCmd.CommandType = CommandType.StoredProcedure
  9.             SqlCmd.Parameters.AddWithValue(“Mode”“GETID”)
  10.             SqlCmd.Parameters.AddWithValue(“ID”, Convert.ToInt32(Drv(“ID”)))
  11.             SqlCon.Open()
  12.             Dim sqlReader As SqlDataReader = SqlCmd.ExecuteReader()
  13.             If sqlReader.HasRows Then
  14.                 While (sqlReader.Read())
  15.                     lblEmpId.Content = sqlReader.GetValue(0).ToString()
  16.                     txtCode.Text = sqlReader.GetValue(1)
  17.                     txtName.Text = sqlReader.GetString(2)
  18.                     txtDate.Text = sqlReader.GetDateTime(3)
  19.                     Pg.Inlines.Add(New Run(sqlReader.GetString(4).ToString()))
  20.                     Fd.Blocks.Add(Pg)
  21.                     rtxtAddress.Document = Fd
  22.                 End While
  23.             End If
  24.             SqlCmd.Parameters.Clear()
  25.             SqlCon.Close()
  26.         Catch ex As Exception
  27.             MessageBox.Show(ex.Message.ToString())
  28.         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.

Advertisements