Introduction:
Searching records is very essential work in any
project. There may be different criteria for searching records. This article
describes record searching in F# depending on different searching criteria. Here
I am using DataView class and it's RowFilter property.
At first, we create a Database and insert records into Database. Here my
Database name is EMP and Database table name is EMP_DET. After this create a
window form and write the following code in program.fx file.
(Note:- For creating window applications in F#, Click here)
//
importing namespace
open System
open
System.Windows.Forms
open
System.Data.SqlClient
open System.Data
open
System.Drawing
// connection string
let constr =
@"Data Source=SERVER_NAME;Initial
Catalog=EMP;Integrated Security=True"
// creating Form and user interface
let frm =
new Form(Height =400, Width =400)
let
dg = new DataGrid(Top =20, Left = 20, Height
=250, Width =340)
dg.BackColor <- Color.LightBlue
let txt =
new TextBox(Top = 280, Left = 70, Width = 110)
let btn =
new Button(Top = 280, Left = 190, Height = 30,
Width = 80)
let lbl =
new Label(Top = 280, Height = 30, Width = 50)
btn.Text <- "Search"
lbl.Text <- "By First Name :"
frm.Controls.Add(lbl)
frm.Controls.Add(txt)
frm.Controls.Add(btn)
frm.Controls.Add(dg)
// creating dataadapter and filling dataset
let da =
new SqlDataAdapter("select
* from emp_det", constr)
let ds =
new DataSet()
da.Fill(ds)|> ignore
let dv =new
DataView(ds.Tables.[0])
dg.DataSource <- dv
// search button click event
btn.Click.Add(fun _->
dv.RowFilter <- "F_Name = '"+txt.Text+"'
"
dg.DataSource <-dv
)
Application.Run(frm)
Output:
Now write first name of employee to show record
for that employee and Click at search Button. Here I have written "alok"
in TextBox. The output window after clicking search button looks like as below
figure.
Like this we can get record of employee by specifying column name and
corresponding value. Like, if we want to get record of employee whose last name
is pandey, then we add the following code in above program.
dv.RowFilter <- "L_Name = '"+txt.Text+"'
"
Now suppose, we have need to filter record depending on different searching
criteria. Now we will not specify column name at first. It will be selected by
user. Look at below code.
//
importing namespace
open System
open
System.Windows.Forms
open
System.Data.SqlClient
open System.Data
open
System.Drawing
// connection string
let constr =
@"Data Source=SERVER_NAME;Initial
Catalog=EMP;Integrated Security=True"
// creating Form and user interface
let frm =
new Form(Height =400, Width =400)
frm.Text <- "DataView Class in
F#......"
let dg =
new DataGrid(Top =20, Left = 20, Height =250,
Width =340)
dg.BackColor <- Color.LightBlue
let btn =
new Button(Top = 320, Left = 80, Height = 30,
Width = 80)
let lbl1 =
new Label(Top = 280, Height = 30, Width = 50)
let txt1 =
new TextBox(Top = 280, Left = 80, Width = 110)
let lbl2 =
new Label(Top = 280, Left = 190,Height =50,
Width = 50)
let txt2 =
new TextBox(Top = 280, Left = 250, Width = 110)
btn.Text <-
"Search"
lbl1.Text <- "Column Name :"
lbl2.Text <- "To Be Search : "
frm.Controls.Add(lbl1)
frm.Controls.Add(txt1)
frm.Controls.Add(lbl2)
frm.Controls.Add(txt2)
frm.Controls.Add(btn)
frm.Controls.Add(dg)
// creating dataadapter and filling dataset
let da =
new SqlDataAdapter("select
* from emp_det", constr)
let ds =
new DataSet()
da.Fill(ds)|> ignore
let dv =new
DataView(ds.Tables.[0])
dg.DataSource <- dv
// search button click event
btn.Click.Add(fun _->
dv.RowFilter <- " "+txt1.Text+"
= '"+txt2.Text+"' "
dg.DataSource <-dv
)
Application.Run(frm)
Output:
Here you will note that searching criteria is not specified. You will write
specified column name and value for searching. I have written column name as
f_name and column value is "alok". It will show record according to
given searching criteria. Click the search Button.
Now write column name as l_name and value as "dwij". Click the
search Button.