|
ADO.NET: Populate a DataSet from a Database
Getting data from a database is easy, and working with data is easier than before.
If you want the results from a database as a forward only, read-only stream of data, you
can execute a command and retrieve the results using the DataReader. For examples on how to use a
DataReader, see Retrieve Data Using OLE DB
and Retrieve Data from SQL Server. For more interactive
operations such as binding to, navigating through, or remoting
the results of a database query, you can place the results in a
DataSet as shown in this example.
The most important concept to remember is that the DataSet is a data structure separate
and distinct from a data store. Although you get data from a database in this example, 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.
This section describes how to use a SqlDataAdapter to get data from a database into a DataSet.
You can add data to the DataSet using the Add method (see
Update a Database from a DataSet), or
directly push data in with the intrinsic XML methods (see
Read and Write XML Data). SqlDataAdapter can be used to
retrieve data from a database but can also push data back
into the database. Here, the focus is on getting data out.
The first step is to construct a SqlDataAdapter. This is done in the same way a SqlCommand is
constructed.
String SelectCmdString = "select * from customers";
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(SelectCmdString, myConnection);
//...or this can also be done as follows:
SqlCommand mySelectCommand = New SqlCommand("select * from customers", myConnection);
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(mySelectCommand);
C#
The SqlDataAdapter and SqlCommand are very similar, except for the Fill and Update methods. The Fill method populates a
DataSet. The Update method takes changes from a DataSet and pushes them back into the
database. This is accomplished by four commands specified on the DataAdapter. These commands are:
SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand. You can explicitly set
these commands to control the statements used at run time to resolve changes,
including the use of stored procedures. For ad hoc scenarios, a CommandBuilder object
can generate these at run time based on a select statement (see
Update a Database from a DataSet). However, this run-time
generation requires an extra round trip to the server to gather required metadata,
so explicitly providing the insert, update, and delete commands at design time will result
in better run-time performance.
NOTE: Visual Studio adds great value to establishing typed SqlDataAdapters and
DataSets, and eventually creates Stored Procedures for you. Explore this feature by using
the ComponentDesigner and Database objects.
Once your SqlDataAdapter is established you can pass it a DataSet to populate:
myDataSet = new DataSet();
mySqlDataAdapter.Fill(myDataSet,"Customers");
C#
The DataSet now holds the results of your query. In fact, the DataSet can hold
results from multiple queries and even relate them (see
Work with Relational Data). Because it holds multiple
results, the DataSet contains a collection of tables. Notice that the Fill
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.
Because the data is stored in a collection of rows in the table, you can easily
use a foreach statement to iterate through the rows:
foreach (DataRow myDataRow in myDataSet.Tables["Customers"].Rows)
{
Console.WriteLine(myDataRow["CustomerId"].ToString());
}
C#
In fact, you can use foreach over the columns as well. The following example demonstrates
placing together all the code in this document. To learn how to update this
data back into the database see Update a Database
from a DataSet.
VB GettingData.aspx
Microsoft .NET Framework SDK QuickStart Tutorials Version 2.0
Copyright � 2004 Microsoft Corporation. All rights reserved.
|