Hi Alina:
<Anwers inline>
--
W.G. Ryan MVP Windows - Embedded
http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
Alina said:
Hello
Supposing that I have a big Sql Server DB (Win XP) with each table about
1000 records. Each record has a flag which will indicate if that record was
inserted, updated or deleted since the last synch. The same DB is running in
a Sql Server CE and I wish to do a synch of the Win CE DB from the Win XP
DB.
Which is the fasted way to do all the operations (that means to parse all
the records and if the flag is "inserted" to perform an insert into the Sql
CE DB, if the flag is "updated" to perform an update into the Sql CE DB
(from the PC DB) and if the flag is "deleted" to prerform a delete from the
Sql CE DB) ?
If you are interested in buying a 3rd party tool, Fitiri's SSCE Direct is a
great choice
http://www.devbuzz.com/content/zinc_fitiri_sscedirect_pg1.asp
They are featured at a site that I'm involved with, but I assure you that
the above is my objective opinion.
However, assuming you want to do this wihtout such a tool. first off, you
DataTable itself has such a flag, so having your own may just convolute the
process (but you probably have your reasons and I don't want to appear
critical, especially when i haven't seen the code).
Anyway, if you cause the rowstate to the set to added, and you have a
properly configured dataAdapter and you call update, it will loop through
the rows, and when it sees that the rowstate is added, it will automatically
invoke the INSERT command and map the values of each item in the record to
the respective parameter. If it sees the rowstate marked as Deleted, it
will invoke the DELETE command when you call Update. Modified calls the
UPDATE Command. All of this presupposes that you have a properly configured
dataadapter and/or commandbuilder in place.
Now, if you want to take the data from the db, you can call fill on a
DataSet, then use its .WriteXML(@"Path:\Filename.xml"); method. This can be
transferred at Sync (or you can use RAPI or whatever other method you want)
and then once it's on the device, you can use
DataSet ds = ds.ReadXML(@"\Storage Card\file.xml"); //I'm just assuming the
path but it could be wherever you put it.
Now, assuming that you wrote the XML with the diffgram option, you have the
rowstate so you can jsut call the Update method on the next line and again,
if your adapter was configured correctly, everything will find it's place to
the table.
You can use your own flag, but what you'll need to do is use a switch
statement for instance, and switch on your flag If it's Added, set the
command's commandtext to your own Insert command , set the parameters and
then call ExecuteNonQuery (of course you need to ensure your connection is
open and all the basic stuff). Do the same for each of the other flag
values. Since you already have this in Rowstate, it seems overkill but if
you have a reason for not using rowstate, then so be it.
Another thing you can do is set the .AcceptChangesDuringFill option of your
Server side app's DataAdapter to false. This will cause all of the rows to
be marked as Added. You can transfer the file like I mention above (or if
you have network coneectivity, you can invoke the SQL server directly) and
then just call update locally on the pda. This will insert all of the rows
in the dataset into your CE table . Here's a walk through
http://www.knowdotnet.com/articles/datasetmerge.html
Anyway, the bottom line is that you can use DataAdapters or you can roll
your own logic. If your Flag values don't correspond to Rowstate (and or
rowstate isn't accurate) then you'll need to roll your own. If you do this,
loop through everything and just call ExecuteNonQuery at each pass.
Let me know if you have any questions.
Cheers,
Bill
