ExecuteReader question

26 Jun 2006 9:16 AM

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

26 Jun 2006 12:34 PM

William (Bill) Vaughn

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.
__________________________________

Show quoteHide quote

"Gary Howlett" <g*@nuclide.co.uk> wrote in message
news:449fa5ed$0$69383$ed2619ec@ptn-nntp-reader01.plus.net...
> 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
>

26 Jun 2006 1:29 PM

Cor Ligthert [MVP]

Gary,

Maybe can these pages be a solution for you, although they are based on
datatables.
(I assume that you can look through the differences in C# and VBNet)

http://www.vb-tips.com/default.aspx?ID=da78342b-9e47-4e81-916f-ba7a1d82b540

http://www.vb-tips.com/default.aspx?ID=79afdb6a-611d-43cd-9186-def86a1baeef

We had as well a page about using a Sort and a Where clause in that, but
that was terrible slow and inaccurate, therefore that is deleted.

Cor

Show quoteHide quote

"Gary Howlett" <g*@nuclide.co.uk> schreef in bericht
news:449fa5ed$0$69383$ed2619ec@ptn-nntp-reader01.plus.net...
> 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
>