VB.NET Datatable and Linq

I have a datatable that I bing to a datagridview. From here the users add, delete and update records. What is the best way to update all the changes back to the database?

Do I have to do it one at a time when the action is preformed or can I do all the updates at the end when they are done with those records?

Thanks In Advance
LVL 2
jimseiwertAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PeterDimitrovCommented:
If you are planning to use dataset update method the update will be executed per row so the number of the updates will be the same as if you update each record separately  (i.e if you have 10 records that has to be updated 10 commands will be send to the database). The cost of each update will be awlays the same and immediate update will work a little better since you will lower thepossibility another user to load data that was changed but notupdated. With dataset update , update of all records together makes sence just in case if you want to use transactions.

If you expect  to have a lot of changes you can consider to avoid dataset update  and to write additional code to collect all records with changes, prepare XML  and update the database in one pass. In that case update at the end will work better.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CodeCruiserCommented:
It is much easier to use the DataAdapter's Update method to propogate all the changes made in dataset back to the database. It should be fine because if the number of records is huge then you probably should not be caching it in memory anyway.
0
jimseiwertAuthor Commented:
Thank you both for your advice.
0
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

PeterDimitrovCommented:
It doesn't matter if the data is updated after one record was changed or at the end when all records were changed. Dataadapter update (in my previous post I am using "dataset update" which is wrong) will send the same number of commands to the database.

case 1:
 change record1
 update:  dataadapter sends command:spUpdateRecord(X)  - or whatever is your insert, update delete command
 change record2
 update : dataadapter sends command : spUpdateRecord(Y)

case 2:
  change record1
  change record2
  update: dataadapter sends command: spUpdateRecord(X)
                 dataadapter sends command: spUpdateRecord(Y)


You can minimize the risk one user to overwrite the changes from another if you update the database more often. So I believe it will be better if you force the update after each row was changed.

In case if the user deletes for example 20 records the dataadapter will send the delete command to the database 20 times which is pretty expensive. You can concider to collect all IDs for deleted records and to send them to the database in one pass. In this case you will need to handle the update by yourself without using dataadapter and obviously will be better to update all the records together.
0
CodeCruiserCommented:
>Dataadapter update (in my previous post I am using "dataset update" which is wrong) will send the same number of commands to the database.
NO. DataAdapter uses the Row.RowState property to determine if it needs to execute any command(delete, insert, update) for that row. If it does not need to, it does not send any command for that row to the database.
0
PeterDimitrovCommented:
Of course. What is not clear ? If one row was changed - 1 command will be send to the database if 2 rows were changed 2 commands will be send  ... see the example. DataAdapter will not optimize the update to combine the updates for 2 rows in one command so the cost of the update will be the same if the user forces the update every time when a record was changed compared to the update after all records were changed.

Read the question again :
Do I have to do it one at a time when the action is preformed or can I do all the updates at the end when they are done with those records?
The answer is - it doesn't matter - the cost is the same.  What is your answer?  I can not see it .

Besides , DataAdapter is easy to use but is not good solution for multiuser environment because the cost of the update per record is high and because it is not preventing one user to overwrite changes from another one.

Again the question was when to update the data not how to update it.
0
CodeCruiserCommented:
>1 command will be send to the database if 2 rows were changed 2 commands will be send
What is the alternative then? I have never came across any command in SQL which would update multiple records in one statement. I would be grateful if you could add to my knowledge.
0
PeterDimitrovCommented:
>What is the alternative then?<
For me the alternative is to collect the data from the rows with changes and to send it as XML in one pass to the stored procedure where it will be restored back to a table variable and processed accordingly. Obviously it is more work, but will avoid update using dataadapter, which is practically a cursor initiated from the client machine. SQLServer2005 and above supports XML , in SQLServer 2000 will be necessary to use sp_xml_preparedocument /  OPENXML ()  / sp_xml_removedocument ( don't know how to deal with other databases)
0
CodeCruiserCommented:
Some code samples from you about this method would have given the jimseiwert more than one options to choose from. May be if you can still provide the complete guide on how to do it may help him now. But i dont see a lot of benefit.
0
PeterDimitrovCommented:
jimseiwert,  if you are still interested from the discussion , what database are you using ?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.