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:

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.