ASP.NET, Filtering,Sorting, Finding rows in Gridview using Dataview
In ASP.Net we are using dataset as a collection of tables. By using dataset we assign the table data to ASP.NET controls(Gridview,Repeater .. etc).
Sometimes we want to filter the data before assign to the controls. In this case what can we do. In Asp.Net we have a super object to do this, that is called Dataview.
By using Dataview object we can filer the data of DataTable. Not only filtering, we can sort the table data and we can find some specified rows also.
Advantages of Dataview:
-->Filtering the table data
-->Sorting the table data
-->we can find some specified rows
Filtering that table data:
If we want to show the table data based on some filtering, we can show by using dataview object instead of putting the condition in Sql Query.
Eg: If we have table called emp where we store the all employee data with their salary. But we want to show the list of employees who has salary more than 5000.
Then put condition like this dv.RowFilter = "salary>50000" ‘where dv is the dataview object
Dim con As New SqlConnection("your database connection string")
Dim sqldad As New SqlDataAdapter("select * from emp1", con)
Dim ds As New DataSet
sqldad.Fill(ds)
'Filtering using Dataview
Dim dv As New DataView
'fill dataview object using dataset
dv = ds.Tables(0).DefaultView
'row filter
dv.RowFilter = "salary>50000"
gv1_filter.DataSource = dv
gv1_filter.DataBind()
In this way we can filter the data by using dataview object.
Sorting the table data:
If we want to show the data in some order based on one column, we can show by using dataview object.
For example take same employee table, but in this case we want show all employee data in some order based on their salary.
‘dv.Sort = "salary asc" 'ascending order
‘dv.Sort = " salary desc" 'descending order
Dim con As New SqlConnection("your database connection string")
Dim sqldad As New SqlDataAdapter("select * from emp1", con)
Dim ds As New DataSet
sqldad.Fill(ds)
Dim dv As New DataView
'fill dataview object using dataset
dv = ds.Tables(0).DefaultView
'sorting
‘ dv.Sort = "salary" 'ascending order (default)
dv.Sort = " salary desc" 'descending order
gv1_sort.DataSource = dv
gv1_sort.DataBind()
If there is no order , by default it takes ascending order.
Removing ParticularRows:
If we want to Remove some rows based on row index, we can delete.
For example, if you want to delete rows 3,4 from employee table, then
dv.Table.Rows[0].Delete();
dv.Table.Rows[1].Delete();
Dim con As New SqlConnection("your database connection string")
Dim sqldad As New SqlDataAdapter("select * from emp1", con)
Dim ds As New DataSet
sqldad.Fill(ds)
Dim dv As New DataView
'fill dataview object using dataset
dv = ds.Tables(0).DefaultView
'removing 3,4 record
dv.Table.Rows(3).Delete()
dv.Table.Rows(4).Delete()
gv1_delete.DataSource = dv
gv1_delete.DataBind()
Finding the particular rows:
In some scenrio we want to find particular row data based on row index.
From employee table,if we want to find employee details at row 4 Then dv.find(4)
Dim con As New SqlConnection("your database connection string")
Dim sqldad As New SqlDataAdapter("select * from emp1", con)
Dim ds As New DataSet
sqldad.Fill(ds)
Dim dv As New DataView
'fill dataview object using dataset
dv = ds.Tables(0).DefaultView
Response.Write(dv.Find(4))
In this way we can use dataview object to Filter, Removing, Sorting the table data
Download source code here
