Database Reference
In-Depth Information
Since two columns are selected by your query, the returned data also comprises a collection of rows
from only these two columns, thus allowing access to only two possible ordinal indexers, 0 and 1.
For formatting purposes, we want to show the column headings FirstName and LastName as the
first header row, and then we want all other value rows to be added below it. So, you can use the
PadeRight
and
PadLeft
methods to format the output in such a way that all the characters will be left-
and right-aligned by padding with spaces on the left/right for a specified total length.
// Print headings
StringBuilder sb=new StringBuilder();
txtValues.AppendText("First Name".PadRight(25));
txtValues.AppendText("Last Name".PadLeft(20));
txtValues.AppendText(Environment.NewLine);
txtValues.AppendText("---------------------------------------------
--------------------");
txtValues.AppendText(Environment.NewLine);
Now you read each row in a
while
loop, fetching values of the two columns with their indexers and
appending these two the TextBox so all the names appear as a list, like shown in Figure 14-2.
// Loop through result set
while (rdr.Read())
{
txtValues.AppendText(rdr[0].ToString());
txtValues.AppendText("\t\t\t");
txtValues.AppendText(rdr[1].ToString());
txtValues.AppendText(Environment.NewLine);
}
After processing all rows in the result set, you explicitly close the reader to free the connection.
// Close reader
rdr.Close();
Using Column Name Indexers
Most of the time, we don't really keep track of column numbers and prefer retrieving values by their
respective column names, simply because it's much easier to remember them by their names, which
also makes the code more self-documenting.
You use column name indexing by specifying column names instead of ordinal index numbers. This
has its advantages. For example, a table may be changed by the addition or deletion of one or more
columns, upsetting column ordering and raising exceptions in older code that uses ordinal indexers.
Using column name indexers would avoid this issue, but ordinal indexers are faster, since they directly
reference columns rather than look them up by name.
The following code snippet retrieves the same columns (FirstName and LastName) that the
previous example did, using column name indexers.
// Loop through result set
while (rdr.Read())
{
txtValues.AppendText(rdr["FirstName"].ToString());
txtValues.AppendText(rdr["LastName"].ToString());
}
