Update database using DataAdapter for DataSet populated from XMLfile?

[NorCan]

Hi,

I have an XML file with data which I am reading into a dataset. I want
to make changes to a table in my database based on values from the
dataset. These changes can include updates, inserts and deletes.

I would like to use a DataAdapter (odbc) to do the update.

However, my dataset was not filled using the DataAdapter... how can I
make use of the DataAdapter to update the datasource in this scenario?


Regards,
Frode

 

[W.G. Ryan MVP]

If the dataset is serialized with the diffgram, then the rowstate of each
row will be in tact and provided that's how you want it in the end database,
then you can just configure an adapter an call Update passing in the
dataset. If the state is different, then just loop through row by row and
update each row manually (which is all the adapter does under the hood).

HTH,

Bill

 

[NorCan]

Thank you for your quick response.

There is no DiffGram involved here. The XML contains information on what
action to take on each row of the dataset. For instance the XML might
contain nodes like these:

<RULE><UPDATE><RULE_ID value='1015'/><RULE_NAME value='w'/><RULE_SHNAME
value='w'/></UPDATE></RULE>
<RULE><INSERT><RULE_ID value='1015'/><RULE_NAME value='w'/></INSERT></RULE>
<COMPANY><DELETE><COMPANY_ID value='1514'/><COMPANY_NR
value='MwPeqY'/></DELETE></COMPANY>

....where the first tag indicates the table ("rule", "company" etc), the
second specifies the action to perform on the database for that row
(insert, update or delete).

These nodes are collected into a dataset, the rows are manipulated to
put them in the correct RowState for their individual operations, then
the database tables are updated one by one. This is where the problem
arises.

At the moment only database inserts are successful. I have tried several
different appoaches to get updates and deletes to work, so many in fact,
that I'm starting to lose control of what I've tried and why they didn't
work.

So as you can imagine, I'm starting to get desperate for ideas here.


Regards,
Frode

 

[W.G. Ryan MVP]

If you don't have the diffgram, just use XPath/XQuery to determine the
values (actually, use whatever you want to read that node) and then
depending on what it is, fire the respective command (which you'll need to
set up for each possible outcome) and use ExecuteNonQuery.