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