Doing some mobile application development (win CE) I was recently faced with a task, that immediately would seem trivial. Come on – how hard can it be to insert more than one row in a database? Well – when the database you are dealing with is a SQL Compact Edition (v. 3.5); you are in for a surprise (or at least I was ;-)).
The default access to the database is a table adapter, which is a very downscaled version compared to the Entity Framework you are used to and take for granted by now. The table adapter unfortunately only allows for single row inserts; which means you are to call this multiple times by default. That does not work well, if you are having more than say 10 rows you want inserted! I was attempting this model at first with approx. 47.000 rows and ended up with a duration of 2.5 hours! So – what do do?
Well – an alternative route exists that brought the duration dramatically down (2.5h –> 6 sec!) for the same number of rows.
By using the below code, you are utilizing a direct access to the SQL CE table and gain a significant performance boost compared to the default table adapter. Do note that when using this route, you do no longer have the luxury of fieldnames, but are limited to using their ordinal places in the table. Note that the ordinal value is the same value you will find in a create table statement for the table you are targeting (in the below example: ‘OnlineProducts’).
Note also in the below code, that the same SqlCeUpdatableRecord (variable: ‘rec’) is used over and over again in the foreach loop. This means no memory optimization is performed by the CLR, which is intentional; before the method is exited.
/// <summary>Update: You might want to initialize the fields of the record before doing the inserts. Otherwise you end up "inheriting" the old values from last iteration :-)
/// Inserts/updates rows.
/// <param name="newRows">The new rows.</param>
public void InsertRows(ProductDataSet.OnlineProductRow newRows)
//insert using direct sql
var cnn = new SqlCeConnection(GetSDFConnection());
var cmd = cnn.CreateCommand();
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = "OnlineProducts"; //tablename!
var rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable);
var rec = rs.CreateRecord();
//update records (use same row)
foreach (var row in newRows)
rec.SetString(5, row.ProductNameLong ?? "");
rec.SetString(7, row.AlternativeProduct ?? string.Empty);
catch (Exception ex)