Use ID column of DataGridView as source of query of another DataGridView

Question

I use LINQ to query my MSSQL 2005 database. I want to show several fields of some tables in one DataGridView. The selection of a row of the DataGridView should result in showing results in another DataGridView based on a column which is not shown in any DataGridView (it is an ID attribute).

What is a good way to accomplish this?

---

My Ideas of Possible Solutions

Bind to an Anonymous Type

I would bind my DataGridView to a BindingSource, which is bound to the result of an anonymous LINQ query (anonymous type). To get the ID to build up the relation to the second DataGridView, I would use reflections.

Bind to a Specific Type

I would create a class which holds all fields I want to show in my DataGridView and additionally the ID field. The BindindSource would bind to an instance of this specific class.

I want to avoid this method, because I would have to create one class for each DataGridView I use in my application and I would have to maintain three parts, the class, the LINQ query and the DataGridView columns.

Answer 1

The simple solution is to ensure the ID attribute is a DataKey on the first Gridview. Add a RowSelecting event to the first Gridview, use the ID attribute for that Row as a parameter for the second Gridview, and DataBind.

If you are trying to display different data depending on the Row that is selected (i.e. data could come from a few different database tables) then I'd suggest you create a GridView for each of those tables, and in your RowSelecting event identify which GridView to bind & display.

It's much simpler and clearer to have static GridViews than it is so dynamically create and format them.

EDIT: So if you have n:m:l structure and want to display all l for a particular n, you need to run a subselect such as SELECT * FROM l WHERE l.m_id IN (SELECT m_id FROM m WHERE m.n_id = n_value)

So in summary:

1. Read up on DataKey property. This will help you store the n value (the id of the n table).
2. Read up on RowSelecting event. This will allow you to catch the n value for the selected Row.
3. Run a query similar to the above and bind your second GridView to the resulting DataSource.

Hope this helps.