Learn WPF CRUD Operations Using VB.NET

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) > W_PF 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.

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

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> </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.

Conclusion

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

Happy Coding!