.NET DataGridView question
-
A contrived example that demonstrates what I'm trying to accomplish: Four tables, like so: main_table: persons_name car color car_lookup: car_id car_description color_lookup: color_id color_description car_color: car_id color_id The idea is that for each type of car in the car_lookup table, there are only a few values in the color_lookup table that are valid. The car_color table has the all of the color_id values that are valid for each car_id (that is, if I select all of the rows for a particluar car_id, I'll retrieve all of the valid color_ids for that car). Question 1: ok design? Is there a better way to approach this? Question 2: I'm using a DataGridView to present the main_table to the end-user for editing. Works fine when I'm just presenting the table with the car and color fields just showing ID numbers. How can I present the data so that the DataGridView presents the car_description instead of the car_id that is in the car field (and this one field is not changeable) and for the color field present a drop down of just the valid colors for the given car_id? This would mean that the drop down is different for each row. If car #1 could be only green or blue, whereas car #2 could be blue or red, then the drop down would reflect that. The database is on a SQL server, the DataGridView is connected to a BindingSource, which is connected to a SqlDataAdapter. Unsure if this belongs in the .NET section or here. Apologies if this is the wrong place. I'm writing in C#, but any general DataGridView info would be appreciated.
-
A contrived example that demonstrates what I'm trying to accomplish: Four tables, like so: main_table: persons_name car color car_lookup: car_id car_description color_lookup: color_id color_description car_color: car_id color_id The idea is that for each type of car in the car_lookup table, there are only a few values in the color_lookup table that are valid. The car_color table has the all of the color_id values that are valid for each car_id (that is, if I select all of the rows for a particluar car_id, I'll retrieve all of the valid color_ids for that car). Question 1: ok design? Is there a better way to approach this? Question 2: I'm using a DataGridView to present the main_table to the end-user for editing. Works fine when I'm just presenting the table with the car and color fields just showing ID numbers. How can I present the data so that the DataGridView presents the car_description instead of the car_id that is in the car field (and this one field is not changeable) and for the color field present a drop down of just the valid colors for the given car_id? This would mean that the drop down is different for each row. If car #1 could be only green or blue, whereas car #2 could be blue or red, then the drop down would reflect that. The database is on a SQL server, the DataGridView is connected to a BindingSource, which is connected to a SqlDataAdapter. Unsure if this belongs in the .NET section or here. Apologies if this is the wrong place. I'm writing in C#, but any general DataGridView info would be appreciated.
The database design is fine, as the car and color has many-to-many relationship. Regarding the database binding, you can have queries in your stored procedure to return multi-recordsets and then you can store them in multi datatables in your dataset with relationship between the datatables... Then bind the cars and based on the car the colors to the dropdown list by iterating through the rows in the GridView...
-
A contrived example that demonstrates what I'm trying to accomplish: Four tables, like so: main_table: persons_name car color car_lookup: car_id car_description color_lookup: color_id color_description car_color: car_id color_id The idea is that for each type of car in the car_lookup table, there are only a few values in the color_lookup table that are valid. The car_color table has the all of the color_id values that are valid for each car_id (that is, if I select all of the rows for a particluar car_id, I'll retrieve all of the valid color_ids for that car). Question 1: ok design? Is there a better way to approach this? Question 2: I'm using a DataGridView to present the main_table to the end-user for editing. Works fine when I'm just presenting the table with the car and color fields just showing ID numbers. How can I present the data so that the DataGridView presents the car_description instead of the car_id that is in the car field (and this one field is not changeable) and for the color field present a drop down of just the valid colors for the given car_id? This would mean that the drop down is different for each row. If car #1 could be only green or blue, whereas car #2 could be blue or red, then the drop down would reflect that. The database is on a SQL server, the DataGridView is connected to a BindingSource, which is connected to a SqlDataAdapter. Unsure if this belongs in the .NET section or here. Apologies if this is the wrong place. I'm writing in C#, but any general DataGridView info would be appreciated.
Your table design seems fine I never use a SQLDataAdapter as a binding source as the adapter is always in the DAL and never comes out, only a datatable comes out of the DAL. I would create a stored proc to service the car grid (CarGetForGrid) that joined the 4 tables and displayed the descriptors and the carid only. the result set would be retrieved via the DAL and used to populate the DGV. I then hide col0 from the user in the DGV I have a major issue with allowing the user to do inline editing of the DGV. This is a personal design issue, I almost never allow inline editing. I force the user to double click the required record and display a dialog nicely layed out for CRUD. This does require rather a lot of dialogs but I have much greater control with a dialog. A list control is dor display purposes only, a dialog is for data entry. This design eliminates all the crap around combos in grids.
Never underestimate the power of human stupidity RAH
-
Your table design seems fine I never use a SQLDataAdapter as a binding source as the adapter is always in the DAL and never comes out, only a datatable comes out of the DAL. I would create a stored proc to service the car grid (CarGetForGrid) that joined the 4 tables and displayed the descriptors and the carid only. the result set would be retrieved via the DAL and used to populate the DGV. I then hide col0 from the user in the DGV I have a major issue with allowing the user to do inline editing of the DGV. This is a personal design issue, I almost never allow inline editing. I force the user to double click the required record and display a dialog nicely layed out for CRUD. This does require rather a lot of dialogs but I have much greater control with a dialog. A list control is dor display purposes only, a dialog is for data entry. This design eliminates all the crap around combos in grids.
Never underestimate the power of human stupidity RAH
Thanks for the advice. I think I'll do what you suggest -- force the user to double-click the row to edit, then present a form showing just that record. Since the editing is minimal, I was hoping to get away with just a table, but this is probably better design.
-
Your table design seems fine I never use a SQLDataAdapter as a binding source as the adapter is always in the DAL and never comes out, only a datatable comes out of the DAL. I would create a stored proc to service the car grid (CarGetForGrid) that joined the 4 tables and displayed the descriptors and the carid only. the result set would be retrieved via the DAL and used to populate the DGV. I then hide col0 from the user in the DGV I have a major issue with allowing the user to do inline editing of the DGV. This is a personal design issue, I almost never allow inline editing. I force the user to double click the required record and display a dialog nicely layed out for CRUD. This does require rather a lot of dialogs but I have much greater control with a dialog. A list control is dor display purposes only, a dialog is for data entry. This design eliminates all the crap around combos in grids.
Never underestimate the power of human stupidity RAH
If you don't use a SQLDataAdapter as your binding source, what do you recommend? There appear to be a few different ways to bind the the DataGridView to the database -- I think I just used a method I found in Microsoft's docs. (While I'm a resonably experienced programmer, I'm pretty new to .NET.)
-
If you don't use a SQLDataAdapter as your binding source, what do you recommend? There appear to be a few different ways to bind the the DataGridView to the database -- I think I just used a method I found in Microsoft's docs. (While I'm a resonably experienced programmer, I'm pretty new to .NET.)
Art Frank wrote:
There appear to be a few different ways
This my friend is an understatement, I usually use 2 data sources, a datatable/view or a List either bound to a bindingsource and the bindingsource as the datasource to the table. I then hide any columns in the DGV I don't like, I have a base rule that the recordID is ALWAYS in column0 and this is almost always hidden. On the dblclick, grab the selected row.cell[0].value and pass it to the dialogs constructor. If you are using a List you can pass the instance of MyClass. I have a Utils class that does all the binding and formatting for me so I dont even think about it. It has some requirements like controls are named and prefixed correctly but I have been doing that for so long it is automatic.
Never underestimate the power of human stupidity RAH