Walkthrough: Creating a Nested GridView Control

http://msdn.microsoft.com/en-us/library/aa992038(VS.80).aspx

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.

An example of nested data would be 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.

In this walkthrough, both GridView controls use SqlDataSource controls to retrieve the data from the data source.

During this walkthrough, you will learn how to do the following:

• Connect to a SQL Server database in Microsoft Visual Web Developer.

• Use the SqlDataSource control to manage data access and binding.

• 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 Web Developer (Visual Studio).

• Microsoft Data Access Components (MDAC) version 2.7 or a later version.

  If you are using Microsoft Windows XP or Windows Server 2003, you already have MDAC 2.7. However, if you are using Microsoft Windows 2000, you might need to upgrade the MDAC already installed on your computer. For more information, see "Microsoft Data Access Components (MDAC) Installation" in the MSDN Library.

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

  Note

  If you need information about how to log on to the computer that is running SQL Server, contact the server administrator.

Creating the Web Site

If you have already created a Web site in Visual Web Developer by completing Walkthrough: Creating a Basic Web Page in Visual Web Developer, you can use that Web site and go to the next section. Otherwise, create a new Web site and page by following these steps.

To create a file system Web site

1. Open Visual Web Developer.

2. On the File menu, click New, and then click Web Site. If you are using Visual Web Developer Express, on the File menu, click NewWeb Site.

   The New Web Site dialog box appears.

3. Under Visual Studio installed templates, click ASP.NET Web Site.

4. In the first Location box, select File System; in the second, enter the name of the folder where you want to keep the pages of the Web site.

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

5. In the Language list, click the programming language you prefer to work in.

6. Click OK.

   Visual Web Developer creates the folder and a new page named Default.aspx.

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 the following procedure, 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. Open or switch to the Default.aspx page.

2. Switch to Design view.

3. 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 click Show Smart Tag.

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

   The Configure Data Source wizard appears.

5. Click New Connection.

   The Add Connection dialog box appears.

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

   • If the Choose Data Source dialog box appears instead of the Connection Properties dialog box, in the Data source list, select the type of data source you will use. For this walkthrough, the data source type is Microsoft SQL Server. In the Data provider list, click .NET Framework Data Provider for SQL Server, and then click Continue.

6. On the Add Connection page, in the Server name text box, type the name of the SQL Server where the sample Northwind database is installed.

7. For the Log on to the server section, select the appropriate option to access the running SQL Server database (integrated security or specific ID and password). If you need to, enter a user name and password.

8. If you entered a password, select the Save my password check box.

9. Click Select or enter a database name, and then enter Northwind.

10. Click Test connection, and when you are sure that it works, click OK.

11. In the Configure Data Source wizard, click Next.

12. Ensure 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 wizard step, select Specify columns from a table or view.

15. In the Name list box, select Customers.

16. In the Columns panel, 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. On the GridView Tasks menu, in the Choose Data Source list box, select the SqlDataSource control added previously, SqlDataSource1.

4. Select the Enable Paging check box.

5. 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 must 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 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. Right-click the GridView control and select Show Smart Tag.

3. Click Edit Columns.

   The Fields dialog box appears.

4. In the Available fields panel, select a 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 box, select ItemTemplate.

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

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

10. In the SqlDataSource Tasks smart tag, click Configure Data Source.

    The Configure Data Source wizard appears.

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

12. In the Configure the Select Statement wizard 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 box.

17. Select None in the Source list box.

18. Click Add.

    This creates a select parameter that you will set to the CustomerID value for each row 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.

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

23. In the GridView Tasks smart tag, in the Choose Data Source list box select the name of the nested SqlDataSource control, SqlDataSource2.

24. Right-click the parent GridView control, GridView1, and select 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 panel for GridView1, click the Events button .

27. In the RowDataBound box, type GridView1_RowDataBound and then press ENTER.

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

    VB

    C#

    C++

    F#

    JScript

    Copy

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

28. Add the following code to the procedure:

    VB

    C#

    C++

    F#

    JScript

    Copy

    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 Control Overview.

To configure a nested SqlDataSource control to cache query results

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

2. Right-click the parent GridView control, GridView1, and then click Edit Template.

3. In the ItemTemplate 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 select Show Smart Tag.

11. 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 box, select Orders.

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

16. Click Finish.

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

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

    You have configured the nested SqlDataSource control, SqlDataSource2, 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 will be displayed.

18. If 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:

    VB

    C#

    C++

    F#

    JScript

    Copy

    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.