Often you will want a method to allow your users to search for specific information within the data that is presented to them. This can be done in a number of ways, one of which you have already seen in the previous section where we used a SELECT statement to retrieve a single record based on the ContactID.
However, if we were to stick with this method, we would end up with a fair bit of code as we would first need to perform the search and populate a DataTable with the results before then binding the results to the dgContacts grid. We would also then need a mechanism allowing the user to retrieve all of the data again (i.e. clear the search results). Another drawback to this method is that we would need to hit the database twice; the first time to perform the search and the second time to clear the search.
There is an alternative which makes filtering the data extremely easy:- Enter the DataView. The DataView - like the DataSet and DataTable - exists in the System.Data hierarchy which means that it is the same object that you will use regardless of which database you are working with. The DataView allows you to perform a number of functions against the data in a DataTable and one of those functions is the ability to filter the data. Also, the DataView can be bound to controls in exactly the same way as the DataTable can.
Before we move onto the code for filtering the data we will need to add a couple of controls to the main form to allow the user to enter their filter criteria. Add the following controls to your main form:
For the purpose of this demonstration, we will perform the filtering on the FirstName field of the Contacts table and we will also do this filtering via the TextChanged event of the Search text box. This allows us to give the user the ability to enter the first character of the first name and the filter will kick in immediately showing only those records that begin with that character.
Add the following code to the Search TextBox TextChanged event:
The code above declares a DataView object and then assigns it to the DataSource of the dgContacts grid. However, it is important to note that the DataSource of the dgContacts grid could be one of two types. The first time the form is loaded the DataSource of the grid is set to a DataTable, but as soon as any filtering is performed, the DataSource will then be set to a DataView object. For this reason, the TypeOf operator is used to determine how to cast the DataSource in order to assign it to the DataView. In addition, if the DataSource object type is a DataTable then the DataTable's DefaultView property is used which returns a DataView object based on the DataTable.
The next part of the code determines how to set the RowFilter of the DataView. If any text is entered then the RowFilter property is set to an SQL WHERE clause which states that all records where the FirstName is "LIKE" that entered in the txtSearch box. Also note that the * character is used as a wildcard character at the end of the search string so that all records starting with the characters entered are returned. Likewise, if the txtSearch text box does not contain any data then the RowFilter property is set to an empty string which states that no RowFilter should be used and thus all data is returned. Finally the dvFilter DataView is assigned as the DataSource to the dgContacts grid.
If you run your program now and enter some text into the search criteria, you should see the grid start filtering the data based on the FirstName field. If you enter text that doesn't match any field values then the grid will not display any records. Likewise, if you delete the contents of the search criteria text box, the grid will then show all of the records. This is all achieved without ever hitting the database.