Export (0) Print
Expand All
4 out of 21 rated this helpful - Rate this topic

Walkthrough: Creating a Master-Detail Windows Form

Visual Studio .NET 2003

In many application scenarios, you want to work with data that comes from more than one table, and often, data from related tables. That is, you want to work with a master-detail relationship between a parent and child table. For example, you might want to get a customer record and see related orders.

The disconnected dataset model in Visual Studio allows you to work with multiple discrete tables in your application and to define a relationship between them. You can then use the relationship to navigate between related records.

This walkthrough illustrates a simple but complete example of working with related tables in a dataset. In the walkthrough, you will create a Windows Form that displays publishers information (the master records) in a list box.

In the walkthrough, you will create the relationship between the tables. You can then use the data-binding facilities of the Windows Form to take advantage of this relationship.

In order to complete this walkthrough, you will need:

  • Access to a server with the Pubs SQL Server sample database.

The walkthrough is split into a number of smaller pieces:

  • Creating the Windows Form.
  • Creating and configuring the dataset that contains the two tables. This includes creating a relationship between the tables.
  • Adding controls to display data and binding them to the appropriate data objects.
  • Writing a few lines of code that will fill the dataset when the form runs.
  • Testing the form.

Creating the Project and Form

The first step is to create a Windows Form.

To create the project and form

  1. If you already have a solution open, select Close Solution from the File menu.
    Note   In production projects, you will very often have multiple projects in the same solution. In this walkthrough, however, you will close any open solution and create a new one along with the project so that there is no interference between what you will do here and any existing forms, datasets, and so on.
  2. From the File menu, point to New, and then choose Project.
  3. In the Project Types pane, choose Visual Basic Projects, Visual C# Projects, or Visual C++ Projects.
  4. In the Templates pane, choose Windows Application for Visual Basic and Visual C# projects, or choose Windows Forms Application (.NET) for Visual C++ projects.
  5. Assign a name to the project that will be unique and will conform to the naming conventions you use. For example, you might name this project Walkthrough_MasterDetail.
  6. When you have assigned a name and specified a new solution, click OK.

    Visual Studio creates a new project and displays a new form called Form1 in the Windows Form Designer.

Creating and Configuring a Dataset

As with most data-access scenarios in Visual Studio, you'll be working with a dataset. A dataset is a container — a cache — that holds the records you are interested in working with.

Note   Using a dataset is only one option for data access, and is not the optimal choice in some scenarios. Working with master-detail data is one scenario in which datasets are a good data-access strategy. For more information, see Recommendations for Data Access Strategies.

In this walkthrough, you will add a dataset to the form. However, you will not do so directly, by manually adding it to the form. Instead, you will perform the following set of steps:

  • Create a data adapter using a wizard. The adapter contains SQL statements used to read and write database information. The wizard helps you define the SQL statements you need. If necessary, the wizard also creates a connection to the database.
  • Generate the dataset schema. In this process, you will have Visual Studio create a new dataset class based on the tables and columns you are accessing. When you generate the dataset class, you will also add an instance of it to the form.

When you have finished generating the dataset, it will contain two tables, the Publishers table and the Titles table, both from the standard Pubs database in SQL Server. The tables have an implicit relationship — the pub_id field is the primary key of the Publishers table and a foreign key in the Titles table. You will turn this implicit relationship into an explicit one that the controls on the form can take advantage of.

In this section of the walkthrough, you will perform a number of steps that will result in Visual Studio generating the dataset for you. For more information on data adapters, see Introduction to Data Adapters. For more information about datasets, see Introduction to Datasets.

Configuring a Data Connection and Data Adapter

To begin, you create data adapters that contain the SQL statement used to populate the dataset later. As part of this process, you define a connection to access a database. You configure the data adapters using a wizard, which makes it easy to create the SQL statements you need for data access.

Note   Keep in mind that users may encounter problems accessing data with your application, depending on the location of the data sources used and the access privileges accorded their computers and user accounts, as well as other issues. For more information, see Troubleshooting Data Access in Visual Studio .NET.

You will need two data adapters, one to get data from the Publishers table and another to get data from the Titles table.

Note   When the wizard is done, you must continue to the next section in order to generate a dataset and complete the data access portion of your form.

To create the data connection and data adapters

  1. From the Data tab of the Toolbox, drag an OleDbDataAdapter object onto the form.
    Note   You could also use the SQL data provider (here, the SqlDataAdapter object), which is optimized for working with SQL Server 7.0 or later. In this walkthrough, you use the OleDbDataAdapter because it is more generic, providing ADO.NET access to any OLE DB-compatible data source. If you use SQL Server version 7.0 or later in your applications, use the SQL data provider.

    The Data Adapter Configuration Wizard starts, which will help you create both the connection and the adapter.

    Security Note   Storing connection-string details (such as the server name, user name, and password) can have implications for the security of your application. Using Windows Integrated Security is a more secure way to control access to a database. For more information, see Database Security.
  2. In the wizard, do the following:
    1. In the second pane, create or choose a connection pointing to the SQL Server Pubs database.
    2. In the third pane, specify that you want to use an SQL statement to access the database.
    3. In the fourth pane, create the following SQL statement:
      SELECT pub_id, pub_name 
      FROM publishers
      

      For assistance building the SQL statement, click Query Builder to launch the Query Builder.

      Note   In this walkthrough, you will populate the dataset with all the rows from the Publishers table. In production applications, you typically optimize data access by creating a query that returns only the columns and rows you need. For an example, see Walkthrough: Displaying Data in a Windows Form Using a Parameterized Query.
  3. Click Finish to complete the wizard.

    When the wizard is complete, you will have a connection (OleDbConnection1 in Visual Basic, or oleDbConnection1 in Visual C# or Visual C++) containing information about how to access your database. You will also have a data adapter (OleDbDataAdapter1 in Visual Basic, or oleDbDataAdapter1 in Visual C# or Visual C++) that contains the SQL statement to get information from the Publishers table.

  4. Drag a second OleDbDataAdapter object onto the form.

    The Data Adapter Configuration Wizard starts again.

  5. Repeat Step 2, with these differences:
    1. In the second pane, choose the same connection you used or created last time.
    2. In the fourth pane, create the following SQL statement to access the Titles table:
      SELECT title_id, title, pub_id, price 
      FROM titles
      
      Note   You must include the
      pub_id
      column.

    The wizard adds OleDbDataAdapter2 to your form in Visual Basic, or oleDbDataAdapter2 in Visual C# or Visual C++.

  6. After the wizard is complete, generate the dataset based on the SQL query that you created during this procedure. For more information, see the next section.

Creating the Dataset

After you have established the means to connect to the database and specified the information you want (via the SQL commands in the data adapters), you can have Visual Studio create a dataset. The dataset is an instance of the DataSet class based on a corresponding XML Schema (.xsd file) that describes the class's elements (tables, columns, and constraints). For more information about the relationship between datasets and schemas, see Introduction to Data Access with ADO.NET.

To generate a dataset

  1. From the Data menu, choose Generate DataSet.
    Tip   If you do not see the Data menu, click in the form; the form must have focus for the menu to appear.

    The Generate Dataset dialog box is displayed.

  2. Select the New option and name the dataset dsPublishersTitles.

    In the list under Choose which table(s) to add to the dataset, make sure both Publishers and Titles is selected.

  3. Check Add this dataset to the designer, and then click OK.

    Visual Studio generates a typed dataset class (DsPublishersTitles or dsPublishersTitles) and a schema that defines the dataset. You will see the new schema (.xsd file) in Solution Explorer.

    Tip   In Solution Explorer for Visual Basic or Visual C#, click Show All Files to see that the schema file's dependent .vb or .cs file, which contains the code that defines your new dataset class. In Solution Explorer for Visual C++, the shema file's dependent .h file is always visible.

    Finally, Visual Studio adds an instance of the new dataset class (DsPublishersTitles1 or dsPublishersTitles1, depending on what programming language you are using— note the sequential number) to the form.

Creating a Relationship Between Dataset Tables

Your dataset contains two tables that you know have a one-to-many relationship. However, the dataset is a passive container (it is not an actual database) that cannot take advantage of the implied relationship between the tables. Therefore, to make the relationship explicit, you create a data relation object.

Note   Notice that you did not create a single query that joins the Publisher and Titles tables in the database. Instead, you create two queries to access the two tables separately. This allows you to manage navigation, relationships, and updates separately for the two tables, giving you substantially more control over the data than if you had based your dataset tables on a join. For more details, see Introduction to DataRelation Objects.

To create a relationship between tables

  1. In Solution Explorer, double-click the schema for the dataset you just created (called DsPublishersTitles.xsd or dsPublishersTitles.xsd).

    The XML Designer opens in Schema view, showing the two tables in your dataset.

  2. From the XML Schema tab of the Toolbox, drag a Relation object onto the Titles table (the child table).

    The Edit Relation dialog box opens with default values derived from the two tables.

  3. Confirm that the following values are set in the Edit Relation dialog box.
    SettingValue
    Namepublisherstitles

    You will need to know this name later; if you change it, be sure you make a note of the new name.

    Parent elementPublishers
    Child elementTitles
    Key fieldspub_id
    Foreign key fieldspub_id
    Create foreign key constraint only (Unchecked)

    Any values not noted above are not important for this walkthrough.

  4. Click OK to close the Edit Relation dialog box.

    A relation icon is displayed between the two tables in the XML Designer. If you need to change relation settings, you can right-click the relation and choose Edit Relation.

  5. Save the schema and close the XML Designer.

At this point you have set up everything you need in order to get information out of database and into a dataset. You are ready to add controls to the form to display the data.

Adding Controls to Display the Data

In this walkthrough, you will add just a few controls: a ListBox control to display a list of publishers, and a DataGrid control to display the titles for that publisher. You can also add labels as needed to indicate what the form is displaying.

Displaying the Publishers in a ListBox Control

The ListBox control displays the publisher names. The control is a complex-bound control; that is, it can display multiple records at once.

To add a bound ListBox control to the form to display publishers

  1. Return to the default form (Form1) that was opened when you created the project. If you have not already done so, switch to the form designer by clicking the tab at the top of the current window.
  2. From the Windows Forms tab of the Toolbox, drag a ListBox control onto the form.
  3. Press F4 to display the Properties window.
  4. In the DataSource property, select DsPublishersTitles1 or dsPublishersTitles1 as the data source.
  5. In the DisplayMember property, display the drop-down list. Select Publishers, expand the Publishers node, and then select pub_name.

    When you are finished, the DisplayMember property should read publishers.pub_name.

    Setting these two properties binds the ListBox control to the publisher name field of the Publishers table.

  6. If you want, add a label in front of the list box to identify it.

Displaying the Titles in a DataGrid Control

Each publisher can have multiple titles. To display titles, therefore, you want to use a control that can display not just multiple records, but multiple columns. A good choice is the DataGrid control.

For this walkthrough, you will want to configure the grid to show only the titles that relate to the currently selected publishers. You can take advantage of the Windows Forms data-binding facility to do this.

You will bind the grid not to the Titles table (as you might guess), but to the data relation object that you created earlier that establishes a relationship between Publishers and Titles. The data relation object is exposed as a property of the Publishers table. As the form is running, whenever you navigate to a new publisher (by selecting it in the list box), the form's data binding framework queries the data relation object to return the corresponding detail records.

To add a bound DataGrid control to the form to display titles

  1. From the Windows Forms tab of the Toolbox, drag a DataGrid control onto the form.
  2. In the Properties window, set the DataSource property to DsPublishersTitles1 or dsPublishersTitles1.
  3. Set the DataMember property to publishers.publisherstitles.

    Setting these two properties binds the grid to the relation object, so that it always contains child records of the current publisher.

  4. Resize the grid so you can see all the columns. Change its height so you will be able to see several title records.

Populating the Dataset

Although the controls on the form are bound to the dataset you created, the dataset itself is not automatically filled in. Instead, you must fill the dataset yourself using a few lines of code that run when the form is initialized. For more information about filling datasets, see Introduction to Datasets.

To populate the dataset

  1. Double-click the form to create a handler for the form's Load event.
  2. In the method, clear the dataset you created, and then call the Fill method of the two data adapters, passing to each call the dataset to fill.

    The following example shows what the complete method will look like:

    ' Visual Basic
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
       DsPublishersTitles1.Clear()
       OleDbDataAdapter1.Fill(DsPublishersTitles1)
       OleDbDataAdapter2.Fill(DsPublishersTitles1)
    End Sub
    
    // C#
    private void Form1_Load(object sender, System.EventArgs e)
    {
       dsPublishersTitles1.Clear();
       oleDbDataAdapter1.Fill(dsPublishersTitles1);
       oleDbDataAdapter2.Fill(dsPublishersTitles1);
    }
    
    // C++
    private:
       System::Void Form1_Load(System::Object *  sender,
          System::EventArgs *  e)
       {
          dsPublishersTitles1->Clear();
          oleDbDataAdapter1->Fill(dsPublishersTitles1);
          oleDbDataAdapter2->Fill(dsPublishersTitles1);
       }
    

Testing

You can now test the form to make sure it displays data the way you intend.

To test the form

  1. Press F5 to run the form.
  2. When the form is displayed:
    • Check that the list box control displays the publisher names.
    • Select different publishers and confirm that the correct titles (and only those) are displayed in the grid.

Next Steps

In this walkthrough, you accomplished two tasks: you created a dataset with two tables and a relationship, and you created a Windows Form to display the related data. Some further steps you could take include:

  • Customize the display of columns in the grid. For more information, see Formatting the Windows Forms DataGrid Control.
  • Use the data, including the data relation, in your own code. (In this walkthrough, the form's data-binding architecture did all the work of resolving related child records.) For more information, see Introduction to DataRelation Objects and Navigating a Relationship between Tables.
  • Separate data access from the user interface. In this walkthrough, you have created a form that accesses the data more-or-less directly (via the dataset). A more flexible and maintainable design is to create a data-access component that handles data access. The form (that is, the user interface) could then interact with the component as needed. The same component could be used by multiple forms (and by other components), which eliminates the overhead and redundancy of redesigning data access for every form you create. For more information on creating component-based data access, see Walkthrough: Creating a Distributed Application.

See Also

Creating Windows Forms | Walkthrough: Simple Data Access in a Windows Form | Introduction to Datasets | Introduction to DataRelation Objects | Navigating a Relationship between Tables

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.