Bulk update from dotnet to oracle

Hi

I have 2000 rows in a csv file with three columns , i want to update these in to oracle database table based on certain condition , i can do this by using looping which is not optimised, can you please suggest me for optimised code or query to update this.
praveen1981Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

santhimurthydCommented:
Construct the Adhoc querry using Stringbuilder and insert the record as single short with Transaction scope

StringBuilder sb = new StringBuilder();
sb.Append("insert into TableName values('" + Val1+"','" + val2 + "','"+ val3 +"')");
sb.Append("insert into TableName values('" + Val1+"','" + val2 + "','"+ val3 +"')");
sb.Append("insert into TableName values('" + Val1+"','" + val2 + "','"+ val3 +"')");

then send the whole record as single to insert
0
slightwv (䄆 Netminder) Commented:
>>then send the whole record as single to insert

You cannot chain commands like this in Oracle.

I believe you will need array binding:
http://www.oracle.com/technetwork/issue-archive/2009/09-sep/o59odpnet-085168.html
0
praveen1981Author Commented:
Construct the Adhoc querry using Stringbuilder and insert the record as single short with Transaction scope

StringBuilder sb = new StringBuilder();
sb.Append("insert into TableName values('" + Val1+"','" + val2 + "','"+ val3 +"')");
sb.Append("insert into TableName values('" + Val1+"','" + val2 + "','"+ val3 +"')");
sb.Append("insert into TableName values('" + Val1+"','" + val2 + "','"+ val3 +"')");

then send the whole record as single to insert

can you please elobarate these, how can we send this whole to the stored Procedure  from dotnet
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

santhimurthydCommented:
To send the input to Storeprocedure construct the string with the SP details as follows

StringBuilder sb = new StringBuilder();
sb.Append("exec <<OracleStoreProcedure>>('" + Val1+"','" + val2 + "','"+ val3 +"')\n");
sb.Append("exec <<OracleStoreProcedure>>('" + Val1+"','" + val2 + "','"+ val3 +"')\n");
sb.Append("exec <<OracleStoreProcedure>>('" + Val1+"','" + val2 + "','"+ val3 +"')\n");

0
slightwv (䄆 Netminder) Commented:
@santhimurthyd

Again,

You cannot chain commands together like this in Oracle and pass the string to a single 'execute' method.

If you are going to keep suggesting this as a viable approach please post a simple working test showing it.
0
slightwv (䄆 Netminder) Commented:
Look up associative arrays to pass a 'list' into a procedure in a single call:

http://docs.oracle.com/cd/B14117_01/win.101/b10117/features004.htm

PL/SQL Associative Array
 
ODP.NET supports PL/SQL Associative Array (formerly known as PL/SQL Index-By Tables) binding.
 
An application can bind an OracleParameter, as a PL/SQL Associative Array, to a PL/SQL stored procedure. The following OracleParameter properties are used for this feature.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
praveen1981Author Commented:
Hi
This solution is fine, but i have used the xml file as input to the stored procedure to update the bulk data.

Thanks for your solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.