DbCommand and parameterized SQL, ORACLE vs SQL Server

I accepted an answer for this question long ago, but for some reason that answer is no longer here... So I guess I need to answer my own question.

What I did was to create a parambuilder class:

class ParamBuilder{    private DbProviderFactory m_factory;    private DbCommandBuilder m_builder;    private string m_parameterMarkerFormat;    public ParamBuilder(DbProviderFactory factory) : this(factory, null)    {    }    public ParamBuilder(DbProviderFactory factory, DbConnection source)    {        m_factory = factory;        m_builder = m_factory.CreateCommandBuilder();        if (source != null)        {            using (DataTable tbl =                source.GetSchema(DbMetaDataCollectionNames.DataSourceInformation))            {                m_parameterMarkerFormat =                      tbl.Rows[0][DbMetaDataColumnNames.ParameterMarkerFormat] as string;            }        }        if (String.IsNullOrEmpty(m_parameterMarkerFormat))            m_parameterMarkerFormat = "{0}";    }    public DbParameter CreateParameter(string parameterName,         out string parameterMarker)    {        DbParameter param = m_factory.CreateParameter();        param.ParameterName =              (string)typeof(DbCommandBuilder).InvokeMember("GetParameterName",                System.Reflection.BindingFlags.Instance |                System.Reflection.BindingFlags.InvokeMethod |                System.Reflection.BindingFlags.NonPublic, null, m_builder,                 new object[] { parameterName });        parameterMarker =             String.Format(System.Globalization.CultureInfo.InvariantCulture,             m_parameterMarkerFormat, param.ParameterName);        return param;    }}

I create a member variable of the ParamBuilder type:

private readonly ParamBuilder m_ParamBuilder;

Then in the method where I use parameters, I use it as follows:

...string paramMarker;DbParameter param = m_ParamBuilder.CreateParameter(destination[i].ColumnName,     out paramMarker);sql.Append(paramMarker);param.Direction = ParameterDirection.Input;param.Value = source[i];Cmd.Parameters.Add(param);...

Make an abstract property to get the format string used in the "values" loop.

class DBOperations public abstract string ParameterStringFormat; ...for (int i = 0; i < values.Length; i++)        {                    sql.Append(String.Format(ParamterStringFormat, columns[i].ColumnName)); // SQL Server        }  class SqlDbOperations : DBOperations public override string ParameterStringFormat { get { return "@{0}"; }}class OracleDBOperations : DBOperations public override string ParameterStringFormat { get { return ":{0}"; }}

Just throwing in another option if you want quick and dirty.

string sql = select * from foo there foo.id = @id;if (isOracle) { sql = replaceAll(sql,"@",":");}