C# 24-Hour Trainer (2015)

Section VII

Specialized Topics

Lesson 35

Programming Databases, Part 2

The simple programs described in the previous lesson are hardly commercial-caliber database applications, but they do let you perform basic database operations with amazingly little code.

In this lesson, you learn how to add a few new features to the programs described in Lesson 34. You learn how to add searching, filtering, and sorting to the programs to make finding data easier.

Searching

In a large database, it can be hard to locate a particular value. A program can make finding records easier by using the BindingContext's Find method. This method takes as parameters the name of a field to search and the value that it should find. It returns the index of the first record that has the desired value.

For example, the following code searches the data in the BindingSource named contactsBindingSource for a record with FirstName value equal to Kim:

int recordNumber = contactsBindingSource.Find("FirstName", "Kim");

Having found the index of the target record, you can then highlight it in some way for the user to see. For example, recall that a BindingSource's CurrencyManager controls the current position within the data. The following code makes the current record be the record found by Find so any controls displaying the data will show this record:

contactsBindingSource.CurrencyManager.Position = recordNumber;

WARNING

Find returns –1 if it cannot find the target string. Be careful not to try to do anything explicitly with record number –1 or your program may crash. That means the previous line of code should really be

if (recordNumber != -1)

contactsBindingSource.CurrencyManager.Position = recordNumber;

If the program is displaying data in a grid, focus moves to the found record's row. If the program is displaying data in field controls, those controls now show the found record's data.

Filtering

The Find method is somewhat restrictive. It only searches for exact matches in a single field and only returns the index of the first record that matches. Often you might prefer more flexibility such as searches that can check conditions (Age > = 21), look for partial matches (LastName begins with S), and combine multiple tests (State is VA or DC). It might also be nice to see all of the records that meet a condition instead of just the first record.

Filters let you perform these kinds of searches. A filter tests each record in a BindingSource's data and selects those that satisfy the test. Any display controls attached to the BindingSource show only the selected records.

To use a filter, set the BindingSource's Filter property to a string describing the records that you want to select. The filter compares each record's fields to values and selects the records that match. For example, the clause State='FL' selects records where the State field has the value FL.

String values should be delimited with single or double quotes. (Single quotes are generally easier to type into a string that is itself delimited by double quotes.) Numeric values should not have delimiters.

Table 35.1 lists the operators that you can use to compare fields to values.

Table 35.1

The LIKE operator performs pattern matching. Use * or % as a wildcard that matches zero or more characters.

You can use the AND, OR, and NOT logical operators to combine the results of multiple comparisons. Use parentheses to determine the evaluation order if necessary.

Table 35.2 lists some example filters.

Table 35.2

You can use the BindingSource's RemoveFilter method to remove the filter and display all of the records again.

Sorting

If you display data in a DataGridView, you can click a column's header to sort the records based on the values in that column. Clicking again reverses the sort order. Sorting doesn't get much easier than that.

If you're displaying the data in fields rather than a grid, however, you don't get automatic sorting. Fortunately, you can make a BindingSource sort simply by setting its Sort property to the name of the field on which you want to sort. Use its RemoveSort method to cancel the sort and display the records in their original order.

Try It

In this Try It, you add filtering to a program that displays records in a grid. You let the user enter a filter and you make the program display only records that match the filter.

Lesson Requirements

In this lesson, you:

· Copy the program you built for the Try It in Lesson 34 (or download Lesson 34's version from the book's website).

· Add a ToolStrip containing a TextBox and a Button.

· When the user clicks the Button, apply the filter entered in the TextBox.

NOTE

You can download the code and resources for this lesson from the website at www.wrox.com/go/csharp24hourtrainer2e.

Hints

· Be sure to protect the program in case the user enters an invalid filter.

Step-by-Step

· Copy the program you built for the Try It in Lesson 34 (or download Lesson 34's version from the book's website).

1. This is straightforward.

· Add a ToolStrip containing a TextBox and a Button.

1. This is straightforward.

· When the user clicks the Button, apply the filter entered in the TextBox.

1. Use code similar to the following:

2. // Apply the filter.

3. private void filterButton_Click(object sender, EventArgs e)

4. {

5. string filter = filterTextBox.Text.Trim();

6. if (filter.Length == 0)

7. {

8. // No filter.

9. contactsBindingSource.RemoveFilter();

10. }

11. else

12. {

13. // Add the filter.

14. try

15. {

16. contactsBindingSource.Filter = filter;

17. }

18. catch (Exception ex)

19. {

20. MessageBox.Show(ex.Message);

21. }

22. }

}

Exercises

1. Copy the program you built for the Try It and replace the ToolBar's TextBox and Button with a “State:” Label and a ComboBox. Make the ComboBox list the state abbreviations present in the database (just hard-code them) plus a blank choice. When the user selects a value, use the selected state to filter the data.

2. Copy the program you built for Exercise 34-1 and add RadioButtons to the right of the TextBoxes, as shown in Figure 35.1. When the user clicks a RadioButton, make the program sort its data using the corresponding field.

Figure 35.1

(Hint: Set each RadioButton's Tag property to the name of the field it represents.)

3. Copy the program you built for Exercise 1. Add a “First Name:” Label and a TextBox to the ToolBar. When the user enters a name, find and highlight the first record with that FirstName value.

(Hint: If the user selects a new State filter, the program must find the name again.)

4. [Hard] Copy the program you built for Exercise 3 and make the program load the States ComboBox's items from the database when it starts. Hints:

· Set the ComboBox's Sorted property to true.

· Create a class-level variable List<string> named States to keep track of the states in the database.

· Use the following code to initialize the list and make the ComboBox use it:

· // See what State values are in the data.

· States = new List<string>();

· States.Add("");

· foreach (DataRow row in contactsDataSet.Contacts.Rows)

· {

· string state = row.Field<string>("State");

· if (!States.Contains(state)) States.Add(state);

· }

· // Load the stateComboBox's items.

stateComboBox.ComboBox.DataSource = States;

5. Copy the program you built for Exercise 2 and add a Filter feature similar to the one you added for this lesson's Try It.

6. Copy the program you built for Exercise 2 and add a Filter by State feature similar to the one you added for Exercise 1.

7. [WPF, Hard] Naturally filtering records is more difficult in WPF than it is in Windows Forms. Copy the program you wrote for Exercise 4 and add a ToolBar, “State:” Label, and ComboBox much as you did for Exercise 1. (You may want to rearrange the program's controls to use a DockPanel.) Use the following code to filter the data:

8. // Filter with the selected state.

9. private void stateComboBox_SelectionChanged(object sender,

10. SelectionChangedEventArgs e)

11. {

12. // Get the dataset.

13. ContactsDataSet contactsDataSet =

14. (ContactsDataSet)this.FindResource("contactsDataSet");

15. // Get the selected state.

16. ComboBoxItem item = stateComboBox.SelectedItem as ComboBoxItem;

17. string state = item.Content.ToString();

18. Console.WriteLine("Filtering by state " + state);

19. // Set the filter.

20. CollectionViewSource contactsViewSource =

21. (CollectionViewSource)FindResource("contactsViewSource");

22. BindingListCollectionView view =

23. (BindingListCollectionView)contactsViewSource.View;

24. if (state.Length == 0) view.CustomFilter = "";

25. else view.CustomFilter = "State = '" + state + "'";

}

26.[WPF, Hard] Copy the program you built for Exercise 34-11 and add a “State:” Label and a ComboBox as you did in Exercise 7. You'll also need to make two additional changes.

First, after you change the filter, you need to update the position label.

Second, you need to change the way you display the current record's position so it uses the selected records and not the entire data table. Modify the ShowPosition method so it uses the following code to determine the number of records selected:

// Get the number of records selected.

CollectionViewSource contactsViewSource =

(CollectionViewSource)FindResource("contactsViewSource");

BindingListCollectionView view =

(BindingListCollectionView)contactsViewSource.View;

int numselected = view.Count;

NOTE

Please select the videos for Lesson 35 online at www.wrox.com/go/csharp24hourtrainer2evideos.