IssacJones
asked on
Advice on using DataTables/DataViews/Grids
Hi
I'm hoping somebody can give me advice on the following scenario using ADO.Net.
I have a Windows Fom which contains two combo boxes and a DataGrid.
When I select certain values in the combo boxes, the DataGrid is populated with some of the data from an SQL table; lets call it TableA for now.
The user is allowed to Add/Edit/Delete entries in the populated DataGrid.
Suppose the user makes Add/Edit/Delete(s) and then re-selects the combos so the data now displayed in the DataGrid may change. Further changes could then be made and the process could be repeated several times.
I'm adding a button which will be called "Update" which when pressed would carry any changes over to the underlying database.
My question is, what is the best way to accomplish this?
Do I really need to download TableA entirely into a DataTable first? Wouldn't it make sense simply to download the rows which match the criteria of the two combo boxes? However, if I do this, won't the logic become complex?
Any advice would be most appreciated.
Issac
I'm hoping somebody can give me advice on the following scenario using ADO.Net.
I have a Windows Fom which contains two combo boxes and a DataGrid.
When I select certain values in the combo boxes, the DataGrid is populated with some of the data from an SQL table; lets call it TableA for now.
The user is allowed to Add/Edit/Delete entries in the populated DataGrid.
Suppose the user makes Add/Edit/Delete(s) and then re-selects the combos so the data now displayed in the DataGrid may change. Further changes could then be made and the process could be repeated several times.
I'm adding a button which will be called "Update" which when pressed would carry any changes over to the underlying database.
My question is, what is the best way to accomplish this?
Do I really need to download TableA entirely into a DataTable first? Wouldn't it make sense simply to download the rows which match the criteria of the two combo boxes? However, if I do this, won't the logic become complex?
Any advice would be most appreciated.
Issac
Nasir Razzaq
If you want to update at the end then you will need to download all data on client and then do the filtering.
ASKER
An update may occur many times
Then you can populate a table every time selection changes and update previous one when the selection changes.
If the table is big, loading everything in memory and filtering with a DataView might not be a good solution because the size of the table might require a lot of resources.
If the table is small, then going back to the server for each selection in the ComboBox might be a little slower.
The code is almost the same between both solutions, it will not be more complex if you go back many times. You will simply filter in the code that fills the DataTable instead of filter in the DataView. You will need to trigger an Update each time, but this is the same routine that is called by the Update button.
Personnally, I work with SQL Server, and going back everytime is not significantly slower than using the DataView, so I almost always go with that method, even with small tables. That way, I do not end up having problems after a couple of years when table becomes bigger.
If the table is small, then going back to the server for each selection in the ComboBox might be a little slower.
The code is almost the same between both solutions, it will not be more complex if you go back many times. You will simply filter in the code that fills the DataTable instead of filter in the DataView. You will need to trigger an Update each time, but this is the same routine that is called by the Update button.
Personnally, I work with SQL Server, and going back everytime is not significantly slower than using the DataView, so I almost always go with that method, even with small tables. That way, I do not end up having problems after a couple of years when table becomes bigger.
ASKER
The thing is that I don't want to perform an update each change a selection is changed.
My thinking is that an update must only be done once an update button is pressed. This allows the possibility that changes could be made to the grid for various combo box selections, and then all changes discarded by closing the form without pressing the update button.
This is probably what will complicate things?
My thinking is that an update must only be done once an update button is pressed. This allows the possibility that changes could be made to the grid for various combo box selections, and then all changes discarded by closing the form without pressing the update button.
This is probably what will complicate things?
ASKER
Here's a little bit more information on the scenario I'm trying to deal with...
Suppose TableA has millions of records.
I don't want to download the entire table from TableA into a DataTable as this would be slow and unncessary.
When the combos are selected, then I need to download some of the rows from TableA and display them in the DataGrid. Changes can then be made to this data but as I mentioned I don't want changes passed to the underlying database until and "Update" button is pressed.
Suppose now the combo selections are changed. This means that I may be passing new rows to those already downloaded from TableA and these are then displayed in the grid. Again, these rows may be modified by the user.
To complicate matters further, the user may specify combos to dispay rows that have already been modified via the grid but not updated to the underlying database! I would then need to display these rows rather than the originals in the database.
I feel that this is fairly complex and I don't see an obvious way to deal with the scenario. However, there must be a way to do it as it seem to me to be something that must occur again and again in applications.
Any further advice would be great.
Issac
Suppose TableA has millions of records.
I don't want to download the entire table from TableA into a DataTable as this would be slow and unncessary.
When the combos are selected, then I need to download some of the rows from TableA and display them in the DataGrid. Changes can then be made to this data but as I mentioned I don't want changes passed to the underlying database until and "Update" button is pressed.
Suppose now the combo selections are changed. This means that I may be passing new rows to those already downloaded from TableA and these are then displayed in the grid. Again, these rows may be modified by the user.
To complicate matters further, the user may specify combos to dispay rows that have already been modified via the grid but not updated to the underlying database! I would then need to display these rows rather than the originals in the database.
I feel that this is fairly complex and I don't see an obvious way to deal with the scenario. However, there must be a way to do it as it seem to me to be something that must occur again and again in applications.
Any further advice would be great.
Issac
Then change
>update previous one when the selection changes.
to
update previous one on button click.
>update previous one when the selection changes.
to
update previous one on button click.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The Merge method looks like exactly what I am looking for.
One further question: what happens when you perform a merge which has rows already modified/deleted in the DataTable? Obviously, in these cases, the original rows should not overwrite the ones in the DataTable. Is there a way to get over this?
Issac
One further question: what happens when you perform a merge which has rows already modified/deleted in the DataTable? Obviously, in these cases, the original rows should not overwrite the ones in the DataTable. Is there a way to get over this?
Issac
Look carefully at my message. There is an overload to Merge in which you can specify to KeepChanges:
Dim currentTable As DataTable
Dim newSelection As DataTable
currentTable.Merge(newSele
Dim currentTable As DataTable
Dim newSelection As DataTable
currentTable.Merge(newSele