C#: How to handle "self-references" for tables within DataGridView?

Question

Situation:

In my database I have a table in which records may reference other records in the same table. Every record has a unique numeric ID (auto-ID column) and a nullable column IDRef which may contain the ID of the record to be referenced.

Now, in my C# Windows Forms application I'm using a DataGridView to allow the user to edit the records in the table. He may add, edit or remove items. I have a typed DataSet to hold the data and a corresponding TableAdapter to load the data from the database and perform updates. The DataGridView is bound to a BindingSource that uses the typed DataSet as its data source.

For the IDRef column I've added a ComboBox-column that populates its values from the same BindingSource as the DataGridView itself. That means the ComboBox is always up to date with the number of records.

Problem:

I'm displaying the ID column in the DataGridView. When adding new rows by clicking into the respective row of the DataGridView, the ID value is automatically populated with negative values. The final auto-ID value is computed only after persisting the changes to the database using the TableAdapter.

Now when I drop down the ComboBox for the IDRef column, I can select the previously existing records (for example 101, 102, etc.), but I can also select records that have been added after the last update to the database (for example -1, -2, -3).

Now: After I use the TableAdapter's Update method to persist the changes to the database, these negative IDs do not exist anymore - I'm referencing non-existant records (in my case this leads to a foreign key constraint error).

Solution?

What I did is:

• Use second DataSet and BindingSource for the ComboBox column
• Add an "Apply" button that updates the changes to the database using the TableAdapter
• Re-read the data into the second DataSet bound to the ComboBox column

This makes sure the user can only select values that have been persisted to the database by pressing the "Apply" button.

Is there a more elegant way than that to handle such "self-references" within tables using the DataGridView control?

EDIT: Changed tags to better fit subject

Answer 1

DataGridView binding to complex business objects

Answer 2

When using two different tables the negative-ID problem is solved by the relation, edit it's properties and set it to be a Foreign key with Cascading updates.

I would expect this to work for self-referencing as well but it's tricky.

On a separate note, I think you should have a different BindingSource (to the same table) for the combobox (or bind w/o a BindingSource).

Note that you do have some other tools available as well, like Adapter.Update (dataRow)