Paging - ADO.NET 2.0

[Victor H. Passador]

Hi !

At first, sorry by my bad english ...

I need to develop a windows form app. This application must be as agnostic
(in database terms) as possible.
The application must run against SQL Server 2000, Oracle and/or DB2.
I need to implement some kind of paging due to that several queries may
returns a great quantity of data.
I've heard that SqlExecutePageReader() method doesn't comes with the release
of ADO.NET 2.0.
Is that true ?
Would somebody advice me with the best method to achieve this "paging" with
ADO.NET 2.0 ?

Thanks in advance.

Best Regards

Victor

 

[Landley]

I use the dataadapter.fill method.

One of the overloads allow you to specify a start and a maximum number of
rows to return.

To page your results, you'll need to know the number of rows your query will
return.

Landers.

 

[Miha Markic [MVP C#]]

Just note that adapter isn't intelligent enough to skip first records
(before the requested page) and thus it will fetch all records from zero to
the end of the page.
For example, if you request 3rd page of 10 records it will fetch all 30
records (but will insert only page into the DataTable)

 

[Victor H. Passador]

Thanks both for your replies.

I'm agree with Miha Markic, if the result set is large, all the records are
sent through the wire and then are discarded at client side.
Regarding the first question, somebody knows if the SqlExecutePageReader
method will be delivered ?.

Best regards,

Victor

Just note that adapter isn't intelligent enough to skip first records
(before the requested page) and thus it will fetch all records from zero
to the end of the page.
For example, if you request 3rd page of 10 records it will fetch all 30
records (but will insert only page into the DataTable)

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

I use the dataadapter.fill method.

One of the overloads allow you to specify a start and a maximum number of
rows to return.

To page your results, you'll need to know the number of rows your query
will
return.

Landers.

 

[Landley]

Is this true?

I was informed that the filtering was done server-side, though my source may
have been unreliable.

I am using this method in a large image database returning 50 images per
page. Our timings getting data back from page 1 and page 1000 were the
same.

Can you point me at your source of information?

Landers

Just note that adapter isn't intelligent enough to skip first records
(before the requested page) and thus it will fetch all records from zero to
the end of the page.
For example, if you request 3rd page of 10 records it will fetch all 30
records (but will insert only page into the DataTable)

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

I use the dataadapter.fill method.

One of the overloads allow you to specify a start and a maximum number of
rows to return.

To page your results, you'll need to know the number of rows your query
will
return.

Landers.

 

[Miha Markic [MVP C#]]

Hi,

Well, Fill method doesn't alter select command in any way it just ignored
first set of irrelevant records - thus server side filtering is
automatically opted out.
But still those records have to be fetched to client so it can ignore them
.
If you want do proper paging you'll have to use stored procuderes I think.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Is this true?

I was informed that the filtering was done server-side, though my source
may
have been unreliable.

I am using this method in a large image database returning 50 images per
page. Our timings getting data back from page 1 and page 1000 were the
same.

Can you point me at your source of information?

Landers

Just note that adapter isn't intelligent enough to skip first records
(before the requested page) and thus it will fetch all records from zero to
the end of the page.
For example, if you request 3rd page of 10 records it will fetch all 30
records (but will insert only page into the DataTable)

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

I use the dataadapter.fill method.

One of the overloads allow you to specify a start and a maximum number of
rows to return.

To page your results, you'll need to know the number of rows your query
will
return.

Landers.

Hi !

At first, sorry by my bad english ...

I need to develop a windows form app. This application must be as
agnostic
(in database terms) as possible.
The application must run against SQL Server 2000, Oracle and/or DB2.
I need to implement some kind of paging due to that several queries
may
returns a great quantity of data.
I've heard that SqlExecutePageReader() method doesn't comes with the
release
of ADO.NET 2.0.
Is that true ?
Would somebody advice me with the best method to achieve this "paging"
with
ADO.NET 2.0 ?

Thanks in advance.

Best Regards

Victor

 

[Landley]

I was under the impression that additional information was sent to server
from the provider when such a query is made.

Thus allowing the database engine server-side to move the cursor to whatever
the position is and return the required number of rows.

This would explain our findings with our time tests. Could it be that
different providers do different things. These tests were with an Informix
database using the Informix .NET provider. I have done any further tests
with any other providers.

A confirmation on what the Fill method actually does behind the scenes is
required. Does Microsoft have any documentation regarding this?

Landers.

Hi,

Well, Fill method doesn't alter select command in any way it just ignored
first set of irrelevant records - thus server side filtering is
automatically opted out.
But still those records have to be fetched to client so it can ignore them
.
If you want do proper paging you'll have to use stored procuderes I think.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Is this true?

I was informed that the filtering was done server-side, though my source
may
have been unreliable.

I am using this method in a large image database returning 50 images per
page. Our timings getting data back from page 1 and page 1000 were the
same.

Can you point me at your source of information?

Landers

Just note that adapter isn't intelligent enough to skip first records
(before the requested page) and thus it will fetch all records from

zero
to

the end of the page.
For example, if you request 3rd page of 10 records it will fetch all 30
records (but will insert only page into the DataTable)

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

I use the dataadapter.fill method.

One of the overloads allow you to specify a start and a maximum

number
of

rows to return.

To page your results, you'll need to know the number of rows your query
will
return.

Landers.

Hi !

At first, sorry by my bad english ...

I need to develop a windows form app. This application must be as
agnostic
(in database terms) as possible.
The application must run against SQL Server 2000, Oracle and/or DB2.
I need to implement some kind of paging due to that several queries
may
returns a great quantity of data.
I've heard that SqlExecutePageReader() method doesn't comes with the
release
of ADO.NET 2.0.
Is that true ?
Would somebody advice me with the best method to achieve this "paging"
with
ADO.NET 2.0 ?

Thanks in advance.

Best Regards

Victor

 

[Sushil Chordia]

Landley, Fill does the following:
1. calls Adapter.SelectCommand.ExecuteReader()
2. Iterates through the datareader like:
While (Reader.Read()) {
//populates the datatable
}
So everytime you call Fill or any of its overloads, all the data from the
server is sent on the wire. Even, when specifying the startRecords and
maxRecords on the Fill, all the data is still received from the server and
selectively loaded in the dataset based on these params.
This implementation is specific to Data Providers in the System.Data
namespace only. Other managed providers may have different behavior.

HTH.
Sushil.

I was under the impression that additional information was sent to server
from the provider when such a query is made.

Thus allowing the database engine server-side to move the cursor to
whatever
the position is and return the required number of rows.

This would explain our findings with our time tests. Could it be that
different providers do different things. These tests were with an
Informix
database using the Informix .NET provider. I have done any further tests
with any other providers.

A confirmation on what the Fill method actually does behind the scenes is
required. Does Microsoft have any documentation regarding this?

Landers.

Hi,

Well, Fill method doesn't alter select command in any way it just ignored
first set of irrelevant records - thus server side filtering is
automatically opted out.
But still those records have to be fetched to client so it can ignore
them
.
If you want do proper paging you'll have to use stored procuderes I
think.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Is this true?

I was informed that the filtering was done server-side, though my
source
may
have been unreliable.

I am using this method in a large image database returning 50 images
per
page. Our timings getting data back from page 1 and page 1000 were the
same.

Can you point me at your source of information?

Landers


"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
Just note that adapter isn't intelligent enough to skip first records
(before the requested page) and thus it will fetch all records from zero
to
the end of the page.
For example, if you request 3rd page of 10 records it will fetch all
30
records (but will insert only page into the DataTable)

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

I use the dataadapter.fill method.

One of the overloads allow you to specify a start and a maximum number
of
rows to return.

To page your results, you'll need to know the number of rows your query
will
return.

Landers.

Hi !

At first, sorry by my bad english ...

I need to develop a windows form app. This application must be as
agnostic
(in database terms) as possible.
The application must run against SQL Server 2000, Oracle and/or
DB2.
I need to implement some kind of paging due to that several queries
may
returns a great quantity of data.
I've heard that SqlExecutePageReader() method doesn't comes with
the
release
of ADO.NET 2.0.
Is that true ?
Would somebody advice me with the best method to achieve this "paging"
with
ADO.NET 2.0 ?

Thanks in advance.

Best Regards

Victor

 

[Sushil Chordia]

The ExecutePageReader method has been removed from .Net Framework for 2.0.
We are actively looking for paging possiblities in the next version, but we
are not sure on what will be included in the next version now.

Thanks,
Sushil

Thanks both for your replies.

I'm agree with Miha Markic, if the result set is large, all the records
are sent through the wire and then are discarded at client side.
Regarding the first question, somebody knows if the SqlExecutePageReader
method will be delivered ?.

Best regards,

Victor

Just note that adapter isn't intelligent enough to skip first records
(before the requested page) and thus it will fetch all records from zero
to the end of the page.
For example, if you request 3rd page of 10 records it will fetch all 30
records (but will insert only page into the DataTable)

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

I use the dataadapter.fill method.

One of the overloads allow you to specify a start and a maximum number
of
rows to return.

To page your results, you'll need to know the number of rows your query
will
return.

Landers.

Hi !

At first, sorry by my bad english ...

I need to develop a windows form app. This application must be as
agnostic
(in database terms) as possible.
The application must run against SQL Server 2000, Oracle and/or DB2.
I need to implement some kind of paging due to that several queries may
returns a great quantity of data.
I've heard that SqlExecutePageReader() method doesn't comes with the
release
of ADO.NET 2.0.
Is that true ?
Would somebody advice me with the best method to achieve this "paging"
with
ADO.NET 2.0 ?

Thanks in advance.

Best Regards

Victor

 

[Landley]

This may explain why I don't get a performance hit on the last page then.

I need to see official documentation to ease my mind now though. So if you
know of any useful links, I would be much appreciated.

Landers

Landley, Fill does the following:
1. calls Adapter.SelectCommand.ExecuteReader()
2. Iterates through the datareader like:
While (Reader.Read()) {
//populates the datatable
}
So everytime you call Fill or any of its overloads, all the data from the
server is sent on the wire. Even, when specifying the startRecords and
maxRecords on the Fill, all the data is still received from the server and
selectively loaded in the dataset based on these params.
This implementation is specific to Data Providers in the System.Data
namespace only. Other managed providers may have different behavior.

HTH.
Sushil.

I was under the impression that additional information was sent to server
from the provider when such a query is made.

Thus allowing the database engine server-side to move the cursor to
whatever
the position is and return the required number of rows.

This would explain our findings with our time tests. Could it be that
different providers do different things. These tests were with an
Informix
database using the Informix .NET provider. I have done any further tests
with any other providers.

A confirmation on what the Fill method actually does behind the scenes is
required. Does Microsoft have any documentation regarding this?

Landers.

Hi,

Well, Fill method doesn't alter select command in any way it just ignored
first set of irrelevant records - thus server side filtering is
automatically opted out.
But still those records have to be fetched to client so it can ignore
them
.
If you want do proper paging you'll have to use stored procuderes I
think.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Is this true?

I was informed that the filtering was done server-side, though my
source
may
have been unreliable.

I am using this method in a large image database returning 50 images
per
page. Our timings getting data back from page 1 and page 1000 were the
same.

Can you point me at your source of information?

Landers


"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
Just note that adapter isn't intelligent enough to skip first records
(before the requested page) and thus it will fetch all records from zero
to
the end of the page.
For example, if you request 3rd page of 10 records it will fetch all
30
records (but will insert only page into the DataTable)

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

I use the dataadapter.fill method.

One of the overloads allow you to specify a start and a maximum number
of
rows to return.

To page your results, you'll need to know the number of rows your query
will
return.

Landers.

Hi !

At first, sorry by my bad english ...

I need to develop a windows form app. This application must be as
agnostic
(in database terms) as possible.
The application must run against SQL Server 2000, Oracle and/or
DB2.
I need to implement some kind of paging due to that several queries
may
returns a great quantity of data.
I've heard that SqlExecutePageReader() method doesn't comes with
the
release
of ADO.NET 2.0.
Is that true ?
Would somebody advice me with the best method to achieve this "paging"
with
ADO.NET 2.0 ?

Thanks in advance.

Best Regards

Victor