Why should we bypass DataSet when updating DB?

Question

hi

1)

a) Why is it preferable to bypass DataSet when updating, deleting or inserting records ( I realize using DataSet takes more CPU time and memory, but are there any other reasons besides that?! )?

b) Are direct updates faster than batch updates? Why?

c) Why do Web services represent only real web application scenario in which it would make sense to perform batch updating through a DataSet?

2) We can use UpdateCommand to insert,delete or update rows in a database, so what is the point of DataAdapter having InsertCommand,UpdateCommand and DeleteCommand objects? Why not just have one Command object instead of three ( or even four, since UpdateCommand could also be used for SELECT statements )?

thank you

Answer 1

1)

a) To use different locking semantics. DataSet relies on optimistic concurrency (expects data wouldn't change until update). There is a performance penalty if data changes on server side which may cause additional roundtrips. It's not ideal for frequently changing data.

b) I can't think of a case where one by one update would be faster than batch update. The only difference is there is more time between lock operations in a direct update leaves more unlocked "gap"s in time. That's not a real advantage though. Normally you should strive to be done with the server as quick as possible.

2) InsertCommand is called for "Added" data while Update command is called for "Changed" data. It seems I don't get this part of the question.

Answer 2

1) I disagree with the whole assumptions made for the question. Also note that b) is plain wrong.

2) because you want different queries to be executed for the different actions, and when you have a dataset loaded with changes you just want to do a simple call that will do all the insert,delete and udpates.