ADO - ADO.NET DataView for Dynamic Data Filtering and Sorting

DataView is an important component of ADO.NET that provides a customizable, filtered, and sorted view of the data stored in a DataTable. It allows developers to display or work with a selected portion of table data without changing the original data contained in the DataTable.

A DataView is particularly useful when an application needs to show the same dataset in different ways. For example, an application may contain a DataTable with thousands of employee records. One screen may need to display only employees from the IT department, while another may need to display employees sorted by salary. Instead of creating multiple copies of the data, a DataView can provide different views of the same DataTable.

1. What is DataView?

A DataView represents a customized view of the rows contained in a DataTable. It does not normally create an independent copy of the underlying data. Instead, it provides a way to look at the existing rows according to specific filtering and sorting conditions.

The basic relationship can be understood as:

DataSet
   |
DataTable
   |
DataView
   |
Filtered and Sorted Rows

For example, suppose a DataTable contains:

ID    Name       Department    Salary
1     Ravi       IT            60000
2     Priya      HR            50000
3     Arun       IT            75000
4     Meena      Finance       65000
5     Kiran      IT            55000

A DataView can be created to show only employees from the IT department:

ID    Name       Department    Salary
1     Ravi       IT            60000
3     Arun       IT            75000
5     Kiran      IT            55000

The original DataTable remains available with all five records.

2. Creating a DataView

A DataView can be created directly from a DataTable.

DataTable employees = new DataTable();

employees.Columns.Add("ID", typeof(int));
employees.Columns.Add("Name", typeof(string));
employees.Columns.Add("Department", typeof(string));
employees.Columns.Add("Salary", typeof(decimal));

employees.Rows.Add(1, "Ravi", "IT", 60000);
employees.Rows.Add(2, "Priya", "HR", 50000);
employees.Rows.Add(3, "Arun", "IT", 75000);
employees.Rows.Add(4, "Meena", "Finance", 65000);
employees.Rows.Add(5, "Kiran", "IT", 55000);

DataView view = new DataView(employees);

Here, employees is the original DataTable, while view provides a customizable representation of its rows.

3. Filtering Data Using RowFilter

One of the most useful features of DataView is RowFilter.

RowFilter allows developers to specify a condition that determines which rows should be visible through the view.

For example:

view.RowFilter = "Department = 'IT'";

The DataView will now contain only records where the Department column is equal to IT.

The resulting data would be:

ID    Name       Department    Salary
1     Ravi       IT            60000
3     Arun       IT            75000
5     Kiran      IT            55000

The filtering operation does not delete HR or Finance records from the original DataTable. They are simply excluded from the current view.

4. Filtering Based on Numeric Values

RowFilter can also be used with numerical conditions.

For example:

view.RowFilter = "Salary > 60000";

This displays employees whose salary is greater than 60,000.

The result would be:

ID    Name       Department    Salary
3     Arun       IT            75000
4     Meena      Finance       65000

Multiple conditions can also be combined.

view.RowFilter = "Department = 'IT' AND Salary > 55000";

This returns IT employees whose salary is greater than 55,000.

5. Using OR Conditions

The OR operator can be used when multiple alternatives are acceptable.

view.RowFilter = "Department = 'IT' OR Department = 'Finance'";

This displays employees belonging to either IT or Finance.

Filtering expressions can use operators such as:

=
<>
>
<
>=
<=
AND
OR
LIKE
IN

This makes DataView useful for many data presentation requirements.

6. Sorting Data Using Sort

Another major feature of DataView is the Sort property.

For example:

view.Sort = "Salary ASC";

This sorts employees by salary in ascending order.

To sort in descending order:

view.Sort = "Salary DESC";

The resulting order would be:

Priya    50000
Kiran    55000
Ravi     60000
Meena    65000
Arun     75000

Sorting can also be performed using multiple columns.

view.Sort = "Department ASC, Salary DESC";

In this case, records are first grouped alphabetically by department, and employees within each department are ordered from highest salary to lowest salary.

7. Combining Filtering and Sorting

Filtering and sorting can be used together.

view.RowFilter = "Department = 'IT'";
view.Sort = "Salary DESC";

The result contains only IT employees and places the employee with the highest salary first.

ID    Name       Department    Salary
3     Arun       IT            75000
1     Ravi       IT            60000
5     Kiran      IT            55000

This combination is particularly useful in applications that provide search and sorting functionality.

8. Displaying DataView in a DataGridView

DataView is commonly used with Windows Forms controls such as DataGridView.

For example:

DataView view = new DataView(employees);

view.RowFilter = "Department = 'IT'";
view.Sort = "Salary DESC";

dataGridView1.DataSource = view;

The grid will display only IT employees, sorted by salary in descending order.

This approach allows the user interface to change the displayed data without modifying the underlying DataTable.

9. DataView and DataTable

It is important to understand the difference between these two components.

DataTable represents the actual in-memory table containing rows and columns.

DataView provides a customized way of looking at those rows.

For example:

DataTable employees;
DataView view;

The DataTable may contain 1,000 employee records, while a particular DataView might display only 100 records because of a filter.

If another part of the application needs a different selection, another view can be created:

DataView itEmployees = new DataView(employees);
itEmployees.RowFilter = "Department = 'IT'";

DataView hrEmployees = new DataView(employees);
hrEmployees.RowFilter = "Department = 'HR'";

Both views use the same underlying DataTable.

10. Creating Multiple Views

One of the main advantages of DataView is that different views can be created from the same table.

DataView highSalary = new DataView(employees);
highSalary.RowFilter = "Salary >= 60000";

DataView itEmployees = new DataView(employees);
itEmployees.RowFilter = "Department = 'IT'";

DataView financeEmployees = new DataView(employees);
financeEmployees.RowFilter = "Department = 'Finance'";

This is useful when different application screens require different representations of the same data.

11. DataViewManager

ADO.NET also provides DataViewManager, which can manage default views for multiple tables within a DataSet.

A DataViewManager can be useful when an application works with several related DataTable objects and needs centralized management of their views.

For example:

DataViewManager manager = new DataViewManager(dataSet);

The manager can provide views for tables contained within the DataSet.

For simple applications, however, directly creating DataView objects is generally easier.

12. Advantages of DataView

DataView provides several practical advantages.

First, it allows developers to filter data without removing records from the original DataTable.

Second, it allows data to be sorted without changing the underlying table's physical row arrangement.

Third, the same DataTable can support multiple views for different application requirements.

Fourth, it works well with data-bound controls such as DataGridView, making it useful for desktop application development.

Finally, it reduces the need to create duplicate datasets simply to display data differently.

13. DataView and Database Queries

A common question is why DataView is needed when SQL can already filter and sort data.

For example, SQL can perform:

SELECT *
FROM Employees
WHERE Department = 'IT'
ORDER BY Salary DESC;

This is generally preferable when the required filtering and sorting can be performed efficiently by the database, especially when dealing with large datasets.

However, DataView becomes useful when the data has already been loaded into a DataTable and the application needs to change the displayed view dynamically.

For example, a desktop application may retrieve employee information once and allow the user to repeatedly filter and sort the already-loaded records. In such situations, using DataView can avoid repeatedly querying the database for every display change.

14. Important Limitation

A DataView is primarily a view of data already available in memory. It should not be considered a replacement for database-side filtering.

If a database contains millions of records, loading all of them into a DataTable and then filtering them with DataView can consume significant memory and processing resources.

A better approach is often to filter large datasets at the database level:

SELECT *
FROM Employees
WHERE Department = 'IT';

Then use DataView for additional client-side filtering or presentation requirements.

15. Practical Example

Consider an employee management application where all employee information has already been retrieved.

DataView employeeView = new DataView(employees);

employeeView.RowFilter = "Department = 'IT'";
employeeView.Sort = "Salary DESC";

foreach (DataRowView row in employeeView)
{
    Console.WriteLine(
        row["Name"] + " - " +
        row["Salary"]
    );
}

The DataView first restricts the records to IT employees and then sorts them by salary in descending order.

The application can later change the view:

employeeView.RowFilter = "Salary >= 60000";
employeeView.Sort = "Name ASC";

Now the same underlying DataTable is represented according to a completely different requirement.

Conclusion

DataView is a useful ADO.NET component for creating dynamic views of data stored in a DataTable. Its primary capabilities are filtering rows through RowFilter and sorting them through Sort. It allows applications to present different subsets and arrangements of the same data without modifying or duplicating the original DataTable.

It is especially valuable in data-bound applications where users need to search, filter, and sort already-loaded information. However, for very large datasets, filtering and sorting should generally be performed at the database level first, with DataView used when client-side manipulation of an existing in-memory dataset is appropriate.