Search Records in Windows Form From SQL Server

Introduction

This article explains how to search the records from a database in a Windows Forms application. I have used the database as SQL Server 2012 and Visual Studio 2012 as the IDE.

Use the following procedure to create the database and table in SQL Server:

create database Records
use Records
create table EmployeeDetails(
EmployeeId int,
FirstName varchar(max),
LastName varchar(max),Address nvarchar(max),
EmailId nvarchar(max)
)

Use the following procedure to insert values into the database table fields:

insert into EmployeeDetails values(1,'Nimit','Joshi','A-43 Vinod Nagar','[email protected]')
insert into EmployeeDetails values(2,'Amit','Senwal','B-44 Sarswati Kunj','[email protected]')
insert into EmployeeDetails values(3,'Pankaj','Lohani','C-45 Sant Nagar','[email protected]')
insert into EmployeeDetails values(4,'Pravesh','Khanduri','D-46 Pratap Vihar','[email protected]')
insert into EmployeeDetails values(5,'Ravi','Kumar','E-47 R.K Puram','[email protected]')
insert into EmployeeDetails values(6,'Ainul','Hasan','F-48 South-X','[email protected]')

The following is the query to ensure that the values were inserted:

select * from EmployeeDetails

Now  I want to show you how to search the records in the database table from the Windows Forms application. Let's use the following steps:

Step 1

Open Visual Studio, then select "Create New Project" --> F# Console Application.

Step 2

Now go the Solution Explorer on the right side of the application. Select the references and right-click on it and select "Add references".

Step 3:

After selecting "Add References", in the framework template you need to select "System.Windows.Forms", "System.Drawing" and "System.Data" while holding down the Ctrl key and click on "Ok."

Step 4:

Write the following code to search the records in the F# editor.

open System  
open System.Windows.Forms  
open System.ComponentModel  
open System.Data  
open System.Drawing  
open System.Data.SqlClient  
let constring = @"Data Source=MCNDESKTOP34;Initial Catalog=Records;User ID=; Password="
let dataadpter = new SqlDataAdapter("Select * from EmployeeDetails", constring)    
let dataset11 = new DataSet()   
dataadpter.Fill(dataset11,"Emptbl")|>ignore   
let searchdataform = new Form(Text="Searching records from database")     
let searchbutton=new Button(Text="Search",Top=470,Left=70)  
let exitbutton=new Button(Text="Exit",Top=470,Left=170)   
let lblempid=new Label(Text="Enter the Employee ID:",Top=10,Width=170,Left=10)  
let lblid=new Label(Text="EmpID:",Top=50)  
let lblfname=new Label(Text="Firstname:",Top=100)  
let lbllname=new Label(Text="Lastname:",Top=150)  
let lbladdress=new Label(Text="Address:",Top=200) 
let lblemailid=new Label(Text="EmailId:",Top=250) 
let empnotext=new TextBox(Top=10,Left=200)  
let emplabel=new Label(Top=50,Left=100,BorderStyle=BorderStyle.FixedSingle)  
let fnamelabel=new Label(Top=100,Left=100,BorderStyle=BorderStyle.FixedSingle)  
let lnamelabel=new Label(Top=150,Left=100,BorderStyle=BorderStyle.FixedSingle) 
let addresslabel=new Label(Top=200,Left=100,Width=150,BorderStyle=BorderStyle.FixedSingle) 
let emailidlabel=new Label(Top=250,Left=100,Width=150,BorderStyle=BorderStyle.FixedSingle)  
let ffont=new Font("Arial", 9.75F,FontStyle.Regular)  
let datagrid = new DataGridView(Top=300,Left=50,Width=530,Height=140)  
let empidcol=new DataGridViewTextBoxColumn()  
let fnamecol=new DataGridViewTextBoxColumn()  
let lnamecol=new DataGridViewTextBoxColumn()  
let addresscol=new DataGridViewTextBoxColumn()  
let emailcol=new DataGridViewTextBoxColumn()  
//adds the columns into our datagrid  
datagrid.Columns.Add(empidcol)|>ignore
datagrid.Columns.Add(fnamecol)|>ignore
datagrid.Columns.Add(lnamecol)|>ignore
datagrid.Columns.Add(addresscol)|>ignore
datagrid.Columns.Add(emailcol)|>ignore
datagrid.DataSource <- dataset11.Tables.["Emptbl"]
searchdataform.Font<-ffont
empidcol.DataPropertyName<-"EmployeeId"
empidcol.HeaderText<-"Employee ID."
fnamecol.DataPropertyName<-"FirstName"
fnamecol.HeaderText<-"First Name"
lnamecol.DataPropertyName<-"LastName"
lnamecol.HeaderText<-"Last Name"
addresscol.DataPropertyName<-"Address"
addresscol.HeaderText<-"Address"
emailcol.DataPropertyName<-"EmailId"
emailcol.HeaderText<-"EmailId"
searchdataform.Controls.Add(datagrid)
//adding the user controsl in form
searchdataform.Controls.Add(exitbutton)  
searchdataform.Controls.Add(searchbutton)  
searchdataform.Controls.Add(lblempid)  
searchdataform.Controls.Add(lblid)  
searchdataform.Controls.Add(lblfname)  
searchdataform.Controls.Add(lbllname)  
searchdataform.Controls.Add(lbladdress) 
searchdataform.Controls.Add(lblemailid) 
searchdataform.Controls.Add(empnotext)  
searchdataform.Controls.Add(emplabel)  
searchdataform.Controls.Add(fnamelabel)  
searchdataform.Controls.Add(lnamelabel)  
searchdataform.Controls.Add(addresslabel)  
searchdataform.Controls.Add(emailidlabel)  
emplabel.Text<-Convert.ToString(dataset11.Tables.["Emptbl"].Rows.Item(0).Item(0))  
fnamelabel.Text<-Convert.ToString(dataset11.Tables.["Emptbl"].Rows.Item(0).Item(1))  
lnamelabel.Text<-Convert.ToString(dataset11.Tables.["Emptbl"].Rows.Item(0).Item(2))  
addresslabel.Text<-Convert.ToString(dataset11.Tables.["Emptbl"].Rows.Item(0).Item(3))
emailidlabel.Text<-Convert.ToString(dataset11.Tables.["Emptbl"].Rows.Item(0).Item(4))
searchbutton.Click.Add(fun search->               
let mutable introws=0
let mutable blnfound=false
let mutable inttotrec=Convert.ToInt32(dataset11.Tables.["Emptbl"].Rows.Count)
let strtext=Convert.ToString(empnotext.Text)  
while((blnfound=false) && (introws<=inttotrec-1)) do
let strempnum=Convert.ToString(dataset11.Tables.["Emptbl"].Rows.Item(introws).Item(0))  
if strtext.ToUpper()=strempnum.ToUpper() then  
     blnfound<-true  
     emplabel.Text<-Convert.ToString(dataset11.Tables.["Emptbl"].Rows.Item(introws).Item(0))  
     fnamelabel.Text<-Convert.ToString(dataset11.Tables.["Emptbl"].Rows.Item(introws).Item(1))  
     lnamelabel.Text<-Convert.ToString(dataset11.Tables.["Emptbl"].Rows.Item(introws).Item(2))   
     addresslabel.Text<-Convert.ToString(dataset11.Tables.["Emptbl"].Rows.Item(introws).Item(3)) 
     emailidlabel.Text<-Convert.ToString(dataset11.Tables.["Emptbl"].Rows.Item(introws).Item(4)) 
     introws<-introws + 1
     if blnfound=false then  
MessageBox.Show("Record not found.","Search",MessageBoxButtons.OK,MessageBoxIcon.Information)|>ignore)
exitbutton.Click.Add(fun exit->
searchdataform.Close())
Application.Run(searchdataform)  

Step 5:

Debug the application by pressing F5 to execute the Windows Forms application. After debugging the application the output will be as shown in the following figure..

Step 6 :

Enter the EmployeeId to search the records from the database table.

Step 7 :

Now you need to just click on the Search Button.

Step 8 :

After clicking on the Search Button you will get and search the records from the database.

Step 9 :

When an employee does not exist in the database table it shows the following message to the user as in the figure given below.

Summary

In this article we explained how to search the records from a database table in a Windows Forms application.