Database Reference
In-Depth Information
It's worth mentioning here that the following code would have produced the same result. Instead of
passing the SQL and connection to the data adapter's constructor, you could have set its
SelectCommand
property with a command that you create with the appropriate SQL and connection.
// Create data adapter
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(sql, conn);
With a populated data set at your disposal, you can now access the data in individual data tables.
(This data set contains only one data table.)
// get data table
DataTable dt = ds.Tables["Production.Product"];
Finally, you use nested
foreach
loops to access the columns in each row and output their data
values to the screen.
// display data
gvProduct.DataSource = ds.Tables["Production.Product"];
Filtering and Sorting in a Data Set
In the previous example, you saw how to extract data from a data set. However, if you're working with
data sets, chances are that you're going to want to do more with the data than merely display it. Often,
you'll want to dynamically filter or sort the data. In the following example, you'll see how you can use
data rows to do this.
Try It: Dynamically Filtering and Sorting Data in a Data Set
We'll get all the rows and columns from the Customers table, filter the result for only German customers,
and sort it by company. We'll use a separate query to find products and fill two data tables in the same
data set.
1.
Select the DataSetandDataAdapter project, right-click, and choose Add
Windows Form. From the opened dialog, make sure the Windows form is
selected, and rename
Form1.cs
to
FilterSort.cs
. Click OK to add this form to
the DataSetandDataAdapter project.
2.
Select the FilterSort form by clicking the form's title bar, and set the Size
property's Width to 350 and Height to 489.
3.
Drag a Text Box control to the form, and position it toward the center of the
form. Select this TextBox control, navigate to the Properties window, and set
the following properties:
•
Set the Name property to txtSort.
•
For the Location property, set X to 12 and Y to 8.
•
Set the Multiline property to True.
•
Set the ScrollBars property to Vertical.
•
For the Size Property, set Width to 312 and Height to 435.
