CODEWITHSUNDEEP
c#asp.netsqldatabase
reaper_unique
reaper_unique

Reputation: 2931

How to use PagedDataSource with SQL for paging?

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?

Upvotes: 1

Views: 4312

Answers (3)

Protector one
Protector one

Reputation: 7271

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.

Upvotes: 2

Zach Green
Zach Green

Reputation: 3460

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.

Upvotes: 2

Doozer Blake
Doozer Blake

Reputation: 7797

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);

Upvotes: 0

Related Questions