SQLCE - Upsert (Update or Insert) - How to prepare a row using common method?

Question

Following is the pseudo-code:

SqlCeResultSet myResultSet = cmd.ExecuteResultSet(Options...etc);
bool found = myResultSet.Seek();
if found {
    //do an Update
    myResultSet.Read() //make current

    //At this point we have a cursor positioned at a row to be edited
    myResultSet.SetString(1, "value for col 1");
    myResultSet.SetString(2, "value for col 2");
    //... etc... 
    myResultSet.SetString(100, "value for col 100");
    //i want to replace above with: CommonMethodToFillRowData(someRow)

    //finally update
    myResultSet.Update();

} else {
    //do an insert
    SqlCeUpdatableRecord myRec = myResultSet.CreateRecord();
    //set primaryKey
    myRec.SetInt32(0, pkValue);

    //At this point we have a cursor positioned at a row to be edited   
    myRec.SetString(1, "value for col 1");
    myRec.SetString(2, "value for col 2");
    //... etc...
    myRec.SetString(100, "value for col 100");
    //i want to replace above with: CommonMethodToFillRowData(someRow)

    //finally insert
    myResultSet.Insert(myRec);
}

From the above, if i have 100 columns to prepare, it has to be repeated twice; What i want is some CommonMethodToFillRowData(); But what Type of parameter do i use for such a method?

CommonMethodToFillRowData(SqlCeResultSet or SqlCeUpdatableRecord ? parmRow) {
    parmRow.SetInt32(col1, value1)
    parmRow.SetString(col2, value2)
    ...etc.
    parmRow.SetString(100, "value for col 100");
}

Directly quoting from MSDN doco on SqlCeUpdatableRecord Class: --> Represents a row of updatable values from the data source. A SqlCeResultSet object contains one or more UpdatableRecords.

If that is the case, why can't i have direct access to a single UpdatableRecord inside SqlCeResultSet, once i position the cursor via a Seek() ?

If that were possible, that would enable me to use:

CommonMethodToFillRowData(SqlCeUpdatableRecord parmRow) {
   //end of story
}

Answer 1

Create a wrapper object that can represent either SqlCeResultSet or SqlCeUpdatableRecord as appropriate. Then write your "save" code once and have it applied as either an update or insert, depending on if the record was found to already exist.

Warning: This is code untested.

public void SavingMyData()
{
    SqlCeResultSet resultSet = cmd.ExecuteResultSet(Options...etc);
    SqlCeWrapper wrapper = new SqlCeWrapper(resultSet);

    wrapper.SetInt32(0, pkValue, true); // Primary Key = true
    wrapper.SetString(1, "value for col 1");
    wrapper.SetString(2, "value for col 2");
    wrapper.SetString(100, "value for col 100");
    wrapper.Commit();
}

...

public class SqlCeWrapper
{
    private readonly bool _found;
    private readonly SqlCeResultSet _resultSet;
    private readonly SqlCeUpdatableRecord _newRecord;

    public SqlCeWrapper(SqlCeResultSet resultSet)
    {
        _resultSet = resultSet;

        _found = resultSet.Seek();
        if (_found)
            resultSet.Read();
        else
            _newRecord = resultSet.CreateRecord();
    }

    public void SetInt32(int ordinal, int value, bool isPrimary = false)
    {
        if (_found && !isPrimary)
            _resultSet.SetInt32(ordinal, value);
        else if (!_found)
            _newRecord.SetInt32(ordinal, value);
    }

    public void SetString(int ordinal, string value, bool isPrimary = false)
    {
        if (_found && !isPrimary)
             _resultSet.SetString(ordinal, value);
        else if (!_found)
            _newRecord.SetString(ordinal, value);
    }

    public void Commit()
    {
        if (_found)
            _resultSet.Update();
        else
            _resultSet.Insert(_newRecord);
    }
}

Note: If you aren't using .NET 4, you'll have to remove the optional parameters. You may also add additional SetX() methods to SqlCeWrapper according to your needs.