[2005] Improve efficiency of insert statements

[Kimmy4]

Hi guys,
I was wondering whether there was a more efficient way to do numerous inserts into an oracle database.
At the moment i am doing:

Open Oracle Connection
for each row in a dgv
create insert command using cells from a row
execute command
go to next row
close oracle connection when inserted all rows

Now sometimes i only around 100 rows to insert, but at other time i have over 20,000, which is done quite slowly.
So i was wondering whether there were any more efficient ways to perform a large number of insert statements?

Any help appreciated

[warrenayen]

Create a dataset, add the table to the dataset. Make all the changes to the table and send that back through the adapter as a bulk transaction...

[stanav]

Create a dataset, add the table to the dataset. Make all the changes to the table and send that back through the adapter as a bulk transaction...

The Update() method of a dataadapter actually goes thru the dataset/datatable and looks for a change in row state. If it sees it, it performs the appropriate action (insert, update, or delete) for the row... So the bottom line is, it is still doing one row at a time.

[Kimmy4]

Create a dataset, add the table to the dataset. Make all the changes to the table and send that back through the adapter as a bulk transaction...

This is what i am originally doing.
Sorry i think i should've given more information. I import from an excel worksheet into my datagridview and then, using insert statements, go through each row and put everything into a table in oracle. I have supplied my code if anybody wants to see exactly what i am doing. The code to import from excel to my datagridview is in the Main form and the code to insert into oracle is in the createStore form.

[Aspnot]

I don't know about Oracle, but I know SQL Server will take in an XML document and then parse that.

When I have had over 20 inserts that need to be done at one time, I create an XML document on the local machine and then pass that in as a parameter for a stored procedure and the stored procedure parses it and inserts the data. This has helped me to reduce a great amount of network traffic.

[Kimmy4]

I dont really know enough on the subject to attempt to try that method as it'll probably cause more errors and problems than necessary....thanx newayz tho!