?
Solved

VB.NET Datatable and Linq

Posted on 2009-07-13
10
Medium Priority
?
501 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

764 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