From a programmer's point of view, one of the biggest defects of the Microsoft SQL Server? The number of rows returned is usually much more than the number of rows that can be accommodated more than the user interface. This kind of embarrassment often traps the developer in a dilemma. Developers should create a very long page that allows users to spend time to scroll browsing, or should you better solve this problem by setting up a manual paging mechanism?
Which solution is better, to a large extent, depending on the characteristics of the data to be retrieved. A longer list consisting of multiple items (such as search results), it is best to pass the size of each page, equal to a relatively short multiple page of each page. A longer list consisting of a single item (such as the article), if the entire insert is in a page, it is more convenient to use. The final analysis result is that the decision should be made according to the overall use of the application. So how do Microsoft ASP.NET solves the data paging problem?
ASP.NET provides powerful data binding controls to format the query results to HTML tags. However, only one control (ie, DataGrid control) is originally supported in these data binding controls. Other controls (such as DataList, Repeater or CheckBoxList) do not support paging. These controls and other list controls do not support paging, not because they do not support paging in the structure, but because they differ from DataGrid, no specific code for processing paging is not included. However, the code for processing paging is quite sampleized, and can be added to all of these controls.
SCOTT MITCHELL introduces DataGrid pagination in the article "CREATING A pageable, Sortable DataGrid". This article also references other useful information on the Web to provide you with the basics and other information about grid paging. You can view this article if you want to see how to make the DataList control. This article demonstrates how to create a custom DataList control with the current index and page size attribute, and you can start the page change event.
The same code can also be used to meet the pagination needs of other list controls such as Listbox and CheckBoxList. However, adding paging functions to each control is actually a very good practice, because, as mentioned above, the paging code is quite sampled. Therefore, for smart programmers, what is the way to implement all of these controls for all these controls better than using a new general paging program control?
A paging program control will be established in this article, which will enable the partner list control to paginate the query results of SQL Server. This control is called SQLPAGER, which supports two types of partner controls - list controls and underlying data list controls.
Significant features of the SQLPAger control
The SQLPAGER control is an ASP.NET composite control that contains a single line table. The line includes two cells-navigation bars and page descriptors. The user interface of the control is strip, ideally, the width is the same as the width of the partner control. The navigation section provides a clickable element to move between the page; the page descriptor section provides users with some feedback information about the currently displayed page.
Figure 1: SQLPAGER controls displayed in the Visual Studio .NET web designer
Like the embedded paging program with the DataGrid control, the SQLPAger control has two navigation modes, which will be next page / previous page and a digital page. In addition, its special attributes Pagerstyle enables you to choose more convenient styles. This control works with the list control. You can specify a such partner control with the ControlTopaginate string property to the paging program. SQLPAGER1.CONTROLTOPAGINATE = "ListBox1";
In general, the paging program first gets the query result of SQL Server, prepares an appropriate record page, and then displays the page through the partner control DataSource property. When the user clicks to view the new page, the paging program retrieves the requested data and displays the data again through the partner control. The paging mechanism is completely transparent to the list control. The data source of the list control is updated by programming, and only the records suitable for the current page are included.
The paging engine of the control has multiple public properties, such as CurrentPageIndex, ItemsPr, and PageCount, through these properties to get the index of the current page, the size of each page, and the total number of pages to display. Paging programs Manage data retrieval and any logic required for paging.
SelectCommand Property Set the command text used by the data. The Connectionstring property defines the name and location of the database and the connection credentials. The way to perform the query depends on the value of the PagingMode property. The possible value of this property is the value of the PagingMode enumeration with its same name - Cached and Noncached. If you select the Cached option, you will search the entire result set using the data adapter and the DataTable object. You can choose to place the result set in the Cache object of the ASP.NET, which can be reused until expired. If you select the Noncached option, the query only retrieves the record that is suitable for the current page. At this time, no data is placed in the cache of ASP.NET. The Noncached mode is almost the same as the custom paging mode of the DataGrid control.
The following table lists all programming interfaces of the SQLPAger control.
Table 1: Programming interface for SQLPAger controls
Name type description
The cacheduration property indicates the number of seconds that the data is retained in the ASP.NET cache. Only used for Cached mode. The default is 60.
The Connectionstring property is used to access the connection string of the selected SQL Server database.
The ControlTopaginate property is the same .aspx page control ID, which will display the recording page retrieved by the paging program. This is a partner control.
The CurrentPageIndex property is obtained and sets 0-based page index.
The itemsperpage property gets and sets the number of records to be displayed per page. The default is 10 items per page.
Pagerstyle Attribute This value indicates the style of the paging program user interface. It can enumerate values for Pagerstyle: NEXTPREV and NUMERICPAGES. In the NextPrev mode, the VCR button will be displayed to the first page, the next page, the next page and the last page. In the NumericPages mode, a drop-down list is displayed to list the index of all available pages.
PagingMode property This value indicates how to retrieve data. It can be enumerated for the PagingMode: Cached and Noncached. If you are cached, the data adapter will be used, and the entire result set will be temporarily placed in the ASP.NET cache. If you are Noncached, you only retrieve the records in the current page. In this case, the cache is not performed.
The selectcommand property is used to check the text of the query. It is best to select-from-wherere. The ORDER BY clause is not supported. Sort is additionally specified by the sortfield property. The name of the Sortfield property is used to sort the fields. This field is used to provide dynamic order by clauses for query. Sort is performed by SQL Server.
The ClearCache method deletes any data stored in the ASP.NET cache.
PageIndexchanged event The default event occurs when the paging program moves to another page. The data structure of the event is the PageChangeDeventargs class, which contains an index of the old page and a new page.
Since the SQLPAger control inherits WebControl, it also has a lot of properties related to the UI to manage fonts, borders, and colors.
Generate SQLPAGER controls
The SQLPAGER control will be generated as a composite control and allow it to inherit the WebControl class. The composite control is unique to the ASP.NET server control, which consists of one or more server controls.
Public Class Sqlpager: WebControl, InamingContainer
{...}
Most of the time is actually generating composite controls unless a fully customized control or existing control is generated. To create SQLPAGER, combine a Table control, and combine several linkbutton controls or a DROPDOWNLIST control based on the style of paging programs.
When you generate a composite control, you need to pay attention to several principles. First, you need to override the CreateChildControls protected method. The CreateChildControls method is inherited from Control. This method will be called when the server control is to display or after returning or after returns.
Protected Override Void CreateChildControls ()
{
/ / Clear existing child controls and their viewState
Controls.clear ();
ClearChildViewState ();
/ / Generate control tree
BuildControlhiRchy ();
}
When you override this method, you need to perform several important operations. Create and initialize any required sub-control instances and add them to the Controls collection of the parent control. However, before generating a new control tree, you should delete any existing sub-controls and clear any ViewState information that the child control may be left.
Composite components also need to implement InamingContainer interfaces so that ASP.NET can be created for it to create a new naming range. This ensures that all controls in the composite control have a unique name. This will also ensure that the return data of the sub-control can be automatically processed.
It is important to become a naming container for the SQLPAger control. In fact, SQLPager contains some linkbutton controls and needs to get and process them to click on the event to navigate the page. As with any other controls in the ASP.NET page, LinkButton is also given an ID that identifies the control of the process returns.
When processing returns an event, there is a match relationship between the target ID of the event to find the target ID of the event and the main form of the host. LinkButton is a sub-control of the paging program, so the code of its server side cannot be run. Does this mean that only the form's direct child control can start and handle server events? Of course, as long as you use a named container.
Change the actual ID of the Embedded Link button to "SQLPAGER1: First" by changing the SQLPAger control implementation in the InamingContainer interface. When the user clicks to view the new page, the return event will be SQLPAGER1: FIRST as the target control. In fact, the algorithm used to identify the target control is more complex than the above. When running, the name of the event destination control is as a string separated by a colon. In fact, this match is done between the sub-control of the form and the first tag that is separated by a colon (such as SQLPAGER1: FIRST). Since the paging program is a sub-control of the form, it will be successful when matching, and the page program acquires a clicked event. If you think this explanation is not enough or puzzled, just download the source code for the SQLPAger control, delete the InamingContainer tag interface and re-compiling. You will see the paging program to return, but cannot process the incident internally. The InamingContainer interface is a tag interface that does not have a method, which acts simply specify the name in the class declaration without any other operation.
Another important aspect of the composite control is that they usually do not need to be customized. The display of the composite control follows the display of its constituent control. When generating a composite control, there is usually no need to overwrite the Render method.
The SQLPAGER tree of the control consists of a single-line table containing two cells. This table inherits most of the visual settings of the paging program, such as foreground color and background color, border, font information, and width. The first cell contains a navigation bar whose structure depends on the value of the PagerStyle property. If the style of the page program is nextprev, the navigation strip consists of four VCR's buttons. Otherwise, it will consist of a drop-down list.
Private vid buildcontrolhirarchy ()
{
/ / Generate a environment table (one line, two cells)
Table t = new table ();
t.font.name = this.font.name;
T.FONT.SIZE = this.font.size;
T. Borderstyle = this.borderstyle;
T. Borderwidth = this.borderwidth;
T. BorderColor = this.bordercolor;
T.WIDTH = this.width;
T.height = this.height;
T.backcolor = this.backcolor;
T. productcolor = this.Forecolor;
/ / Generate the line in the table
TableRow Row = New TableRow ();
T.ROWS.ADD (ROW);
/ / Generate a cell with a navigation bar
Tablecell Cellnavbar = New TableCell ();
IF (PagersTyle == this.pagerstyle.nextprev)
BuildnextPrevui (Cellnavbar);
Else
BuildNumericPageSui (Cellnavbar);
Row.cells.Add (Cellnavbar);
/ / Generate a cell with a page index
Tablecell CellPageDesc = New TableCell ();
CellpageDesc.horizontalalign = horizontalalign.right;
BuildCurrentPage (CellPageDesc);
Row.cells.Add (CellPageDesc);
// Add the form to the control tree
This.Controls.Add (t);
}
Adding individual controls to the correct Controls collection is extremely important to the correct display of the paging program. The outermost table must be added to the Controls collection of the paging program. The link button and drop-down list must be added to the Controls collection of the corresponding table cell. The code used to generate a link button navigation bar will be given below. Each button is displayed with a webdings character, which can be disabled as needed and bound to the internal Click event handler.
Private Void BuildnextPrevui (TableCell Cell)
{
Bool isvalidpage = (CurrentPageIndex> = 0) &&
CurrentPageIndex <= Totalpages-1);
Bool canmoveback = (CurrentPageIndex> 0);
Bool canmoveforward = (CurrentPageIndex / / Show << button LinkButton first = new linkbutton (); First.id = "first"; First.Click = New EventHandler (First_Click); First.font.name = "Webdings"; First.font.size = fontunit.medium; First.forecolor = forecolor; First.Tooltip = "First"; First.Text = "7"; First.enabled = isvalidpage && canmoveback; Cell.Controls.Add (first); : } Another style of the paging program (listed in the drop-down list) is shown below: Private void BuildNumericPageSui (TableCell Cell) { / / Display a drop-down list DropdownList Pagelist = New DropDownList (); Pagelist.id = "pagelist"; Pagelist.autopostback = true; Pagelist.selectedIndexchanged = New EventHandler (PageList_Click); Pagelist.font.name = this.font.name; Pagelist.font.size = font.size; Pagelist.forecolor = forecolor; IF (Totalpages <= 0 || CurrentPageIndex == -1) { Pagelist.Items.Add ("No Pages"); Pagelist.enabled = false; Pagelist.selectedIndex = 0; } Else // Plip list { For (int i = 1; i <= Totalpages; i ) { ListItem item = new ListItem (i.tostring (), (i-1) .tostring ()); Pagelist.Items.Add (item); } Pagelist.selectedIndex = CurrentPageIndex; } } All event handlers (Click and SelectedIndexchange "will eventually change the currently displayed pages. Both methods call a common private Private method. Private void first_click (Object Sender, Eventargs E) { Gotopage (0); } Private void Pagelist_Click (Object Sender, Eventargs E) { DropDownList Pagelist = (DropDownList) Sender; INT pageIndex = Convert.Toint32 (Pagelist.SelectedItem.Value); Gotopage (PageIndex); } Private void gotopage (int pageIndex) { // Prepare event data PageChangeDeventargs e = new pagechangedeventArgs (); E.OLDPAGEINDEX = CURRENTPAGEINDEX; E.NewpageIndex = pageIndex; / / Update the current index CurrentPageIndex = PageIndex; // Start the page change event OnPageIndexchanged (E); // Bind new data DataBind (); } The processor of other navigation buttons and first_click is only different from the page numbers that they pass to the GotoPage method. The Gotopage method is responsible for handling the pageIndexchanged event and responsible for starting the data binding process. It prepares event data (old pages and new page indexes) and triggers events. Gotopage is defined as Private, but you can change the displayed page by programming using the CurrentPageIndex property. Public Int CurrentPageIndex { Get {return control.Toint32 ("currentpageindex");} Set {ViewState ["CurrentPageIndex"] = Value; } Like all properties listed in Table 1, the implementation method of the currentPageIndex property is also quite simple. It saves its content to ViewState and restores it. During the data binding process, the page index will be verified.
