Getting data from a database is easy, and working with data is easier and better than before. This section describes how to use a SQLCommandSet to get data from a database into a DataSet.
<p>
The most important concept to remember is that the DataSet is a data structure separate and distinct from a data store. Although in this example we get data from a database, it doesn't matter where the data comes from; the DataSet will always present a consistent programming model. It is a simple collection of data with relational database characteristics. There is no Load, Open or Execute on a DataSet because it doesn't know where it gets its data from.
<p>
You can add data to the DataSet using the Add method (see <a href="UpdateDataFromDB.aspx">Updating Data in a Database</a>) for more details), or directly push data in with the intrinsic XML methods (see <a href="readandwritexml.aspx">Reading and Writing XML Data</a>); SQLDataSetCommand is yet another tool: it can also push data back into the database. Here we focus on getting data out.
<p>
First we construct a SQLDataSetCommand. This is done in the same way a SQLCommand is constructed.
<P>
<div class=code>
SQLDataSetCommand CustomersDSCommand = new SQLDataSetCommand("select * from customers", myConnection);
</div>
<p>
A SQLDataSetCommand is much like a SQLCommand with a few specific differences. Most significantly, the FillDataset and Update methods. The FillDataset method populates a DataSet. The Update method takes changes from a DataSet and pushes them back into the database. This is accomplished by placing four commands on the CommandSet (thus, the set of commands). These commands are: SelectCommand, UpdateCommand, InsertCommand and DeleteCommand. These commands can be constructed for you at run-time (you need only set the SelectCommand and the others will be set on demand), you can easily take more control of these. For example, if you use different stored procedures for Insert, Update, Delete, and so on you can establish this pattern in your SQLDataSetCommand.
<p>
**NOTE**: Visual Studio adds great value to establishing typed SQLDatasetCommands and DataSets, and eventually creates Stored Procedures for you. Explore this feature by using the Component designer and Database Objects.
<p>
Once your SQLDataSetCommand is established you can pass it a DataSet to populate:
The DataSet now holds the results of your query. In fact, the DataSet can hold results from multiple queries and even relate them (see <a href="relationaldata.aspx">Relational Data</a>). Because it holds multiple results, the DataSet contains a collection of tables. Notice that the FillDataSet method has "Customers" as the second argument. This is the name of the table to fill in the DataSet. If that table does not exist, it is created for you.
<p>
Because the data is stored in a collection of rows in the table, you can easily use For Each statement to iterate through the rows:
<p>
<div class=code>
<br> foreach( DataRow Customer in dsCustomer.Tables["Customers"].Rows)
In fact, you can use For Each over the columns as well. Below is an example placing together all of the code in this document. To learn how to update this data back into the database see <a href="updatedatafromdb.aspx">Updating Data in a Database</a>.
Note: Anytime a SQLDatasetCommand, SQLConnection or SQLCommand is referenced, the same API is generated on the corresponding ADO versions that work through OLEDB.
