Reader Level:
ARTICLE

DataView Class in FSharp

Posted by A K Articles | F# November 15, 2011
The DataView class helps to view a customized record as well as provide an easy way for filtering, sorting, searching records of DataTable.
  • 0
  • 0
  • 10950

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.

dataview in f#

Step 2: Go to Solution Explorer and Right Click on References. 
 
dataview in f#

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

dataview in f#

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.

dataview in f#

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:

dataview in f#

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

dataview in f#

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

dataview in f#

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:

dataview in f#

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

dataview in f#

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:

dataview in f#

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

dataview in f#

COMMENT USING

Trending up