<%@ Page Language="C#" MasterPageFile="~/howto/howto.master" %> <%@ Register TagPrefix=Acme Namespace=Acme %> <%@ Register TagPrefix="Acme" TagName="SourceRef" Src="~/util/SrcRef.ascx"%>

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); Dim SelectCmdString As String = "select * from customers" Dim mySqlDataAdapter As SqlDataAdapter = New SqlDataAdapter(SelectCmdString, myConnection) '...or this can also be done as follows: Dim mySelectCommand as SqlCommand = New SqlCommand("select * from customers", myConnection) Dim mySqlDataAdapter as SqlDataAdapter = new SqlDataAdapter(mySelectCommand) 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);

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"); myDataSet = New DataSet() mySqlDataAdapter.Fill(myDataSet,"Customers") DataSet * myDataSet = new DataSet(); mySqlDataAdapter->Fill(myDataSet,"Customers");

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()); } Dim myDataRow as DataRow For Each myDataRow In myDataSet.Tables("Customers").Rows Console.WriteLine(myDataRow("CustomerId").ToString()) Next for(int i =0;i< myDataSet->Tables->Item["Customers"]->Rows->Count; i++) { DataRow * row = myDataSet->Tables->Item["Customers"]->Rows->Item[i]; Console::WriteLine(row->Item["CustomerID"]->ToString()); }

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.