I've come up with a solution that allowed me an automated approach, yet helped improve performance at the same time... Hope the technique can help others too. My issue was when using System.Data (via SqlCE, but applicable for other database backends too). Each time I tried to create the SQL command object to perform insert, update, or whatever, and add the "parameters" to the sql object, get proper data types, etc was killing performance. So, I did this for the Insert/Update. On my data manager class (one per table I work with), I added to objects of IDbCommand objects, one for Insert/Update respectively. During the constructor, I would pre-query the table to get a structure of the final row object and pre-build the query and parameters (skipping over the primary key ID) something like...
private void BuildDefaultSQLInsert()
{
// get instance to the object ONCE up front
// This is a private property on the data manager class of IDbCommand type
oSQLInsert = GetSQLCommand("");
// pre-build respective insert statement and parameters ONCE.
// This way, when actually called, the object and their expected
// parameter objects already in place. We just need to update
// the "Value" inside the parameter
String SQLCommand = "INSERT INTO MySQLTable ( ";
String InsertValues = "";
// Now, build a string of the "insert" values to be paired, so
// add appropriate columns to the string, and IMMEDIATELY add their
// respective "Value" as a parameter
DataTable MyTable = GetFromSQL( "Select * from MySQLTable where MyIDColumn = -1" );
foreach (DataColumn oCol in MyTable.Columns)
{
// only add columns that ARE NOT The primary ID column
if (!(oCol.ColumnName.ToUpper() == "MYIDCOLUMN" ))
{
// add all other columns comma seperated...
SQLCommand += oCol.ColumnName + ",";
InsertValues += "?,";
// Ensure a place-holder for the parameters so they stay in synch
// with the string. My AddDbParm() function would create the DbParameter
// by the given column name and default value as previously detected
// based on String, Int, DateTime, etc...
oSQLInsert.Parameters.Add(AddDbParm(oCol.ColumnName, oCol.DefaultValue));
}
}
// Strip the trailing comma from each element... command text, and its insert values
SQLCommand = SQLCommand.Substring(0, SQLCommand.Length - 1);
InsertValues = InsertValues.Substring(0, InsertValues.Length - 1);
// Now, close the command text with ") VALUES ( "
// and add the INSERT VALUES element parms
SQLCommand += " ) values ( " + InsertValues + " )";
// Update the final command text to the SQLInsert object
// and we're done with the prep ONCE
oSQLInsert.CommandText = SQLCommand;
}
Next, When I need to actually perform the inserts for all records as I go through, I do that via my Add() function and pass in an instance of the DataRow I am working on. Since the SQLInsert object is already built with respective parameters, I can just cycle through the data row of same type as the data manager is responsible for, and just update the parameter objects with the data row's current "values"
public Boolean AddMyRecord(DataRow oDR)
{
// the parameter name was set based on the name of the column,
// so I KNOW there will be a match, and same data type
foreach (IDbDataParameter oDBP in oSQLInsert.Parameters)
oDBP.Value = oDR[oDBP.ParameterName];
ExecuteMySQLCommand( oSQLInsert );
}
With some timed-trials against the handheld device, the before and after times to run / validate about 20 queries and 10 inserts from about 10 seconds down to 2.5 seconds. The technique was similar for that of doing SQLUpdate, but forcing the WHERE clause to the primary ID column of the table at the end of the building string / object cycle. It works great. Now, if I ever need to expand the structure or column sequences of the table, I dont have to change ANY code for the insert, update processes.