So today I had an idea,  could SQL Compact be used to record data in pretty much realtime?    So I needed to look at raw insert performance and the best way to really make SQL Compact cook.

So this baby using the SqlCeCommand.Prepare method shoots through multiple record inserts.    The Prepare method tells the SQL Compact to expect more sql parameters and backs off trying to interpret the query (this is over simplifying the process).

Putting things in a SQL transaction also gives us the ability to roll everything back if we have problem.    I think the answer to our realtime recording is pretty much a YES.

* Note, I pass in a 2 dimensional array of SQLCE parameters each representing a row of multiple columns.

public object BulkInsert(SqlCeParameter[,] ourparams)
{
object ret=null;
if (ourparams.GetLength(0) == 0) return ret;
StringBuilder sbsql = new StringBuilder();
sbsql.Append("INSERT INTO ");
sbsql.Append(tablename+" ");
sbsql.Append("(");
sbsql.Append(" [COLORCODE] ");
sbsql.Append(",[COMMENTS] ");
sbsql.Append(") ");
sbsql.Append("VALUES ");
sbsql.Append("(");
sbsql.Append(" @colorcode "); // Maps To Database Field - [COLORCODE]
sbsql.Append(",@comments "); // Maps To Database Field - [COMMENTS]
sbsql.Append(")");
bool isopen = (Cn!=null && Cn.State == ConnectionState.Open);
SqlCeCommand cmd = null;
SqlCeTransaction trans = null;
bool allok = true;
try
{
if (!isopen) this.Open();
trans = Cn.BeginTransaction();
cmd = new SqlCeCommand(sbsql.ToString(),this.Cn,trans);
}
catch
{
if (cmd!=null) cmd.Dispose();
if (trans != null) trans.Dispose();
if (!isopen) Close();
showexception("Sql - Could Not Open Db Connection", sbsql.ToString());
return null;
}
for (int intcount = 0; intcount < ourparams.GetLength(1); intcount++)
cmd.Parameters.Add(ourparams[0, intcount]);
// see if we are really inserting multiple records
if (ourparams.GetLength(0)>1)
{
try
{
cmd.Prepare();
}
catch
{
if (cmd!=null) cmd.Dispose();
if (trans != null) trans.Dispose();
if (!isopen) Close();
showexception("Sql - Could Not Prepare Parameters", sbsql.ToString());
return ret;
}
for (int rowcount = 0; rowcount < ourparams.GetLength(0); rowcount++)
{
if (rowcount > 0) // first row has already been setup
for (int intcount = 0; intcount < ourparams.GetLength(1); intcount++)
cmd.Parameters[intcount].Value = ourparams[rowcount, intcount].Value;
try
{
cmd.ExecuteNonQuery();
}
catch (SqlCeException e)
{
allok = false;
trans.Rollback();
showerrors(e, cmd.CommandText);
}
}
}
else
{
try
{
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT @@IDENTITY"
ret = CoreDb.DbToString(cmd.ExecuteScalar());
}
catch (SqlCeException e)
{
allok = false;
trans.Rollback();
showerrors(e, cmd.CommandText);
}
}
if (allok)
if (trans != null) trans.Commit();
if (cmd!=null) cmd.Dispose();
if (trans!=null) trans.Dispose();
if (!isopen) this.Close();
return ret;
}