Send collection to stored procedure

There are times when you want to insert more than one row to a table(s) through a stored procedure using .Net. This can be done using a for/while loop but that would make too many trips to the database. Best way is to send the parameters as a collection and the ODP client the do the rest for you. This will run all the insert queries at one go in a single Database trip and do a commit. It enhances a large amount of performance.

C# code:
public void InsertIntoDB(object response)
{
   // Oracle objects
   OracleConnection myConnection = null;
   try
  {
   myConnection = OpenOracleConnection();
   OracleCommand myCommand = GetInsertIntoDB(myConnection, response);
   myCommand.ExecuteNonQuery();
  }
  catch (Exception ex)
  {..}
  finally
  {
    myConnection.Close();
    myConnection = null;
  }
}

public static OracleCommand GetInsertIntoDB(object response)


{

OracleCommand myCommand = myConn.CreateCommand();

myCommand.Connection = myConn;

myCommand.CommandType = CommandType.StoredProcedure;

myCommand.CommandText = "insert";



string[] tranIds = new string[4];

int[] tranAmts = new int[4];

string[] tranNames = new string[4];

string[] tranAddress = new string[4];



for (int i = 0; i < 4; i++)

{

tranIds[i] = response[i].tranId;

tranAmts[i] = Convert.ToInt16(response[i].tranAmt);

tranNames[i] = response[i].tranName;

tranAddress[i] = response[i].tranAddres;

}



myCommand.Parameters.Add("p_tran_id", OracleDbType.Varchar2, tranIds, ParameterDirection.Input);

myCommand.Parameters.Add("p_tran_amt", OracleDbType.Int16, tranAmts, ParameterDirection.Input);

myCommand.Parameters.Add("p_tran_name", OracleDbType.Varchar2,tranNames, ParameterDirection.Input);

myCommand.Parameters.Add("p_tran_Addres", OracleDbType.Varchar2,tranAddress, ParameterDirection.Input);



myCommand.ArrayBindCount = tranIds.Length;



return myCommand;

}

Stored Procedure:

1. Inside Spec:

procedure insert(
p_tran_id in varchar2,
p_tran_amt in integer,
p_tran_name in varchar2,
p_tran_Addres in varchar2
);
 
2. Inside Body:
 
procedure insert(
p_tran_id in varchar2,
p_tran_amt in integer,
p_tran_name in varchar2,
p_tran_Addres in varchar2
)

AS
BEGIN
INSERT INTO TableName
(tran_id,
tran_amt,
tran_name,
tran_Addres
)
VALUES (p_tran_id,
p_tran_amt,
p_tran_name,
p_tran_Addres
);

COMMIT;

END insert;