Pagination | data, let me say a few words about this article. This is isn't a entirely original article but a asp.net adaptation of the ASP article that I ' ve posted a few months ago. Original article can be found Here:ado recordsets paging in ASP.
The purpose of this article are to show you 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 your need a better control over your own custom presentation of data and O F navigation provided to your users. This is why you are not the 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 are somewhat limiting in the way of presentation The Data and page navigation links. Beside that, there are plenty of the samples and articles on "How" to "use" new asp.net capabilities by the other written.
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 does 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 would grow accordingly. That leaves me as as a anyone with the similar application requirements no other choice, but to develop some kind of al Gorithm to display records in the smaller chunks-pages.
The Everyone is familiar with the way search results are displayed by Internet search engines. You get the "a" page of results that are limited to some number of records (for example) and some links To go to the "the", previous, Next or the last page. Some sites give the ability to go directly to specific page number, Some use a mixture of both.
So how do does one implements data paging mechanism with asp.net? Specifically, how do we implement the record paging using ado.net?
Let's pretend that we have a database with the table called Tblitem this is used to store information about our Items (WHA Tever they are?). Let me also imagine this one of the fields in Tblitem called ItemName. We are given a task of creating a set of pages to give a user a ability to search for the the items by the ItemName field. We decided to make a set of two pages. One page would display the search form and one for the results of the search.
Please excuse me, but I'll skip all the variable declarations and HTML formatting.
The page should is 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 being 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 a ado.net DataSet with the This items that contain our keyword in their itemname.
We create a SQL statement that'll do the search:
SQL = "SELECT * from Tblitem WHERE itemname like '%" & Keyword.replace ("'", "'") & "%"
Notice that I ' ve used the Replace function to double a quotes in the search string. Without it if user enters a single quote in his/her Keyword you'll 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 the above lines of Code:first we construct new OleDbConnection object using our connection str Ing. 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 have a reference to the "the" the "the" the "the" "The" "represents" our data and retrieve the number of records (row s) 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" for us to display the records we need To figure some things out. I also it is better to create and display navigation links on the top of the page before the results.
At I-figure out couple of things:do we have no results from our search and if so many pages of R Esults do we have?
Presence of the results is easily determent by checking record count (notice we don't have EOF property anymore):
If nreccount = 0 Then
... ' Clean up
... ' Do the no results HTML here
Response.Write ("No Items found.")
Response.End
... ' Done
End If
The number of pages we have is obviously depends on the number of items we want to display per page. Ado.net does not have all those cool properties, we ' ve come to like and use in ADO. PageSize, PageCount, AbsolutePage properties of ADO Recordset object are not available to us anymore. We'll have to resort to some very simple calculations into order to determent number of pages of data we have at hand.
Npagecount = nreccount \ Records_per_page
If nreccount Mod records_per_page > 0 Then
Npagecount + 1
End If
Now we are need to talk about the current page number. Since We want this page (results.aspx) to is able to display any one of the pages of results we have to have a way to spec Ify which page would the user. We'll do it by passing a additional parameter to my results.aspx script that we'll call "Page". So "link to" our page could look like this:
Http://..../results.aspx?Keyword=BlahBlahBlah&Page=3
With this said we can figure out the number of the page this is requested by the user by simple checking page parameter:
Npage = Convert.ToInt32 (Request.QueryString ("Page"))
We need to make sure this number of page requested by user is within the acceptable range and fix it if needed:
If Npage < 1 Or npage > Npagecount Then
Npage = 1
End If
Now we can create my navigation links by simply linking to this page with the same value for Keyword and the specific E number. For example:
' Page
Response.Write ("<a href=") results.aspx? Keyword= "& Keyword &" &page=1 ">first page</a>")
' Previous page
Response.Write ("<a href=") results.aspx? Keyword= "& Keyword &" &page= "& (NPage-1). ToString () & "" ">prev. page</a>")
' Next page
Response.Write ("<a href=") results.aspx? Keyword= "& Keyword &" &page= "& (Npage + 1). ToString () & "" ">next page</a>")
' Last page
Response.Write ("<a href=") results.aspx? Keyword= "& Keyword &" &page= "& Npagecount.tostring () &" ">last page</a>"
' 15th page
Response.Write ("<a href=") results.aspx? Keyword= "& Keyword &" &page=15 ">15th page</a>")
There is a alternative way of providing navigational links provided in my sample script.
3. Display a page of results
All we have are to be to display a page of results.
Before we do so though we need to find out what are the indexes of the starting and ending rows (records) of this page a Re
Nstart = Records_per_page * (nPage-1)
Nend = Nstart + records_per_page-1
If nend > NRecCount-1 Then
Nend = nRecCount-1
End If
Now we are ready to output a page of records. In contrast to ADO, with ado.net we should is not a while loop. We already know the indexes of records we want to show, so for loop would work just great:
For i = Nstart to Nend
Response.Write (DT. Rows (i) ("ItemName") & "<br>")
Next
That is it. Sample that I have included are a bit more complex, because I ' ve combined both HTML search form and displaying of the Results in one page. This is why there is a additional Mode parameter being used in every link. All the code concerning displaying of the results of the search are in the Showresults () function.
