J.d. meier
table of Contents
1. Introduction 2, Question 3, Solution 4, Sample Code 5, Analysis 6, Conclusion
Introduction
The large recordset is displayed in the Active Server Pages (ASP) application, which is probably a problem you are familiar with. This article has been discussed in-depth discussion on this issue and its solutions and sample code. This sample code can be applied to your specific situation. This sample code is designed as a server-side solution, which is independent of the browser. In addition, I will point out the questions you need to consider when you design your own solution.
problem
Your query returns a large recordset. You need to provide a simple way to browse these results, that is, only the subset of results on each page. To effectively complete this job, you need to have an in-depth understanding of how ActiveX (R) data objects (ADO) and database work together.
solution
How to divide your record set into "page" instead of large-scale results? The so-called page, basically, you specify a lot of records that should be displayed together. For example, if you have 100 records in your record, you may display 10 records per page.
ADO provides two ways, PageSize and AbsolutePage. These methods allow you to specify the number of records to be displayed per page, and position the cursor on a page.
After opening the record set, the basic step is:
Specify PageSize for this recordset. It indicates the number of records to display per page. Specifies the ABSOLUTEPAGE of the recordset. This will record the pointer to the page of the page, the beginning of the page. Display the record page. To complete this step, you want to loop the entire recordset with the settings of the settings, or until the end of the file.
Sample code
The following example code illustrates the page establishment process. With it, you can build the prototype of your own solution. In your own code, make sure you want to complete the following steps:
Add an error handling. Add the limit on the number of records returned to the query. Record by condition filtration. (, For example, establish a WHERE clause). Use stored procedures or views.
Be sure to modify my sample code by changing the connection string and the SQL statement to point to your database. Since the code uses ADO constants, such as AdoSerServer, you must reference ADO TypeLibrary in your global.asa file, or include Adovbs.inc files in the ASP page. Note that Visual InterDev (R) automatically generates TypeLibrary references when you set the project reference to Microsoft ADO.
Note that there are two ways to provide a navigation bar:
Shownavbar. It provides users with a method of jumping to a specified page with a recording count (see Figure 1). To achieve this step, it uses the RecordCount and PageCount properties. Shownavbarfast. The method does not provide the ability to jump to the specified page, nor does it provide a record count, but can be retrieved by the Cachesize property (see Figure 2).
Pagethroughroughrs.asp
<% @ Language = VBScript%>
<% OPTION Explicit%>
'Make sure you reference ADO TYPELIB or use Adovbs.inc
DIM IPAGENUM, IROWSPERPAGE
Main
Sub main ()
DIM RST
DIM SSQL, SCONNSTRING
If Request.QueryString ("ipagenum") = "" ""
ipagenum = 1
Else
Ipagenum = Request.QueryString ("ipagenum")
Ipagenum = CINT (ipagenum)
END IF
IROWSPERPAGE = 10
Sconnstring = "provider = sqloledb.1; password = xyz123; user ID = Webuser;" & _
"Initial catalog = northwind; data source = mysqlserver;" & _
"network = dbmssocn;"
'The following SQL retrieves all columns from the SQL view.
'To optimize performance:
'- Use stored procedures, views, or specify columns in SELECT
'- Use the conditions that limit the return record (for example, the WHERE clause)
SSQL = "Select CategoryName, ProductName, QuantityPerUnit,"
SSQL = SSQL & "UnitsInstock, Discontinued"
SSQL = SSQL & "from [Products By Category]"
Set RST = GetRecords (Sconnstring, SSQL)
WriteTableHeader RST
WriteTableBody RST, IROWSPERPAGE, IPAGENUM
Shownavbar RST
'ShowFastnavBar method does not use Recordcount
'Or pagecount, so it retry the number of records is only equal to
'Record the number of cachesize specified.
'Showfastnavbar RST
Cleanup RST
End Sub
Function GetRecords (Sconnstring, SSQL)
DIM CNN
DIM RST
SET CNN = Server.createObject ("AdoDb.Connection")
CNN.Connectionstring = sconnstring
Nn.open
SET RST = Server.createObject ("AdoDb.Recordset")
Set Rst.ActiveConnection = CNN
'When the recordset is opened, AduseClient's CursorLocation
'Will retrieve all records.
'aduseserver allows Cachesize
Rst.cursorLocation = aduseserver
'When using server-side cursors, Cachesize
'Limit the number of rows retrieved. We will only grab the displayed
'Number of records - IROWSPERPAGE
rst.cachesize = irowsperpage
Rst.open SSQL, AdoPenStatic, AdlockReadonly
Set getRecords = RST
END FUNCTION
Sub WriteTableHeader (RST)
DIM FLD
Response.write "
" & fld.name & " b> td>" Next
Response.write " TR>" End Sub Sub WriteTableBody (RST, IROWSPAGE, IPAGENUM) DIM iLOOP DIM FLD iLoop = 1 Rst.pageSize = rowsperpage Rst.absolutePage = ipagenum 'Write the current page of the record Do While (not Rst.eof) and (iLoop <= IROWSPARPAGE) Response.write " |
" & fld.value & " td>"
NEXT iLOOP = iLOOP 1 Rst.movenext Response.write " TR>" Loop Response.write " TABLE>" End Sub Sub Shownavbar (RST) DIM ipagecount DIM iLOOP DIM SSCRIPTNAME 'This release provides a richer user navigation, but 'Depends on RecordCount and PageCount, 'It offsets the server-side game 'Specify the benefits of cachesize. Response.Write " SScriptName = Request.ServerVariables ("script_name") IF ipagenum> 1 THEN Response.write " << Previous a> END IF ipageCount = RST.PAGECOUNT DO Until iLoop> ipagecount f iLoop = ipagenum kil Response.write " & CSTR (iLoop) &" b> " Else Response.write " " END IF iLOOP = iLOOP 1 Loop IF not rst.eof then Response.write " Next >> Response.write " END IF Response.write "Page" & ipagenum & "of" & ipagecount " Response.write Rst.Recordcount & "Records" notes? End Sub Sub Showfastnavbar (RST) DIM ipagecount DIM iLOOP DIM SSCRIPTNAME 'When specifying cachesize and using server-side cursors, 'This method is particularly effective because it does not use Recordcount 'And PageCount. Users need to have experience. Response.Write " SScriptName = Request.ServerVariables ("script_name") IF ipagenum> 1 THEN Response.write " << Previous a> END IF IF not rst.eof then Response.write " Next >> Else Response.write " END IF Response.write "Page" & ipagenum End Sub SUB Cleanup (RST) IF not rst is nothing then IF Rst.State = AdstateOpen Then Rst.Close SET RST = Nothing END IF End Sub Script> analysis Several questions you need to pay attention to design paging solutions: Cursor positioning problem. If you use a client cursor, you will read all records each time you open the record set. Therefore, since all records are read, they will be quickly accessed after accessing the RecordCount or pageCount property. If you use the server-side cursor, you will only retrieve the required records. You can specify a number of records to read with a Cachesize property to improve performance. However, if you use the server-side cursor, and the RecordCount or PageCount property, all records will be read, and the performance is not improved. You must compromise between user interfaces with more information and more navigating, with the performance of all records. When using the server-side cursor, the CURSORTYPE attribute must be AdoPenStatic or AdoPenKeySet to use paging. Page is not always the best user page. It may only be applied to users from searching results or browse the product catalog. Try the record classification to make the more relevant records appear in the previous page (for example, using SQL ORDER BY clauses). There are so many users can do. Just retrieve the columns you need to display (ie, avoid select *). Only records you need to display are retrieved. Make sure the filter is filtered (ie, using the WHERE clause). Here are some tips to keep in mind: Pack your logic in the method. How to use the logic and data access logic, which simplifies the work of loading the code into a Windows script component, Visual Basic script editing (VBScript) class or component. The change is easier, and the code maintenance is also improved. Testing and debugging is also improved due to the invision and cancellation of the annotation method. The Typelibrary referenced by Ado is a better solution with the ADOVBS.INC. This is because the ASP is to read the entire file in memory when processing the included file, not only the part they need. in conclusion Page is a general technology, many web applications use it to provide a good way to browse a lot of records. When designing a page solution, you need to consider some problems, such as how to retrieve records, what type of user navigation needs to be provided. Although the best solution depends on your specific application, the technology in this article will help you make better design decisions.
转载请注明原文地址:https://www.9cbs.com/read-125108.html New Post(0)
|