SQLCE - Upsert (update or insert) - How to prepare a string using a common method?

Question

Below 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 needs to be repeated twice; What I want is CommonMethodToFillRowData (); But what type of parameter am I using 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"); } 

Direct quoting from the MSDN doco in the SqlCeUpdatableRecord class: -> Represents a row of updated values ​​from a data source. The SqlCeResultSet object contains one or more UpdatableRecords.

If so, why can’t I have direct access to one updated registry inside SqlCeResultSet, as soon as I put the cursor through Seek ()?

If it were possible, it would allow me to use:

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

Answer 1

Create a wrapper object that can represent both SqlCeResultSet and SqlCeUpdatableRecord . Then write your “save” code once and apply it as an update or insert, depending on whether the found entry already exists.

Warning: This code has not been verified.

 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 are not using .NET 4, you will have to remove the optional parameters. You can also add additional SetX() methods to SqlCeWrapper to suit your needs.

Answer 2

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

Answer 3

How about using an enumerator in an SqlCeResultSet instead of a 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 4

I'm not sure what you are trying to do, and I do not know exactly about these components, so there may be a more efficient approach. But according to MSDN, SqlCeResultSet is a SqlCeDataReader , which is a DbDataReader that 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()); } 

This is unpleasant, and the performance will not be great, but it can do what you are looking for if all you want to do is collapse the string filling into one section of code.