Using WPF CRUD Operations With VB.NET

in this article, we are going to:

1. create a database.
2. create stored procedures.
3. create a wpf application in vb.net.
4. perform crud operations.

1. create a database

open sql server 2016. then, click the 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 a 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 

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

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

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

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

import namespaces in your mainwindow.xaml.vb file.

imports system.data  
imports system.data.sqlclient  
imports curd_gridvb.employee  

declare global variables and connection strings 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 and add, update, and ddelete 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 and pass different modes 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 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>  
                <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, 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.