极光下载站 - 打造齐全的绿色软件家园

极光下载站

当前位置: 首页 技术开发 NET专区 关于数据分页(转自www.codeproject.com)

关于数据分页(转自www.codeproject.com)

时间:2006/7/19 10:56:02 作者:佚名 人气:272

First of all, let me say a few words about this article. This is not an entirely original article but an ASP.NET adaptation of the ASP article that I’ve posted a few months ago. Original article can be found here: ADO Recordset Paging in ASP.

The purpose of this article is to show how to implement data paging with ADO.NET. It is not a generic ASP.NET or even ADO.NET tutorial. The article looks at the very specific case when you need a better control over your own custom presentation of data and of navigation provided to your users. This is why you will not see any WebForms or WebControls here. I did not use new DataGrid control even though it comes with the data paging capabilities. DataGrid control is very powerful and has a lot of useful features, but it is somewhat limiting in the way of presentation of data and page navigation links. Beside that, there are plenty of samples and articles on how to use new ASP.NET capabilities written by other people.

Every once in a while I come across the task of displaying a large number of records on the web. The good example is displaying the results of a search. Most of the time I do not know the number of records that I have to display in advance. In addition to this, as the usage of the application growth the size of the database will grow accordingly. That leaves me as well as anyone with the similar application requirements no other choice, but to develop some kind of algorithm to display records in the smaller chunks - pages.


Everyone is familiar with the way search results are displayed by Internet search engines. You get the first page of results that are limited to some number of records (20 for example) and some navigational links to go to the first, previous, next or the last page. Some sites give you the ability to go directly to specific page number, some use a mixture of both.

So how does one implements data paging mechanism with ASP.NET? Specifically, how do we implement record paging using ADO.NET?

Let’s pretend that we have a database with the table called tblItem that is used to store information about our Items (whatever they are?). Let me also imagine that one of the fields in tblItem called ItemName. We are given a task of creating a set of pages to give a user an ability to search for the items by the ItemName field. We decided to make a set of two pages. One page will display the search form and one for the results of the search.

Please excuse me, but I will skip all the variable declarations and HTML formatting.

First page should be easy. It’s a standard HTML form that could look something similar to this:

...
<FORM ACTION="results.asp" METHOD="GET">
Item Name: <INPUT TYPE="text" NAME="Keyword"> <INPUT TYPE="submit" VALUE=" Find ">
</FORM>
...

Second page is where all the magic should happen. This is what the second page (results.aspx) should be able to do:
1. Receive the Keyword that user have entered.
2. Search the database for records containing Keyword.
3. Display a page of resulting records.
4. Provide user with some navigation links to display more pages of results if needed.


1. Receive Keyword
Receiving the Keyword is as easy as:

Keyword = Request.QueryString("Keyword").Trim()

2. Search the database and retrieve data.
Now we have everything we need to get an ADO.NET DataSet with the items that contain our keyword in their ItemName.

First we create a sql statement that will do the search:

SQL = "SELECT * FROM tblItem WHERE ItemName LIKE '%" & Keyword.Replace("'", "''") & "%'"

Notice that I’ve used Replace function to double single quotes in the search string. Without it if user enters a single quote in his/her Keyword you will receive an error.

Let's try to open a database connection and get the data:

Try
odConn = New OleDbConnection(strConn)
odAdapt = New OleDbDataAdapter(SQL, odConn)
DS = New DataSet
odAdapt.Fill(DS)

' Get our DataTable
DT = DS.Tables(0)

' Get record count
nRecCount = DT.Rows.Count

Catch e As Exception
Response.Write("Error: <b>" & e.Message & "</b><p>")
nRecCount = 0
End Try

This is what’s going on in the above lines of code: First we construct new OleDbConnection object using our connection string. We create OleDbDataAdapter next providing our SQL statement and reference to the connection object. After creating new DataSet object we instruct our DataAdapter to populate (fill) DataSet with the data out of our database. Then we get a reference to the first table in the DataSet that represents our data and retrieve the number of records (rows) returned in that table. Try and Catch are obviously there to try and catch any errors during these database operations.

4. Navigation Links
Yes it is a fourth step. I did leave the third step (displaying of the results) for the last because in order for us to display the records we need to figure some things out. I also think it is better to create and display navigation links on the top of the page before the results.

At this point we have to figure out couple of things: do we have any results from our search and if so how many pages of results do we have?

Presence of the results is easily determent by checking record count (