About six months ago, Milind wrote a post about how to leverage the Data Source Window to build look-up binding. In that article, Milind briefly described one way to do that through drag-and-drop. In this post, I’ll show you different ways to use the Data Source Window and build look-up binding through easy drag-and-drop gesture. I’ll also explain what is look-up binding, if you are not familiar with this term.

What is Look-Up Binding

Sometimes it is useful to display data in a user-friendly UI format, but store the data in a format that is more meaningful to your database program. For example, an order form might display the customer-company items by CompanyName in a list box. However, the data table recording the order would contain the unique ID numbers representing the customer. The following tables show an example of how to store and display order-form data.

Orders

OrderID

CustomerID

Freight

ShipAddress

10643

ALFKI

29.46

Obere Str. 57

10308

ANATR

1.61

Avda. de la Constitución 2222

Customers:

CustomerID

CompanyName

ContactName

ALFKI

Alfreds Futterkiste

Maria Anders

ANATR

Ana Trujillo Emparedados y helados

Ana Trujillo

 

In this scenario, one table, Orders, stores the actual information you are concerned with displaying and saving.. The other table, Customers, contains only appearance-related information about which ID number is equivalent to which company name and contact name, and nothing about the actual orders information.  The tables are designed like this, because these two tables have one-to-many relationship, i.e. one customer could have multiple orders.

When we develop an order form, we can drag and drop the order table from the Data Source Window in Visual Studio. The fields on the left side of the form in the picture below shows what’s created for us after we drop the properties of the Orders table.

However, instead of showing the “CustomerID”, “EmployeeID” and “Ship Via” (ShipperID), we’d like to see more user-friendly names, like “CompanyName”, “EmployeeName” or “Shipper CompanyName”, which are not stored in the Orders. In this case, we need to do look-up binding, i.e. we use a single table A to display (and save back) the information (like CompanyName, EmployeeName and Shipper CompanyName) from a related table B. Table B contains the real data that we want to look up and display.

clip_image001 

How to Build Look-Up Binding with the Data Source Window

There are two ways that you can create look-up bindings through drag-and-drop gesture with the VS Data Source Window. And these gestures only work on the controls that support look-up bindings. In VS2010, the following controls support it by default:

·         ComboBox

·         ListBox

·         ListView

·         Selector

n  Option 1:

Milind’s post describes the way to drag and drop from the entity, which is summarized below:

Suppose we have the Customers and Orders tabled added into the Data Source Window and want to do something like the above picture does.

Steps:

1. Select the Orders from DSWin and set it to DetailsView.

2. Change the control type associated with CustomerID within Order table to ComboBox (or other control with LookupBindingProperty attribute registered).

3. Drag and drop the Order table to the designer and generate the DetailsView of Orders. XAML code is generated as below:

ItemsSource="{Binding Source={StaticResource ordersViewSource}}"

DisplayMemberPath="CustomerID"

4. Select the lookup table (say Customers), and drag and drop to the existing lookup control (created in step3, say customerIDComboBox).

5. Lookup table generated with as below:

ItemsSource="{Binding Source={StaticResource customersViewSource}}"

DisplayMemberPath="CompanyName"

SelectedValuePath="CustomerID"

SelectedValue="{Binding Path=CustomerID, Source={StaticResource ordersViewSource}}"

IsSynchronizedWithCurrentItem="False"

n  Option 2:

Another drag-and-drop gesture that is supported for the look-up binding is first with associated look-up control, and then adds the data property from its related table with foreign key column bound as “SelectValue”:

Steps:

1.    Select Customer entity and change its control associated type to ComboBox.

2.    D&D the Customer table to designer, a look-up binding is generated as below:

ItemsSource="{Binding Source={StaticResource customersViewSource}}"

DisplayMemberPath="CompanyName"

SelectedValuePath="CustomerID"

IsSynchronizedWithCurrentItem="True"

3.    Drag the related table’s data property, say Orders.CustomerID, and then drop to the ComboBox which is already bound to “Customer”. XAML is generated by adding the following:

SelectedValue="{Binding Path=CustomerID, Source={StaticResource ordersViewSource}}"

IsSynchronizedWithCurrentItem="False"

 

So you can see that it’s pretty easy to build look-up bindings with the Data Source Window, and both of the techniques yield the same result. Last but not least, the same gesture works in the different projects that support the Data Source Window, including WinForms, WPF, Silverlight, and WCF RIA Services.

Have fun!