Solved

VB.NET Datatable and Linq

Posted on 2009-07-13
10
494 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
  • 5
  • 4
10 Comments
 
LVL 1

Accepted Solution

by:
PeterDimitrov earned 250 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 250 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now