11.5 Creating a Data Grid | Programming ASP.NET 3.5

You are now ready to return to the data grid examples from Chapter 10 and recode them by accessing the database. You will remember that in Example 10-7 and Example 10-8, you created a simple data grid, and then populated it with data from an ArrayList object. You can re-create that now using ADO.NET to get bug data from the database.

To start, create a new C# project, SimpleADODataGrid. Drag a DataGrid control onto the form, Visual Studio will name it DataGrid1. Accept all the default attributes as offered.

In the code-behind page's Page_Load method, you get the Bugs table from the database, just as you did in Example 11-3:

string connectionString =     "server=YourServer; uid=sa; " +    "pwd=YourPassword; database=ProgASPDotNetBugs"; // get records from the Bugs table string commandString =     "Select BugID, Description from Bugs"; // create the dataset  command object  // and the DataSet SqlDataAdapter dataAdapter =     new SqlDataAdapter(    commandString, connectionString); DataSet dataSet = new DataSet(  ); // fill the dataset  object dataAdapter.Fill(dataSet,"Bugs"); // Get the one table from the DataSet DataTable dataTable = dataSet.Tables[0];

This time, however, you'll bind to the data grid rather than to a list box. To do so, you set the DataGrid control's DataSource property to dataTable, the DataTable object you get from the dataset, and then call DataBind on the data grid:

DataGrid1.DataSource=dataTable; DataGrid1.DataBind(  );

When you run the page, hey! presto! the data grid is connected, as shown in Figure 11-8.

Figure 11-8. A simple data grid

Notice that the columns in the data grid have titles. These are the names of the columns from the Bugs table. Unless you tell it otherwise, the data grid picks up the titles from the columns in the database. You'll see how to modify this in a later example.

11.5.1 Displaying Relational Data

If you change the commandString in Example 11-2 from:

string commandString =     "Select BugID, Description from Bugs";

to:

string commandString =     "Select * from Bugs";

to get all the fields in the table, the output (shown in Figure 11-9) reflects the fact that some of the fields have numeric IDs that do not convey a lot of information to the user.

Figure 11-9. Showing the ID fields

The information you would like to show is the name of the product and the name of the person filing the report. You accomplish this by using a more sophisticated SQL select statement in the command string:

string commandString =  "Select b.BugID, b.Description, p.ProductDescription,  peo.FullName from Bugs b join lkProduct p on b.Product = p.ProductID  join People peo on b.Reporter = peo.PersonID ";

In this select statement, you are drawing fields from three tables: Bugs, lkProduct, and People. You join the lkProduct table to the Bugs table on the ProductID in the Bugs record, and you join the People table to the PersonID of the Reporter field in Bugs.

The results are shown in Figure 11-10.

Figure 11-10. Using the join statement

This is better, but the headers are not what we might hope, and the grid is a bit ugly. The best way to solve these problems is with attributes for the DataGrid, as you saw in Chapter 10. Adding just a few attributes to the data grid, you can control which columns are displayed and how the headers are written, and you can provide a nicer background color for the header row. The following code does this:

<asp:DataGrid  runat="server" CellPadding="5"  HeaderStyle-BackColor="PapayaWhip" BorderWidth="5px"  BorderColor="#000099" AlternatingItemStyle-BackColor="LightGrey"  HeaderStyle-Font-Bold="True" AutoGenerateColumns="False">    <Columns>       <asp:BoundColumn DataField="BugID" HeaderText="ID" />       <asp:BoundColumn DataField="Description"        HeaderText="Description" />       <asp:BoundColumn DataField="ProductDescription"        HeaderText="Product" />       <asp:BoundColumn DataField="FullName"        HeaderText="Reported By" />    </Columns> </asp:DataGrid>

You will remember from Chapter 10 that the AutoGenerateColumns attribute tells the grid whether to pick up all the columns from the data source; by setting it to false, you tell the grid that you will specify which columns to display in the Columns attribute.

Nested within the Columns attribute are BoundColumn attributes, which delineate which field will supply the data (e.g., BugID, ProductDescription, FullName) and the header to display in the DataGrid (e.g., BugID, Product, Reported By). The result is shown in Figure 11-11.

Figure 11-11. Using attributes to control the display

11.5.2 Displaying Parent/Child Relationships

You would like to offer the user the ability to see the complete history for a given Bug. To do this, you'll add a column with a button marked "History." When the user clicks on the button, you'll display a second grid with the Bug History.

The BugHistory records act as child records to the Bug records. For each Bug there will be a set of one or more BugHistory records. For each BugHistory record there will be exactly one Bug parent record. This section will explore the first of a number of ways to display these related records. Alternative ways to display this relationship will be shown later in this chapter.

To start, add the ButtonColumn to the Data Grid and add an attribute for the OnSelectedIndexChanged event. Set the DataKeyField attribute to BugID; this is the primary key for the Bugs table and will serve as the foreign key for the BugHistory grid:

<asp:DataGrid  runat="server"  DataKeyField="BugID"   CellPadding="5" HeaderStyle-BackColor="PapayaWhip" BorderWidth="5px"  BorderColor="#000099" OnItemDataBound="OnItemDataBoundEventHandler"  OnSelectedIndexChanged="OnSelectedIndexChangedHandler"  AlternatingItemStyle-BackColor="LightGrey" HeaderStyle-Font-Bold="True"  AutoGenerateColumns="False" EnableViewState="true">    <Columns>       <asp:ButtonColumn Text="History" CommandName="Select" />       <asp:BoundColumn DataField="BugID" HeaderText="Bug ID" />       <asp:BoundColumn DataField="Description"           HeaderText="Description" />       <asp:BoundColumn DataField="Reporter"           HeaderText="Reported By" />       <asp:BoundColumn DataField="Response"           HeaderText="Most Recent Action" />       <asp:BoundColumn DataField="Owner"           HeaderText="Owned By" />       <asp:BoundColumn DataField="StatusDescription"           HeaderText="Status" />       <asp:BoundColumn DataField="SeverityDescription"           HeaderText="Severity" />       <asp:BoundColumn DataField="DateStamp"           HeaderText="LastUpdated" />    </Columns> </asp:DataGrid>

Add a Panel control to hold the history grid. This serves the same purpose as the details panel in Example 10-11; you'll make this panel visible or invisible depending on whether or not you are showing the history of a bug. Add the following code to the HTML page:

<asp:Panel  Runat="server">    <asp:DataGrid  AutoGenerateColumns="False"      HeaderStyle-Font-Bold="True" AlternatingItemStyle-BackColor="LightGrey"      BorderColor="#000099" BorderWidth="5px"      HeaderStyle-BackColor="PapayaWhip"      CellPadding="5" Runat="server">       <Columns>          <asp:BoundColumn DataField="Response"              HeaderText="Most Recent Action" />          <asp:BoundColumn DataField="Owner"              HeaderText="Owned By" />          <asp:BoundColumn DataField="StatusDescription"              HeaderText="Status" />          <asp:BoundColumn DataField="SeverityDescription"              HeaderText="Severity" />          <asp:BoundColumn DataField="DateStamp"              HeaderText="LastUpdated" />       </Columns>    </asp:DataGrid> </asp:Panel>

The supporting code-behind page is shown in Example 11-6 for C# and Example 11-7 for VB.NET. Complete analysis follows the listings.

Example 11-6. C# code-behind page

using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Text; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; namespace BugHistoryDynamic {    /// <summary>    /// Summary description for WebForm1.    /// </summary>    public class WebForm1 : System.Web.UI.Page    {       protected System.Web.UI.WebControls.DataGrid DataGrid1;       protected System.Web.UI.WebControls.DataGrid HistoryGrid;       protected System.Web.UI.WebControls.Panel BugHistoryPanel;           public WebForm1(  )       {          Page.Init += new System.EventHandler(Page_Init);       }       // When the item is added to the bug grid,        // if the status is high write it in red       public void OnItemDataBoundEventHandler(          Object sender, DataGridItemEventArgs e)       {          ListItemType itemType = (ListItemType)e.Item.ItemType;          if (itemType == ListItemType.Header ||              itemType == ListItemType.Footer ||              itemType == ListItemType.Separator)             return;                    if (((DataRowView)e.Item.DataItem).             Row.ItemArray[8].ToString(  ) == "High")          {             TableCell severityCell =                 (TableCell) e.Item.Controls[6];             severityCell.ForeColor = Color.FromName("Red");          }       }       // the user has selected a row       // display the history for that bug       public void OnSelectedIndexChangedHandler(          Object sender, EventArgs e)      {        UpdateBugHistory(  );     }       // If the user has selected a row       // display the history panel       private void UpdateBugHistory(  )     {        int index = DataGrid1.SelectedIndex;        if (index != -1)        {           // get the bug id from the data grid           int bugID =                (int) DataGrid1.DataKeys[index];           // Get a dataset based on that BugID           DataSet dataSet =               CreateBugHistoryDataSet(bugID);           // bind to the table returned and make           // the panel visible           HistoryGrid.DataSource=dataSet.Tables[0];           HistoryGrid.DataBind(  );           BugHistoryPanel.Visible=true;        }        else        {           // no history to display, hide the panel           BugHistoryPanel.Visible=false;        }     }       // The first time you load the page, populate the       // bug grid and hide the history grid       private void Page_Load(          object sender, System.EventArgs e)     {        if (!IsPostBack)        {           // hide the history panel           UpdateBugHistory(  );           // set the data source for the            // grid to the first table            DataSet ds = CreateBugDataSet(  );           DataGrid1.DataSource=ds.Tables[0];           DataGrid1.DataBind(  );        }           }       // create a dataset for the bug history records       private DataSet CreateBugHistoryDataSet(int bugID)       {          // connection string to connect to the Bugs Database          string connectionString =              "server=YourServer; uid=sa; " +             "pwd=YourPassword; database=ProgASPDotNetBugs";          // Create connection object, initialize with           // connection string. Open it.          System.Data.SqlClient.SqlConnection connection =              new System.Data.SqlClient.SqlConnection(connectionString);          connection.Open(  );          // create a second command object for the bugs history table          System.Data.SqlClient.SqlCommand command =              new System.Data.SqlClient.SqlCommand(  );          command.Connection = connection;          StringBuilder s =              new StringBuilder("Select BugID, StatusDescription, ");          s.Append("SeverityDescription, Response, ");          s.Append("FullName as Owner, DateStamp ");          s.Append("from BugHistory h ");          s.Append("join People o on h.Owner = o.PersonID ");          s.Append("join lkStatus s on s.statusid = h.status ");          s.Append(             "join lkSeverity sev on sev.SeverityID = h.severity ");          s.Append("where BugID = " + bugID);          command.CommandText= s.ToString(  );          // create a second data adapter and add the command           // and map the table          // then fill the dataset from this second adapter          SqlDataAdapter dataAdapter = new SqlDataAdapter(  );          dataAdapter.SelectCommand = command;          dataAdapter.TableMappings.Add("Table", "BugHistory");          DataSet dataSet = new DataSet(  );          dataAdapter.Fill(dataSet);          return dataSet;       }       // create a dataset for the bug table       private DataSet CreateBugDataSet(  )       {          // connection string to connect to the Bugs Database          string connectionString =              "server=YourServer; uid=sa; " +              "pwd=YourPassword; database=ProgASPDotNetBugs";          // Create connection object, initialize with           // connection string. Open it.          System.Data.SqlClient.SqlConnection connection =              new System.Data.SqlClient.SqlConnection(connectionString);          connection.Open(  );          // Create a SqlCommand object and assign the connection          System.Data.SqlClient.SqlCommand command =              new System.Data.SqlClient.SqlCommand(  );          command.Connection=connection;          // build the selection statement          StringBuilder s =              new StringBuilder(             "Select b.BugID, h.BugHistoryID, b.Description,h.Response, ");          s.Append("o.FullName as owner, ");          s.Append("p.ProductDescription, ");          s.Append("r.FullName as reporter, ");          s.Append("s.StatusDescription, ");          s.Append("sev.SeverityDescription, ");          s.Append("h.DateStamp ");          s.Append("from  ");          s.Append(            "(select bugID, max(bugHistoryID) as maxHistoryID ");          s.Append("from BugHistory group by bugID) t ");          s.Append("join bugs b on b.bugid = t.bugid ");          s.Append(             "join BugHistory h on h.bugHistoryID = t.maxHistoryID ");          s.Append("join lkProduct p on b.Product = p.ProductID  ");          s.Append("join People r on b.Reporter = r.PersonID  ");          s.Append("join People o on h.Owner = o.PersonID ");          s.Append("join lkStatus s on s.statusid = h.status ");          s.Append(             "join lkSeverity sev on sev.SeverityID = h.severity ");          // set the command text to the select statement          command.CommandText=s.ToString(  );          // create a data adapter and assign the command object          // and add the table mapping for bugs          SqlDataAdapter dataAdapter = new SqlDataAdapter(  );          dataAdapter.SelectCommand=command;          dataAdapter.TableMappings.Add("Table","Bugs");          // Create the dataset and use the data adapter to fill it          DataSet dataSet = new DataSet(  );          dataAdapter.Fill(dataSet);          return dataSet;       }       private void Page_Init(object sender, EventArgs e)       {          InitializeComponent(  );       }       #region Web Form Designer generated code       /// <summary>       /// Required method for Designer support - do not modify       /// the contents of this method with the code editor.       /// </summary>       private void InitializeComponent(  )       {              this.Load += new System.EventHandler(this.Page_Load);       }       #endregion    }    }

Example 11-7. B.NET code-behind page

Imports System Imports System.Web Imports System.Web.UI Imports System.Web.UI.WebControls Imports System.Data Public Class WebForm1    Inherits System.Web.UI.Page    Protected WithEvents DataGrid1 As _       System.Web.UI.WebControls.DataGrid    Protected WithEvents HistoryGrid As _       System.Web.UI.WebControls.DataGrid    Protected WithEvents BugHistoryPanel As _       System.Web.UI.WebControls.Panel #Region " Web Form Designer Generated Code "    'This call is required by the Web Form Designer.    <System.Diagnostics.DebuggerStepThrough(  )> Private Sub InitializeComponent(  )    End Sub    Private Sub Page_Init(ByVal sender As System.Object, _       ByVal e As System.EventArgs) _       Handles MyBase.Init       'CODEGEN: This method call is required by the Web Form Designer       'Do not modify it using the code editor.       InitializeComponent(  )    End Sub #End Region    Private Sub Page_Load( _       ByVal sender As System.Object, _       ByVal e As System.EventArgs) Handles MyBase.Load       'Put user code to initialize the page here       If Not IsPostBack Then          UpdateBugHistory(  )          Dim ds As DataSet = CreateBugDataSet(  )          DataGrid1.DataSource = ds.Tables(0)          DataGrid1.DataBind(  )       End If    End Sub    Public Sub DataGrid1_ItemDataBound( _       ByVal sender As Object, _       ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) _       Handles DataGrid1.ItemDataBound       Dim myItemtype As ListItemType       myItemtype = CType(e.Item.ItemType, ListItemType)       If (myItemtype = ListItemType.Header) _       Or (myItemtype = ListItemType.Footer) _       Or (myItemtype = ListItemType.Separator) Then          Return       End If       Dim obj As Object = _          CType(e.Item.DataItem, DataRowView).Row.ItemArray(8)       If CType(e.Item.DataItem, DataRowView).Row.ItemArray(8).ToString( )_             = "High" Then          Dim severityCell As TableCell = _             CType(e.Item.Controls(6), TableCell)          severityCell.ForeColor = Color.FromName("Red")       End If    End Sub    Private Function CreateBugHistoryDataSet(ByVal bugID As Integer) _          As DataSet       Dim connectionString As String = _          "server=YourServer; uid=sa; pwd=YourPassword; " + _          "database=ProgASPDotNetBugs"       Dim connection As _          New System.Data.SqlClient.SqlConnection(connectionString)       connection.Open(  )       Dim command As New System.Data.SqlClient.SqlCommand(  )       command.Connection = connection       Dim s As New String( _          "Select BugID, StatusDescription, severityDescription, ")       s = s & "Response, FullName as owner, DateStamp from BugHistory h "       s = s & "join People p on h.owner = p.personID "       s = s & "join lkStatus s on s.statusid = h.status "       s = s & "join lkSeverity sev on sev.severityID = h.severity "       s = s & "where bugid = " & bugID       command.CommandText = s       Dim myDataAdapter As New SqlClient.SqlDataAdapter(  )       myDataAdapter.SelectCommand = command       myDataAdapter.TableMappings.Add("Table", "BugHistory")       Dim ds As New DataSet(  )       myDataAdapter.Fill(ds)       Return ds    End Function    Private Function CreateBugDataSet(  ) As DataSet       Dim connectionString As String = _          "server=YourServer; uid=sa; pwd=YourPassword; " + _          "database=ProgASPDotNetBugs"       Dim connection As _          New System.Data.SqlClient.SqlConnection(connectionString)       connection.Open(  )       Dim command As New System.Data.SqlClient.SqlCommand(  )       command.Connection = connection       Dim s As New String( _          "Select b.bugID, h.bugHistoryID, b.description, h.response, ")       s = s & "o.Fullname as owner, p.ProductDescription, "       s = s & "r.FullName as reporter, "       s = s & "s.statusDescription, sev.SeverityDescription, h.DateStamp "       s = s & "from (select bugID, max(bugHistoryID) as maxHistoryID "       s = s & "from BugHistory group by bugID) t "       s = s & "join bugs b on b.bugid = t.bugID "       s = s & "join BugHistory h on h.bugHistoryID = t.maxHistoryID "       s = s & "join lkProduct p on b.Product = p.ProductID  "       s = s & "join People r on b.Reporter = r.PersonID  "       s = s & "join People o on h.Owner = o.PersonID  "       s = s & "join lkStatus s on s.statusid = h.status  "       s = s & "join lkSeverity sev on sev.SeverityID = h.severity  "       command.CommandText = s       Dim myDataAdapter As New SqlClient.SqlDataAdapter(  )       myDataAdapter.SelectCommand = command       myDataAdapter.TableMappings.Add("Table", "Bugs")       Dim ds As New DataSet(  )       myDataAdapter.Fill(ds)       Return ds    End Function    Public Sub DataGrid1_SelectedIndexChanged( _       ByVal sender As Object, _       ByVal e As System.EventArgs) _       Handles DataGrid1.SelectedIndexChanged       UpdateBugHistory(  )    End Sub    Private Sub UpdateBugHistory(  )       Dim index As Integer = DataGrid1.SelectedIndex       If index <> -1 Then          Dim bugID As Integer = _             CType(DataGrid1.DataKeys(index), Integer)          Dim myDataSet As DataSet = CreateBugHistoryDataSet(bugID)          HistoryGrid.DataSource = myDataSet.Tables(0)          HistoryGrid.DataBind(  )          BugHistoryPanel.Visible = True       Else          BugHistoryPanel.Visible = False       End If    End Sub End Class

The Page_Load event handler creates the dataset for the bug grid the first time the page is viewed (that is, the IsPostBack property is false).

When the user clicks on the History button, the OnSelectedIndexChangedHandler event fires. You call a private method, UpdateBugHistory, that determines if the Panel control should be shown or not.

UpdateBugHistory checks the SelectedIndex property from the DataGrid. If the value of SelectedIndex is not -1 (that is, if a selection has been made), the index is used as an offset into the DataGrid's DataKeys collection.

The dataset itself is created by the CreateBugHistoryDataSet method into which you pass the bugID as a parameter. This method formulates an SQL select statement and fills a dataset with the resulting records.

When you first display the page, only the Bug data grid is displayed, as shown in Figure 11-12.

Figure 11-12. Displaying the Bug DataGrid

If the user clicks on the History button, you retrieve the index of the item clicked on and use that as an offset into the Datakeys collection to get the BugID. With the BugID, you can create a dataset of the matching history records, which is displayed in the HistoryDataGrid in the BugHistoryPanel that you now make visible, as shown in Figure 11-13.

Figure 11-13. Displaying the bug history

11.5.3 Using a DataReader

In the previous example, the BugHistory grid was filled from a table in a dataset. While datasets are very powerful disconnected data sources, they may require more overhead than is needed in this example.

If what you want to do is to retrieve a set of records and then immediately display them, an SqlDataReader or an OleDbDataReader object may be more efficient.

DataReaders are very limited compared to datasets. They offer only a "firehose" cursor for forward-only iteration through a set of results. You can also use DataReaders to execute a simple insert, update, or delete SQL statement.

Because datasets have greater overhead than DataReaders, you should choose a DataReader as your data source whenever possible. DataReaders are not disconnected, however, and so you lose the specific advantages of disconnected datasets. You will certainly need a dataset to meet any of the following requirements:

To pass a disconnected set of data to another tier in your application or to a client application.
To persist your results either to a file or to a Session object.
To provide access to more than one table and to relationships among the tables.
To bind the same data to multiple controls. Remember, a DataReader object provides forward-only access to the data; you can not reiterate through the data for a second control.
To jump to a particular record or to go backwards through a set of data.
To update a number of records in the back-end database using a batch operation.

When you have simpler requirements, however, the DataReader object is a great lightweight alternative to the more complicated dataset. Rewriting the previous example to use a DataReader is almost trivial. You'll modify the CreateBugHistoryDataSet method to return an SqlDataReader object rather than a dataset.

To get started, set up the connection string, SqlConnection object, and SqlCommand object exactly as you did previously. Once your Command object is established, create the DataReader. You cannot call the DataReader's constructor directly; instead you call ExecuteReader on the SqlCommand object; what you get back is an instance of SqlDataReader, as the following code fragment shows:

SqlDataReader reader =     command.ExecuteReader(CommandBehavior.CloseConnection);

The optional CommandBehavior.CloseConnection argument is an enumerated value that tells the SqlDataReader object that when it is closed, it should close the connection to the database.

You can then assign that SqlDataReader object as the DataSource for your DataGrid:

HistoryGrid.DataSource=reader;

After you bind the DataGrid, you must call Close on the SqlDataReader to tell it to break the connection to the database:

HistoryGrid.DataBind(  ); reader.Close(  );

That's all there is to it. To modify Example 11-6 to use SqlDataReader, make the following three changes:

Modify the return value and name of the CreateBugHistoryDataSet as follows:
```
private SqlDataReader CreateBugHistoryDataReader(int bugID)
```

Replace the following lines from what was CreateBugHistoryDataSet:

SqlDataAdapter dataAdapter = new SqlDataAdapter(  ); dataAdapter.SelectCommand = command; dataAdapter.TableMappings.Add("Table", "BugHistory"); DataSet dataSet = new DataSet(  ); dataAdapter.Fill(dataSet); return dataSet;

with these lines:

SqlDataReader reader =     command.ExecuteReader(CommandBehavior.CloseConnection); return reader;

Modify these three lines from UpdateBugHistory:

DataSet dataSet =      CreateBugHistoryDataSet(bugID);  HistoryGrid.DataSource=dataSet.Tables[0];  HistoryGrid.DataBind(  );

with this replacement:

SqlDataReader  reader = CreateBugHistoryDataReader(bugID); HistoryGrid.DataSource=reader; HistoryGrid.DataBind(  ); reader.Close(  );

Recompile and the program will now use a (connected forward-only firehose) SqlDataReader rather than a (disconnected) SqlDataSet to bind the Bug History data grid.

11.5.4 DataView

In Example 11-6, you query the database for the history records each time you redraw the history grid. An alternative is to retrieve all of the history records once, and then to filter the results to retrieve the history records you want to display. In a larger database, this might become unwieldy, but, for example, you might fill the Bugs table with just the 50 most recent bugs, and you would then fill the history table with the history records for just those 50 bugs. In this way you can reduce the number of calls to the database, in exchange for holding many more records in memory.

To make this work, you'll assign the historyGrid's data source to a DataView object, rather than to a table. The DataView object will represent a "view" of the table, typically filtered by the particular bug of interest.

You can revise the previous example by getting all the BugHistory records at the same time that you get all the bug records. You'll put the Bug records into one table in the dataset, and the BugHistory records into a second table in the dataset.

When the page is first created, you'll create a DataView object based on the second table (BugHistory), and you'll make that view be the DataSource for the HistoryGrid:

DataView historyView = new DataView(ds.Tables[1]); HistoryGrid.DataSource = historyView;

When the user clicks on a record you will once again get the BugID by using the selected row as an index into the DataGrid object's DataKeys collection. This time, however, you will use that bugID to filter the view you've created:

historyView.RowFilter = "BugID = " + bugID;

The RowFilter property of the DataView object allows you to filter the view for those records you want. The view will only present records which match the filter. RowFilters use the SQL syntax of a where clause. The RowFilter above equates to the clause "where BugID = 2".

Unfortunately, your class is destroyed and re-created each time the page is posted. Your historyView object will not persist, even if you were to make it an instance variable of the WebForm1 class. You could, of course, re-create the view by reissuing the query, but this would undermine the point of getting the entire set of history records in the first place.

In a production system, you might get the view from outside your application. For example, you might be interacting with a web service that provides the DataView. In this example, since you don't have such a web service, you'll stash the DataView into the session state.

Saving your view in session state works fine as long as your server is on a single machine. Once your server grows to multiple machines, you'll probably save session state to a database, in which case it is silly to keep the DataView in session state. At that point, you might as well issue smaller queries directly to the database for each update, rather than retrieving the entire set of history records from the database into session state and then back into your program and then filtering the results.

To save the DataView in session state, you just create a "key" 506a string which will be used to identify your session state variable:

Session["historyView"] = historyView;

Here the DataView object historyView is saved to session state with the string "historyView" as its key. Session variables act like properties; you can simply assign them to an object, remembering to cast to the appropriate type:

DataView historyView = (DataView) Session["historyView"];

11.5.5 Creating Data Relations

Because the DataSet acts as a disconnected model of the database, it must be able to represent not only the tables within the database, but the relations among the tables as well.

The DataSet captures these relationships in a DataRelationCollection that you access through the read-only Relations property. The DataRelationCollection is a collection of DataRelation objects, each of which represents a relationship between two tables.

Each DataRelation object relates a pair of DataTable objects to each other through DataColumn objects. The relationship is established by matching columns in the two tables.

The DataRelation objects retrieved through the Relations property of the DataSet provides you with meta-data: data about the relationship among the tables in the database. You can use this meta-data in a number of ways. For example, you can generate a schema for your database from the information contained in the dataset.

In the next example, you will create DataRelation objects to model two relationships within the Bugs database. The first DataRelation object you create will represent the relationship between the Bugs table and the BugHistory table through the BugID. The second relationship you will model is between the BugHistory table and the lkSeverity table through the SeverityID.

You will remember that the BugHistory table uses the BugID from the Bugs table as a foreign key. You thus need a column object for the BugID column in each of the tables:

System.Data.DataColumn dataColumn1; System.Data.DataColumn dataColumn2; dataColumn1 =     dataSet.Tables["Bugs"].Columns["BugID"]; dataColumn2 =     dataSet.Tables["BugHistory"].Columns["BugID"];

With these two columns in hand, you are ready to initialize the DataRelation object that you will use to model the relationship between the Bugs and BugHistory tables. You pass in the two data columns along with a name for the relationship, in this case BugsToHistory:

dataRelation = new System.Data.DataRelation(    "BugsToHistory",     dataColumn1,     dataColumn2);

You can of course combine the declaration and initialization of the DataRelation object:

System.Data.DataRelation dataRelation =     new System.Data.DataRelation(    "BugsToHistory",     dataColumn1,     dataColumn2);

You now add the Relation to the DataRelationCollection collection in the dataset:

dataSet.Relations.Add(dataRelation);

To create the second DataRelation, between the BugHistory and lkSeverity tables, you first create a "lkSeverity" table within the dataset:

StringBuilder s3 =     new StringBuilder(   "Select SeverityID, SeverityDescription from lkSeverity"); command3.CommandText= s3.ToString(  ); SqlDataAdapter dataAdapter3 = new SqlDataAdapter(  ); dataAdapter3.SelectCommand = command3; dataAdapter3.TableMappings.Add("Table", "lkSeverity"); dataAdapter3.Fill(dataSet);

You are now ready to create the data relation between the History table and the Severity table:

dataColumn1 = dataSet.Tables["lkSeverity"].Columns["SeverityID"]; dataColumn2 = dataSet.Tables["BugHistory"].Columns["Severity"];     dataRelation =     new System.Data.DataRelation(    "HistoryToSeverity",     dataColumn1,     dataColumn2); dataSet.Relations.Add(dataRelation);

In the previous example, you did not need to get the Severity value in the select statement that builds the BugHistory table. You joined on the lkSeverity table and got the SeverityDescription, but not the ID. To create the relation, however, you now do need that value. Be sure to modify the select statement:

StringBuilder s2 =     new StringBuilder("Select BugID, BugHistoryID,    StatusDescription, "); s2.Append(     "Severity, SeverityDescription, Response,  FullName as    Owner, DateStamp "); s2.Append("from BugHistory h "); s2.Append("join People o on h.Owner = o.PersonID "); s2.Append("join lkStatus s on s.statusid = h.status "); s2.Append(    "join lkSeverity sev on sev.SeverityID = h.severity "); command2.CommandText= s2.ToString(  );

If you neglect to select the SeverityID (History.Severity) and you try to establish a relation between History.Severity and lkSeverity.SeverityID you will get an error at run time that the column is null. This can make you a bit crazy until you figure out that there is no Severity column in the BugHistory table within the dataset even though that column certainly does exist in the database.

You can now display these relations by creating a data grid and setting its dataSource to the Relations table of the dataSet. In the .aspx file add this code:

<asp:DataGrid  Runat="server"    HeaderStyle-Font-Bold AlternatingItemStyle-BackColor="LightGrey"     BorderColor="#000099" BorderWidth="5px"     HeaderStyle-BackColor="PapayaWhip"     CellPadding="5" Runat="server"/>        <br>

In the Page_Load method of the code-behind file, add these two lines:

BugRelations.DataSource=ds.Relations; BugRelations.DataBind(  );

In a real product, you might create a nested grid structure in which you would show first a Bug and then all its history elements. Rather than focusing on the user interface, in this example you'll just build a string output of these relationships, printing these to an HTML page using an ASP Label control.

Figure 11-14 shows the result of displaying both the collection of DataRelation objects and a hand-built string produced by iterating through the Bugs table and the related BugHistory records.

Figure 11-14. Showing the Bug and BugHistory relations

Figure 11-14 shows three grids. The first is created from the Bugs table, as seen in previous examples. The second is created from the lkSeverity table added in this example. The final grid's data source is the Relations table from the dataset. It shows that you've created two relation objects: BugsToHistory and HistoryToSeverity.

Below the three grids is the text output produced by walking through the relationships between Bugs and BugHistory. For each Bug (e.g., BugID 1) you see the BugHistory records with that same BugID (e.g, the eight bug History records for BugID 1).

Normally, as here, the parent/child relationship between two tables is managed by the DataRelation object; the two tables are otherwise independent of one another.

With XML however, parent/child relationships are represented by nesting attributes one within the other. To facilitate synchronizing with an XML document or writing out the dataset as XML (using the DataSet object's WriteXml method), the DataRelation object has a Nested property. When Nested is set to true, the child rows of the relation are nested within the parent column when written as XML or when synchronized with an XML data document.

The .aspx file is very similar to the previous examples, you have only to add the new grids and the label for output:

<asp:DataGrid  Runat="server"    HeaderStyle-Font-Bold="True" AlternatingItemStyle-BackColor="LightGrey"     BorderColor="#000099" BorderWidth="5px" HeaderStyle-BackColor="PapayaWhip"     CellPadding="5" Runat="server"/>    <br> <asp:DataGrid  Runat="server"    HeaderStyle-Font-Bold="True" AlternatingItemStyle-BackColor="LightGrey"     BorderColor="#000099" BorderWidth="5px" HeaderStyle-BackColor="PapayaWhip"     CellPadding="5" Runat="server"/>        <br> <asp:Label  Runat="server"></asp:Label>

To create the label showing the relationships, you'll work your way through the Bugs table by hand, finding all the related BugHistory items for each of the Bug objects.

You iterate through the rows in the Bugs data table. For each row, you create an output string with the BugID, and then you get a collection of the child rows defined by the BugsToHistory relation:

DataTable tblBugs = ds.Tables["Bugs"]; foreach (DataRow currentRow in tblBugs.Rows) {    outputString += "BugID: " + currentRow["BugID"] + "<br/>";    DataRow[] childRows = currentRow.GetChildRows("BugsToHistory");

The childRows DataRow collection contains all the child rows for the current row in the Bugs table. The childRow relationship is established by the DataRelation named BugsToHistory, which established a relationship between the BugID foreign key in BugHistory and the BugID key in Bugs.

You can now iterate through that childRows collection, printing whatever information you want to display for each BugHistory record for the current bug:

foreach (DataRow historyRow in childRows) {    outputString += historyRow["BugHistoryID"] + ": " +        historyRow["Response"] + "<br>"; }

When you've iterated through all the rows, you can assign the resulting string to the Text property of the label you've added to your .aspx page:

showRelations.Text=outputString;

The complete annotated C# source code for the code-behind page is shown in Example 11-8.

Example 11-8. Code-behind page

using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Text; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; namespace BugHistoryDataGridRelations {    /// <summary>    /// Summary description for WebForm1.    /// </summary>    public class WebForm1 : System.Web.UI.Page    {       // the Bugs Data Grid       protected System.Web.UI.WebControls.DataGrid DataGrid1;       // the Data Grid for the history items displayed using       // a filtered view       protected System.Web.UI.WebControls.DataGrid HistoryGrid;       // the Data Grid to show the lkSeverity table       protected System.Web.UI.WebControls.DataGrid SeverityGrid;       // the Data Grid to show the DataRelations you've created       protected System.Web.UI.WebControls.DataGrid BugRelations;       // The panel to hold the history grid        protected System.Web.UI.WebControls.Panel BugHistoryPanel;       // The label for the hand-crafted string showing the       // relation between a Bug and its child History records       protected System.Web.UI.WebControls.Label showRelations;       // unchanged from previous example       public WebForm1(  )       {          Page.Init += new System.EventHandler(Page_Init);       }       // unchanged from previous example       public void OnItemDataBoundEventHandler(          Object sender, DataGridItemEventArgs e)       {          ListItemType itemType = (ListItemType)e.Item.ItemType;          if (itemType == ListItemType.Header ||              itemType == ListItemType.Footer ||              itemType == ListItemType.Separator)             return;                    if (((DataRowView)e.Item.DataItem).Row.ItemArray[8].ToString(  )              == "High")          {             TableCell severityCell = (TableCell) e.Item.Controls[6];             severityCell.ForeColor = Color.FromName("Red");          }       }       // unchanged from previous example       public void OnSelectedIndexChangedHandler(          Object sender, EventArgs e)        {          UpdateBugHistory(  );       }       // unchanged from previous example       private void UpdateBugHistory(  )       {            int index = DataGrid1.SelectedIndex;          if (index != -1)          {             // get the bug id from the data grid             int bugID =  (int) DataGrid1.DataKeys[index];             DataView historyView = (DataView) Session["historyView"];             historyView.RowFilter = "BugID = " + bugID;             HistoryGrid.DataSource = historyView;             HistoryGrid.DataBind(  );             BugHistoryPanel.Visible=true;          }          else          {             BugHistoryPanel.Visible=false;          }       }       // build the various tables, views, dataSets  and data relations       private void Page_Load(object sender, System.EventArgs e)       {          if (!IsPostBack)          {             // hide the history panel             UpdateBugHistory(  );             // call the method which creates the tables and the relations             DataSet ds = CreateDataSet(  );             // set the data source for the grid to the first table              DataGrid1.DataSource=ds.Tables[0];             DataGrid1.DataBind(  );             // create the DataView and bind to the History grid             DataView historyView = new DataView(ds.Tables[1]);             HistoryGrid.DataSource = historyView;             Session["historyView"] = historyView;             HistoryGrid.DataBind(  );             // bind the severity grid to the              SeverityGrid.DataSource=ds.Tables["lkSeverity"];             SeverityGrid.DataBind(  );             // bind the BugRelations grid to the Relations collection             BugRelations.DataSource=ds.Relations;             BugRelations.DataBind(  );             // create the output string to show the relationship             // between each bug and its related BugHistory records             String outputString = "";             DataTable tblBugs = ds.Tables["Bugs"];             // for each Bug show its bugID and get all the             // related history records             foreach (DataRow currentRow in tblBugs.Rows)             {                outputString += "BugID: " + currentRow["BugID"] + "<br/>";                // the child relationship is created by the BugsToHistory                // data relationship created in CreateDataSet(  )                DataRow[] childRows =                   currentRow.GetChildRows("BugsToHistory");                // for each historyRow in the child collection                // display the response (current status) field                foreach (DataRow historyRow in childRows)                {                   outputString += historyRow["BugHistoryID"] + ": " +                       historyRow["Response"] + "<br>";                }                outputString += "<br/>";             }                          // update the label             showRelations.Text=outputString;                               }             }       // updated to get the lkSeverity table and to create       // two DataRelation objects - one for Bug to BugHistory       // and a second for BugHistory to lkSeverity       private DataSet CreateDataSet(  )       {          // connection string to connect to the Bugs Database          string connectionString =              "server=YourServer; uid=sa; pwd=YourPassword; " +             "database=ProgASPDotNetBugs";          // Create connection object, initialize with           // connection string and open the connection          System.Data.SqlClient.SqlConnection connection =              new System.Data.SqlClient.SqlConnection(connectionString);          connection.Open(  );          // Create a SqlCommand object and assign the connection          System.Data.SqlClient.SqlCommand command =              new System.Data.SqlClient.SqlCommand(  );          command.Connection=connection;          // build the selection statement          StringBuilder s = new StringBuilder(             "Select b.BugID, h.BugHistoryID, b.Description,h.Response, ");          s.Append("o.FullName as owner, ");          s.Append("p.ProductDescription, ");          s.Append("r.FullName as reporter, ");          s.Append("s.StatusDescription, ");          s.Append("sev.SeverityDescription, ");          s.Append("h.DateStamp ");          s.Append("from  ");          s.Append(             "(select bugID, max(bugHistoryID) as maxHistoryID ");          s.Append("from BugHistory group by bugID) t ");          s.Append("join bugs b on b.bugid = t.bugid ");          s.Append(             "join BugHistory h on h.bugHistoryID = t.maxHistoryID ");          s.Append("join lkProduct p on b.Product = p.ProductID  ");          s.Append("join People r on b.Reporter = r.PersonID  ");          s.Append("join People o on h.Owner = o.PersonID ");          s.Append("join lkStatus s on s.statusid = h.status ");          s.Append(             "join lkSeverity sev on sev.SeverityID = h.severity ");          // set the command text to the select statement          command.CommandText=s.ToString(  );          // create a data adapter and assign the command object          // and add the table mapping for bugs          SqlDataAdapter dataAdapter = new SqlDataAdapter(  );          dataAdapter.SelectCommand=command;          dataAdapter.TableMappings.Add("Table","Bugs");          // Create the dataset and use the data adapter to fill it          DataSet dataSet = new DataSet(  );          dataAdapter.Fill(dataSet);          // create a second command object for the bugs history table          System.Data.SqlClient.SqlCommand command2 =              new System.Data.SqlClient.SqlCommand(  );          command2.Connection = connection;          // This time be sure to add a column for Severity so that you can          // create a relation to lkSeverity          StringBuilder s2 =              new StringBuilder(              "Select BugID, BugHistoryID, StatusDescription, ");          s2.Append(             "Severity, SeverityDescription, Response,                 FullName as Owner, DateStamp ");          s2.Append("from BugHistory h ");          s2.Append("join People o on h.Owner = o.PersonID ");          s2.Append("join lkStatus s on s.statusid = h.status ");          s2.Append(             "join lkSeverity sev on sev.SeverityID = h.severity ");          command2.CommandText= s2.ToString(  );          // create a second data adapter and           // add the command and map the table          // then fill the dataset  from this second adapter          SqlDataAdapter dataAdapter2 = new SqlDataAdapter(  );          dataAdapter2.SelectCommand = command2;          dataAdapter2.TableMappings.Add("Table", "BugHistory");          dataAdapter2.Fill(dataSet);          // create a third command object for the lkSeverity table          System.Data.SqlClient.SqlCommand command3 =              new System.Data.SqlClient.SqlCommand(  );          command3.Connection = connection;          StringBuilder s3 =              new StringBuilder(             "Select SeverityID, SeverityDescription from lkSeverity");          command3.CommandText= s3.ToString(  );          // create a third data adapter           // and add the command and map the table          // then fill the dataset  from this second adapter          SqlDataAdapter dataAdapter3 = new SqlDataAdapter(  );          dataAdapter3.SelectCommand = command3;          dataAdapter3.TableMappings.Add("Table", "lkSeverity");          dataAdapter3.Fill(dataSet);          // declare the DataRelation and DataColumn objects          System.Data.DataRelation dataRelation;          System.Data.DataColumn dataColumn1;          System.Data.DataColumn dataColumn2;          // set the dataColumns to create the relationship          // between Bug and BugHistory on the BugID key         dataColumn1 =              dataSet.Tables["Bugs"].Columns["BugID"];          dataColumn2 =              dataSet.Tables["BugHistory"].Columns["BugID"];                       dataRelation =              new System.Data.DataRelation(             "BugsToHistory",              dataColumn1,              dataColumn2);          // add the new DataRelation to the dataset          dataSet.Relations.Add(dataRelation);            // reuse the DataColumns and DataRelation objects          // to create the relation between BugHistory and lkSeverity          dataColumn1 = dataSet.Tables["lkSeverity"].Columns["SeverityID"];          dataColumn2 = dataSet.Tables["BugHistory"].Columns["Severity"];                       dataRelation =              new System.Data.DataRelation(             "HistoryToSeverity",              dataColumn1,              dataColumn2);          // add the HistoryToSeverity relationship to the dataset          dataSet.Relations.Add(dataRelation);            return dataSet;       }       // unchanged from previous example       private void Page_Init(object sender, EventArgs e)       {          InitializeComponent(  );       }       #region Web Form Designer generated code       /// <summary>       /// Required method for Designer support - do not modify       /// the contents of this method with the code editor.       /// </summary>      private void InitializeComponent(  )       {              this.Load += new System.EventHandler(this.Page_Load);       }       #endregion    }    }

The VB.NET version of the source code is shown in Example 11-9.

Example 11-9. Code-behind page

Imports System Imports System.Web Imports System.Web.UI Imports System.Web.UI.WebControls Imports System.Data Imports System.Data.SqlClient Imports System.Text Public Class Example_11_9   Inherits System.Web.UI.Page #Region " Web Form Designer Generated Code "   'This call is required by the Web Form Designer.   <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent(  )   End Sub   Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid   Protected WithEvents HistoryGrid As System.Web.UI.WebControls.DataGrid   Protected WithEvents BugHistoryPanel As System.Web.UI.WebControls.Panel   Protected WithEvents SeverityGrid As System.Web.UI.WebControls.DataGrid   Protected WithEvents BugRelations As System.Web.UI.WebControls.DataGrid   Protected WithEvents showRelations As System.Web.UI.WebControls.Label   'NOTE: The following placeholder declaration is required by the Web Form Designer.   'Do not delete or move it.   Private designerPlaceholderDeclaration As System.Object   Private Sub Page_Init(ByVal sender As System.Object, _       ByVal e As System.EventArgs) Handles MyBase.Init     'CODEGEN: This method call is required by the Web Form Designer     'Do not modify it using the code editor.     InitializeComponent(  )   End Sub #End Region   ' build the various tables, views, dataSets  and data relations   Private Sub Page_Load(ByVal sender As System.Object, _       ByVal e As System.EventArgs) Handles MyBase.Load     If Not IsPostBack Then       ' hide the history panel       UpdateBugHistory(  )       ' call the method which creates the tables and the relations       Dim ds As DataSet = CreateDataSet(  )       ' set the data source for the grid to the first table        DataGrid1.DataSource = ds.Tables(0)       DataGrid1.DataBind(  )       ' create the DataView and bind to the History grid       Dim historyView As New DataView(ds.Tables(1))       HistoryGrid.DataSource = historyView       Session("historyView") = historyView       HistoryGrid.DataBind(  )       ' bind the severity grid to the        SeverityGrid.DataSource = ds.Tables("lkSeverity")       SeverityGrid.DataBind(  )       ' bind the BugRelations grid to the Relations collection       BugRelations.DataSource = ds.Relations       BugRelations.DataBind(  )       ' create the output string to show the relationship       ' between each bug and its related BugHistory records       Dim outputString As String = ""       Dim tblBugs As DataTable = ds.Tables("Bugs")       ' for each Bug show its bugID and get all the       ' related history records       Dim currentRow As DataRow       For Each currentRow In tblBugs.Rows         outputString += "BugID: " & currentRow("BugID") & "<br/>"         ' the child relationship is created by the BugsToHistory         ' data relationship created in CreateDataSet(  )         Dim childRows As DataRow(  ) = _               currentRow.GetChildRows("BugsToHistory")         ' for each historyRow in the child collection         ' display the response (current status) field         Dim historyRow As DataRow         For Each historyRow In childRows           outputString += historyRow("BugHistoryID") & ": " & _               historyRow("Response") & "<br>"         Next         outputString += "<br/>"       Next       ' update the label       showRelations.Text = outputString     End If   End Sub   ' unchanged from previous example   Public Sub DataGrid1_ItemDataBound(ByVal sender As Object, _     ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) _     Handles DataGrid1.ItemDataBound     Dim myItemtype As ListItemType     myItemtype = CType(e.Item.ItemType, ListItemType)     If (myItemtype = ListItemType.Header) Or _         (myItemtype = ListItemType.Footer) Or _         (myItemtype = ListItemType.Separator) Then       Return     End If     Dim obj As Object = _        CType(e.Item.DataItem, DataRowView).Row.ItemArray(8)     If CType(e.Item.DataItem, DataRowView).Row.ItemArray(8).ToString(  ) _         = "High" Then       Dim severityCell As TableCell = CType(e.Item.Controls(6), TableCell)       severityCell.ForeColor = Color.FromName("Red")     End If   End Sub   ' updated to get the lkSeverity table and to create   ' two DataRelation objects - one for Bug to BugHistory   ' and a second for BugHistory to lkSeverity   Private Function CreateDataSet(  ) As DataSet     ' connection string to connect to the Bugs Database     Dim connectionString As String = "server=YourServer; uid=sa; " + _         "pwd=YourPassword; database=ProgASPDotNetBugs"     ' Create connection object, initialize with      ' connection string and open the connection     Dim connection As New _        System.Data.SqlClient.SqlConnection(connectionString)     connection.Open(  )     ' Create a SqlCommand object and assign the connection     Dim command As New System.Data.SqlClient.SqlCommand     command.Connection = connection     ' build the selection statement     Dim s As New StringBuilder("Select b.BugID, h.BugHistoryID, _         b.Description,h.Response, ")     s.Append("o.FullName as owner, ")     s.Append("p.ProductDescription, ")     s.Append("r.FullName as reporter, ")     s.Append("s.StatusDescription, ")     s.Append("sev.SeverityDescription, ")     s.Append("h.DateStamp ")     s.Append("from  ")     s.Append("(select bugID, max(bugHistoryID) as maxHistoryID ")     s.Append("from BugHistory group by bugID) t ")     s.Append("join bugs b on b.bugid = t.bugid ")     s.Append("join BugHistory h on h.bugHistoryID = t.maxHistoryID ")     s.Append("join lkProduct p on b.Product = p.ProductID  ")     s.Append("join People r on b.Reporter = r.PersonID  ")     s.Append("join People o on h.Owner = o.PersonID ")     s.Append("join lkStatus s on s.statusid = h.status ")     s.Append("join lkSeverity sev on sev.SeverityID = h.severity ")     ' set the command text to the select statement     command.CommandText = s.ToString(  )     ' create a data adapter and assign the command object     ' and add the table mapping for bugs     Dim dataAdapter As New SqlDataAdapter     dataAdapter.SelectCommand = command     dataAdapter.TableMappings.Add("Table", "Bugs")     ' Create the dataset and use the data adapter to fill it     Dim DataSet As New DataSet     dataAdapter.Fill(DataSet)     ' create a second command object for the bugs history table     Dim command2 As New System.Data.SqlClient.SqlCommand     command2.Connection = connection     ' This time be sure to add a column for Severity so that you can     ' create a relation to lkSeverity     Dim s2 As New _       StringBuilder("Select BugID, BugHistoryID, StatusDescription, ")     s2.Append("Severity, SeverityDescription, Response, _       FullName as Owner, DateStamp ")     s2.Append("from BugHistory h ")     s2.Append("join People o on h.Owner = o.PersonID ")     s2.Append("join lkStatus s on s.statusid = h.status ")     s2.Append("join lkSeverity sev on sev.SeverityID = h.severity ")     command2.CommandText = s2.ToString(  )     ' create a second data adapter and      ' add the command and map the table     ' then fill the dataset  from this second adapter     Dim dataAdapter2 As New SqlDataAdapter     dataAdapter2.SelectCommand = command2     dataAdapter2.TableMappings.Add("Table", "BugHistory")     dataAdapter2.Fill(DataSet)     ' create a third command object for the lkSeverity table     Dim command3 As New System.Data.SqlClient.SqlCommand     command3.Connection = connection     Dim s3 As New StringBuilder( _         "Select SeverityID, SeverityDescription from lkSeverity")     command3.CommandText = s3.ToString(  )     ' create a third data adapter      ' and add the command and map the table     ' then fill the dataset  from this second adapter     Dim dataAdapter3 As New SqlDataAdapter     dataAdapter3.SelectCommand = command3     dataAdapter3.TableMappings.Add("Table", "lkSeverity")     dataAdapter3.Fill(DataSet)     ' declare the DataRelation and DataColumn objects     Dim dataRelation As System.Data.DataRelation     Dim dataColumn1 As System.Data.DataColumn     Dim dataColumn2 As System.Data.DataColumn     ' set the dataColumns to create the relationship     ' between Bug and BugHistory on the BugID key     dataColumn1 = DataSet.Tables("Bugs").Columns("BugID")     dataColumn2 = DataSet.Tables("BugHistory").Columns("BugID")     dataRelation = New System.Data.DataRelation("BugsToHistory", _         dataColumn1, dataColumn2)     ' add the new DataRelation to the dataset     DataSet.Relations.Add(dataRelation)     ' reuse the DataColumns and DataRelation objects     ' to create the relation between BugHistory and lkSeverity     dataColumn1 = DataSet.Tables("lkSeverity").Columns("SeverityID")     dataColumn2 = DataSet.Tables("BugHistory").Columns("Severity")     dataRelation = New System.Data.DataRelation("HistoryToSeverity", _         dataColumn1, dataColumn2)     ' add the HistoryToSeverity relationship to the dataset     DataSet.Relations.Add(dataRelation)     Return DataSet   End Function   ' unchanged from previous example   Public Sub DataGrid1_SelectedIndexChanged(ByVal sender As Object, _       ByVal e As System.EventArgs) Handles DataGrid1.SelectedIndexChanged     UpdateBugHistory(  )   End Sub   ' unchanged from previous example   Private Sub UpdateBugHistory(  )     Dim index As Integer = DataGrid1.SelectedIndex     If index <> -1 Then       Dim bugID As Integer = CType(DataGrid1.DataKeys(index), Integer)       Dim historyView As DataView = _          CType(Session("historyView"), DataView)       historyView.RowFilter = "BugID = " & bugID       HistoryGrid.DataSource = historyView       HistoryGrid.DataBind(  )       BugHistoryPanel.Visible = True     Else       BugHistoryPanel.Visible = False     End If   End Sub End Class