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.

Answer 2

How about using the enumerator on the SqlCeResultSet instead of the Read method like this

IEnumerator enumerator = myResultSet.GetEnumerator();
bool found = enumerator.MoveNext();
SqlCeUpdatableRecord record;
if (found) {
    record = (SqlCeUpdatableRecord)enumerator.Current;
    MethodToFill(record);
    myResultSet.Update();
} else {
    record = myResultSet.CreateRecord();
    MethodToFill(record);
    myResultSet.Insert(record);
}

private void MethodToFill(SqlCeUpdatableRecord recordToFill) {
    recordToFill.SetString(0, "Hello");
    recordToFill.SetString(1, "World");
    // etc
}

Answer 3

I'm not sure what you're trying to do, and am not sure about those components specifically, so there's probably a more efficient approach altogether. But according to MSDN, SqlCeResultSet is a SqlCeDataReader, which is a DbDataReader, which implements IDataRecord... and SqlCeUpdateableRecord also implements IDataRecord.

So, does this work?

public static void DoStuff()
{
    SqlCeCommand cmd = new SqlCeCommand();
    SqlCeResultSet myResultSet = cmd.ExecuteResultSet(ResultSetOptions.None);
    var reader = myResultSet.Read();

    bool found = myResultSet.Seek(DbSeekOptions.After);
    if (found)
    {
        myResultSet.Read();
        CommonMethodToFillRowData(myResultSet);
        myResultSet.Update();
    }
    else 
    {
        SqlCeUpdatableRecord myRec = myResultSet.CreateRecord();
        CommonMethodToFillRowData(myRec);
        myResultSet.Insert(myRec);
    }
}

// All the messy Type-wrangling is hidden behind the scenes
public static void CommonMethodToFillRowData(this IDataRecord RowToFill)
{
    RowToFill.SetInt32(1, 42);
    RowToFill.SetString(2, "Foo");
    // etc...
}

// Since SetInt32 seems to do the same thing in either inherited Type
public static void SetInt32(this IDataRecord RowToFill, int Ordinal, int Value)
{
    Type rowType = RowToFill.GetType();
    if (rowType == typeof(SqlCeResultSet))
        ((SqlCeResultSet)RowToFill).SetInt32(Ordinal, Value);
    else if (rowType == typeof(SqlCeUpdatableRecord))
        ((SqlCeUpdatableRecord)RowToFill).SetInt32(Ordinal, Value);
    else
        throw new ArgumentException("Method does not know what to do with Type " + rowType.ToString());
}

// Since SetString seems to do the same thing in either inherited Type
public static void SetString(this IDataRecord RowToFill, int Ordinal, string Value)
{
    Type rowType = RowToFill.GetType();
    if (rowType == typeof(SqlCeResultSet))
        ((SqlCeResultSet)RowToFill).SetString(Ordinal, Value);
    else if (rowType == typeof(SqlCeUpdatableRecord))
        ((SqlCeUpdatableRecord)RowToFill).SetString(Ordinal, Value);
    else
        throw new ArgumentException("Method does not know what to do with Type " + rowType.ToString());
}

It's nasty and the performance won't be great, but it might do what you're looking for if all you want to do is collapse the row filling into one section of code.

Answer 4

How about:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Collections;
using System.Data;

namespace SqlCeRecord_Test
{
    class Program
    {
        static void Main(string[] args)
        {
            // Arguments for update
            int lookFor = 1;
            string value = "AC/DC";

            // Arguments for insert
            lookFor = Int16.MaxValue;
            value = "joedotnet";

            using (SqlCeConnection conn = new SqlCeConnection(@"Data Source=C:\Users\xeej\Downloads\ChinookPart2\Chinook.sdf"))
            {
                conn.Open();

                using (SqlCeCommand cmd = new SqlCeCommand("Artist"))
                {
                    SqlCeUpdatableRecord myRec = null;
                    cmd.Connection = conn;
                    cmd.CommandType = System.Data.CommandType.TableDirect;
                    cmd.IndexName = "PK_Artist";
                    SqlCeResultSet myResultSet = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
                    bool found = myResultSet.Seek(DbSeekOptions.FirstEqual, new object[] { lookFor });

                    if (found)
                    {
                        myResultSet.Read();
                    }
                    else
                    {
                        myRec = myResultSet.CreateRecord();
                    }
                    foreach (KeyValuePair<int, object> item in CommonMethodToFillRowData(value))
                    {
                        if (found)
                        {
                            myResultSet.SetValue(item.Key, item.Value);
                        }
                        else
                        {
                            myRec.SetValue(item.Key, item.Value);
                        }
                    }
                    if (found)
                    {
                        myResultSet.Update();
                    }
                    else
                    {
                        myResultSet.Insert(myRec);
                    }
                }
            }
        }

        private static Dictionary<int, object> CommonMethodToFillRowData(string value1) //TODO add more values
        {
            var dict = new Dictionary<int, object>();
            dict.Add(1, value1);
            return dict;
        } 
    }
}