DataTables are a great solution for caching data in memory.  In fact, out of the box, there is no .NET class that is more suited for the task than a DataTable.

 

But, a DataTable, in and of itself, is not flexible enough to support sorting, searching and filtering of data.  The only 2 methods that a DataTable has that can be used for searching are:  the Find method, which lets you locate a row based on its primary key value; and the other is the Select method, which returns an array of rows based on filter criteria.  When you want more flexibility for performing sorting, searching, and filtering, you’ll want to use a DataView.

 

A DataView was essentially designed to be a view of the data that is stored in a DataTable.  It is not a separate cache of data, so it does not maintain its own copy of data.  But, it provides a more flexible way of working with the data that is stored in a DataTable, and in many times is actually used in place of a DataTable.

 

Every DataTable has a Default DataView.  If you didn’t know that, consider this:

 

 

 

Setting Things Up…

First, let’s create a new Visual Basic Windows Forms Application project called “WorkingWithDataViews”.  Add 2 DataGridViews to the form and set the AutoSizeColumnMode property for each to “DisplayedCells”, then add 2 labels, and a button.  Mine looks like this:

 

 

 

Next, before we get started working with DataView’s, let’s create a DataTable and populate it with some data so that we have something to work with.

 

If you are new to DataTables, then you may want to take a look at 2 other articles that I’ve written about them:

 

ADO.NET:  DataTables, An Introduction

 

ADO.NET:  DataTables, In-depth

 

 

Create an Employee DataTable, then fill it with some employees.

 

You can just double-click on Button1, and insert this code into the Button1_Click event handler, if you want.

 

 

The Employees table above will be used for all of the DataView examples below, except for the Sorting dates example.

 

 

How DataViews Work…

As mentioned above, a DataView is simply a view of the data that is stored in a DataTable.  It is not a separate copy of the data.  Therefore, viewing the contents of a DataView is just like viewing the contents of a DataTable.

 

To demonstrate this, let’s take a look at this code added to the Button1_Click event handler:

 

 

Notice that DataGridView1 is displaying data directly from the DataTable, and DataGridView2 is displaying data directly from the table’s default DataView

 

Now, run the project (F5), and click Button1. 

 

You’ll immediately notice that both DataGridViews look exactly the same.  They are both displaying the same data.

 

 

 

DataViews are only a view of the data

To prove that the DataView is simply a view of the data stored in the DataTable, and not a separate copy:  in DataGridView1 (top DataGridView), change the LastName for Paola from “Rael” to “Lima”.  Make sure to move to a different cell after changing the name.

 

Notice that in DataGridView2 (bottom DataGirdView), Paola’s name immediately changes, so that both DataGridViews are displaying the same data.

 

Here’s how it looks:

 

 

 

Another thing to notice is that the DataGridViews are not in synch:  Their row positions are separate from each other.  So, if in DataGridView1, you click on the cell containing “Anthony”, DataGridView2 does not immediately move to that row.  Furthermore, if you click on a cell of any row in DataGridView2, DataGridView1 does not automatically move to that row.

 

 

DataView References

Now, let’s go back and change the code so that both DataGridViews are using DataViews, just like I’ve done here:

 

 

Now run the program again, and notice that both DataGridViews are displaying the exact same data.

 

In DataGridView1, try clicking on cells that are in different rows.  Notice that DataGridView2 automatically moves to the same row that DataGridView1 is on (although not the same cell).  Try clicking on different cells in DataGridView2, and notice that DataGridView1 automatically moves to the same row.

 

Important notice

There is something that I would really like you to notice here.  Even though my code example above declared two different variables (view1 and view2), both variables are actually pointing to the same DataView in memory, which in this case is the table’s DefaultView.

 

This is very important to understand.  Because, any sorting or filtering applied to one DataView, automatically affects the other.

 

Let’s test this out by sorting view1 by the FirstName column:

 

 

Now, run the program (F5) and look at the results.  The data in both DataGridViews are sorted by the FirstName column, as you can see:

 

 

 

DataViews have a constructor

If you were to try to use multiple DataViews for displaying data from a DataTable in different controls on the same form, like we are above, you may find yourself frustrated when you realize they are all in synch, especially if you are trying to apply different sorting and filtering for each DataView.

 

This is a mistake I made when I first started programming with DataViews.

 

Fortunately, the DataView has a constructor that can be used to create a completely separate DataView object in memory.  This means that any sorting and filtering applied to that view does not affect any other view that references the same DataTable.  Here’s an example of what I mean:

 

 

Now run the program again (F5), and notice that DataGridView1 is sorted, but DataGridView2 is not sorted.

 

 

 

Sorting…

The examples above have already shown you how to sort a column, using the “FirstName” column.  When sorting was applied, the FirstName column was automatically sorted in Ascending order.

 

There are 2 directions that sorting can be applied:  In Ascending (ASC) order, and in Descending (DESC) order.  Ascending is the default.

 

To specify which direction to apply when sorting, simply add the “ASC” or “DESC” keywords after the column you wish to sort, as such:

 

 

 

Sorting by multiple columns

Sorting can not only be applied to one column, but it can also be applied to multiple columns.  All you have to do is list the columns that you want to sort by, separated by a comma. 

 

For example, to sort by LastName, and then FirstName, use the following syntax:

 

 

To specify the sort direction for each individual column, simply add the ASC or DESC keywords after the column names:

 

 

 

Data Types affect sorting

Another very important note is that the Data Type of a column affects the way the data is sorted.  Because Strings sort one way, Integers sort another way, Dates sort another way, etc…  So if your sorting does not work the way you expect, go back and check the Data Type of the column you are trying to sort, and make sure it is correct for the type of data that is being stored in it.

 

Let me give you an example:

 

 

Take a look at the result here:  It’s not sorted correctly. 

 

 

 

Notice that the first date is January 2008, then January 2009, then October 2007, then October 2008, etc.

 

That’s why it’s very important to make sure your columns have the correct data type. 

 

 

Now, let’s fix this problem by setting the Date column’s data type to Date, and then try sorting again:

 

 

And the result:

 

 

 

Searching…

Not only can DataViews be used for sorting, but they can be used for Searching as well.  The Find and FindRows methods are the two methods that the DataView uses to perform searches.

 

Set the Sort property First

Before searching can be performed using the Find or FindRows methods, the Sort property must be set.  It can be set to 1 column (as “FirstName”) or to multiple columns (as "LastName, FirstName”).  Setting the Sort property on a DataView causes the corresponding DataTable to create an index on each column referenced.

 

Using the Find method

When a search is performed using the Find method, you pass the value you want to search for (eg.:  “Gary”), or an array of values you want to search for (eg.:  “Paola”, “Rael”), that correspond to the columns applied to the Sort property.  When a match is found, an integer is returned that indicates the index of the matching row within the DataView.  If no matching row is found, then a –1 is returned.

 

Here’s an example of using the Find method to search for “Gary”:

 

 

 

Here’s another example of using the Find method to search for multiple values (a FirstName and a LastName) matching “Paola" “Rael”:

 

 

 

Using the FindRows method

The FindRows method can be used to return an array of rows that match the value(s), you are searching for.  This is useful when there may be multiple rows containing the same value(s). 

 

If you’ve been looking at the data we’ve been using for demonstration, you’ll notice that there are two persons with the last name of “Rael”, they are “Paola” and “Anthony”.

 

Let’s use the FindRows method to display rows that have a LastName of “Rael":

 

 

 

Remember that, like the Find method, the FindRows method can search for multiple values that correspond the columns applied to the Sort property.

 

So, let’s see an example of searching for rows that have a FirstName of “Paola” and a LastName of “Rael”:

 

 

 

Filtering…

Ok, so now we’ve learned how to Sort a DataView, and how to Search it using the Find and FindRows method.  Now we’re ready for filtering.

 

Filtering is, in my opinion, one of the most useful features of a DataView.  It is very common to have a DataTable filled with records, and then filter a DataView so that a subset of the records can be displayed on a form for a user.

 

There are, essentially, two filters that can be applied to a DataView, they are:  a RowFilter and a RowStateFilter.

 

Let’s begin by looking at the RowFilter.

 

Using the RowFilter

Filtering rows in a DataView is as easy as setting the RowFilter property to an expression that is similar to an SQL WHERE clause.  You have to include the name of the column(s) you want to filter, and the value(s) you want to filter by.

 

An important thing to keep in mind, is the type of data in the column you are filtering.  If a column contains data of a String data type, then be sure to enclose your criteria in single quotes (‘), if a column contains data of a Date data type, then be sure to enclose your criteria in a pound character (#), if your column contains data of a numeric data type, like Integer, then don’t use any enclosure.

 

Here are some examples:

 

 

 

Wild Cards

The RowFilter property allows you to perform searches using wild cards at either the beginning or ending of a string search.  Either * or % can be used as a wild card.

 

When you use a wild card character to perform a search, you must also use the LIKE keyword, instead of the equal (=) sign.

 

As a note, wild card characters can only be used to search columns that have a String data type.

 

Here are some examples of performing wild card searches:

 

 

 

Not only can simple searches be performed, as demonstrated above, but complex searches can be performed as well.  Complex searches use more search more than 1 column for matching criteria.

 

When performing a complex search, use the AND or OR keywords.

 

Here are some examples performing complex wild card searches:

 

 

 

Using the RowStateFilter

The RowStateFilter property allows you to filter a DataView by the state of the rows in the table.  For example, a new row that has just been added to the table has a RowState of “Added”; conversely, a row that has been deleted has a RowState of “Deleted”. 

 

The RowStateFilter can be set to any of the following DataViewRowState enumerations:

 

    -  Added:  Filter for Added rows

    -  CurrentRows:  Filter for Non-deleted rows.  This is the default

    -  Deleted:  Filter for deleted rows

    -  ModifiedCurrent:  Filter for Modified rows, displaying the current values

    -  ModifiedOriginal:  Filter for Modified rows, displaying the original values

    -  None:  No rows are included

    -  OriginalRows:  Filter for deleted, modified, and unmodified rows,

       displaying the original values

    -  Unchanged:  Filter for unmodified rows

 

 

As a note both the RowFilter and RowStateFilter can be applied at the same time.

 

So, here are some examples of using the RowStateFilter:

 

 

 

Iteration…

Now, we’ve seen how to use a DataView, how to sort it, how to search it, and how to filter it.  The next question is, how do you iterate through the DataView?

 

There are times when you need to be able to loop through each row that is in the DataView so that you can perform a certain action, such as testing for a condition, or updating a value.  In those times, you can use Iteration or Looping to accomplish the task.

 

Looping through a table is as easy as using the Do…Loop structure, starting at the first index, and continuing through the last.  Here’s an example of looping through all of the rows in a table:

 

 

 

In addition to looping through each row in the view, you can also Iterate through each row using a For Each…Next structure, as follows:

 

 

 

Getting Distinct Records…

We are almost to the end of our DataView discussion.  But before we finish, there’s one more tip I’d like to share with you.

 

The DataView has the ability to output it’s contents to a new DataTable by using it’s ToTable method.  One nice feature of the ToTable method is that it is overloaded, and one overload allows you to specify the columns you want to output, and whether or not you want only distinct records copied to the table. 

 

A Distinct record is determined by the columns you choose to copy to a new table, not whether every column in the row is an exact match.

 

This is really worth knowing, because there are times when you may suspect there are duplicate rows in a DataTable.  The question will arise, how do I remove the duplicates?  This is how:

 

 

Note:  Because the Item column was the only column that was copied to the new table, the differences in the ID column were ignored, and only the Item column was evaluated for duplicates.

 

 

Summary

A DataView is the tool of choice, when it comes to viewing the data that is stored in a DataTable, and performing actions such as sorting, searching and filtering, and even grouping.  Plus, DataViews can be used in place of DataTables when performing DataBinding.