Exploring Secrets of BindingSource Filters

Exploring Secrets of BindingSource Filters

isual Studio gives developers the extraordinary capability to build a database application with a working graphical user interface using just a few mouse clicks. The canonical form of this with VS2005 uses the DataGridView control. Populating the DataGridView requires a round-trip to the database every time the underlying query needs to be changed due to user actions. The time lag between the request and a response may be a fraction of a second?but could easily be several seconds, depending on network load, database load, etc. This article discusses a technique to dynamically update the underlying query of a DataGridView virtually instantaneously. The perceived performance difference is dramatic (akin to the difference between browser-embedded JavaScript updating versus page refreshing from a web server).

The magic is buried in the Filter property of the BindingSource of the DataGridView control. Before you see how to effectively use that property, it’s worth doing some rapid application development to create a basic demonstration framework. The first sample application is a simple form with a DataGridView connected to a SQL Server database and a TextBox. Text entered in the TextBox is injected into the query underlying the DataGridView, updating the DataGridView results on each keystroke. The TextChanged event of the TextBox updates the BindingSource filter of the DataGridView. But from a user’s point of view, the application acts like a cumulative selection filter.

First Sample Application
I’ll walk through creating the project for this first application step-by-step. Advanced users will be able to skim through it rapidly.

Create a new project called “FilterDemo” in Visual Studio 2005. In the new project, open the Data Sources pane and then select the “Add New Data Source?” link as shown in Figure 1 (or you can use the menu item Data?> Add New Data Source).

Figure 1. Adding a New Data Source: The figure shows a new project with a blank form, and the Data Sources pane opened.

Adding a New Data Source to your Project
To hook up a database connection, you need a database. To be able to show some dramatic effects in the final product, I’ve used the standard Microsoft AdventureWorks database. If you don’t have it, you can download it here, and install it before continuing.

To create a connection, after selecting “Add New Data Source link?” you’ll see the Data Source Configuration Wizard, which begins with the Data Source selection dialog (see Figure 2).

Figure 2. Selecting a Data Source: Select the Database choice for your data source.
Figure 3. Select a Data Connection: If you have an existing data connection to AdventureWorks, select it; otherwise, select “New Connection…” to create a new one.
Figure 4. The Add Connection Dialog: In this dialog you enter the parameters for a database connection. In this case the server is a local instance of SqlServer2005 Express (.SQLEXPRESS) that requires Windows Authentication.

If you have previously defined data connections on your system, selecting “Next” takes you to the Data Connection selection dialog, where you can select an existing connection (see Figure 3). If you do not have a connection to the AdventureWorks database, select “New Connection?” to get to the Add Connection dialog (see Figure 4). If you have no predefined data sources, you’ll see the Add Connection dialog immediately.

Use the Test Connection button to make sure you can connect to the database; When you have a successful connection, click OK to close the dialog. You’ll see the new connection string in the Data Connection selection dialog dropdown near the top of Figure 3. In that dialog, when you select a connection string, it appears in the text field near the bottom, letting you view the parameters clearly. This connection string is read-only; if you wish to modify its components you must edit the connection by opening the Server Explorer (View?> Server Explorer), opening the context menu for the appropriate connection, and selecting Modify Connection. The Modify Connection dialog (identical to the Add Connection dialog in Figure 4) defines the components of the connection string; some from the main dialog; some from the sub-dialog accessible through the Advanced? button.

Clicking Next from the data connection dialog takes you to the final dialog in the wizard, the Database Object Selection dialog shown in Figure 5.

Figure 5. Data Objects Selection: Leave all boxes unchecked and just click Finish here.
Figure 6. Database Connection: A connection to the AdventureWorks database has been added to the FilterDemo project.

Just click the Finish button in this dialog. VS will warn you that the DataSet is empty; that is OK for now. That will close the dialog and return you to the VS designer. You should see the new data source in the Data Sources pane (see Figure 6).

Adding a Table to the DataSet
Switch back to the Solution Explorer pane in Visual Studio and double-click the AdventureWorksDataSet.xsd file to open the dataset designer (see Figure 7).

Figure 7. Empty DataSet Designer: The empty dataset designer appears when you double-click the AdventureWorkdDataSet.xsd file in Solution Explorer.
Figure 8. TableAdapter Configuration Wizard: The first screen of the TableAdapter Configuration Wizard prompts you to select a data connection.

Right-click the designer surface to open a context menu, and then choose “Add –> Table Adapter.” This opens the TableAdapter Configuration Wizard (see Figure 8).

Figure 9. Command Type Selection: For this example, leave the first choice (Use SQL statements) selected as the command type.

You’ve already established a connection to the AdventureWorks database; just make sure it is selected in the drop-down box, and then select Next to get to the command type selection screen (see Figure 9).

Select Next from the Command Type Selection screen, and you will be prompted to enter a query. Copy this SQL text into the wizard then select Finish.

   SELECT       i.[CustomerID]       ,c.[Title]       ,c.[FirstName]       ,c.[MiddleName]       ,c.[LastName]       ,c.[Suffix]       ,c.[Phone]       ,c.[EmailAddress]       ,c.[EmailPromotion]       ,a.[AddressLine1]       ,a.[AddressLine2]       ,a.[City]       ,[StateProvinceName] = sp.[Name]       ,a.[PostalCode]   FROM [Sales].[Individual] i       INNER JOIN [Person].[Contact] c        ON c.[ContactID] = i.[ContactID]       INNER JOIN [Sales].[CustomerAddress] ca        ON ca.[CustomerID] = i.[CustomerID]       INNER JOIN [Person].[Address] a        ON a.[AddressID] = ca.[AddressID]       INNER JOIN [Person].[StateProvince] sp        ON sp.[StateProvinceID] = a.[StateProvinceID]   WHERE i.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID]        FROM [Sales].[Customer]        WHERE UPPER([Sales].[Customer].[CustomerType]) = 'I');
Author’s Note: The preceding code is a slightly pared down version of the query used to generate the Sales.vIndividualCustomer view in the AdventureWorks DB. You could generate the query yourself in SqlServer 2005 Management Studio Express by opening the context menu for the view and selecting Script View As –> Create To –> New Query Editor Window.)

The wizard will close and you’ll see a table on the dataset designer surface. Go to the Properties pane and change the name from the default DataTable1 to CustomerTable. Your screen should resemble Figure 10.

Figure 10. Adding a TableAdapter: The figure shows the Dataset Designer with the successfully created CustomerTable table adapter.

VS has taken the liberty of defining the first field (CustomerID) as the primary key, designated with a small key icon in the table. For this query, though, that is not sufficient to uniquely define rows in the data; if you used the default, you’d get a key violation at runtime. To avoid that, select both the CustomerID and the AddressLine1 fields, right-click to open the context menu, and choose “Set Primary Key.” You should see key icons next to both fields.

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 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.

Towards a Real-World Application
Now that you have had a taste of what the BindingSource filter can do, here’s how you can go beyond the concepts from the simple demo application and make a much more realistic application. Here’s a slightly altered version of the full query from the Sales.vIndividualCustomer view of the AdventureWorks database, to wit:

   SELECT        i.[CustomerID]       ,c.[Title]       ,c.[FirstName]       ,c.[MiddleName]       ,c.[LastName]       ,c.[Suffix]       ,c.[Phone]       ,c.[EmailAddress]       ,c.[EmailPromotion]       ,at.[Name] AS [AddressType]       ,a.[AddressLine1]       ,a.[AddressLine2]       ,a.[City]       ,[StateProvinceName] = sp.[Name]       ,a.[PostalCode]       ,[CountryRegionName] = cr.[Name]   FROM [Sales].[Individual] i       INNER JOIN [Person].[Contact] c        ON c.[ContactID] = i.[ContactID]       INNER JOIN [Sales].[CustomerAddress] ca        ON ca.[CustomerID] = i.[CustomerID]       INNER JOIN [Person].[Address] a        ON a.[AddressID] = ca.[AddressID]       INNER JOIN [Person].[StateProvince] sp        ON sp.[StateProvinceID] = a.[StateProvinceID]       INNER JOIN [Person].[CountryRegion] cr        ON cr.[CountryRegionCode] = sp.[CountryRegionCode]       INNER JOIN [Person].[AddressType] at        ON ca.[AddressTypeID] = at.[AddressTypeID]   WHERE i.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID]        FROM [Sales].[Customer]        WHERE UPPER([Sales].[Customer].[CustomerType]) = 'I'); 

The only difference is that this version adds back the final column, CountryRegionName, because it makes the demo to come more interesting. I’ll skip the detailed creation of this second demo application in the interests of brevity, but you can download the finished application to experiment with it and dissect it at your leisure.

The basic construction, though, is the same as the first time around. You already have the data connection to the AdventureWorks database. Modify your existing DataTable in the dataset designer so that it uses the revised query, or create a new one. The remaining differences are cosmetic: This version has two more TextBox controls, seven CheckBox controls, and three RadioButton controls that enable extensive query customizability, plus a diagnostic TextBox to show you the WHERE clauses that you are building when you exercise the collection of controls.

When you execute the program, it should look like the program output shown in Figure 19, assuming you have the AdventureWorks database hooked up.

Figure 19. Advanced Demo: The advanced demo includes TextBox, RadioButton, and CheckBox controls for extensive filtering.
Figure 20. Advanced Data Filtering: After selecting options and typing values, the data table in this instance has been reduced from 18000 rows to 8 rows.

Experiment with all the controls on the left half of the screen; observe how the data table contracts and expands as you select or deselect elements. I draw your attention particularly, though, to the diagnostic TextBox on the right side, where you will see the WHERE clause that you have constructed. Figure 20 shows an example with several controls.

You will note that the WHERE clause can get fairly convoluted. With the controls set as shown in Figure 20 the WHERE clause is:

   (convert([CustomerID], 'System.String') LIKE '%34%'   AND [CountryRegionName] IN ('France', 'United States')   AND [AddressType]='Home'   AND AddressLine2  '')

The filter contains a compound clause (several AND keywords), with subclauses for equality, inequality, inclusion, and matching. You can even include supported function calls (the convert function here).

The Clause-building Engine: Using SqlFilterBuilder
The source code for this program, not counting the VS-generated code or the seven lines needed to define some variables, is just seventeen lines long, distributed through nine methods. Credit for its brevity belongs to the SqlFilterBuilder engine from the open-source code library available on my CleanCode website). I’ll describe the API for the engine as I discuss the code method by method from the bottom up.

Each control on the form needs an event handler. Each TextBox will have a TextChanged event handler. The event handler for the City field, for example, is:

   private void cityFilterTextBox_TextChanged(object sender, EventArgs e)   {       cityClause = builder.GetMatchPhrase(         "City", cityFilterTextBox.Text);       CombineClauses();   }

The first line uses the SqlFilterBuilder.GetMatchPhrase method with the City field name and the contents of the TextBox as the value. This will produce a sub-phrase such as [City] LIKE ‘%Albuq’. The second line of the event handler calls a CombineClauses() method common to all the event handlers, which assembles all the clauses derived from the controls. I’ll discuss that method shortly.

The event handler for the ID field looks almost identical:

   private void idFilterTextBox_TextChanged(object sender, EventArgs e)   {       idClause = builder.GetMatchPhrase(          "CustomerID", idFilterTextBox.Text);       CombineClauses();   }

But appearances may be deceiving. Look back at the example at the beginning of the section. Instead of a sub-phrase such as [CustomerID] LIKE ‘%34%’ the result instead was convert([CustomerID], ‘System.String’) LIKE ‘%34%’. Why? The CustomerID field, unlike the City field, is an integer, not a string. The BindingSource filter is more strict than Query Analyzer and other standard query tools, and it will balk at comparing an integer to a string?to do that, you must first convert the integer to a string. The cleverly named convert function performs that conversion. And, as you may have surmised, the SqlFilterBuilder handles that conversion automatically, hence you only need to add one line to your application code.

The last TextBox has a label of just “Name,” chosen deliberately because users can enter a fragment of either the first or the last name (the first demo only filtered by surname). So, to filter on either of two database fields, the event handler for the nameFilterTextBox requires some additional logic compared to the two event handlers you just saw:

   private void nameFilterTextBox_TextChanged(object sender, EventArgs e)   {       nameClause = builder.GetAlternation(           builder.GetMatchPhrase("FirstName", nameFilterTextBox.Text),           builder.GetMatchPhrase("LastName", nameFilterTextBox.Text)       );       CombineClauses();   }

The preceding code builds subclauses for both the first name and the last name fields filtering by the user’s entry in the nameFilterTextBox. These are wrapped in a call to the GetAlternation method. GetAlternation(“a”, “b”), for example, yields simply [a] OR [b].

The only other non-grouped control on the form is a lone CheckBox, which runs this code when its checked state changes.

   private void ignoreEmptyAddr2CheckBox_CheckedChanged(object sender, EventArgs e)   {       blankAddrClause = ignoreEmptyAddr2CheckBox.Checked ? "AddressLine2  ''" : "";       CombineClauses();   }

This sub-clause is straightforward, it just includes the literal expression or not, depending on the state of the CheckBox.

The checkbox and radio button groups are somewhat more interesting to work with. All the members of the checkbox group use the same event handler, so whenever any CheckBox in the group is checked, the handler code reevaluates the regionClause:

   private void regionCheckBox_CheckedChanged(object sender, EventArgs e)   {       regionClause = builder.GetPhrase("CountryRegionName",           regionGroupBox.GetSelectedCheckBoxNames());       CombineClauses();   }

This method uses the most general SqlFilterBuilder method, GetPhrase, which returns an equality, an inclusion, or a match phrase, depending on its arguments. In this case, the input value comes from the GetSelectedCheckBoxNames method of an IdentifyingGroupBox, a custom user control derived from the standard GroupBox. An IdentifyingGroupBox (also from my CleanCode library) provides two additional methods; you will see the second shortly. The return value from this method is a list of the names of the currently selected checkboxes. For example, when just one checkbox is checked, the SqlFilterBuilder might return [CountryRegionName] = ‘Canada’, whereas when more than one checkbox is checked the method returns an inclusion phrase such as [CountryRegionName] IN (‘France’, ‘United States’).

The group of radio buttons requires a slightly different treatment. Address radio buttons, the way I have chosen to model them here, fall into two categories: those that refer to specific address types (mailing, home, shipping, etc.) and one additional button that refers to all address types (the “both” button in this case). You could use a single event handler for all the buttons, like the region checkboxes, but in this case that would involve some conditional code. It’s simpler to make one event handler for each category:

   private void allAddressRadioButton_CheckedChanged(object sender, EventArgs e)   { // handle the "Both" button here       addressClause = "";       CombineClauses();   }      private void addressRadioButton_CheckedChanged(object sender, EventArgs e)   { // handle all individual address types here       addressClause = builder.GetPhrase("AddressType",           addressGroupBox.GetSelectedRadioButtonName());       CombineClauses();   }

When users select the “Both” radio button, it actually means that they don’t want any filtering, so the handler code simply set the addressClause to an empty string. The other radio buttons use the second event handler. Because a GroupBox (and hence an IdentifyingGroupBox) enforces mutual exclusion on its RadioButton members, the GetSelectedRadioButtonName will always return a single name, so the resulting sub-clause will always be an equality clause.

With the subclause-building code in hand, the remaining relevant method is the CombineClauses method, which unifies all the pieces you have just seen. It contains just two lines of code:

   private void CombineClauses()   {       customerTableBindingSource.Filter = builder.GetConjunction(           nameClause, cityClause, idClause,           regionClause, addressClause, blankAddrClause);       filterOutputTextBox.Text = customerTableBindingSource.Filter;   }

The first line creates a conjunctive phrase from all the subclauses created as users selected different controls, and assigns that to the BindingSource Filter property, which then works its magic. The second line is merely diagnostic; it displays the actual string onscreen for you.

There is one subtle but important point regarding the SqlFilterBuilder that makes it simple to use, the principle of collapsing to a singularity. This occurs in two stages and is best illustrated by example. Consider again the single line of code to generate the regionClause:

       regionClause = builder.GetPhrase("CountryRegionName",           regionGroupBox.GetSelectedCheckBoxNames());

When multiple check boxes are checked, the SqlFilterBuilder generates an inclusion subphrase. When only a single check box is checked, it generates an equality subphrase. But what happens when no check boxes are checked? In my model, checking no boxes means: do not filter on any of the choices. So GetPhrase collapses to the empty string when its argument list is empty as opposed to CountryRegionName = ”.

The second stage of this principle addresses the parallel question: what happens when empty subphrases are passed to GetConjunction or GetAlternation? Those subphrases collapse as well. So if the regionClause collapses because no checkboxes are checked, the CombineClauses method simply ignores the empty string when assembling the main clause. So any of the six subclauses that are assembled in CombineClauses are free to independently collapse?or not?based on user actions. Together, these two stages implement the general semantics of my model: if the user has not specified some restriction, do not apply a filter.

Finally, this line in the Form_Load event handler provides a context for the SqlFilterBuilder to use in certain cases.

   builder = new SqlFilterBuilder(adventureWorksDataSet.CustomerTable);

The SqlFilterBuilder can handle most things without any context, but having the context is occasionally invaluable. You saw one such instance in the idFilterTextBox event handler, where the SqlFilterBuilder could automatically apply a convert function for non-string field types.

As you can see, the SqlFilterBuilder engine simplifies the process of building an application that not only provides a remote data selection query to a live database, but also layers on top of the base query a secondary, in-memory data selection query?all with very few lines of code.

Note that while this technique is broadly applicable, it is not universally so. Not all data sources support filtering. DataTables and DataViews implement the IBindingListView interface, which exposes a SupportsFiltering property that indicates whether the data source supports filtering?thanks to Mark Rideout, Microsoft Program Manager, for that key point.

You can download the code for both the simple and the more advanced sample projects discussed in this article. That includes the DLLs for the CleanCode library modules discussed above, but not the source code. You can get the CleanCode source here.


Share the Post: