DbCommand and parameterized SQL, ORACLE vs SQL Server
I have an application that will, among other things, store various data into a database.
The database might be ORACLE or SQL Server. The SQL is created dynamically based on configuration and values picked up during execution.

By using DbProviderFactory my db methods are able to work with either ORACLE or SQL Server without writing custom code for any of the databases, except from one thing; parameters/bind variables. For ORACLE I need to use `":ParameterName"` whereas for SQL Server I need to use `"@ParameterName"`. Is there any way to make this generic?

Sample code:

        
    public class DbOperations
    {
        private DbProviderFactory m_factory;
        private DbConnection m_CN;
     
        ...

        private void InsertToDb(ValueType[] values, ColumnType[] columns)
        {	  
            DbCommand Cmd = m_factory.CreateCommand();
            Cmd.Connection = m_CN;
                
            StringBuilder sql = new StringBuilder();
            sql.Append("INSERT INTO ");
            sql.Append(DestinationTable);
            sql.Append(" (");
      
            for (int i = 0; i < columns.Length; i++)
            {
                sql.Append(columns[i].ColumnName);
                if (i < columns.Length - 1) 
                sql.Append(", ");
            }
            sql.Append(") VALUES (");
    
            for (int i = 0; i < values.Length; i++)
            {        
                //sql.Append(String.Format(":{0}", columns[i].ColumnName));  //ORACLE
                sql.Append(String.Format("@{0}", columns[i].ColumnName)); // SQL Server
            }       

            DbParameter param = m_factory.CreateParameter();
            param.Direction = ParameterDirection.Input;
            param.ParameterName = columns[i].ColumnName;
            param.Value = values[i];
            Cmd.Parameters.Add(param);

            if (i < columns.Length - 1)           
                sql.Append(", ");
          }
          sql.Append(")");
          Cmd.CommandText = sql.ToString();
          Cmd.ExecuteNonQuery();
    }




DbCommand and parameterized SQL, ORACLE vs SQL Server
===

I have an application that will, among other things, store various data into a database.
The database might be ORACLE or SQL Server. The SQL is created dynamically based on configuration and values picked up during execution.

By using DbProviderFactory my db methods are able to work with either ORACLE or SQL Server without writing custom code for any of the databases, except from one thing; parameters/bind variables. For ORACLE I need to use `":ParameterName"` whereas for SQL Server I need to use `"@ParameterName"`. Is there any way to make this generic?

Sample code:

        
    public class DbOperations
    {
        private DbProviderFactory m_factory;
        private DbConnection m_CN;
     
        ...

        private void InsertToDb(ValueType[] values, ColumnType[] columns)
        {	  
            DbCommand Cmd = m_factory.CreateCommand();
            Cmd.Connection = m_CN;
                
            StringBuilder sql = new StringBuilder();
            sql.Append("INSERT INTO ");
            sql.Append(DestinationTable);
            sql.Append(" (");
      
            for (int i = 0; i < columns.Length; i++)
            {
                sql.Append(columns[i].ColumnName);
                if (i < columns.Length - 1) 
                sql.Append(", ");
            }
            sql.Append(") VALUES (");
    
            for (int i = 0; i < values.Length; i++)
            {        
                //sql.Append(String.Format(":{0}", columns[i].ColumnName));  //ORACLE
                sql.Append(String.Format("@{0}", columns[i].ColumnName)); // SQL Server
            }       

            DbParameter param = m_factory.CreateParameter();
            param.Direction = ParameterDirection.Input;
            param.ParameterName = columns[i].ColumnName;
            param.Value = values[i];
            Cmd.Parameters.Add(param);

            if (i < columns.Length - 1)           
                sql.Append(", ");
          }
          sql.Append(")");
          Cmd.CommandText = sql.ToString();
          Cmd.ExecuteNonQuery();
    }



