Walkthrough: Creating a Nested GridView Control

The GridView control displays rows of data in a grid (an HTML table), displaying one data row per grid row. This walkthrough shows you how to extend the functionality of the GridView control so that individual grid rows can display data from a related data table. In the walkthrough, you show related data in a nested GridView control — a GridView control inside the grid row of the parent GridView control.

In this walkthrough, you will see how to create a GridView control that displays a list of customers where each row of the GridView control includes another GridView control that displays the orders for that customer. Both GridView controls use SqlDataSource controls to retrieve the data from the data source.

Tasks illustrated in this walkthrough include the following;

• Connect to a SQL Server database in a Microsoft Visual Studio Web site project.

• Use the SqlDataSource control to manage data access.

• Display data returned from the database in the GridView control.

• Create a TemplateField with nested controls to be displayed by the GridView control.

• Dynamically customize the display for each row based on run-time conditions.

• Optionally, use data caching with the SqlDataSource control to reduce requests made to the database.

Prerequisites

In order to complete this walkthrough, you will need:

• Visual Studio or Visual Web Developer Express installed on your computer.

  Note

  If you are using Visual Studio, the walkthrough assumes that you selected the Web Development collection of settings when you started Visual Studio the first time. For more information, see How to: Select Web Development Environment Settings.

• Access to the SQL Server Northwind database. For information about how to download the SQL Server sample Northwind database, see Installing Sample Databases on the Microsoft SQL Server Web site.

Creating the Web Site

If you have already created a Web site in Visual Studio (for example, by completing Walkthrough: Creating a Basic Web Forms Page in Visual Studio), you can use that Web site and go to the next section. Otherwise, create a new Web site and page.

This walkthrough uses a Web site project. You could use a Web application project instead. For information about the difference between these Web project types, see Web Application Projects versus Web Site Projects in Visual Studio.

To create a file system Web site

1. Open Visual Studio or Visual Web Developer Express.

2. In the File menu, click New Web Site.

   The New Web Site dialog box is displayed.

3. Under Installed Templates, click Visual Basic or Visual C# and then select ASP.NET Empty Web Site.

4. In the Web location box, select File System, and then enter the name of the folder where you want to keep the pages for your Web site.

   For example, type the folder name C:\WebSites\NestedGridView.

5. Click OK.

   Visual Studio creates a Web site project that includes a Web.config file.

Adding a Data Source for the GridView Control

To display data on an ASP.NET Web page, you need the following:

• A connection to a data source (such as a database). In this walkthrough, you will create a connection to the SQL Server Northwind database.

• A control on the page to display the data.

In the following procedure, you will display data in a GridView control. The GridView control will get its data from the SqlDataSource control.

To add a data source for the GridView control

1. If your Web site does not have an App_Data folder, in Solution Explorer, right-click the Web site name, select Add ASP.NET Folder, and then click App_Data.

2. Copy Northwind.mdf from the following folder to your App_Data folder:

   C:\SQL Server 2000 Sample Databases

3. If your Web site does not have a Default.aspx file, create one by performing the following steps:

   1. In Solution Explorer, right-click the project name and then click Add New Item.

   2. In the Add New Item dialog box, under Installed Templates select Visual C# or Visual Basic.

   3. Select Web Form, enter Default.aspx in the Name box, and then click Add.

4. If Default.aspx is not open, open it.

5. Switch to Design view.

6. In the Toolbox, from the Data group, drag a SqlDataSource control onto the page.

   If the SqlDataSource Tasks smart tag does not appear, right-click the SqlDataSource control and then click Show Smart Tag.

7. In the SqlDataSource Tasks panel, click Configure Data Source.

   The Configure Data Source wizard appears.

8. Click New Connection.

   The Add Connection dialog box appears.

   • If the Data source list does not display Microsoft SQL Server Database File (SqlClient), click Change, and in the Change Data Source dialog box, select Microsoft SQL Server Database File.

   • If the Choose Data Source dialog box appears instead of the Connection Properties dialog box, in the Data source list, select Microsoft SQL Server Database File. In the Data provider list, make sure .NET Framework Data Provider for SQL Server is selected, and then click Continue.

9. In the Add Connection dialog box, click Browse, navigate to the App_Data folder of the project you are working on, and select the Northwind.mdf file.

10. Click OK.

11. In the Choose Your Data Connection wizard step, click Next.

12. In the Save the Connection String to the Application Configuration File step, make sure that the Yes, save this connection as check box is selected.

13. Name the connection NorthwindConnectionString and then click Next.

14. In the Configure the Select Statement step, select Specify columns from a table or view.

15. In the Name list, select Customers.

16. In the Columns list, select the CustomerID and CompanyName columns.

17. Click Next.

18. Click Finish.

Adding a GridView Control to Display Data

After you have established a data source to retrieve the data, you must add a control to the page to display the data.

In the following procedure, you will display data in a GridView control. The GridView control will get its data from the SqlDataSource control that you added previously.

To add and configure a GridView control for displaying data

1. Ensure that you are in Design view for the Default.aspx page.

2. In the Toolbox, from the Data group, drag a GridView control onto the page.

3. Click the smart tag to show the GridView Tasks panel.

4. In the Choose Data Source list, select the SqlDataSource control that you added previously (SqlDataSource1).

5. Select the Enable Paging check box.

6. In the Properties window, expand the RowStyle property for the GridView control, and then set the VerticalAlign property to Top. This will align the text in the grid row at the top of the cells, because the nested grid will display multiple lines.

Adding a Nested GridView Control to Display Related Data

You will now create a nested data source and GridView control to display related data in each row. To do so, you will do the following:

• Create a TemplateField for the rows displayed by the GridView control.

• Add a nested GridView control and SqlDataSource control to the TemplateField element to display related data for individual rows.

• Add a procedure to handle the RowDataBound event of the parent GridView control in order to set the select parameter value for the nested SqlDataSource control.

To add and configure a nested GridView control for displaying data

1. Ensure that you are in Design view for the Default.aspx page.

2. Click the GridView control, and then click the smart tag.

3. In the GridView Tasks panel, click Edit Columns.

   The Fields dialog box appears.

4. In the Available fields panel, select TemplateField, and then click Add.

5. In the TemplateField properties panel, set HeaderText to Orders.

6. Click OK.

7. In the GridView Tasks panel, click Edit Templates.

8. In Template Editing Mode panel, from the Display list, select ItemTemplate.

9. In the Toolbox, from the Data group, drag a SqlDataSource control onto the page to the editable area of the ItemTemplate element.

10. In the SqlDataSource Tasks panel, click Configure Data Source.

    The Configure Data Source wizard appears.

11. For the Choose Your Data Connection step, select the NorthwindConnectionString option that you created earlier in this walkthrough and then click Next.

12. In the Configure the Select Statement step, select Specify columns from a table or view.

13. In the Name list, select Orders.

14. In the Columns panel, select the OrderID and OrderDate columns.

15. Click WHERE to add a parameter for the SELECT statement.

16. In the Add WHERE Clause window, select CustomerID in the Column list.

17. Select None in the Source list.

18. Click Add.

    This creates a select parameter that you will set to the CustomerID value for each row that is bound to the parent GridView control.

19. Click OK.

20. Click Next.

21. Click Finish.

22. In the Toolbox, from the Data group, drag a GridView control to the editable area of the ItemTemplate.

23. Click the GridView smart tag, and then in the Choose Data Source list, select the name of the nested SqlDataSource control (SqlDataSource2).

24. Right-click the parent GridView control (GridView1) and then click Show Smart Tag.

25. In the GridView Tasks panel, click End Template Editing.

    Note

    The nested GridView control is not displayed in Design view.

26. In the Properties window for GridView1, click the Events button ().

27. Double-click inside the value box for RowDataBound.

    Visual Studio creates an event handler for the RowDataBound event of the GridView control. The code will resemble the following example:

    Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
    
    End Sub
    

    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
    
    }
    

28. Inside the skeleton event handler, add the following code:

    If e.Row.RowType = DataControlRowType.DataRow Then 
        Dim s As SqlDataSource = CType(e.Row.FindControl("SqlDataSource2"), SqlDataSource)
        s.SelectParameters(0).DefaultValue = e.Row.Cells(0).Text
    End If
    

    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        SqlDataSource s = (SqlDataSource)e.Row.FindControl("SqlDataSource2");
        s.SelectParameters[0].DefaultValue = e.Row.Cells[0].Text;
    }
    

    The code retrieves the CustomerID value for the current row and sets it as the select parameter value for the nested SqlDataSource control (SqlDataSource2).

29. Save the changes to the files.

Testing the Page

You can now run the page.

To test the page

1. Press CTRL+F5 to run the page.

   The GridView control displays the data rows from the Northwind Customers table with data from the Northwind Orders table for each customer nested in each row.

2. Close the browser.

Using Caching with a Nested GridView Control

At this point in the walkthrough, you have created a page with nested GridView controls. As currently written, the page queries the database one time for the parent GridView control, and again for each nested GridView control.

To reduce database load, you can modify your nested query to return all rows, cache the results, and then filter the results for each nested GridView control.

Note

For large databases, this is not a practical solution. If you are retrieving data from a large database, build a data object that retrieves only the required rows and bind to it using the ObjectDataSource control. For more information, see ObjectDataSource Web Server Control Overview.

To configure a nested SqlDataSource control to cache query results

1. In the Default.aspx page, switch to Design view.

2. In the smart tag panel for the GridView1 control, click Edit Templates.

3. In the ItemTemplate element for the Orders column, select the nested SqlDataSource control (SqlDataSource2).

4. In the Properties window, set the EnableCaching property to true.

5. Set the FilterExpression property to CustomerID='{0}'.

6. In the FilterParameters property, click the ellipsis button () to open the Parameter Collection Editor window.

7. In the Parameter Collection Editor window, click Add Parameter.

8. In the Name box, enter CustomerID.

9. Click OK.

10. Right-click the nested SqlDataSource control (SqlDataSource2) and then click Show Smart Tag.

11. In the smart tag panel, click Configure Data Source.

12. Leave the connection information unchanged and click Next.

13. In the Configure the Select Statement step, select Specify columns from a table or view.

14. In the Name list, select Orders.

15. In the Columns section, check the OrderID, CustomerID, and OrderDate columns, and then click Next.

16. Click Finish.

17. When you are prompted to refresh the fields and keys for the GridView2 control, click No.

    The columns displayed by the nested GridView control will not change.

    You have configured the nested SqlDataSource control to retrieve all the order data from the database and cache it locally. When the nested GridView control is bound to the data, a filter is applied so that only orders related to the CustomerID value for the current row are displayed.

18. If you are using a code-behind page, right-click a blank area on the page, and then click View Code.

19. Change the code for the RowDataBound event handler to read as follows:

    If e.Row.RowType = DataControlRowType.DataRow Then 
        Dim s As SqlDataSource = CType(e.Row.FindControl("SqlDataSource2"), SqlDataSource)
        s.FilterParameters(0).DefaultValue = e.Row.Cells(0).Text
    End If
    

    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        SqlDataSource s = (SqlDataSource)e.Row.FindControl("SqlDataSource2");
        s.FilterParameters[0].DefaultValue = e.Row.Cells[0].Text;
    }
    

    In this version of the code, instead of setting a value in the SqlDataSource control's SelectParameters collection, you set a value in its FilterParameters collection.

20. Save the file.

Testing the Page

You can now run the page.

To test the page

1. Press CTRL+F5 to run the page.

   The GridView control displays the data rows from the Northwind Customers table with data from the Northwind Orders table for each customer nested in each row. However, all the data that is bound to the GridView control will be requested one time, when the first row is data bound. The data is cached for use with the remaining rows of the GridView control.

2. Close the browser.

Next Steps

This walkthrough has illustrated how you can extend the functionality of the GridView control to display related data by using nested GridView controls on an ASP.NET Web page. You can also extend your application to enable data to be updated and deleted or to enable users to insert new records by using a DetailsView or FormView control. You might also use controls other than a TextBox control, such as a DropDownList control, to change a value. For more information, see the following topics:

• Walkthrough: Creating Master/Detail Web Pages in Visual Studio

• Walkthrough: Editing and Inserting Data in Web Pages with the DetailsView Web Server Control

• Walkthrough: Displaying a Drop-Down List While Editing in the GridView Web Server Control

See Also

Tasks

How To: Secure Connection Strings when Using Data Source Controls

Walkthrough: Displaying a Drop-Down List While Editing in the GridView Web Server Control

Walkthrough: Basic Data Access Using the SqlDataSource Control in Web Pages