Windows DevCenter    
 Published on Windows DevCenter (http://www.windowsdevcenter.com/)
 See this if you're having trouble printing code examples


Displaying Master-Details Relationships in ASP.NET 2.0

by Wei-Meng Lee
04/24/2007

One of the common tasks in manipulating databases is displaying master-details relationships. A very good example is the SQL Server 2000 sample database pubs, which contains several interrelated tables. The authors table is related to the titles table through the titleauthor table (see Figure 1).


Figure 1. The relationships between the authors, titleauthor, and the titles tables in the pubs database

In this article, I will show you how you can use the GridView and DetailsView controls to display records in the authors and titles tables.

Displaying the Master

Using Visual Studio 2005, create a new web site project and name it MasterDetails. Drag the GridView control from the Toolbox and drop it onto default.aspx. In the GridView's Smart tag, select the Choose Data Source drop-down list and select <New Data source…> (see Figure 2).


Figure 2. Configuring a data source for the GridView control

Next, follow these steps:
  1. In the Data Source Configuration Wizard dialog, choose Database and click OK.
  2. You will now be asked to choose a connection to the desired database. Click the New Connection... button to choose your database.
  3. In the Add Connection dialog, you have the option to choose the different types of databases that you can use for your project. Click the Change… button and select Microsoft SQL Server.
  4. For the Server name entry, enter ".\SQLEXPRESS" (assuming that you have installed the SQL Server 2005 Express edition database on your local computer). Select the pubs database (see Figure 3). Again, I am assuming that you have the pubs sample database installed on your computer. Click OK.

  5. Figure 3. Selecting the database server and database name

  6. You will now be asked to save the connection string information in the application configuration file. Click Next.
  7. In the next dialog, select the authors table and select all its fields (see Figure 4). Click Next.

  8. Figure 4. Specifying the table and its fields

  9. You can now preview the content of the selected table by clicking on the Test Query button (see Figure 5). Click Finish.


Figure 5. Previewing the content of the authors table

That's it! To enable the GridView to support paging, sorting, and selection, check the respective checkboxes in its Smart tag as shown in Figure 6.


Figure 6. Enabling the GridView to support paging, sorting, and selection

The steps outlined above use the wizard in Visual Studio 2005 to configure the data source control for the GridView control. Alternatively, you can also manually copy the following code into the Source View of default.aspx:

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
AllowSorting="True" AutoGenerateColumns="False"  
DataKeyNames="au_id" DataSourceID="SqlDataSource1">
    <Columns>
        <asp:CommandField ShowSelectButton="True" />
        <asp:BoundField DataField="au_id" HeaderText="au_id" 
           ReadOnly="True" SortExpression="au_id" />
        <asp:BoundField DataField="au_lname" HeaderText="au_lname" 
           SortExpression="au_lname" />
        <asp:BoundField DataField="au_fname" HeaderText="au_fname" 
           SortExpression="au_fname" />
        <asp:BoundField DataField="phone" HeaderText="phone" 
           SortExpression="phone" />
        <asp:BoundField DataField="address" HeaderText="address" 
           SortExpression="address" />
        <asp:BoundField DataField="city" HeaderText="city" 
           SortExpression="city" />
        <asp:BoundField DataField="state" HeaderText="state" 
           SortExpression="state" />
        <asp:BoundField DataField="zip" HeaderText="zip" 
           SortExpression="zip" />
        <asp:CheckBoxField DataField="contract" HeaderText="contract" 
           SortExpression="contract" />
    </Columns>
</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
   ConnectionString="<%$ ConnectionStrings:pubsConnectionString %>"
   SelectCommand="SELECT * FROM [authors]">
</asp:SqlDataSource>

Below is the connection string in web.config:

<configuration>
   <appSettings/>
   <connectionStrings>
      <add name="pubsConnectionString" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=pubs;Integrated Security=True"
         providerName="System.Data.SqlClient" />
   </connectionStrings>
   <system.web>
   ...

Finally, beautify the GridView control by applying the Sand & Sky scheme to the GridView control. (using the Auto Format… link in the Smart tag). Press F5 to debug the application. Figure 7 shows that you can select a record by clicking the Select link.


Figure 7. Viewing and selecting records

Displaying the Details

In the last section, you displayed all the authors' information using the GridView control. Let's now display the books written by each author. The first step is to display the list of titles of books written by a particular author.

Add a new GridView control to the default.aspx page and choose a new data source (see Figure 8).


Figure 8. Adding a second GridView control

You will walk through the same steps as outlined in the last section, except that when it comes to selecting the table to display, you will select the titleauthor table (see Figure 9). In this case, you are only interested in displaying the ids of the books, hence select only the title_id column. In addition, the title_id of the books are dependent on which author is selected in the first GridView control, so you need to configure the data source by clicking the WHERE… button.


Figure 9. Configuring the data source for the second GridView control

In the Add WHERE Clause dialog, set its various fields as shown in Figure 10. Remember to click on the Add button to add the WHERE clause to the data source control.


Figure 10. The value for the au_id column is obtained from the first GridView control

Basically this means that the value for the au_id column in GridView2 is obtained from the GridView1 control. GridView1, if you recall, has the DataKeyNames attribute set to "au_id":

 
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
AllowSorting="True" AutoGenerateColumns="False"  
DataKeyNames="au_id" DataSourceID="SqlDataSource1">
    <Columns>

Hence, the au_id of the author selected in GridView1 will be used as the value for the au_id column in GridView2.

The following shows the source view of what we have just added in this section:

<asp:GridView ID="GridView2" runat="server" 
   AutoGenerateColumns="False" DataSourceID="SqlDataSource2">
   <Columns>
      <asp:BoundField DataField="title_id" HeaderText="title_id" 
         SortExpression="title_id" />
   </Columns>
</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource2" runat="server" 
   ConnectionString="<%$ ConnectionStrings:pubsConnectionString %>"
   SelectCommand="SELECT [title_id] FROM [titleauthor] WHERE ([au_id] = @au_id)">
   <SelectParameters>
      <asp:ControlParameter ControlID="GridView1" Name="au_id" 
         PropertyName="SelectedValue"
         Type="String" />
      </SelectParameters>
</asp:SqlDataSource>

As usual, check the various checkboxes in the GridView's Smart tag to enable it for paging, sorting, and selection (see Figure 11). Also beautify it by applying the Autumn scheme.


Figure 11. Configuring the second GridView control

Press F5 to debug the application. You will see that when you now select an author from the first GridView control, the corresponding title IDs of the books written by the selected author will be displayed in the second GridView control (see Figure 12).


Figure 12. Displaying the title ids of books authored by the selected author

The final step is to add a DetailsView control to the page so that you can view the details of the book written by the author.

Drag and drop the DetailsView control onto default.aspx and configure a new data source (see Figure 13).


Figure 13. Adding the DetailsView control to the page

For the table name, choose titles and select all columns (see Figure 14). Click on the WHERE… button.


Figure 14. Configuring the data source for the DetailsView control

Configure the Add WHERE Clause dialog as shown in Figure 15 and click on Add. The title_id column in the DetailsView control will now be obtained from the title_id field from GridView2.


Figure 15. Setting the values for the WHERE clause

The following shows the source view of what you have just added:

<asp:DetailsView ID="DetailsView1" runat="server" 
   AutoGenerateRows="False" DataKeyNames="title_id"
   DataSourceID="SqlDataSource3" Height="50px" Width="125px">
   <Fields>
      <asp:BoundField DataField="title_id" HeaderText="title_id" 
         ReadOnly="True" SortExpression="title_id" />
      <asp:BoundField DataField="title" HeaderText="title" 
         SortExpression="title" />
      <asp:BoundField DataField="type" HeaderText="type" 
         SortExpression="type" />
      <asp:BoundField DataField="pub_id" HeaderText="pub_id" 
         SortExpression="pub_id" />
      <asp:BoundField DataField="price" HeaderText="price" 
         SortExpression="price" />
      <asp:BoundField DataField="advance" HeaderText="advance" 
         SortExpression="advance" />
      <asp:BoundField DataField="royalty" HeaderText="royalty" 
         SortExpression="royalty" />
      <asp:BoundField DataField="ytd_sales" HeaderText="ytd_sales" 
         SortExpression="ytd_sales" />
      <asp:BoundField DataField="notes" HeaderText="notes" 
         SortExpression="notes" />
      <asp:BoundField DataField="pubdate" HeaderText="pubdate" 
         SortExpression="pubdate" />
   </Fields>
</asp:DetailsView>

<asp:SqlDataSource ID="SqlDataSource3" runat="server" 
   ConnectionString="<%$ ConnectionStrings:pubsConnectionString %>"
   SelectCommand="SELECT * FROM [titles] WHERE ([title_id] = @title_id)">
   <SelectParameters>
      <asp:ControlParameter ControlID="GridView2" Name="title_id" 
         PropertyName="SelectedValue" Type="String" />
   </SelectParameters>
</asp:SqlDataSource>

You also need to add in the DataKeyNames attribute to GridView2 and set its value to "title_id":

        <asp:GridView DataKeyNames="title_id" 
           ID="GridView2" runat="server" 
           AutoGenerateColumns="False" 
           DataSourceID="SqlDataSource2"

Finally, check the Enable Paging checkbox and apply the Slate scheme (see Figure 16).


Figure 16. Beautifying the DetailsView control

That's it! Press F5 to debug the application. You can now select an author, and then select a title, to display the details of the book in the DetailsView control (see Figure 17).


Figure 17. The application in action!

Summary

In this article, you have seen how to use the GridView and DetailsView controls to display records from interrelated tables. And notice that most of the code blocks are written declaratively (in fact, you didn't even write them--they're written by the wizards!).

Wei-Meng Lee (Microsoft MVP) http://weimenglee.blogspot.com is a technologist and founder of Developer Learning Solutions http://www.developerlearningsolutions.com, a technology company specializing in hands-on training on the latest Microsoft technologies.


Return to Windows DevCenter.

Copyright © 2009 O'Reilly Media, Inc.