batching up commands
Posted: Mon 20 Mar 2006 16:19
hi
I have various hierachical table structures in my database, that I retrieve to my program.
I have written a generic procedure that (when given the primary key of the top table) recursively works its way down the tree structure (by querying each table to find out which foreign keys reference it), querying the database for each table and bringing it back into a table of its own in my dataset.
It all works well, but I would like to make sure I am doing it the most efficient way. Sometimes I do 20 database queries in quick succession, so I was wondering if it was possible to batch up the oraclecommands and then call them all at once at the end, building the dataset in one hit.
I know with sqlcommand you can batch up commands using a ';' between statements. However, I dont think this works for returning data, just inserting and updating, and oracle doesnt like it when i try with a ';'.
Anyone any suggestions?
I have various hierachical table structures in my database, that I retrieve to my program.
I have written a generic procedure that (when given the primary key of the top table) recursively works its way down the tree structure (by querying each table to find out which foreign keys reference it), querying the database for each table and bringing it back into a table of its own in my dataset.
It all works well, but I would like to make sure I am doing it the most efficient way. Sometimes I do 20 database queries in quick succession, so I was wondering if it was possible to batch up the oraclecommands and then call them all at once at the end, building the dataset in one hit.
I know with sqlcommand you can batch up commands using a ';' between statements. However, I dont think this works for returning data, just inserting and updating, and oracle doesnt like it when i try with a ';'.
Anyone any suggestions?