Datagrid usage sourced from SQL server.

Question

I would like to know the best way to use a datagrid control linked to a combination (join) of data tables in a way that both simply allows both display, creation of new rows in underlying tables and deletion.

The datagrid appears to offer the latter capabilities but I have not found a way that I am happy with to do more than just display on one grid and offer specific separate edit create and delete facilities.

Suppose for the sake of illustration that the database contains:-

Customer Table
* CustomerID
* CustomerName

Order Table
* CustomerID
* OrderLineItem
* OrderLineQuanity

And that I want to lose the CustomerID for display purposes but would like to be able to create new customers and delete existing ones, perhaps with a confirmatory dialog.

Answer 1

If the relationship is 1 to many you can go the route of using Master Detail. [link text][1]

[1]: http://msdn.microsoft.com/en-us/library/aa479344.aspx/"Master Detail"

Answer 2

If I understand your question correctly, you have a query that performs a join of several tables which you display on a single grid. You’d like the user to be able to manipulate that grid and have the underlying tables reflect the changes.

An approach is to solve this problem is to implement stored procedures to perform the CRUD operations. The stored procedures will contain the logic to insert, update and delete records from all of the required tables. Each procedure will need to have a parameter for each bound field on the grid. Set the procedures to be the insert, update and delete commands on your data source.

So imagine if you are adding a new record to the grid. The grid calls the insert command, passes the parameters to the stored procedure. Then within the stored procedure you’ll create the logic to determine if the new line in the grid requires a new customer or if it’s an existing customer then adjust the operation accordingly.

Answer 3

CSharpAtl is correct, use a Master-Detail control. An example of using one in a WinForm app is at http://msdn.microsoft.com/en-us/library/y8c0cxey.aspx.

WinForm DataGrids support add, edit, and delete of both Master and Detail records. As for your question about what happens if you change a Detail record so it matches a new Master; that is not possible. By design a Detail row only contains records that match the Master, you cannot (for example) change an order to belong to a new customer because the Detail row does not contain any customer information.

If you want to move a Detail row to another Master, you have to create a new Detail row for the new Master, copy the data from the old Detail row, and delete the old Detail row. If you're ambitious you could support Cut and Paste or Drag and Drop of Detail rows, but internally you have to Create/Copy/Delete.