Database Reference
In-Depth Information
// Create connection
SqlConnection conn = new SqlConnection(connString);
You create a data adapter, assigning to its
SelectCommand
property a command that encapsulates the
query and connection (for internal use by the data adapter's
Fill
method).
// Create Data Adapter
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(sql, conn);
You then create and fill a data set.
// Create and Fill Data Set
DataSet ds = new DataSet();
da.Fill(ds, "Production.Product");
Each query returns a separate result set, and each result set is stored in a separate data table (in the
order in which the queries were specified). The first table is explicitly named Product; the second is given
the default name Location.
You get the data table collection from the data set's
Tables
property for ease of reference later.
// get the data tables collection
DataTableCollection dtc = ds.Tables;
As part of displaying the first data table, you declare two strings.
// Set display filter
string fl = "Color = 'Black'";
// Set sort
string srt = "ProductNumber asc";
The first string is a
filter expression
that specifies row selection criteria. It's syntactically the same as
a SQL
WHERE
clause predicate. You want only those rows where the Color column equals Black. The
second string specifies your sort criteria and is syntactically the same as a SQL
ORDER BY
clause, giving a
data column name and sort sequence.
You use a
foreach
loop to display the rows selected from the data table, passing the filter and sort
strings to the
Select
method of the data table. This particular data table is the one named Product in the
data table collection.
// display filtered and sorted data
foreach (DataRow row in dtc["Production.Product"].Select(fl, srt))
{
txtSort.AppendText(row["Name"].ToString().PadRight(25));
txtSort.AppendText("\t\t");
txtSort.AppendText(row["ProductNumber"].ToString());
txtSort.AppendText(Environment.NewLine);
}
You obtain a reference to a single data table from the data table collection (the
dtc
object) using the
table name that you specify when creating the data set. The overloaded
Select
method does an internal
search on the data table, filters out rows not satisfying the selection criterion, sorts the result as
prescribed, and finally returns an array of data rows. You access each column in the row, using the
column name in the indexer.
It's important to note that you can achieve the same result—much more efficiently—if you simply
use a different query for the customer data.
