Combobox for Foreign Key in DataGridView

Question

I have a database containing two tables, Products and Licences. Licences.ProductID has a foreign key reference to Products.ProductID (i.e. licenses for that product).

How do I represent that relationship in a WinForms DataGridView?

When feeding the DataGridView (SQL Metal and through LINQ to SQL), the ProductLicences.ProductID, it automatically generates a column with a text field expecting a "Product" (which of course I can't enter...).

How do I change this column to contain a combobox listing the available products?

---

I have an connection (inherits from Linq.DataContext), the data source assigned to the DataGridView is a Link.IQueryable, generated as such:

var ds = from c in m_connection.Licences
    select c;

Answer 1

In this case, the behaviour you are trying to imitate is a Lookup Combo. You don't want to use the Product field of the License class, you actually want to use the ProductID field.

Here's a quick step-by-step:

1. Remove the Product column in the grid; keep only the ProductID.

2. Change the ProductID column's ColumnType property to DataGridViewComboBoxColumn.

3. Change this column's DataSource to a new BindingSource with the DataSource set to MyProject.Product (you can just follow the wizard, probably the same way you did it for the grid itself, but using Product instead of License).

4. Change the DisplayMember of this column to whatever text you want to show in the combo box, for example, ProductName.

5. Change the ValueMember to the actual primary key, such as ProductID.

6. Before populating the GridView itself, initialize the new productsBindingSource with product data, i.e. with productBindingSource.DataSource = context.Products;.

That's it. Now when you SubmitChanges (assuming you kept the DataContext open the whole time), it will update the ProductID with the correct reference. Note that it might not update the Product reference on the class that it saved; if you need to make use of the actual entity reference for any reason then you might need to call the DataContext.Refresh method on it first. But don't worry about this unless you need to use the entity class outside the grid.