Walkthrough: Displaying and Formatting Data with the DataList Web Server Control

A common task in Web pages is to display data — in effect, to create data reports. In this walkthrough, you will work with the DataList control, which allows you to create free-form layout for records that you display on an ASP.NET Web page.

During this walkthrough you will learn how to:

• Add a DataList control and use templates to lay out the data that is displayed by theDataList control.

• Bind the DataList control to a data source.

• Add a child data control and some code to the DataList control to display data in a master/detail relationship.

Prerequisites

In order to complete this walkthrough, you will need:

• Microsoft Visual Web Developer (Visual Studio).

• Access to the SQL Server Northwind database. For information about downloading and installing 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 running SQL Server, contact the server administrator.

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

  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.

Creating the Web Site

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 is displayed.

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

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

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

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

   The programming language you choose will be the default for your Web site, but you can set the programming language for each page individually.

6. Click OK.

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

Adding a DataList Control

For the first part of this walkthrough, you will add a DataList control, and then configure its data source.

To add and configure a DataList control to display data in a grid

1. Switch to Design view.

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

3. Right-click the DataList control, and then click Show Smart Tag.

4. On the DataList Tasks menu, in the Choose Data Source list, click New Data Source.

   The Configure Data Source wizard appears.

5. Click Database.

   This specifies that you want to get data from a database that supports SQL statements. (This includes SQL Server and other OLE-DB–compatible databases.)

6. In the Specify an ID for the data source box, a default data source control name is displayed (SqlDataSource1). You can leave this name.

7. Click OK.

   The Configure Data Source wizard displays a page in which you can create a connection.

8. Click New Connection.

   • If the Choose Data Source page appears, 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 the click Continue.

   The Add Connection page appears.

9. On the Add Connection page, in the Server name text box, type the name of the computer running the SQL Server database.

10. For the logon credentials, select the option that is appropriate for accessing the computer running the SQL Server database (either integrated security or a specific ID and password) and, if required, enter a user name and password.

11. Select the Save my Password check box.

12. Click the Select or enter a database name button, and then enter Northwind.

13. Click Test Connection, and when you are sure that it works, click OK.

    The Configure Data Source wizard appears with the connection information filled in.

14. Click Next.

    The Configure Data Source wizard displays a page in which you can choose to store the connection string in the configuration file. Storing the connection string in the configuration file has two advantages:

    • It is more secure than storing it in the page.

    • You can reuse the same connection string in multiple pages.

15. Be sure that the Yes, save this connection as check box is selected, and then click Next. You can leave the default connection string.

    The Configure Data Source wizard displays a page in which you can specify what data you want to fetch from the database.

16. Make sure that Specify columns from a table or view is selected.

17. In the Name list, click Categories.

18. Under Columns, select the CategoryID and CategoryName check boxes.

    The Configure Data Source wizard displays the SQL statement that you are creating in a box at the bottom of the page.

    Note

    The Configure Data Source wizard allows you to specify selection criteria (a WHERE clause) and other SQL query options. For this part of the walkthrough, you will create a simple statement with no selection or sort options.

19. Click Next.

20. Click Test Query to be sure that you are getting the data you want.

21. Click Finish.

    The Configure Data Source wizard closes and you are returned to the SqlDataSource control. If you view the properties for the SqlDataSource control, you can see that the Configure Data Source wizard has created values for the ConnectionString and SelectQuery properties. Even though Properties in Visual Web Developer displays the full connection string, only the connection string identifier is saved in the page (in this case, NorthwindConnectionString).

Formatting the Layout of the DataList Control

At this point, you have a DataList control on the page with a default layout for information from the Categories table. The benefit of the DataList control is that you can create a free-form layout for the data. In this section, you will work with a template and configure it with text and controls to customize the data display.

To format the layout in the DataList control

1. Right-click the DataList control, click Edit Template, and then click Item Templates.

   The DataList control switches to template editing mode and displays template boxes for these templates:

   • ItemTemplate, which contains the text and controls that are displayed by default in the DataList control.

   • AlternatingItemTemplate, which is an optional template in which you can create a layout that is used for every other data record. Typically, the AlternatingItemTemplate property is similar to the ItemTemplate property, but the AlternatingItemTemplate property uses a different background color for a banded effect.

   • SelectedItemTemplate, which defines the layout for a data record that is explicitly selected using a button click or other gesture. Typical uses for this template are to provide an expanded view of a data record or to serve as the master record for a master/detail relationship. You must write code to support putting a record into selected mode. (You will not do so in this walkthrough. For more information, see How to: Allow Users to Select Items in DataList Web Server Controls and Walkthrough: Creating Master/Detail Web Pages in Visual Studio.)

   • EditItemTemplate, which defines the layout for edit mode for a data record. Typically, the EditItemTemplate property includes editable controls, such as the TextBox and CheckBox controls in which users can modify the data record. You must write code to support putting a record into edit mode and to handle saving the record when the edits are complete. (You will not do so in this walkthrough. For information about writing code to edit records, see How to: Allow Users to Edit Items in DataList Web Server Controls.)

   By default, Visual Web Developer populates the item template with a data-bound Label control for each data column in the data source. In addition, Visual Web Developer generates static text for each label to act as a caption.

2. Drag the right-hand resize handle to widen the DataList control so it takes up most of the page width.

3. Edit the item template to rearrange the Label controls and create a new caption so that the template contents look similar to the following code example.

   Name: [CategoryNameLabel] (ID: [CategoryIDLabel])
   

4. Click the CategoryNameLabel control. In Properties, expand the Font node, and then set Bold to true.

5. In Design view, on the DataList control, right-click the title bar, click Edit Template, and then click Separator Template.

   The separator template allows you to specify what text or other elements are displayed between data records.

6. In the Toolbox, from the HTML group, drag a Horizontal Rule element into the separator template.

7. On the DataList control, right-click the title bar, and then click End Template Editing.

Testing the DataList Control

You can now test the layout that you have created.

To test the DataList control

• Press CTRL+F5 to run the page.

  The page displays a list of category names and IDs, with a line between each record.

Displaying Related Information Inside the DataList Control

The DataList control that you are using currently displays individual Category records, using the custom layout that you defined previously. You changed the default layout only a little bit, but you can see that by working with the template, you can arrange text and controls, format the content, and modify the data record display in other ways.

The layout can include the current record, as well as related records. In this part of the walkthrough, you will change the layout so that each row displays a category and the products in that category. In effect, you will display a master/detail relationship within each row of the DataList control. You will need to write a short piece of code to enable this scenario.

To display related information inside the DataList control

1. Right-click the DataList control, click Edit Templates, and then click Item Templates.

2. In the Toolbox, from the Standard group, drag a BulletedList control onto the item template, and then drop the BulletedList control below the category information.

   The BulletedList control can display data, with one data column per bulleted item.

3. Right-click the BulletedList control, and then click Show Smart Tag.

4. Click Choose Data Source.

   You will create a second data source control to read the related records.

5. In the Choose Data Source dialog box, in the Select a data source list, click New Data Source.

6. Click Database.

7. In the Specify an ID for the data source box, type bulletedListDataSource.

   Although you can usually leave the default name for the data source, in this case it is useful to assign a specific, predictable name to the data source so that you can reference it later in code.

8. Click OK.

   The Configure Data Source wizard appears.

9. In the Which data connection should your application use to connect to the database? list, click the name of the connection that you created earlier in this walkthrough, and then click Next.

   The Configure Data Source wizard displays a page in which you can create an SQL statement.

10. Under Specify columns from a table or view, in the Name box, click Products.

11. In the Columns box, select the ProductName box.

    The BulletedList control displays only one column.

12. Click the WHERE button.

    The Add WHERE Clause dialog box appears.

13. In the Column list, click CategoryID.

14. In the Operator list, click =.

15. In the Source list, click None.

    This indicates that the value of the CategoryID column will be provided in a variable.

16. Click Add, click OK, click Next, and then click Finish.

    Testing the query will not work with the variable.

17. In the Select a data field to display in the BulletedList list, click ProductName. Click OK.

18. In the DataList control, right-click the title bar, and then click End Template Editing.

Writing Code to Set the Category ID

The SQL statement that you created for the bulletedListDataSource control looks similar to the following code:

SELECT [ProductName] FROM [Products] WHERE ([CategoryID] = @CategoryID)

At run time, the DataList control displays a list of category records, which includes both the category name and the category ID. In the preceding section, you added a BulletedList control that will display all the products for each category. As each category record is being displayed, the DataList control executes a query to get the products for that category, using the preceding query.

For each record being displayed, you must provide the category ID to the query. You can do this by writing a short piece of code that gets the category ID from the record that is currently being displayed, and then passes the category ID as a parameter to the query in the bulletedListDataSource control.

To write code to set the category ID

1. Click the DataList control, and then in Properties, on the toolbar, click Events.

2. Double-click ItemDataBound to create an event handler for that event.

3. Copy the following code into the handler.

   If e.Item.ItemType = ListItemType.Item Or _
       e.Item.ItemType = ListItemType.AlternatingItem ThenDim ds As SqlDataSource
     ds = CType(e.Item.FindControl("bulletedListDataSource"), _
         SqlDataSource)
     Dim categoryID AsString
     categoryID = DataBinder.Eval(e.Item.DataItem, _
         "categoryid").ToString()
     ds.SelectParameters("CategoryID").DefaultValue = _
         categoryID
   EndIf
   

   if (e.Item.ItemType == ListItemType.Item ||
       e.Item.ItemType == ListItemType.AlternatingItem)
   {
     SqlDataSource ds;
     ds = e.Item.FindControl("bulletedListDataSource")
         as SqlDataSource;
     ds.SelectParameters["CategoryID"].DefaultValue =
         DataBinder.Eval(e.Item.DataItem,
         "categoryid").ToString();
   }
   

   The ItemDataBound event is raised for each data record being displayed, providing you with an opportunity to read or modify the data. This code does the following:

   1. Checks to be sure that the event is occurring while binding an ItemTemplate or AlternatingItemTemplate object (and not a SeparatorTemplate object or other type of template).

   2. Uses the FindControl method to get a reference to the SqlDataSource control instance that is created inside each item template.

   3. Gets the value of the CategoryID data column by evaluating the current DataItem property.

   4. Sets the CategoryID variable of the parameterized query by setting its DefaultValue property in the SelectParameters collection.

Testing the Code

You can now test your code.

To test the code to set the category ID

• Press CTRL+F5 to run the page.

  The DataList control displays each category record, and for each record, it also displays, in a bulleted list, the products that belong to that category.

Next Steps

This walkthrough has illustrated a simple but complete scenario for displaying and formatting data with the DataList Web server control. You can create more sophisticated data-driven pages and applications using the techniques and controls illustrated in the walkthrough. For example, you might want to:

• Write code to allow users to select items in the DataList Web server control. For more information, see How to: Allow Users to Select Items in DataList Web Server Controls.

• Create a master record for a master/detail relationship. For more information, see Walkthrough: Creating Master/Detail Web Pages in Visual Studio.

• Learn how to use output caching in your data applications to improve performance. For more information, see Walkthrough: Using Output Caching to Enhance Web Site Performance.

See Also

Tasks

How to: Allow Users to Select Items in DataList Web Server Controls

Walkthrough: Creating Master/Detail Web Pages in Visual Studio

Reference

DataList