59
Chapter 4
Accessing the Right Data Values
After completing this chapter, you will be able to:

Find items in a DataTable by primary key

Search for DataRow instances using standard query statements

Obtain a set of DataRow objects sorted by one or more columns

Add expression columns to a DataTable that present calculated values
Although adding records to a DataTable is important, the real value of ADO.NET lies in get-
ting those records back out in a variety of ways. Fortunately, the data framework includes
many different methods and tools to fulfill that very purpose.
This chapter introduces a few of the most basic tools, all of which appear in the DataTable,
DataColumn, and DataRow classes you’ve already met. Each data table includes features that
let you select just the records you need. These features are flexible enough to rival those you
might find in traditional databases. The chapter also includes a discussion of “expression col-
umns,” a way to add useful values to each table row without adding any actual data.
Note
The exercises in this chapter all use the same sample project, a tool that queries data
from a sample DataTable. Although you will be able to run the application after each exercise,
the expected results for the full application might not appear until you complete all exercises in
the chapter.
Both forms in the sample application use the DataGridView control, one of the standard controls
provided with Visual Studio. Chapter 21, “Binding Data with ADO.NET,” discusses the ADO.NET-
specific features of this control.
Querying and Sorting Data
In Chapter 3, “Storing Data in Memory,” you learn how to iterate through all the records in a
DataTable object’s Rows collection. However, there are times when you need to access only

C#). The method throws an exception if you apply it to tables with no defined primary key.
Note
It is possible to add two rows with the same primary key to a DataTable by disabling its
constraints (as discussed in Chapter 5, “Bringing Related Data Together”). In such tables, the Find
method returns only the first row with a matching primary key value.
Finding a Row by Primary Key: C#
1. Open the “Chapter 4 CSharp” project from the installed samples folder. The project in-
cludes two Windows.Forms classes: TableExaminer and ResultsViewer.
2. Open the source code view for the TableExaminer form. Locate the ActPrimaryKey_Click
event handler. This routine obtains a long-integer value from the user and then uses it
as the primary key lookup value in the application’s sample DataTable. Most of the code
exists to ensure that the user provides a valid ID.
Dwonloaded from: iDATA.ws
Chapter 4 Accessing the Right Data Values
61
3. Locate the try...catch statement just after the “Perform the lookup” comment. In the try
block, add the following statement:
result = workTable.Rows.Find(usePrimaryKey);
This line performs the actual primary-key lookup, returning the DataRow of the match-
ing record, or null when the key doesn’t match any of the table’s primary keys.
4. Run the program. On the Lookup By Primary Key tab, enter a value in the Primary Key
field (try 2352), and then click the Lookup button. The matching row appears in a sepa-
rate window (not shown here).
Finding a Row by Primary Key: Visual Basic
1. Open the “Chapter 4 VB” project from the installed samples folder. The project includes
two Windows.Forms classes: TableExaminer and ResultsViewer.
2. Open the source code view for the TableExaminer form. Locate the ActPrimaryKey_Click
event handler. This routine obtains a long-integer value from the user and then uses it
as the primary key lookup value in the application’s sample DataTable. Most of the code
exists to ensure that the user provides a valid ID.

you make to these rows affect the underlying table.
Dwonloaded from: iDATA.ws
Chapter 4 Accessing the Right Data Values
63
The filter expression passed to the Select method uses a SQL-like syntax to build a Boolean
statement that will either match or not match specific rows in the table. Any of the columns
in your DataTable object is fair game for comparisons. As an example, the following expres-
sion will return all rows with a Salary column value of at least 100,000:
Salary >= 100000
Columns can be compared to each other and standard mathematical expressions can en-
hance the column elements.
Bonus > Salary * 0.15
You can string together multiple criteria using the Boolean operators AND, OR, and NOT, and
use parentheses to force evaluation in a specific order.
Age >= 18 AND (InSchool = True OR LivingAtHome = True)
Table 4-1 lists the some of the elements you can use in filter expressions. To view the full doc-
umentation for filter expressions, access the Visual Studio online help entry for “DataColumn.
Expression Property.”
TABLE 4-1
Filter Expression Elements
Event Name Triggering Action
Column names Any of the column names from the DataTable. Surround column
names that contain embedded space characters or other non-
alphanumeric characters with a set of square brackets, as in [Full
Name] for a column named Full Name.
<, >, <=, >=, <>, = Use the standard comparison operators to compare columns to
literal values, to each other, or to more complex expressions.
IN
Match from a collection of comma-delimited elements.
BillDenomination IN (5, 10, 20)