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.