Walkthrough: Creating a Lookup Table
A lookup table is used to display information from one table based on the value of a foreign-key field in another table. For example, consider a table of Orders in a sales database. Each record in the Orders table includes a CustomerID indicating which customer placed the order. The CustomerID is a foreign key pointing to a customer record in the Customers table. When presenting a list of Orders (from the Orders table) you may want to display the actual customers name, as opposed to the CustomerID. Since the customers name is in the customers table, and you are presenting data from the Orders table, you need to create a lookup table, which takes the CustomerID value in the Orders record, and uses that value to navigate the relationship and return the more readable, customer name. This concept is known as a lookup table.
Tasks illustrated in this walkthrough include:
Creating a new Windows Application project.
Creating and configuring a dataset with the Data Source Configuration Wizard.
Selecting the control to be created on the form when dragging items from the Data Sources window. For more information, see How to: Set the Control to be Created when Dragging from the Data Sources Window.
Creating data-bound controls by dragging items from the Data Sources window onto a form.
Setting up a lookup table by dropping a related table onto a control bound through a foreign-key relation.
In order to complete this walkthrough, you need:
Access to the Northwind sample database. For more information, see How to: Install Sample Databases.
To create the new Windows Application project
From the File menu, create a new project.
Name the project LookupTableWalkthrough.
Select Windows Application and click OK. For more information, see Creating Windows-Based Applications.
The LookupTableWalkthrough project is created and added to Solution Explorer.
To create the data source
On the Data menu, click Show Data Sources.
In the Data Sources window, select Add New Data Source to start the Data Source Configuration Wizard.
Select Database on the Choose a Data Source Type page, and then click Next.
On the Choose your Data Connection page do one of the following:
If a data connection to the Northwind sample database is available in the drop-down list, select it.
Select New Connection to launch the Add/Modify Connection dialog box. For more information, see Add/Modify Connection Dialog Box (General).
If your database requires a password, select the option to include sensitive data, and then click Next.
Click Next on the Save connection string to the Application Configuration file page.
Expand the Tables node on the Choose your Database Objects page.
Select the Customers and Orders tables, and then click Finish.
The NorthwindDataSet is added to your project and the two tables appear in the Data Sources window.
To create data-bound controls on the form
Expand the Customers node in the Data Sources window.
Change the drop type of the related Orders table to Details by selecting Details from the control list on the Orders node. For more information, see How to: Set the Control to be Created when Dragging from the Data Sources Window.
Expand the related Orders node and change the CustomerID column's drop type to a combo box by selecting ComboBox from the control list on the CustomerID node.
Drag the related Orders node from the Data Sources window onto Form1.
Data-bound controls with descriptive labels appear on the form, along with a tool strip (BindingNavigator) for navigating records. A NorthwindDataSet, CustomersTableAdapter, BindingSource, and BindingNavigator appear in the component tray.
You establish the lookup table by setting properties on the CustomerID combo box:
To create the lookup table functionality on the form
Drag the main Customers node from the Data Sources window directly onto the CustomerID combo box on Form1.
The table that follows is for reference only; the properties are automatically set during the drag gesture in the procedure above. The designer sets the following properties as described in the "Explanation of setting" below. Depending on your table's structures, you may need to adjust the properties for the lookup table to function properly.
Explanation of setting
If you need to make an adjustment, then set this property to the BindingSource of the table with the column you want to display. (CustomersBindingSource for this walkthrough.)
Visual Studio sets this property to the first column after the primary key that has a string data type for the table you drag onto the control.
If you need to make an adjustment, then set this property to the column name you want to display.
Visual Studio sets this property to the first column participating in the primary key, or the first column in the table if no key is defined.
If you need to make an adjustment, then set this property to the primary key in the table with the column you want to display.
Visual Studio sets this property to the original column dragged from the Data Sources window.
If you need to make an adjustment, then set this property to the foreign-key column in the related table. (CustomerID in the Orders table for this walkthrough.)
Depending on your application requirements, there are several steps you may want to perform after creating a data-bound form. Some enhancements you could make to this walkthrough include:
Adding search functionality to the form. For more information, see How to: Add a Parameterized Query to a Form in a Windows Application.
Adding functionality to send updates back to the database. For more information, see Walkthrough: Saving Data to a Database (Single Table).
Adding the Orders table to the dataset by selecting Configure DataSet with Wizard from within the Data Sources window. Then you can add controls that display related data by dragging the related Orders node (the one below the Fax column within the Customers table) onto the form. For more information, see How to: Display Related Data in a Windows Application.