Exploring Secrets of BindingSource Filters : Page 4

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.

Previous Page