How to have ADO.NET consume persisted XML ADO recordset for updates

Question

I am working on a .NET 2.0 conversion of a multi-layer client-server application. For the time-being, we're converting the server-side to .NET but leaving client apps in COM (VB6). My current work is focused on getting data to/from the detached COM based clients.
The current version under development is using ADO(COM) recordsets persisted as XML and sent to the clients. Clients then load up the XML in to a recordset object for read/write access to data. Data is sent back to the server in the same format.
Upon receipt of a persisted XML recordset for update, we do some rather kludgy parsing to produce UPDATE statements which get pushed through ADO.NET to update the source database accordingly.

In (very rough) pseudo-code:

adodb.recordset oRS = load(ADOXML)
for each rec in oRS
{
  string sSQL = "Update table set value = " + rec.ValueField + " where key = " + rec.KeyField
  executeNonQuery(sSQL)
}

*DISCLAIMER: The actual logic is nowhere near this crude, but the end-result is the same.

I'm wondering if anyone has a better solution to get the updated data back in to the database...?

Thanks,

Dan

Answer 1

Have you considered using a TableAdapter to perform an update on the data once you have set all rows to modified?

If you have a preconfigured SQLTableAdapter which has update functions built in then by reading in the table from XML into the dataset you can then call the update function from the table adapter to update the central DB with every row in the table.

var tableadapter = new MyTableAdapter();

var dataset = new MyDataSet();

dataset.ReadXml("c:\myxmldata.xml");

foreach(var row in dataset.tables[0].rows)
{
   row.SetModified();
}

tableadapter.Update(dataset);

Answer 2

Hi

Have you tried a set based update?

Regards