1

I don't understand how you can only get the number of needed rows from a database when you need to know the total amount of rows inorder to get paging to work.

What I mean is: I followed this example: http://www.c-sharpcorner.com/uploadfile/rizwan328/datalist-custom-paging-in-Asp-Net-using-C-Sharp/

But instead of using a dataTable I have a database.

I get the news from the database like this, this gets all the news from my news table:

List<News> news = News.GetNews();

public static List<News> GetNews(){
    List<News> news = new List<News>();
    using (SqlConnection conn = new SqlConnection(CONNSTRING)) {
            SqlCommand cmd = new SqlCommand("SELECT * FROM News_news ORDER BY date DESC", conn);

That gets all the data from all the news items.

Then I create a PagedDataSource:

PagedDataSource objPds = new PagedDataSource();
objPds.DataSource = news;
objPds.AllowPaging = true;
objPds.PageSize = numRows;
//Set the current page 
objPds.CurrentPageIndex = CurrentPage;

//Set the buttons

Putt the data in a repeater that will show the first 5 items on the aspx page

repeater1.DataSource = objPds;
repeater1.DataBind();

Like this I have a paged front page that shows me the total number of pages and the current page and when I click on the button for the next page I do it all over again.

But I know this isn't correct since I always get all the news and then discared the news I don't need. but please, how do need to do it so that I only get the needed news items?

Do I need to first count the number of rows in my table?

reaper_unique
  • 2,916
  • 3
  • 28
  • 48

3 Answers3

2

To use PagedDataSource, all records will be returned during the query, but you can use persistent data storage (Session and ViewState) to store the data set so that each page request does not have to hit the database again.

If you want to only ever pull back the records for the page you are viewing, as far as I know you will have to roll your own paging solution. I ran in to the need for my own paging solution a while back, and here is a SO question I asked that helped me do what I needed, Paging choice, on database or in the web application.

Community
  • 1
  • 1
Zach Green
  • 3,421
  • 4
  • 29
  • 32
2

You can use custom paging by setting VirtualItemCount to the total number of items and AllowCustomPaging to true. That way you can retrieve just those sets of records from your data source that your PagedDataSource needs during a particular postback.

Protector one
  • 6,926
  • 5
  • 62
  • 86
0

You don't specifically need to know the number of results to have PagedDataSource work. It will figure that out for you given the DataSource.

In your sample above, you aren't specifically setting the PageSize, so you need to add

objPds.PageSize = 5;

Given your example though, you might consider just changing or adding to GetNews to add TOP to the query, as it doesn't appear you really need paging. What you need is just the first 5 results (or at least, that's how I'm reading it).

public static List<News> GetNews(int Top){
    //OTher stuff here.
    SqlCommand cmd = new SqlCommand(String.Format("SELECT TOP {0} * FROM News_news ORDER BY date DESC", Top ), conn);
}

Then call the following and you can get rid of PagedDataSource completely.

List<News> news = News.GetNews(5);
Doozer Blake
  • 7,677
  • 2
  • 29
  • 40
  • objPds.PageSize = numRows; numRows is defined in the beginning as 5 ;) The problem is that I know how to get the top 5 elements and even how to use row_numbers, but not how properly implement it with PagedDataSource because that needs the total amount of elements. So I'm wonder how I can correctly implement pagedDataSource so that I don't have to go the database all the time. objPds.DataSource doesn't accept an int or something, does it? – reaper_unique Nov 24 '11 at 11:18
  • @reaper_unique I can't believe I completely glossed over that. I apologize. DataSource won't take an int, no. It accepts anything that implements IEnumerable. You'll have to reload DataSource on each load, and it has to be the same DataSource each page load for the paging to work correctly. You could save your data in the application cache if you don't want to hit the DB on each load. – Doozer Blake Nov 24 '11 at 13:10