Exploring Secrets of BindingSource Filters : Page 3

Connecting a DataGridView to the Data
Switch back to the design view of the project's GUI form (select "Form1.cs [Design]" from the Solution Explorer pane or just click the "Form1.cs [Design]" tab in the main work area if you still have it open), and then open the Data Sources pane. Finally drag the Customer Table item from the Data Sources pane to the form. Visual Studio adds four controls to the component tray and a DataGridView to the form (see Figure 11).

Figure 11. Connecting a DataGridView: Dragging the Customer Table from the DataSources pane onto the form surface automatically adds a DataGridView to the form body, a BindingNavigator to the top edge of the form, and four components to the component tray.

Figure 12. Setting Field Widths: To set field widths for a DataGridView. Select the DataGridView (1), open its Task Menu (2), choose Edit Columns (3), select a column (4), and specify a field width (5).

At this point, you could run the application and the DataGridView would fill with the data from the database. But you should make just a few tweaks to the user interface to optimize the display space for the data. Enlarge the form and resize the DataGridView control to fill most of the form. Next, tailor the data columns to fit as much as possible. Select the DataGridView, open its task menu (via the tiny arrow at the top right corner of the control), and choose "Edit Columns" (see Figure 12).

Set the widths of the columns to the values shown in Table 1.

Column Name	Width
CustomerId	60
Title	40
FirstName	60
MiddleName	25
LastName	60
Suffix	25
Phone	75
EmailAddress	100
EmailPromotion	25
AddressLine1	100
AddressLine2	50
City	60
StateProvinceName	40
PostcalCode	25

Back on the Properties pane of the DataGridView, also change the RowTemplate --> Height from the default value of 22 to 14, which will let the DataGrid show more rows. Finally, change the RowHeadersWidth from the default value of 41 to 20 or so, to reduce the wasted space in the row selection (leftmost) column.

Connecting a TextBox to Filter the DataGridView
Now that you have a DataGridView connected to a data source, you need to add a TextBox that will let users filter the records, and connect it to the DataGridView. Drag a TextBox from the toolbox to the top of the form and change its Name property to surnameFilterTextBox (see Figure 13).

Figure 13. Add a Filter TextBox: Drag a TextBox onto the form and rename it to surnameFilterTextBox.

Double-click the TextBox to create an event handler, and open the code for the TextChanged event. You need only add a single line of code to give life to the filter:

   private void surnameFilterTextBox_TextChanged(
      object sender, EventArgs e)
   {
       customerTableBindingSource.Filter =
           "LastName like '%" + 
           surnameFilterTextBox.Text + "%'";
   }

Figure 14 shows all the code in Form1.cs. It consists of just three lines of code, two of which were inserted automatically by Visual Studio.

Figure 14. Form Source: The entire code for Form1.cs providing a dynamic instantaneous filter to the database.

Figure 15. FilterDemo In Action: The figure shows how the FilterDemo looks when you first launch it, showing data from AdventureWorks with no filter yet applied.

Build and execute the project, and you should see something resembling Figure 15. Note that the navigator tool strip at the top reports 18,508 records. Keep an eye on this number as you proceed.

Type some characters in the box. As you add each character, the DataGridView instantly updates to include the additional constraints you are imposing. In contrast, Figure 16 shows the data after entering two characters into the filter TextBox: "u" and "s". Note that only rows containing "us" in the LastName field are visible. There are now only 130 rows in the table instead of the original 18,508. The size of the slider in the right-most scroll bar gives you a visual feel for the number of records as well.

Figure 16. Filter Applied: Filtering with a two-character substring reduces the visible rows in the table to 130.

Figure 17. Further Filtering: Adding a third more character reduces the table to just two rows.

If you add one more character, an "e," you're left with just two rows (see Figure 17).

Figure 18. "LIKE" Exception: An exception occurs when using the "LIKE" operator on incompatible types.

The field I picked (LastName) was not by accident—it needs to have a string data type. If instead you were to wire the filter to the CustomerID field, you would see the error in Figure 18.

That is, while most SQL query tools allow you to say [CustomerID] LIKE '%55%' the interpreter for the BindingSource filters does not. Hence, you need some extra horsepower to help with non-string fields. You will see how to handle this in the second demo application shortly.

Valid Values for The Filter Property
You just saw one example of an assignment to the Filter property of a BindingSource. From that one instance, you might surmise that it allows certain fragments of a SQL WHERE clause. But how extensive a fragment? Rather than just repeat what Microsoft specifies, take a look at Microsoft's documentation reference page, which provides the basics of the BindingSource.Filter property. Then, go to the DataColumn.Expression reference page for the full syntactic details.

Previous Page

Next Page