Reading Data with the DataSet
You've just seen how to read data with a DataReader, so now let's look at how to accomplish the same task with the DataSet. First, let's take a detailed look at the structure of the DataSet.
The DataSet is the central object in ADO.NET; all operations of any complexity use it.
A DataSet contains a set of DataTable objects representing the database tables that you are working with.
| Note | Note: It can be just one DataTable. |
Each DataTable object has children DataRow and DataColumn objects representing the rows and columns of the database table. You can get to all the individual elements of the tables, rows, and columns through these objects, as you see in a moment.
Filling the DataSet with Data
Your favorite activity with the DataSet will probably be to fill it with data using the Fill() method of a data adapter object.
Why is Fill() a method of the data adapter and not the DataSet? This is because the DataSet is an abstract representation of data in memory, while the data adapter is the object that ties the DataSet to a particular database. Fill() has many overloads, but the one you will be using in this chapter takes two parameters — the first specifies the DataSet you want filled, and the second is the name of the DataTable within the DataSet that will contain the data you want loaded.
Accessing Tables, Rows, and Columns in the DataSet
The DataSet object has a property named Tables that is a collection of all the DataTable objects within the DataSet. Tables is of type DataTableCollection and has an overloaded indexer, which means that you can access each individual DataTable in one of two possible ways:
-
By table name: thisDataSet.Tables["Customers"] specifies the DataTable called Customers.
-
By index (the index is zero-based): thisDataSet.Tables[0] specifies the first DataTable in the DataSet.
Within each DataTable, there is a Rows property that is a collection of the individual DataRow objects. Rows is of type DataRowCollection, and is an ordered list, indexed by row number. Thus
myDataSet.Tables["Customers"].Rows[n]
specifies row number n-1 (remember the index is zero-based) in the Customers DataTable of thisDataSet. (Of course, you could have used the alternative index syntax to specify the DataTable as well.)
You might expect DataRow to have a property of type DataColumnCollection, but it's not as simple as that, because you want to take advantage of the data type of the individual columns in each row, so that a column containing character data becomes a string, a column containing an integer becomes an integer object, and so on.
The DataRow object itself has an indexer property that is overloaded, allowing you to access individual columns by name, and also by number. Thus
thisDataSet.Tables["Customers"].Rows[n]["CompanyName"]
specifies the CompanyName column of row number n-1 in the Customers DataTable of thisDataSet — the DataRow object here is thisDataSet.Tables["Customers"].Rows[n].
The structure just discussed is a little confusing, but Figure 24-5 gives you a picture of it.
Figure 24-5
In the following Try It Out you see all of this in practice by creating a program that uses the DataSet and its child classes. Let's start with a program that simply reads data. It is similar in function to the DataReading program, but accomplishes its task via the DataSet instead.
Try It Out – Reading Data with the DataSet
 |
Follow these steps to create the DataSetRead program in Visual Studio 2005.
-
Create a new console application called DataSetRead in the directory C:\BegVCSharp\ Chapter24.
-
Begin by adding the using directives for the ADO.NET classes you will be using:
#region Using Directives using System; using System.Data; // Use ADO.NET namespace using System.Data.SqlClient; // Use SQL Server data provider namespace using System.Collections.Generic; using System.Text; #endregion -
Now add the following code to the Main() method:
static void Main(string[] args) { // Specify SQL Server-specific connection string SqlConnection thisConnection = new SqlConnection( @"Server=(local)\sqlexpress;Integrated Security=True;" + "Database=northwind"); // Create DataAdapter object SqlDataAdapter thisAdapter = new SqlDataAdapter( "SELECT CustomerID, ContactName FROM Customers", thisConnection); // Create DataSet to contain related data tables, rows, and columns DataSet thisDataSet = new DataSet(); // Fill DataSet using query defined previously for DataAdapter thisAdapter.Fill(thisDataSet, "Customers"); foreach (DataRow theRow in thisDataSet.Tables["Customers"].Rows) { Console.WriteLine(theRow["CustomerID"] + "\t" + theRow["ContactName"]); } thisConnection.Close(); Console.Write("Program finished, press Enter/Return to continue:"); Console.ReadLine(); } -
Compile and execute this program. You will see the list of customer IDs and company names, as shown in Figure 24-6.
Figure 24-6
How It Works
First, you create a connection, and then use this connection to create a SqlDataAdapter object:
SqlConnection thisConnection = new SqlConnection( @"Server=(local)\sqlexpress;Integrated Security=True;" + "Database=northwind"); SqlDataAdapter thisAdapter = new SqlDataAdapter( "SELECT CustomerID, ContactName FROM Customers", thisConnection);
The next step is to create the DataSet that you want filled with data:
DataSet thisDataSet = new DataSet();
Now that you have your DataSet and your data adapter object in place (SqlDataAdapter here, since you are using the SQL Server provider), you can proceed to fill a DataTable in the DataSet:
thisAdapter.Fill(thisDataSet, "Customers");
A DataTable named Customers will be created in this DataSet. Note that this occurrence of the word Customers does not refer to the Customers table in the Northwind database — it specifies the name of the DataTable in the DataSet to be created and filled with data.
Now that the DataSet has been filled, you can access the individual rows and columns. The process for this is straightforward — you loop through all the DataRow objects in the Rows collection of the Customers DataTable. For each DataRow, you retrieve the values in the CustomerID and ContactName column:
foreach (DataRow theRow in thisDataSet.Tables["Customers"].Rows) { Console.WriteLine(theRow["CustomerID"] + "\t" + theRow["ContactName"]); } I mentioned earlier that the DataRow object has an indexer property that lets you access its individual columns by name and also by number. Thus, theRow["CustomerID"] specifies the CustomerID column of theRow DataRow, and theRow["ContactName"] specifies the ContactName column of theRow DataRow. Alternatively, you could have referred to the columns by number — CustomerID would be theRow[0] (it is the first column retrieved from the database), and ContactName as theRow[1].
You may have noticed that you have not explicitly opened or closed a connection in this example — the data adapter takes care of this for us. The data adapter object will open a connection as needed and close it again once it has finished its work. The data adapter will leave the state of the connection unchanged — so if the connection was open before the data adapter started its work, it will be still be open after the data adapter has finished.
Okay, you've seen how to read in data from a database. You've used a DataReader, which requires a connection to the database to be maintained while it is doing its work. You've also just used the data adapter to fill a DataSet — with this method the data adapter deals with the connection for you, opening it and closing it as needed. the DataReader also reads in a forward-only manner — it can navigate through records or jump to a given record. As its name suggests, the DataReader only reads data — the DataSet offers tremendous flexibility for reading and writing data, and working with data from different sources. You see the power of the DataSet unfold as you move through the chapter.
Reading data is only going to be half of what you want — you will usually want to modify data, add new data, or delete data. So let's get on with that. Your next step is to look at updating a database.
|
EAN: N/A
Pages: 278