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','[e-mail]')

insert into EmployeeDetails values(2,'Amit','Senwal','B-44 Sarswati Kunj','[e-mail]')

insert into EmployeeDetails values(3,'Pankaj','Lohani','C-45 Sant Nagar','[e-mail]')

insert into EmployeeDetails values(4,'Pravesh','Khanduri','D-46 Pratap Vihar','[e-mail]')

insert into EmployeeDetails values(5,'Ravi','Kumar','E-47 R.K Puram','[e-mail]')

insert into EmployeeDetails values(6,'Ainul','Hasan','F-48 South-X','[e-mail]')

 

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.