N
ote The Fill() method will actually open and close the Connection for you if you don't
open it first, however, it is better to explicitly open and close the Connection
because that way it is clearer what your program is doing. Also, if you're calling the
F
ill() method repeatedly over a short span of code, you'll want to keep the database
connection open and close it only when you're finished.
The DataSet is now populated with a DataTable named Products. You can read the
Products DataTable from myDataSet using the following example:
DataTable myDataTable = myDataSet.Tables["Products"];
You can also read the Products DataTable using an int value:
DataTable myDataTable = myDataSet.Tables[0];
You can display the column values for each row in myDataTable using the following
foreach loop that iterates over the DataRow objects stored in myDataTable; notice the use
of the myDataTable object's Rows property:
foreach (DataRow myDataRow in myDataTable.Rows)
{
Console.WriteLine("ProductID = " + myDataRow["ProductID"]);
Console.WriteLine("ProductName = " + myDataRow["ProductName"]);
Console.WriteLine("UnitPrice = " + myDataRow["UnitPrice"]);
}
The Rows property returns a DataRowCollection object that allows you to access all the
DataRow objects stored in myDataTable. You can read each column value in a DataRow
using the name of the column; for example, to read the ProductID column value you use
myDataRow["ProductID"]. You can also use the numeric position of the column; for
example, myDataRow[0] returns the value for the first column. This is the ProductID
column.
You can also use the following code to iterate over all the DataTable, DataRow, and
DataColumn objects stored in myDataSet:
foreach (DataTable myDataTable in myDataSet.Tables)
{
foreach (DataRow myDataRow in myDataTable.Rows)
{
foreach (DataColumn myDataColumn in myDataTable.Columns)
{
Console.WriteLine(myDataColumn + "= " +
myDataRow[myDataColumn]);
}
}
}
Notice you don't need to know the names of the DataTable or DataColumn objects to
display them. The call to the WriteLine() method displays myDataColumn, which returns
the name of the column, and myDataRow[myDataColumn], which returns the column
value for the current row.
N
ote You'll see the details of the DataTable, DataRow, and DataColumn classes in
Chapter 11.
Listing 10.1 shows a program that uses the code examples shown in this section.
Listing 10.1: POPULATEDATASETUSINGSELECT.CS
/*
PopulateDataSetUsingSelect.cs illustrates how to populate a DataSet
object using a SELECT statement
*/
using System;
using System.Data;
using System.Data.SqlClient;
class PopulateDataSetUsingSelect
{
public static void Main()
{
SqlConnection mySqlConnection =
new SqlConnection(
"server=localhost;database=Northwind;uid=sa;pwd=sa"
);
// create a SqlCommand object and set its CommandText property
// to a SELECT statement that retrieves the top 5 rows from
// the Products table
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText =
"SELECT TOP 5 ProductID, ProductName, UnitPrice " +
"FROM Products " +
"ORDER BY ProductID";
// create a SqlDataAdapter object and set its SelectCommand
// property to the SqlCommand object
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
// create a DataSet object
DataSet myDataSet = new DataSet();
// open the database connection
mySqlConnection.Open();
// use the Fill() method of the SqlDataAdapter object to
// retrieve the rows from the table, storing the rows locally
// in a DataTable of the DataSet object
Console.WriteLine("Retrieving rows from the Products table");
int numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Products");
Console.WriteLine("numberOfRows = " + numberOfRows);
// close the database connection
mySqlConnection.Close();
// get the DataTable object from the DataSet object
DataTable myDataTable = myDataSet.Tables["Products"];
// display the column values for each row in the DataTable,
// using a DataRow object to access each row in the DataTable
foreach (DataRow myDataRow in myDataTable.Rows)
{
Console.WriteLine("ProductID = " + myDataRow["ProductID"]);
Console.WriteLine("ProductName = " + myDataRow["ProductName"]);
Console.WriteLine("UnitPrice = " + myDataRow["UnitPrice"]);
}
}
}
The output from this program is as follows:
Retrieving rows from the Products table
numberOfRows = 5
ProductID = 1
ProductName = Chai
UnitPrice = 18
ProductID = 2
ProductName = Chang
UnitPrice = 19
ProductID = 3
ProductName = Aniseed Syrup
UnitPrice = 10
ProductID = 4
ProductName = Chef Anton's Cajun Seasoning
UnitPrice = 22
ProductID = 5
ProductName = Chef Anton's Gumbo Mix
UnitPrice = 21.35
Using a Range of Rows
In this section, you'll learn how to populate a DataSet with a range of rows. Now, the
Fill() method is overloaded and a partial list of Fill() methods is as follows:
int Fill(DataSet myDataSet)
int Fill(DataTable myDataTable)
int Fill(DataSet myDataSet, string dataTableName)
int Fill(DataSet myDataSet, int startRow, int numOfRows,
string dataTableName)
where
dataTableName specifies a string containing the name of the DataTable to fill.
startRow is an int that specifies the position of the row in the result set to read (starting
at 0).
NumOfRows is an int that specifies the number rows to read.
The range of rows from startRow to startRow + numOfRows is then stored in the
DataTable . The int returned by the Fill() method is the number of rows retrieved from
the database.
As you can see, the final Fill() method allows you to populate a DataSet with a range of
rows. The following example shows the use of this Fill() method to store a range of rows.
It retrieves the top five rows from the Products table, but stores only three rows in the
Products DataTable, starting at position 1 (because rows are numbered starting at 0,
position 1 corresponds to the second row in the result set returned by the SELECT
statement):
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText =
"SELECT TOP 5 ProductID, ProductName, UnitPrice " +
"FROM Products " +
"ORDER BY ProductID";
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
DataSet myDataSet = new DataSet();
int numberOfRows = mySqlDataAdapter.Fill(myDataSet, 1, 3, "Products");
The numberOfRows variable is set to 3-the number of rows myDataSet was populated
with. One thing to remember is the DataAdapter still retrieves all five rows from the
Products table, but only three are actually used to populate the DataSet: the other two are
thrown away.
Listing 10.2 shows a program that uses the code examples shown in this section.
Listing 10.2: POPULATEDATASETUSINGRANGE.CS
/*
PopulateDataSetUsingRange.cs illustrates how to populate a DataSet
object with a range of rows from a SELECT statement
*/
using System;
using System.Data;
using System.Data.SqlClient;
class PopulateDataSetUsingRange
{
public static void Main()
{
SqlConnection mySqlConnection =
new SqlConnection(
"server=localhost;database=Northwind;uid=sa;pwd=sa"
);
// create a SqlCommand object and set its CommandText property
// to a SELECT statement that retrieves the top 5 rows from
// the Products table
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText =
"SELECT TOP 5 ProductID, ProductName, UnitPrice " +
"FROM Products " +
"ORDER BY ProductID";