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.
Table 1. The table shows the preferred widths for the various columns in the DataGridView.
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 accidentit 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.