asp.net include pagination in dynamically generated table

Question

I have existing client code wherein sql data reader is being used to pull records from a database and in code behind, table content is being generated as string and then assigned to a asp:literal element on the page.

Dim myreader As SqlDataReader = selectcmd.ExecuteReader

If myreader.HasRows Then
str = str & "" // str is string

While myreader.Read()
If Not IsDBNull(myreader("column1")) Then
  str = str & "<tr><td align=left><font color=white>" & myreader("column1") & "</font></td></tr>"
End If
End While

// in the end string is assigned to Text attribute of asp:literal element

The client wants that pagination should be used, and that only 10 rows should be visible at a time and rest should be viewed via paging.

Can anyone suggest how this can be achieved.

Thanks.

Answer 1

Most of the time doing this sort of HTML concatenation on the server side is a very bad idea.

For your scenario, you could possibly use a SQLDataSource to feed the data to a gridview and just enable support for paging & sorting. You wouldn't have to write a single line of code to make it work, besides declaring your data source on the markup.

There's an excellent tutorial here.

If that doesn't cut it for you, you'll have to implement paging yourself by asking the database to return exactly the number of rows you want based on the current page number. In general, you'll need to use ROW_NUMBER() and TOP to page the results on the database side.

Here's an example on how to do paging on the database side.