Now and again we all come across situations where we need to save multiple records to a database, and the easy way is to set up a loop and insert the records one at a time. However there is a way to insert multiple records at onceĀ  using ODP.NET (also works for DDTek). It involves creating arrays for the parameters where you would normally have single values, and setting the ArrayBindCount of the command object to the number of elements in the array. Sample code:


public bool QueueEmail(EmailQueueItemCollection coll)
{
  bool retVal = false;
  OracleCommand cmd = new OracleCommand("CAEProc.CAE_FOF_EMAILQUEUE_PKG.QueueItem", conn);
  cmd.CommandType = CommandType.StoredProcedure;

  string[] emails = new string[coll.Count];
  string[] userids = new string[coll.Count];
  int[] emaildefs = new int[coll.Count];
  long[] secids = new long[coll.Count];

  for (int i = 0; i < coll.Count; i++ )
  {
    emails[i] = coll[i].EmailAddress;
    userids[i] = coll[i].UserId;
    emaildefs[i] = 1;
    secids[i] = coll[i].SecID;
  }

  //set the number of elements we're passing in
  cmd.ArrayBindCount = coll.Count;

  OracleParameter p_emailAddress = new OracleParameter("p_emailAddress_c", OracleDbType.VarChar);
  p_emailAddress.Direction = ParameterDirection.Input;
  p_emailAddress.Value = emails;
  cmd.Parameters.Add(p_emailAddress);

  OracleParameter p_userId = new OracleParameter("p_user_id_c", OracleDbType.VarChar);
  p_userId.Direction = ParameterDirection.Input;
  p_userId.Value = userids;
  cmd.Parameters.Add(p_userId);

  OracleParameter p_email_def_id = new OracleParameter("p_email_def_id", OracleDbType.Number);
  p_email_def_id.Direction = ParameterDirection.Input;
  p_email_def_id.Value = emaildefs;
  cmd.Parameters.Add(p_email_def_id);

  OracleParameter p_SECID = new OracleParameter("p_cae_sec_id", OracleDbType.Number);
  p_SECID.Direction = ParameterDirection.Input;
  p_SECID.Value = secids;
  cmd.Parameters.Add(p_SECID);

  OracleParameter p_result_n = new OracleParameter("p_result_n", OracleDbType.Int32);
  p_result_n.Direction = ParameterDirection.Output;
  cmd.Parameters.Add(p_result_n);

  conn.Open();

  // Make the Command object a SelectCommand for DataAdapter
  try
  {
    int recordsInserted = cmd.ExecuteNonQuery();
    int tmpInt = 0;
    retVal = (recordsInserted > 0);
  }
  catch (Exception ex)
  {
    LoggingHelper.Log(ex, "Exception when adding emails to queue");
  }
  finally
  {
    conn.Close();
  }

  return retVal;
}

About these ads