Using Multiple Tables in a Single Dataset

So far, the datasets we've used only held a single table of data, but a dataset can contain several tables at once. To see how this works, take a look at the Ch09_03 example in the code for this book. This example uses two SqlDataAdapter controls, sqlDataAdapter1 , connected to the authors table in the pubs database, and sqlDataAdapter2 , connected to the titleauthor table in the pubs database (in this example, we'll select all fields of both these tables). When you create a dataset using these data adapters, you can add data from both adapters into a single dataset.

To see how this works, create these data adapter objects now and select Data, Generate Dataset. You can add both data tables to a single dataset if you select them both in the Generate Dataset dialog box, as you see in Figure 9.20.

Figure 9.20. Adding multiple tables to a dataset.

When you close the Generate Dataset dialog box, you've added both tables to the same dataset. To see the data in these tables, add two data grids to the main form, and set the DataSource property of both to the new dataset object, DataSet11 . Set the DataMember property of the first data grid to authors and the DataMember property of the second data grid to titleauthor . All that's left is to use the adapters to fill DataSet11 like this, which we can do when the form loads:

 private void Form1_Load(object sender, System.EventArgs e) {  sqlDataAdapter1.Fill(dataSet11);   sqlDataAdapter2.Fill(dataSet11);  } 

And that's it; you've stored two tables in one dataset. You can see the result in Figure 9.21, where both data grids are bound to the same dataset, but are displaying data from different tables.

Figure 9.21. Retrieving multiple tables from a dataset.

C# keeps track of the multiple tables in a dataset with the Tables collection, which we're going to take a look at more closely in Chapter 10. Each element in a Tables collection is a Table object, which corresponds to a data table. Each Table object has a Rows collection that holds the data in the rows of that table as Row objects. To access the data in an individual field, you use the Item property, and you can pass the field's name or number to the Item property. For example, field 0 in the authors table is the au_id field, so these statements both retrieve the author ID of author number 5 in the authors table (more on handling databases in code in Chapter 10):

 string ID = dataSet11.Tables[0].Rows[5].Item[0]; string ID = dataSet11.Tables[0].Rows[5].Item["au_id"]; 

Here's another useful visual tool you can use when you have a data adapter object. You can take a look at the data in that object by choosing Data, Preview Data in the IDE, and then clicking the Fill Dataset button in the Data Adapter Preview dialog box that opens. You can see this dialog box at work in Figure 9.22. This dialog box is useful when you want to look at the data a data adapter provides you at design time.

Figure 9.22. Previewing dataset data.

Although datasets don't hold any data at design time, you can get a look at a dataset's properties by selecting Data, Dataset Properties, or by right-clicking a dataset and selecting Dataset Properties. Doing so opens the Dataset Properties dialog box you see in Figure 9.23; the properties you see there include, among other things, the format of each field in each table in the dataset.

Figure 9.23. Previewing dataset properties.