Displaying Related Data

  • Article

In this lesson, you will learn how to display related data in two separate tables on a Windows Form.

The previous four lessons describe how to create a database and modify and display data from it. When you install SQL Server Compact 3.5 with Visual Basic Express, you have access to a sample database named Northwind.sdf, which contains several tables.

Prerequisites

To create and access a SQL Server Compact 3.5 database by using Visual Basic Express, you must first install SQL Server Compact 3.5. If you did not install it when you installed Visual Basic Express, you have to do so before continuing. For more information, see Using SQL Server Compact 3.5 (Visual Studio).

Try it!

To connect to the Northwind database

  1. On the File menu, click New Project.

  2. In the New Project dialog box, in the Templates pane, click Windows Forms Application, and then click OK.

    A new Windows Forms project opens.

  3. On the Data menu, click Show Data Sources.

    The Data Sources window opens.

  4. In the Data Sources window, click Add New Data Source.

    The Data Source Configuration Wizard opens.

  5. Select Database and then click Next.

  6. Click New Connection.

    The Add Connection dialog box opens.

  7. In the Add Connection dialog box, check whether the Data source is Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Microsoft SQL Server Compact 3.5). If it isn't, click Change, select Microsoft SQL Server Compact 3.5 in the Change Data Source dialog box, and then click OK.

  8. In the Add Connection dialog box, click Browse and locate the Northwind.sdf database in Program Files\Microsoft SQL Server Compact Edition\v 3.5\Samples, on the drive on which you installed SQL Server Compact 3.5.

  9. Click OK to close the dialog box, and then, in the Data Source Configuration Wizard, click Next. If you are prompted to copy the data file to your project, click Yes.

  10. On the next page of the wizard, the Yes, save the connection as check box should be selected. Click Next to continue.

  11. On the Choose Your Database Objects page, expand the Tables node, and then select the check boxes for both the Order Details table and the Products table.

  12. Click Finish.

    A local database file is added to your project. Notice that a NorthwindDataSet object appears in the Data Sources window.

  13. In Solution Explorer, select the Northwind.sdf node and make sure that the Copy to Output Directory property is set to Copy if newer.

  14. On the File menu, click Save All to save your project.

When there is related data in two tables, the relationship is visible in the Data Sources window. For example, if you expand the Products table, you will notice that the Order Details table is displayed as being part of the Products table. If you drag this Order Details table or any one of its fields to a Windows Form, you can show the relationship between the table or field and the Products table on the form. For example, you can move through the products in the Products table and display the corresponding order information for each product.

Try it!

  1. In Solution Explorer, right-click Form1 and then click View Designer.

  2. In Solution Explorer, click the Data Sources tab.

  3. Expand the Products table, select Product Name, click the drop-down arrow next to Product Name, and then click Label.

  4. Drag the Product Name field to the Windows Form.

  5. Drag the Order Details table (the one that is located in the Products table) to the form, placing it under the label.

    A DataGridView control is added to the form.

  6. Press F5 to run the program.

  7. When the application starts, click the Move Next button on the toolbar at the top of the form.

  8. Verify that the order detail information in the DataGridView control is updated whenever the product changes.

Next Steps

In this lesson, you learned how to add data-bound controls to a Windows Form that navigates through related data. In the next lesson, you will learn about the O/R Designer.

Next Lesson: Creating LINQ to SQL Classes: Using the O/R Designer

See Also

Tasks

Storing and Accessing Data

Creating Your First Database

Getting the Information You Need: Connecting to an Existing Database

Showing Information to the User: Displaying Data in Your User Interface

Adding or Modifying Your Records: Updating Data

Other Resources

Managing Your Records: Using Data in Your Program

Visual Basic Guided Tour

SQL Server Compact 3.5 Books Online