Slow Update/insert into SQL Server CE using LinqToDatasets

Question

I have a mobile app that is using LinqToDatasets to update/insert into a SQL Server CE 3.5 File.

My Code looks like this:

// All the MyClass Updates
MyTableAdapter myTableAdapter = new MyTableAdapter();

foreach (MyClassToInsert myClass in updates.MyClassChanges)
{
    // Update the row if it is already there
    int result = myTableAdapter.Update(myClass.FirstColumn, 
                                       myClass.SecondColumn, 
                                       myClass.FirstColumn);
    // If the row was not there then insert it.
    if (result == 0)
    {
        myTableAdapter.Insert(myClass.FirstColumn, myClass.SecondColumn);
    }
}

This code is used to keep the hand held database in sync with the server database. Problem is if it is a full update (first time for example) there are a lot of updates (about 125). That makes this code (and more loops like it take a very long time (I have three such loops that take over 30 seconds each).

Is there a faster or better way to do updates/inserts like this?

(I did see this Codeplex Project, but I could not see how to make it work with both updates and inserts.)

Answer 1

You should always use SqlCeResultSet for data access on mobile devices for maximum performance and memory usage. You must identify the data to be inserted and then use code like the SqlCeBulkCopy sample, and use similar code by using the Seek and Update methods of the SqlCeResultSet.