Executing a select statement multiple times?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

What is the efficient way of executing the database sequence multiple times?

Suppose for example, I have a DB Sequence like this:
“Select SequenceName.NEXTVAL from TBLName;â€

I want to execute this multiple times and populate a collection. How do I
achieve this efficiently? Do I have to use DataReader multiple times? If so,
how do I do that?
There’s no support for stored procedures/functions in the DBMS, so I can’t
go that route.
Any code snippets will help.

Thanks.
 
The answer depends on "What do you need to execute this query for?".

DataReader could work, and will be the fastest for a single user scenario,
but a dataadapter might work better if the sequence is used to for example
generate unique ids, when you don't want to use guids.
In which scenario, your best bet could still be executing the sequence on a
per request basis, and not allowing that operation to be a part of any other
transaction (to prevent deadlocks). You might even want to consider
Command.Execute*.* .. there are indeed a lot of possibilities :-)

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
Please reply to the newsgroups instead of email so everyone can benefit from
your reply.
 
Hi,

If this is the same SQL statement, then you could just load data into
DataSet once and use this data in a multiple places. If you need to execute
different SQL statements but once, then ExecuteReader is a fastest way to do
this. Implementation depends on a design of your application.
 
Thanks Mazur.
I guess, i should put the question differently.
Here's what the DBSequence does when you execute on SQL:
"Select SequenceName.NEXTVAL from TBLName;"
Assuming the sequence starts at 1,gives a value of 1 first time.
When i execute the same statement again, gives a value of 2
When i execute the same statement again, gives a value of 3

I need to a collection of 10 sequence values. So my question was how would i
use the same datareader to execute 10 times or is there any efficient way?
 
What are you trying to accomplish? Why do you need a sequence?

how about

i = i + 1 ?
 
"Database sequences are special database objects that are used to generate
integer values according to rules defined when the sequence was created.
Database sequences are generally are used to create primary keys, they can
also be used to generate random numbers."

I was giving that sequence just as an example.. please follow the thread,
instead of coming up with "smart" answers. It kinda sidetracks my original
question related to ADO.Net.
 
You make no sense. Issuing a select statement multiple times to generate a
sequence is ridiculous.

You never stated what you are trying to do, I "might" have suggested a
proper approach. So we are supposed to "guess" what you are actually doing?
That is a waste of everyone's time.

I simply meant that you can generate the sequence yourself, in code.

But not knowing what you are actually doing, I would be throwing answers in
the dark.

http://www.aspfaq.com/show.asp?id=2516

Jeff
 
Hi,

It looks like your statement generates only one new value at a time. Right?
If yes then I would suggest to use some sort of stored procedure (if
possible) and pass number of values you need to get back. Then inside of
this SP you could execute same statement in a loop and store values in a
temp table, then return result from this temp table. If I understand problem
correctly, I do not see any good solution for this except this one
 
Thanks Mazur. Sprocs not supported. Have mentioned in my first post on this
thread.
You are right in that it returns new value every time.

Going in a loop to execute the DataReader is the way to go?
 
No

Nathan said:
Thanks Mazur. Sprocs not supported. Have mentioned in my first post on this
thread.
You are right in that it returns new value every time.

Going in a loop to execute the DataReader is the way to go?
Click to expand...
 
How do you plan to pass state from one iteration to the next? How do you
start over? etc etc

Perhaps:

set @prev = 0

Select min(nextval) from table where
nextval > @prev

select @prev = nexval

select min(nextval) from table where
nextval > @prev

etc

So I take it the nextval isn't sequential, and there could be gaps?
Otherwise, my previous i = i+ 1 would work, as long as you retrieved the
base number first from the db. But without knowing exactly what you're
doing, it's impossible to guess.

Jeff
 
Or..

Select NexVal from table
UNION
select nextval from table
UNION

....

Then call it with one DataReader call

Jeff
 
Back
Top