0

I am trying to insert values in Oracle DB. I have a stored procedure which right now insert one row at a time. But reading more about bulk insert here (http://stackoverflow.com/questions/343299/bulk-insert-to-oracle-using-net, http://dotnetslackers.com/articles/ado_net/BulkOperationsUsingOracleDataProviderForNETODPNET.aspx), it seems like it is better to pass parameter value as an array. I have a generic list something like this i.e. employeeList.

class employee()
{ 
  public int ID {get; set;} 
   public string Name {get; set;}
}

How can i call my stored procedure (i know i need to make change with my in parameter in it, to take array of parameter) and pass each parameter value as an array to do bulk insert (trying to follow the example link)?

sanjeev40084
  • 9,227
  • 18
  • 67
  • 99
  • Is there a reason you need to use a stored procedure, rather than the array binding feature of ODP.NET? The article you link to describes that first, and it's much simpler than passing arrays to a stored procedure. – Odrade Jun 16 '11 at 20:33
  • the stored proc. has couple of logic, depending on which it insert/update table, that's why. Also it's been used by other app. – sanjeev40084 Jun 17 '11 at 13:08

1 Answers1

0

Even if you pass in an array you still can only do one DML at a time.
If you are wanting bulk loading for speed, write out a file and use SQL Loader bulk insert into your table.
It is the only way to get super fast performance for thousands of rows.

Threekill
  • 189
  • 7
  • Or, like Odrade recommended, one could use also array binding to get comparable performance. Neither solution will work if his stored procedure requires logic-before-insert. – Jay Sullivan Mar 25 '13 at 18:46