title: Efficiently inserting multiple records in oracle db using Oracle client
body: <p>I've C#-WCF/Winforms application.It inserts 450,000 plus records in a staging table in Oracle database using Oracle Client API and a stored proc having a simple insert query.
  Its taking about 15 minutes to insert records in db and sometimes the records dont get inserted too..giving all sorts of timeout errors at the wcf side.
  Is there any efficient way of doing these inserts?</p>
  
  <p>Thanks for reading.</p>
  
  <p>Here's my code which does the batch insert:</p>
  
  <pre><code>OracleTransaction tran = null; 
  UpdateRowSource oldURS = this.cmd.UpdatedRowSource; 
  OracleCommand oldCmd = this.dbAdapter.InsertCommand; 
  int oldUBS = this.dbAdapter.UpdateBatchSize; 
  try 
  { 
      SetOutputParams(); 
      this.OpenDBConnection(); 
      tran = this.dbConn.BeginTransaction(); 
      this.cmd.Transaction = tran; 
      this.cmd.UpdatedRowSource = UpdateRowSource.OutputParameters; 
      this.dbAdapter.InsertCommand = this.cmd; 
      this.dbAdapter.UpdateBatchSize = size; 
      this.dbAdapter.Update(data); 
      tran.Commit(); 
      SetOutputParamValues(); 
  } 
  catch (OracleException ex) 
  { 
      if (tran != null) { 
          tran.Rollback(); 
      } 
      throw; 
  } 
  finally 
  { 
      this.CloseDBConnection(); 
      this.cmd.Parameters.Clear(); 
      this.cmd.UpdatedRowSource = oldURS; 
      this.dbAdapter.InsertCommand = oldCmd; 
      this.dbAdapter.UpdateBatchSize = oldUBS; 
  } 
  </code></pre>
  
  <p>}</p>