Searching Records using RowFilter in F#


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:

searching record in f#

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.

searching record in f#

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:

searching record in f#

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. 

searching record in f#

Now write column name as l_name and value as "dwij". Click the search Button. 

searching record in f#