Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VB.NET Datatable and Linq

Posted on 2009-07-13
10
Medium Priority
?
502 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:jimseiwert
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 1

Accepted Solution

by:
PeterDimitrov earned 1000 total points
ID: 24845751
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
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 1000 total points
ID: 24848268
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
 
LVL 2

Author Closing Comment

by:jimseiwert
ID: 31603099
Thank you both for your advice.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:PeterDimitrov
ID: 24850147
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24850462
>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
 
LVL 1

Expert Comment

by:PeterDimitrov
ID: 24851449
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24857490
>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
 
LVL 1

Expert Comment

by:PeterDimitrov
ID: 24859520
>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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24860001
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
 
LVL 1

Expert Comment

by:PeterDimitrov
ID: 24861368
jimseiwert,  if you are still interested from the discussion , what database are you using ?
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question