ExecuteReader question

  • Thread starter Thread starter Gary Howlett
  • Start date Start date
G

Gary Howlett

Hi,

Im using c# and sql 2005 to retrieve data.

What im trying to do is...

1) Connect to webservice
2) Open DB connection
3) Read & return 100 records
4) Do some process
5) Reconnect to webservcice
6) start now at record (currently 101)
7) Read & return 100 records
8) Loop 4

I dont wish to use data sets, but i was wondering if there is a way in say
T-SQL etc that you perform a SELECT statement to say just get 100 records a
time but starting at record X.

At the moment im calling Read(); on the SqlDataReader x times then when i
get to start record i want, start pulling the fields out.

Is this the quickest way? is there an alternative?

Mamy Thanks

Gary
 
A Web Service (or ASP) application does not (generally) maintain state, but
it can. However, keeping state (such as a rowset). I (and others) have been
asking for a way to execute a stateless query that returns the Nth block of
rows from a query for some time now. This is not that easy to do
(apparently) as it requires significant changes in the query optimizer. In a
connected client/server application one can create a keyset (server-side)
cursor and accomplish this quite easily. In your case you'll have to use
plan "B" where your query specifies a column set that spans the rowset and
permits you to specify an incrementing index. For example, if this was
simply a table you could fetch ranges of customer numbers. Each subsequent
query would fetch the next set using a TOP query.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top