collections directly to database

[jac]

Hey,
Newbie in using ADO.NET.

What I do now : I read the database with an OleDbDataReader. For each row I read I make an object (let say Person + that object contains a collection with the addresses etc.). All the objects I put in a collection called ‘Persons’.
Then I work on that collection (delete, update, add). Each object in the collection contains a state (Add, Update or deleted state).

At the end I want to go back to the database.
What is a best practice to do this?
- Do I pump my collection ‘Persons’ in a dataset, and for each row I set the datarowstate (= state of each object in my collection). I don’t know if this is possible. And then I do dataset.Update and Dataset.Acceptchanges?? Is that not too much (collection to dataset, dataset to database)??
- I cannot use the SQLCommandBuilder because I need joins. What do I have to use if I will update the database???
Thanks in advance.

[Mohamoss]

Hi
A best practice is to use dataset form the outset. The dataset object
record all the changes that is done on a each row on any of it table ( this
is why it keep many stats for the row ) . when you are done you can use the
update method of the data adaptor passing to it as argument your connection
and your dataset . this update method will write all the updates ( that
is in the data set ) back to your database .
The way this is done , as the dataset keep the states of the rows , it
generate the appropriate SQL that would get it from the original state (
when it was first filled ) to the current state ( the state of the dataset
now ) . the good part is that you don't need to worry about all that
however.
One thing I need to mention is that you need to call the update method
on the dataAdaptor BEFORE CALLING ACCEPT CHANGES ON THE DATASET ( as
accept changes delete all info of past states on the dataset ) this method
make the data set throw all past changes and consider the current state as
the original state . so you should call this method only after you did the
update so that the dataset would consider it self as a fresh new one .
I hope this was clear enough , but I think you still need to read a bit
about these methods
Mohamed Mahfouz
MEA Developer Support Center
ITworx on behalf of Microsoft EMEA GTSC