Best way to return a select number of rows?

[Jeff]

I'm trying to create a "paged" experience in ASP.NET, my query returns about
1000 records, not a ton, but they're url's to images and I just want to
display 9 on a page.

I am using a DataList control, which doesn't support paging, it's my
understanding that the DataGrid would actually request the whole query,
which is exactly what I don't want (I don't want 1000 records returned, just
9).

I've come up with a Stored Procedure that I pass the number of the first
record, and it just returns 9 rows. The problem is that Stored Procedure
isn't very dynamic, and I'm building my query based upon input from the
user. I have limited experience with Stored Procedures, but it would seem
that I have to create one for each of my varying possible queries (joining
different tables, that sort of thing). I think it's possible to use this
method, but it doesn't seem as efficient as what I did in regular ASP.

So, I was wondering if there was another way, something like a DataSet. I
am wondering if the .fill method would work, or if there's a way to start
from an absolute position. I was hoping to say something like starting from
the 400th record, read the next 9 into this DataSet. Then, I'd just bind
that DataSet to the DataList.

Any help would be great. Hopefully I've described the problem in a way that
everyone can understand and not just me.

 

[Miha Markic [MVP C#]]

Hi Jeff,

I'm trying to create a "paged" experience in ASP.NET, my query returns about
1000 records, not a ton, but they're url's to images and I just want to
display 9 on a page.

I am using a DataList control, which doesn't support paging, it's my
understanding that the DataGrid would actually request the whole query,
which is exactly what I don't want (I don't want 1000 records returned, just
9).

I've come up with a Stored Procedure that I pass the number of the first
record, and it just returns 9 rows. The problem is that Stored Procedure
isn't very dynamic, and I'm building my query based upon input from the
user. I have limited experience with Stored Procedures, but it would seem
that I have to create one for each of my varying possible queries (joining
different tables, that sort of thing). I think it's possible to use this
method, but it doesn't seem as efficient as what I did in regular ASP.

What did you do in ASP?

So, I was wondering if there was another way, something like a DataSet. I
am wondering if the .fill method would work, or if there's a way to start
from an absolute position. I was hoping to say something like starting from
the 400th record, read the next 9 into this DataSet. Then, I'd just bind
that DataSet to the DataList.

There is a such overload of Fill method.
Its weak point is that it fetches (but not fills) all records until
start+count record.
IOW if you want only last 10 records it will fetch all records (and fill
only last 10).
So, your stored procedure is better.

 

[Kevin Yu [MSFT]]

Hi Jeff,

Thank you for posting in the community!

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to get certain number of
records from the server begin with certain index. If there is anything
unclear, please feel free to let me know.

Generally, we can achieve this in two ways.

1. We can do this on the server side. We can modify the stored procedure to
make it return the result set which only contains the specified records.
This is fast, because all the jobs are done on the server side. However, as
you mentioned this is quite complicated and has to make one for each query.

2. There is an overload in SqlDataAdapter.Fill method public int
Fill(DataSet dataSet, int startRecord, int maxRecords, string srcTable);
This overload can fill a certain number of result set into DataSet from a
start index. I think this might match your requirement. However, it might
be a little slower than the first method. Here I write a code snippet for
paging:

private DataSet GetPagedRecords(int page)
{
const int iNum = 9;
int iStart = (page - 1) * iNum;
DataSet ds = new DataSet();
this.sqlDataAdapter1.Fill(ds, iStart, iNum, "Table1");
return ds;
}

Hope this helps. Does this answer your question? If anything is unclear,
please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Kevin Yu [MSFT]]

Thanks for Miha's response.

Hi Jeff,

For more information about that overload for the Fill method, please check
the following link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatacommondbdataadapterclassfilltopic6.asp

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Jeff]

Am I missing something here? I figure I must be.

In my example, I only have 1000 records, but I could eventually have 10000
or more. I do NOT want to return that many records under any circumstance.
This makes the .fill method unacceptable. I've also been told that I should
keep application logic in the application and database logic in the
database. Using the Stored Procedure in this manner goes directly against
that and is not a good solution, it just happened to give the desired
results.

With DAO and the previous ADO, I could just do a Move Next, so I could have
a loop that moved next for 500 times and then I'd start reading the next 9
records from the database.

Is there no way to accomplish this task with ADO.Net? If there isn't, I
must be missing what's so great about Ado.Net. Does Whidbey address this?

Thanks for everyone's responses so far...

 

[Kevin Yu [MSFT]]

Hi Jeff,

I think the following overload of SqlDataAdapter.Fill will meet your
require.

public int Fill(DataSet dataSet, int startRecord, int maxRecords, string
srcTable);

Actually it steps through to the start record with a loop start reading the
next 9 records from the database just as you mentioned.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Jeff]

Thank you very much. I'll give it a try.

I couldn't find an example of where I did this, and it might have been with
DAO (not ADO), but I could have sworn I was able to specify the absolute
starting point in a RecordSet, so I didn't have to do the Move.Next Loop
thing.

I know you're probably not a developer over there, but I've Googled the
Internet, and the ability to return paged data is a very desired one, and
there are a lot of examples out there that solve the problem, usually in
horrible ways.

I know I wasn't the first (or the last post) on this topic, so thanks for
your help!

-Jeff

 

[Kevin Yu [MSFT]]

Hi Jeff,

Thank you for your feedback. If you have suggestion to make our future
products better, please feel free to send mails to (e-mail address removed).
Your suggestions will be highly appreciated.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."