WinForms databinding and foreign key relationships

Question

I'm developing a WinForms application (.Net 3.5, no WPF) where I want to be able to display foreign key lookups in a databound DataGridView.

An example of the sort of relationship is that I have a table of OrderLines. Orderlines have a foreign key relationship to Products and Products in turn have a foreign key relationship to ProductTypes.

I'd like to have a databound DataGridView where each row represents an orderline, displaying the line's product and producttype.

Users can add or edit orderlines direct to the grid and choose the product for the order line from a comboBoxColumn - this should then update the producttype column, showing the producttype for the selected product, in the same row.

The closest to a good fit that I've found so far is to introduce a domain object representing an orderline then bind the DataGridView to a collection of these orderlines. I then add properties to the orderline object that expose the product and the producttype, and raise relevant notifypropertychanged events to keep everything up to date. In my orderline repository I can then wire up the mappings between this orderline object and the three tables in my database.

This works for the databinding side of things, but having to hand code all that OR-mapping in the repository seems bad. I thought nHibernate would be able to help with this wiring up but am struggling with the mappings through all the foreign keys - they seem to work ok (the foreignkey lookup for an orderline's product creates the correct product object based on the foreign key) until I try to do the databinding, I can't get the databound id columns to update my product or producttype objects.

Is my general approach even in the right ballpark? If it is, what is a good solution to the mapping problem?

Or, is there a better solution to databinding rows including foreign key lookups that I haven't even considered?

Answer 1

Hi David, welcome to StackOverflow :)

Normally what you would do is base the information in the drop down on two values ValueMember and DisplayMember.

The ValueMember is the source of the actual controls value (this will be the key value in the order line), the display member is the value that is displayed to the user instead of the value (this will be the FK value).

Is there no particular reason you cannot just return all the data required and set these properties?

Answer 2

This article may help "A Look Under the Hood of Windows Forms Data Binding".

Answer 3

Here's a good "How Do I" video that demonstrates data binding:

http://windowsclient.net/learn/video.aspx?v=52579

Answer 4

My original question obviously wasn't clear, sorry about that.

The problem wasn't with databinding to a DataGridView in general, or with the implementation of a DataGridViewComboBoxColumn - as the people who have answered already rightly say, that is well documented on the web.

The problem I've been trying to solve is with the refresh of properties that are drilling down through relationships.

In my orders example, when I change the value of the "Product" column, the "Product Type" column is not being updated - even though in the code I am setting the property and firing the NotifyPropertyChanged event. (In debug I go to all the right places)

After a lot of poking around I realised that this was not even working when I directly set the "Product Type" property of datasource, rather that setting it in the "Product" setter.

The other thing that I believe has me back on the right track is that when I provide a mocked dataccess layer, created in the main form, everything works fine.

Also, when I copy the IList made by nHibernate to a IBindingList - everything again appears fine.

So the problem is I think with threading and the NotifyPropertyChanged events being lost when using certain datasources, in certain ways (wish I could be more definitive than that!)

I'm going to keep researching better ways of resolving this than copying the IList to the IBindingList - maybe I need to learn about thread marshalling.

Edit

I've now developed a solution that solves the issue and think I understand what was confusing me - basically it appears that anything but basic property databinding doesn't play nicely for lists that aren't derived from BindingList - as soon as I was trying to databind to properties that fired chained NotifyPropertyChanged events, things went haywire and I my events got lost.

The data access solution I have now is using a variation of the Rob Conery IRepository pattern, returning my collections to be bound as a custom class I made, a SortableBindingLazyList that derives from BindingList, implements the Sort Core methods and also stores its internal list as a query, delaying the list materialisation.

Answer 5

Well, I don't know whether it's supported by the DataGridView, but when you're doing regular WinForms databinding (say, to a regular TextBox) you can use property paths to navigate through object relationships.

Something like this:

myTextBox.DataBindings.Add("Text", anOrderLine, "OrderedPart.PartNumber");

Would be worth seeing if this works in your situation too.

Answer 6

I think the problem you're having is that when you are binding to a grid, it is not enough to support INotifyPropertyChanged, but you have to fire the ListChanged events in your IBindingList implementation and make sure that you override and return true for the SupportsChangeNotification property. If you don't return true for this, the grid won't look for it to know if the data has changed.

In .NET 2.0+, you can create a generic collection using the BindingList class, this will take care of most of the nastiness (just don't forget to override and return true for the SupportsChangeNotification property).

If the class you use for data binding has a property that is a collection (such as IBindingList or BindingList), then you can bind the foreign key grid to that property directly. When you configure the bindings in the Forms designer, just select the collection property as the data source for the grid. It should "just work". The only sneaky part is making sure that you handle empty or null collections the right way.