Update/Insert to a table using SQLCeResultSet

Question

I have a SQL Compact Edition Database that I update periodically (via web services).

The part where I write to the database is taking way too long. I am currently doing it with Linq to Datasets (as seen in this question). I have heard that if I do it with with SQLCeResultSet that it will work faster.

So, given that I have a table like this:

tblClient
   +- CLIENT_ID      {Unique identifier} (Primary Key)
   +- CLIENT_NAME    {varchar (100)}
   +- CLIENT_ACTIVE  {bit}

And I have it in object that I get from my web services that look like this:

class Client
{
   public Guid ClientID { get; set; }
   public String ClientName { get; set; }
   public bool Active { get; set; }
}

How would I get 100 Client objects into the database?

Updating existing rows and inserting rows that are not already in the database (determined by the primary key)?

Any example code would be great. I have an SqlCeConnection, but nothing else.

Thanks for any help!

Answer 1

It's going to look something like this:

(Edited for insert or update)

void Foo(SqlCeConnection connection)
{
    using (var cmd = new SqlCeCommand())
    {
        cmd.CommandType = CommandType.TableDirect;
        cmd.CommandText = "MyTableName";
        cmd.Connection = connection;
        cmd.IndexName = "PrimakryKeyIndexName";

        using (var result = cmd.ExecuteResultSet(
                            ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
        {
            int pkValue = 100; // set this, obviously

            if (result.Seek(DbSeekOptions.FirstEqual, pkValue))
            {
                // row exists, need to update
                result.Read();

                // set values
                result.SetInt32(0, 1);
                // etc. 

                result.Update();
            }
            else
            {
                // row doesn't exist, insert
                var record = result.CreateRecord();

                // set values
                record.SetInt32(0, 1);
                // etc. 

                result.Insert(record);
            }
        }
    }
}