DataGridView and two tables - best approach?

Hi folks,

I can see this has been asked a few times, but the more I read, the more confused I get. Could anybody point me in the right direction please.

The basic problem is this: I have a database with three tables:
stock
orders
order_details

Stock and Orders are self explanatory. Order_details is a lookup table, holding basically just the orderID, stockID and quantity for each item in the order. That's not very friendly for the user however, so what I want to show is a DataGridView with some elements from order_details, and some from stock, ie:
Part_Code, Item_Name, Description, Quantity, Price

I can create a database view for this easily enough, and bind it to a datagridview. It shows existing items in the order fine, but the problem is the view doesn't update when I add new elements to order_details in the dataset. It appears that it's bound to the main database, and so won't update until I save the record.

That also has the problem that it's now much harder to update the database. A simple datagridview bound to order_details would make it easy for the user to enter quantities and for me to save the record, this one now needs a custom update statement.

In summary:
- I want to show data from two tables
- I want the datagridview editable (well, the quantity column anyway)
- I want the datagridview to update when I add entries in the dataset

What is the best/easiest way to do this? Is my current approach of a view in the database and custom update statements sensible? How can I get the datagridview to refresh whenever there's a change to the local dataset?

thanks everyone,

Ross

First up, controls are not bound to databases. ADO.NET follows a disconnected model. You open a connection, query the database, create a local cache and then disconnect. That local cache is your DataSet and your controls will be bound to the DataTables it contains.

Secondly, the whole point of data-binding is that what affects one affects the other, i.e. if you have one or more controls bound to a DataTable, any changes you make to the DataTable will be reflected in the control and vice versa.

Thirdly, because ADO.NET is disconnected, changes you make to your local cache, i.e. DataSet, have no effect on the database. You have to execute SQL code to save those changes from the local cache to the database.

So, you can use use a view in the database or not. It doesn't really matter to the app. Views can be directly updatable but they usually aren't. Whether you have a view in the database that you perform a simple query against to populate your DataSet or you perform a more complex query against multiple tables, you're probably going to have to execute exactly the same SQL statements against exactly the same database tables to save your changes.

No, I understand all that. The problem with using a view is that it doesn't seem well connected to the local dataset.

When I have a DataGridView bound to a table in the dataset, the control updates as soon as I add data to the table.

However, the DataGridView I'm using that's bound to a view doesn't update. It appears that the view is treated as a separate table rather than a join of two tables, so the view doesn't update until the data is saved to the main database and the view updated.

And finally, it does make a difference whether I use a view or not. If I have a datagrid bound to a table, updating the database is a couple of lines of code, Visual Studio generates all the SQL for me. If however I bind to a view, I have to manually edit the insert, update and delete statements for that view, and I have to maintain those changes any time there are updates to the database structure.

But even knowing all that, I'm struggling to find a way to display what I want without it feeling like a horrible bodge.

Right now I'm torn between two approaches:

1. Use a view for the dataset
This displays exactly what I want, but:
- It's hard to update the database, I need to write custom insert, update and delete statements
- It's hard to add rows, instead of just adding fields, I need to manually pull data from two tables

2. Use an unbound DataGridView
- I can now easily add rows to the tables
- And updating the database is far simplier
- However, I need to add rows to the unbound DataGridView manually too, doubling the coding
- I also need to add code so that user edits to the DataGridView make it back to the tables

That's all I can think of so far, are there any other approaches?

I'm not sure you do understand all that.

Quote:

---

However, the DataGridView I'm using that's bound to a view doesn't update.

---

It's is not possible to have a grid bound to a database view. It's not possible to have a grid bound to a database table either. Your grid is bound to a DataTable, i.e. a local object. It's not bound to anything in the database at all.

Quote:

---

And finally, it does make a difference whether I use a view or not. If I have a datagrid bound to a table, updating the database is a couple of lines of code, Visual Studio generates all the SQL for me. If however I bind to a view, I have to manually edit the insert, update and delete statements for that view, and I have to maintain those changes any time there are updates to the database structure.

---

The system will generate all the SQL for you if you get data from a single table, but it won't if you want data from multiple tables in the same DataTable, whether the source is a view or query incorporating a join. You've said yourself that you want data from multiple tables so the system is not going to generate any SQL for you.

Sorry, bad terminology. It's not bound directly to the view, it's bound to a datatable that's created when I attempt to link to a view via the dataset wizard.

I think I've found a better solution though. I already have the relationships established between all these tables, and it seems that I can create extra columns in the tables in my local dataset that pull data from other sources.

So I'm updating the Order_Details table with:

Code:

---

me.dataset.Tables("Order_Details").Columns.Add("MfrPartNo", System.Type.GetType("System.String"), "Parent(FK_Order_Details_Stock).MfrPartNo")

---

That appears to add an extra read only column, that displays the information I want from the second table.

So now I can have a datagridview that's just bound to Order_Details, with all the auto-generated SQL goodness, but I can display the fields I want from the related table.

It also makes editing quantities a piece of cake since the users edits are being saved directly to Order_Details too.

All I have to do now is see if this works. :-D

Cool. Never actually added a column in that manner myself.

No, I just happened to hit on one thread talking about it, it's vaguely referenced in MSDN just where it talks about table joins, but I'm surprised I've not seen it mentioned before in relation to DataGrids / DataGridViews. I've seen tons of articles saying their inability to work with table joins / multiple tables is their major weakness, but it looks like this has been supported for ages.

I spent most of yesterday trying to think of a neater way to do this, which shows how little documentation there is on this. These externally referenced columns are perfect. It's a bit odd modifying a datatable to add a reference, but it works, and I can't think of many situations they wouldn't work for.

I think I might investigate this further and blog about it. Hopefully then others won't have quite so much trouble finding out how to do what you've done. :thumb:

Cool, let me know the location of your post when it's done, I'm very much an amateur programmer, so it'll be good to see how somebody more knowledgeable approaches it :)

I'm loving this though. I've already added rows pointing to two other tables. If you just want to display the details to the user it's perfect.

I'm guessing that if you wanted to let the user edit any of these values, you'd want to add a combo box control within the DataGridView. I think I've done that before, but it was a long time ago, a couple of years at least.

jmcilhinney,

just curious if you ever posted anything on this...?

I checked your blog but didnt see it, but not sure I was getting a listing for ALL of your articles...