title: DbCommand and parameterized SQL, ORACLE vs SQL Server
body: <p>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.</p>
  
  <p>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 <code>":ParameterName"</code> whereas for SQL Server I need to use <code>"@ParameterName"</code>. Is there any way to make this generic?</p>
  
  <p>Sample code:</p>
  
  <pre><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 &lt; columns.Length; i++)
          {
              sql.Append(columns[i].ColumnName);
              if (i &lt; columns.Length - 1) 
              sql.Append(", ");
          }
          sql.Append(") VALUES (");
  
          for (int i = 0; i &lt; 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 &lt; columns.Length - 1)           
              sql.Append(", ");
        }
        sql.Append(")");
        Cmd.CommandText = sql.ToString();
        Cmd.ExecuteNonQuery();
  }
  </code></pre>