MSDN Documentation

ADO.NET DataViews

DataViews provide a flexible way to view and manipulate data from a DataTable. They allow you to sort, filter, and navigate through the data without modifying the original DataTable itself. This is particularly useful in UI scenarios where you need to present data in different ways to the user.

What is a DataView?

A DataView object is an in-memory representation of the data in a DataTable. It doesn't copy the data; instead, it acts as a dynamic view, allowing you to:

• Sort: Reorder rows based on one or more columns.
• Filter: Select a subset of rows based on specified criteria.
• Navigate: Move through the filtered and sorted rows.
• Update: Although the DataView itself is a view, changes made through it are reflected in the underlying DataTable, and vice-versa (with some caveats).

Creating a DataView

You can create a DataView by instantiating the DataView class and passing a DataTable to its constructor:

// Assuming 'dt' is an existing DataTable
DataView dv = new DataView(dt);
                

You can also create a DataView from a DataSet, which will automatically create a DataView for the first table in the DataSet. To specify a particular table:

// Assuming 'ds' is an existing DataSet and 'MyTable' is the name of the DataTable
DataView dv = new DataView(ds.Tables["MyTable"]);
                

Sorting Data

The Sort property of a DataView allows you to specify the sort order. The syntax is similar to SQL's ORDER BY clause.

// Sort by 'LastName' in ascending order, then 'FirstName' ascending
dv.Sort = "LastName ASC, FirstName ASC";

// Sort by 'HireDate' in descending order
dv.Sort = "HireDate DESC";
                

If you set the Sort property to an empty string (""), the default order of the rows in the underlying table will be restored.

Filtering Data

The RowFilter property is used to filter the rows displayed by the DataView. The syntax resembles SQL's WHERE clause.

// Filter for rows where 'City' is 'London'
dv.RowFilter = "City = 'London'";

// Filter for rows where 'Salary' is greater than 50000
dv.RowFilter = "Salary > 50000";

// Combine multiple conditions
dv.RowFilter = "Country = 'USA' AND Age > 30";
                

Supported operators include:

• Comparison: >, <, >=, <=, =, <>, BETWEEN
• Logical: AND, OR, NOT
• Wildcard: LIKE with % and _

Applying Sorting and Filtering Together

You can apply both sorting and filtering simultaneously. The RowFilter is applied first, and then the Sort property is applied to the filtered results.

dv.RowFilter = "OrderDate >= '2023-01-01'";
dv.Sort = "CustomerID ASC";
                

Navigating and Accessing Data

You can iterate through the rows of a DataView using a `foreach` loop or by accessing rows by their index.

foreach (DataRowView rowView in dv)
{
    // Access data using column names or index
    Console.WriteLine($"ID: {rowView["CustomerID"]}, Name: {rowView["CustomerName"]}");
}

// Accessing a specific row by index
DataRowView specificRow = dv[0]; // The first row in the view
Console.WriteLine($"First row City: {specificRow["City"]}");
                

Updating Data Through DataView

Changes made to the data through the DataView are propagated to the underlying DataTable. You can add, delete, and modify rows via the DataView.

// Modify a row
dv[0]["City"] = "New York";

// Add a new row (create a DataRow first)
DataRow newRow = dt.NewRow();
newRow.ItemArray = new object[] { 101, "Jane Doe", "London", 35000 };
dt.Rows.Add(newRow); // Add to DataTable, DataView will reflect it

// Delete a row
dv.Delete(1); // Deletes the row at index 1 in the DataView
                

Benefits of Using DataViews

• Performance: Sorting and filtering are performed in memory, which is typically faster than re-querying the database for different views.
• UI Integration: Easily bind DataView objects to UI controls like DataGridViews (in Windows Forms) or GridViews (in ASP.NET) for dynamic data presentation.
• Reduced Database Load: Avoids multiple round trips to the database to fetch slightly different data sets.
• Data Presentation Flexibility: Allows multiple views of the same data from a single DataTable.

DataViews are a powerful tool in ADO.NET for managing and presenting data efficiently. Understanding their capabilities for sorting, filtering, and navigation can significantly enhance your data-driven applications.