DataView Class in FSharp

Introduction:

The DataView is Class of Disconnected Architecture of the .NET framework. It represent customized views of a Table and provide facility for sorting, filtering, searching the records by its given properties. There is not need to write SQL statements for that. Here, we will use some DataView's properties in an F# application.

At first we should have a Database. So we create a Database and insert some records into the Database Table. Write the following SQL statements.

CREATE DATABASE EMP

USE EMP

CREATE TABLE EMP_DET
(
ID INT PRIMARY KEY,
F_NAME VARCHAR(20),
L_NAME VARCHAR(20),
SALARY VARCHAR(10)
)
 
INSERT INTO EMP_DET VALUES(1,'DEEPAK','DWIJ','10000')
INSERT INTO EMP_DET VALUES(2,'ALOK','PANDEY','12000')
INSERT INTO EMP_DET VALUES(3,'AMIT','SINGH','13000')
INSERT INTO EMP_DET VALUES(4,'RAJESH','TRIPATHI','15000')
INSERT INTO EMP_DET VALUES(5,'PRAMOD','SHARMA','17000')
INSERT INTO EMP_DET VALUES(6,'SATISH','KUMAR','11000')
INSERT INTO EMP_DET VALUES(7,'VINEET','MISHRA','12000')

Creating an F# Application: Follow the given steps.

Step 1: Open Visual Studio and create an F# application.

Step 2: Go to Solution Explorer and Right Click on References. 
 

Step 3: Click on Add References. Then a pop-up window with caption Add Reference will open

Step 4:Click on .NET in the Add Reference window and select System.Windows.Forms, System.Drawing and System.Data with holding down Ctrl key and Click on Ok.

Step 5: Now we use DataView's properties in our program.

Sort: This is use to sort the column in ascending order or descending order. The default sorting order is ascending. 

// 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 defsortbtn = new Button(Top = 280, Left = 30, Height = 30, Width = 80)
let ascsortbtn = new Button(Top = 280, Left = 140, Height = 30, Width = 80)
let dessortbtn = new Button(Top = 280, Left = 250, Height = 30, Width = 100)
defsortbtn.BackColor <- Color.LightGray
defsortbtn.Text <- "Default Sorting"
ascsortbtn.BackColor <- Color.LightGray
ascsortbtn.Text <- "Sort By Fisrt Name"
dessortbtn.BackColor <- Color.LightGray
dessortbtn.Text <- "Sort By Last Name(desc order)"
frm.Controls.Add(defsortbtn)
frm.Controls.Add(ascsortbtn)
frm.Controls.Add(dessortbtn)
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
//     defsortbtn click event
defsortbtn.Click.Add( fun _->
v.Sort <- "F_Name"
dg.DataSource <- dv
)
//     ascsortbtn click event
ascsortbtn.Click.Add( fun _->
dv.Sort <- "F_Name asc"
dg.DataSource <- dv
)
//    dessortbtn click event
dessortbtn.Click.Add( fun _->
dv.Sort <- "L_Name desc"
dg.DataSource <- dv
)
Application.Run(frm)

Output:



Now Click at Default Sorting or Sort By Fisrt Name. It will sort the F_Name column in asending order.



Sort By Last Name(desc order) Button click sorts Last Name in Descending order.



Count: The count properties return the number of record in DataView. 

// 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 filterbtn = new Button(Top = 280, Left = 30, Height = 30, Width = 80)
filterbtn.Text <- "Count"
frm.Controls.Add(filterbtn)
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
filterbtn.Click.Add( fun _->
MessageBox.Show("No.of Records : " + dv.Count.ToString())|>ignore
)
Application.Run(frm)

Output:



Click on the Button. The total number of record will be show in a message box.



RowFilter: The rowfilter property filter the row of DataView.
Suppose we want to display record of employee whose last name is PANDEY. Here, we will use rowfilter property of DataView.

// 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 filterbtn = new Button(Top = 280, Left = 30, Height = 30, Width = 80)
filterbtn.Text <- "Row Filter"
frm.Controls.Add(filterbtn)
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
filterbtn.Click.Add( fun _->
// filter by last name as pandey
dv.RowFilter <- "L_Name = 'pandey'"
dg.DataSource <- dv
)
Application.Run(frm)

Output:

Now Click on the Row Filter Button. It will show the record whose last name is PANDEY.