How do I implement paging please? - DataReader Object

Hi, I am a beginner in ASP.NET 2.0

I am creating a simple blog website.

I am using a DataReader object to collect all of the entries in the database (in one full sweep) and then display them by adding rows to a table and populating them.

I would like to enable paging so that only 5 blog entries at a time are displayed. The user should be able to page backwards and forwards 5 at a time.

Please can somebody give me some ideas of how I should do this?

Should I read all of the data into some kind of list so that all of the data is then readily available? An arraylist or something?

I realise that the DataReader object reads in one continuous stream so I couldn't expect it to read backwards and forwards from the database.

I include a portion of my code below which reads the database.

Many thanks.

Dim objCommand As New SqlCommand
objCommand.Connection = objConnection
objConnection.Open()
objCommand.CommandText = "SELECT BlogID, Date, Blog FROM BlogSite2_Blogs ORDER BY Date DESC"

Dim objReader As SqlDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)

Dim dateDateTime As Date
Dim strBlogEntry As String
Dim intBlogID As Integer

Do While objReader.Read()
intBlogID = objReader.GetValue(0)
dateDateTime = objReader.GetDateTime(1)
strBlogEntry = objReader.GetString(2)

CreateBlogRow(intBlogID, dateDateTime, strBlogEntry)
Loop

objReader.Close()

check this link

http://weblogs.asp.net/scottgu/archive/2006/01/07/434787.aspx

|||

Hi

I would do it in Database with stored procedure.

In the SP described bellow you Just need to pass in a short SQL statement, the Order By clause, and the start row and end row you'd like to return in the resultset.

CREATE PROCEDURE ReturnPage(@.Select varchar(1000), @.OrderByvarchar(1000), @.StartRowint, @.EndRowint)ASBEGIN declare @.ColListvarchar(2000);declare @.Where varchar(2000);declare @.iint;declare @.i2int;declare @.tmpvarchar(1000);declare @.decvarchar(1000);declare @.fvarchar(100);declare @.dvarchar(100);declare @.Symbolchar(2);declare @.SQLvarchar(5000);declare @.Sortvarchar(1000);set @.Sort = @.OrderBy +', 'set @.dec =''set @.Where =''set @.SQL =''set @.i = charindex(',' , @.Sort)while @.i != 0begin set @.tmp =left(@.Sort,@.i-1)set @.i2 = charindex(' ', @.tmp)set @.f =ltrim(rtrim(left(@.tmp,@.i2-1)))set @.d =ltrim(rtrim(substring(@.tmp,@.i2+1,100)))set @.Sort =rtrim(ltrim(substring(@.Sort,@.i+1,100)))set @.i = charindex(',', @.Sort)set @.symbol =casewhen @.d ='ASC'then'>'else'<'end +casewhen @.i=0then'='else''end set @.dec = @.dec +'declare @.' + @.f +' sql_variant; 'set @.ColList =isnull(replace(replace(@.colList,'>','='),'<','=') +' and ','') + @.f + @.Symbol +' @.' + @.fset @.Where = @.Where +' OR (' + @.ColList +') 'set @.SQL = @.SQL +', @.' + @.f +'= ' + @.fend set @.SQL = @.dec +' ' +'SET ROWCOUNT ' +convert(varchar(10), @.StartRow) +'; ' +'SELECT ' +substring(@.SQL,3,7000) +' from (' + @.Select +') a ORDER BY ' + @.OrderBy +'; ' +'SET ROWCOUNT ' +convert(varchar(10), 1 + @.EndRow - @.StartRow) +'; ' +'select * from (' + @.Select +') a WHERE ' +substring(@.Where,4,7000) +' ORDER BY ' + @.OrderBy +'; SET ROWCOUNT 0;'exec(@.SQL)END

Pls refer toEfficient and DYNAMIC Server-Side Paging with T-SQLfor details.

|||

Thank you very much indeed for this Young Fang. I don't understand it all but I shall have a good read and no doubt, I shall learn a lot of new things :)