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!

Ebbie

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);
            }
        }
    }
} 

Assailant

I notice that there is no call to Update. Does Insert do an update if the row already exists? – Ebbie

ResultSetOptions should be set to Updatable. My understanding is Update() only applies to existing records, not inserts. – Distill

Sorry, this was for inserts only. You'll have to modify the logic if you want to do replacements. I'd do a Seek on the PK field, then do a Read, if the read returns true, then modify the record and update, otherwise create and insert. – Assailant

@Assailant - Do I have to load in all the records first to do that? Is that going to slow things down again? Will the insert give me some sort of failure indicator because the primary key is already there? Maybe I can use that rather than having to read in all the records just to see if my record is there. – Ebbie

I'm confused. Load all of what records? As I said, just seek on the PK to see if it's there and insert if not. – Assailant

No, it is me that is confused. But I think I understand now. I thought I had to select into memory all of the records to be able to see if the one I was looking for was there. I am seeing now that SQLCeResultSet does not work that way. Thanks for the updated example. I will try it out. – Ebbie

That worked great! So much faster! I had to make one modification to the update stuff. After the seek I had to do a read or the update failed ("No data exists for the row/column."). Once I did that though it all worked. (I updated your example to show the read, if you disagree, feel free to remove it.) – Ebbie

It was from memory, so I'm a little surprised that's all that needed changing. – Assailant