• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

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
0
IssacJones
Asked:
IssacJones
  • 4
  • 3
  • 3
1 Solution
 
CodeCruiserCommented:
If you want to update at the end then you will need to download all data on client and then do the filtering.
0
 
IssacJonesAuthor Commented:
An update may occur many times
0
 
CodeCruiserCommented:
Then you can populate a table every time selection changes and update previous one when the selection changes.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jacques Bourgeois (James Burger)Commented:
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.
0
 
IssacJonesAuthor Commented:
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?
0
 
IssacJonesAuthor Commented:
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
0
 
CodeCruiserCommented:
Then  change

>update previous one when the selection changes.

to

update previous one on button click.
0
 
Jacques Bourgeois (James Burger)Commented:
Without your more precise explanation, I see 2 ways to do the thing.

The first is simply to ask the user if he want to save the previous changes when he makes a new selection:
If yourDataTable.GetChanges IsNot Nothing Then
   If MessageBox.Show("Do you want to save the changes made to the previous selection?")=DialogResultYes Then
      'Call your Update method
   End If
End If
'Refresh the grid with the new selection

Open in new window


The other one is an amalgam of both approaches.

- Fill the DataTable with the first selection and display the data.

- When the user makes a new selection, simply merge the result of the first. There is a Merge method on the DataTable with an overload that enables you to specify that you want to keep the changes when merging.

- Apply a DataView with the new selection on the merged DataTable and display the DataView.

Depending on the number of rows added on each selection and the number of selections, you can end up with something that is manageable. You might want to put a check on the number or rows and inform the user when it becomes too big and offer them to Update before their computer blows out. Then restart the procedure with a cleared DataTable.

Personnally, if possible, I would prefer the first method, where you Update on each selection. In a multi-user environment, the less time you have between updates, the less chances you have of getting concurrency exceptions. But if the users make frequent changes to the selection, they could find the messages annoying, and then the DataView option might be better.
0
 
IssacJonesAuthor Commented:
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
0
 
Jacques Bourgeois (James Burger)Commented:
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(newSelection, True)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now