title: Update and insert records into Oracle table using OracleDataAdapter from DataTable
body: <p>a question from a newbie to c# and apologies for the lenght of it.  I have the following scenario.  I have a small console application that populates a datatable by connecting to an external system and then needs to update existing records and insert new ones into an oracle table.  The columns in the datatable are not named the same as the oracle table columns and not in the same order.  I read another post on here with a similar scenario (loading from a file into a table) and it mentioned that doing an update/insert with an OracleDataAdapter would work.  A simplified datatable and oracle table are</p>
  
  <p><code>DataTable table = new DataTable();
  table.Columns.Add("Product", typeof(String));
  table.Columns.Add("Price", typeof(double));
  table.Columns.Add("Effective_Date", typeof(DateTime));</code></p>
  
  <p><code>//sample data
  table.Rows.Add("abcd", 1.011, DateTime.Today);
  table.Rows.Add("efg", 1.00, DateTime.Today);
  table.Rows.Add("hijk", 20, DateTime.Today);</code></p>
  
  <p>The oracle table has the structure</p>
  
  <p><code>ITEM VARCHAR2(20 BYTE) NOT NULL ENABLE, 
  EFF_DATE DATE, 
  VALUE NUMBER</code></p>
  
  <p>I have tried the following code to use the datatable and an adapter to update the oracle table but I'm missing something.  I'm also wondering if I'm barking up the wrong tree.  The majority of examples I have seen of using a dataadapter first does a select from the table and then puts the results into a grid where a user would be able to add, update, insert, or delete records and then uses the dataadapter to update the table.  In my case I'm wondering if I get it to work if all records in the datatable will be treated as an insert anyway as there is no connection between the datatable and the oracle table.</p>
  
  <p>I'm using the Oracle.ManagedDataAccess.Client to connect and do the updates</p>
  
  <pre><code>public static void UpdateOrSaveItems(DataTable dt)
  {
      String insert_statement, update_statement, select_statement;
  
      select_statement = "SELECT * from items";
      insert_statement = "INSERT INTO items (item, eff_date, value) values (:pInsItem,:pInsEffDate,:pInsValue)";
      update_statement = "UPDATE items set eff_date = :pUpdEffDate, value = :pUpdValue where item = :pUpdItem";
  
      using (OracleConnection conn = theDatabase.ConnectToDatabase())
      {
          using (OracleDataAdapter oraAdapter = new OracleDataAdapter(select_statement, conn))
          {   
  
           //build update/insert commands and parameters
           oraAdapter.UpdateCommand = new OracleCommand(update_statement, conn);
           oraAdapter.InsertCommand = new OracleCommand(insert_statement, conn);
  
           oraAdapter.UpdateCommand.BindByName = true;
           oraAdapter.InsertCommand.BindByName = true;
  
           OracleParameter pUpdItem = new OracleParameter("pUpdItem", OracleDbType.Varchar2);
           pUpdItem.SourceColumn = dt.Columns[0].ColumnName;                  
  
           OracleParameter pUpdEffDate = new OracleParameter("pUpdEffDate", OracleDbType.Date);
           pUpdEffDate.SourceColumn = dt.Columns[2].ColumnName;
  
           OracleParameter pUpdValue = new OracleParameter("pUpdValue", OracleDbType.Double);
           pUpdValue.SourceColumn = dt.Columns[1].ColumnName;
  
           OracleParameter pInsItem = new OracleParameter("pInsItem", OracleDbType.Varchar2);
           pUpdItem.SourceColumn = dt.Columns[0].ColumnName;
  
           OracleParameter pInsEffDate = new OracleParameter("pInsEffDate", OracleDbType.Date);
           pInsEffDate.SourceColumn = dt.Columns[2].ColumnName;
  
           OracleParameter pInsValue = new OracleParameter("pInsValue", OracleDbType.Double);
           pInsValue.SourceColumn = dt.Columns[1].ColumnName;              oraAdapter.UpdateCommand.Parameters.Add(pUpdItem);
  
           oraAdapter.UpdateCommand.Parameters.Add(pUpdEffDate);
           oraAdapter.UpdateCommand.Parameters.Add(pUpdValue);
  
           oraAdapter.InsertCommand.Parameters.Add(pInsItem);
           oraAdapter.InsertCommand.Parameters.Add(pInsEffDate);
           oraAdapter.InsertCommand.Parameters.Add(pInsValue);
  
           oraAdapter.Update(dt);
      }
   }
  }
  </code></pre>
  
  <p>When I run this I get an error that I cannot insert a null into column that is defined as a key.  In the datatable none of them are null.  I'm missing something on telling it where the data is but am unsure what it is.  Also wondering if this is the right way to do this sort of thing.  I wanted to avoid  </p>
  
  <p><code>loop through datatable
  select to see if record is in oracle table
  if in table update else insert</code></p>
  
  <p>because the volume of records could a couple of hundred thousand and wasn't sure what the performance would be like.</p>