I am developing an application that will be able to be used offline from the
SQL server and am trying to come up with a good way to do this. My thought at
this moment is to basically work "offline" all the time and sync on a regular
basis when connected.
To do this, I have written several procedures to load and save data from the
database and an XML file. However, when I load the informatiom from the XML
file and then try to save it to the database, it's acting as if every record
is a new record instead of updating existing records, and since the records
have the same primary key value as records already in the database, the
program is throwing an exception, as it should. What I want it to do is to
know that the records are existing records and that they should update the
database, not add to it (unless it IS a new record, in which case I'd want it
to add - but I haven't gotten there yet)
A condensed version of my code is below (this is enough to cause the
problem). I'm testing in a console app.
Any ideas? I've had a little luck by sending ds.AcceptChanges after I load
the dataset originally from the XML. But once I do that more than once, I
lose the first set of changes that I made to the dataset. I'm thinking I may
be into something with DiffGrams, but am not sure where to get some info on
that.
-------------------
dim ds as Database
Sub Main
'Load from the database, save to an XML file
ds=new dataset
LoadFromDatabase
SaveToXML
'Reset the dataset, and then load from the XML, and update the database
ds=new dataset
LoadFromXML
SaveToDATABASE
End Sub
Sub LoadFromDatabase
ds.tables.add("companies")
dim cn as new sqlconnection("data source=...")
dim cmd as new sqlcommand("select * from companies")
dim da as new sqldataadaptor(cmd)
da.fill(ds.tables("companies")
End Sub
Sub SaveToXML
ds.WriteXML("c:\data.xml")
End Sub
Sub LoadFromXML
ds.ReadXML("c:\data.xml")
End Sub
Sub SaveToDatabase
dim dt as datatable=ds.tables("companies")
dim cn as new sqlconnection("data source=...")
dim cmd as new sqlcommand9'select * from companies")
dim da as new sqldataadaptor(cmd)
dim cb as new sqlcommandbuilder(da)
da.update(dt)
End Sub