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.
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;
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.
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.
Operator | Purpose |
= |
Equal to |
<> |
Not equal to |
< |
Less than |
> |
Greater than |
<= |
Less than or equal to |
>= |
Greater than or equal to |
LIKE |
Matches a pattern |
IN |
Is in a list of values |
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.
Filter | Selects |
LastName = 'Johnson' |
Records where LastName is Johnson |
FirstName = 'Ann' OR FirstName = 'Anne' |
Records where FirstName is Ann or Anne |
FirstName LIKE 'Pam%' |
Records where FirstName begins with Pam |
State IN('NY','NC','NJ') |
Records where State is NY, NC, or NJ |
(Balance < -50) OR ((Balance < 0) AND (DaysOverdue > 30)) |
Records where the account is overdrawn by more than $50 or where the account has been overdrawn by any amount for more than 30 days |
You can use the BindingSource
's RemoveFilter
method to remove the filter and display all of the records again.
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.
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.
In this lesson, you:
ToolStrip
containing a TextBox
and a Button
.
Button
, apply the filter entered in the TextBox
.
// Apply the filter.
private void filterButton_Click(object sender, EventArgs e)
{
string filter = filterTextBox.Text.Trim();
if (filter.Length == 0)
{
// No filter.
contactsBindingSource.RemoveFilter();
}
else
{
// Add the filter.
try
{
contactsBindingSource.Filter = filter;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
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.RadioButton
s to the right of the TextBox
es, as shown in Figure 35.1. When the user clicks a RadioButton
, make the program sort its data using the corresponding field.
(Hint: Set each RadioButton
's Tag
property to the name of the field it represents.)
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.)
States ComboBox
's items from the database when it starts. Hints:
ComboBox
's Sorted
property to true
.List<string>
named States
to keep track of the states in the database.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;
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:
// Filter with the selected state.
private void stateComboBox_SelectionChanged(object sender,
SelectionChangedEventArgs e)
{
// Get the dataset.
ContactsDataSet contactsDataSet =
(ContactsDataSet)this.FindResource("contactsDataSet");
// Get the selected state.
ComboBoxItem item = stateComboBox.SelectedItem as ComboBoxItem;
string state = item.Content.ToString();
Console.WriteLine("Filtering by state " + state);
// Set the filter.
CollectionViewSource contactsViewSource =
(CollectionViewSource)FindResource("contactsViewSource");
BindingListCollectionView view =
(BindingListCollectionView)contactsViewSource.View;
if (state.Length == 0) view.CustomFilter = "";
else view.CustomFilter = "State = '" + 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;