. Net tool-use the dynamic soft code generator to quickly generate a pagination GridView

Although the pagination function is provided in the GridView, we are still used to retrieving the required data. The AspNetPager control is used here and is free of charge. Of course it is not an advertisement, it is also suitable for your own use after experiment with a lot of controls, combined with the code generator can quickly generate pagination GridView

AspNetPager official site: Download to dll and source code
Http://www.webdiyer.com/AspNetPager/default.aspx

The paging storage process is as follows: slightly modified according to tie Quan's code

[Copy to clipboard]

CODE:

USE [×× database name]
GO
/***** Object: StoredProcedure [dbo]. [GetRecordFromPage] script Date: 03/18/2008 13:35:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ===================================================== ======
/*
Function Name: GetRecordFromPage
Function: obtains data on a specified page.
Parameter description: @ tblName indicates the name of the table containing data.
@ FldName key field name
@ PageSize number of records per page
@ PageIndex: page number to be obtained
@ OrderType: Sorting type. Values: 0 (ascending) and 1 (descending ).
@ StrWhere query condition (Note: Do not add where)
Prepared by: Tie Quan
Created:
Modification time:
*/
-- ===================================================== ======
Create procedure [dbo]. [GetRecordFromPage]
@ TblName varchar (255), -- table name
@ FldName varchar (255), -- field name
@ PageSize int = 10, -- page size
@ PageIndex int = 1, -- page number
@ IsReCount bit = 0, -- returns the total number of records. If the value is not 0, the system returns
@ OrderType bit = 0, -- set the sorting type. If the value is not 0, the sorting type is descending.
@ StrWhere varchar (2000) = ''-- Query condition (Note: Do not add where)
AS

Declare @ strSQL varchar (6000) -- subject sentence
Declare @ strTmp varchar (1000) -- Temporary Variable
Declare @ strOrder varchar (500) -- sort type

If @ OrderType! = 0
Begin
Set @ strTmp = '<(select min'
Set @ strOrder = 'order by ['+ @ fldName +'] desc'
End
Else
Begin
Set @ strTmp = '> (select max'
Set @ strOrder = 'order by ['+ @ fldName +'] asc'
End

Set @ strSQL = 'select top '+ str (@ PageSize) +' * from ['
+ @ TblName + '] where [' + @ fldName + ']' + @ strTmp + '(['
+ @ FldName + ']) from (select top' + str (@ PageIndex-1) * @ PageSize) + '['
+ @ FldName + '] from [' + @ tblName + ']' + @ strOrder + ') as tblTmp )'
+ @ StrOrder

If @ strWhere! =''
Set @ strSQL = 'select top '+ str (@ PageSize) +' * from ['
+ @ TblName + '] where [' + @ fldName + ']' + @ strTmp + '(['
+ @ FldName + ']) from (select top' + str (@ PageIndex-1) * @ PageSize) + '['
+ @ FldName + '] from [' + @ tblName + '] where (' + @ strWhere + ')'
+ @ StrOrder + ') as tblTmp) and (' + @ strWhere + ')' + @ strOrder

If @ PageIndex = 1
Begin
Set @ strTmp =''
If @ strWhere! =''
Set @ strTmp = 'where ('+ @ strWhere + ')'

Set @ strSQL = 'select top '+ str (@ PageSize) +' * from ['
+ @ TblName + ']' + @ strTmp + ''+ @ strOrder
End

If @ IsReCount! = 0
Set @ strSQL = 'select count (*) as Total from ['+ @ tblName +'] '+ 'where' + @ strWhere

Exec (@ strSQL)

Steps:
1. The paging code in the DAL layer is as follows:

[Copy to clipboard]

CODE:

///
/// Retrieve the data list by PAGE
///
/// Number of pages per page
/// Index of the current page
/// Query string
/// Set the sorting type. If the value is not 0, the sorting type is in descending order.
///
Public DataSet GetList (int PageSize, int PageIndex, string strWhere, string OrderType)
{
SqlParameter [] parameters = {
New SqlParameter ("@ tblName", SqlDbType. VarChar, 255 ),
New SqlParameter ("@ fldName", SqlDbType. VarChar, 255 ),
New SqlParameter ("@ PageSize", SqlDbType. Int ),
New SqlParameter ("@ PageIndex", SqlDbType. Int ),
New SqlParameter ("@ IsReCount", SqlDbType. Bit ),
New SqlParameter ("@ OrderType", SqlDbType. Bit ),
New SqlParameter ("@ strWhere", SqlDbType. VarChar, 1000 ),
};
Parameters [0]. Value = "Accounts_Users ";
// Change 2007.11.22 here to sort by UserName
Parameters [1]. Value = "UserName ";
Parameters [2]. Value = PageSize;
Parameters [3]. Value = PageIndex;
Parameters [4]. Value = 0;
Parameters [5]. Value = int. Parse (OrderType );
Parameters [6]. Value = strWhere;
Return DbHelperSQL. RunProcedure ("GetRecordFromPage", parameters, "ds ");
}

BLL layer:

[Copy to clipboard]

CODE:

///
/// Retrieve the data list by PAGE
///
/// Number of pages per page
/// Index of the current page
/// Query string
/// Set the sorting type. If the value is not 0, the sorting type is in descending order.
///
Public DataSet GetList (int PageSize, int PageIndex, string strWhere, string OrderType)
{
Return dal. GetList (PageSize, PageIndex, strWhere, OrderType );
}

2. Drag a GridView control on the page and an AspNetPager control,
As follows:

[Copy to clipboard]

CODE:

<Webdiyer: AspNetPager id = AspNetPager
CustomInfoHTML = "% RecordCount % Page % CurrentPageIndex % of % PageCount % Order % StartRecordIndex %-% EndRecordIndex %"
FirstPageText = "Homepage" HorizontalAlign = "Center" InputBoxStyle = "width: 19px" LastPageText = "last page"
Meta: resourcekey = "AspNetPager" NextPageText = "" OnPageChanged = "AspNetPager_PageChanged"
PageSize = "10" PrevPageText = "Previous Page" ShowCustomInfoSection = "Left" ShowInputBox = "Always"
Style = "font-size: 12px" Width = "90%">

3. Add the following code to the cs file:
In the PageLoad event:

[Copy to clipboard]

CODE:

If (! This. IsPostBack)
{
// Determine the data quantity
HDHG. BLL. Accounts. Accounts_Users blluser = new HDHG. BLL. Accounts. Accounts_Users ();
This. AspNetPager. RecordCount = blluser. GetCount (strWhere );
BindGridView ();
}

Bind data Functions

[Copy to clipboard]

CODE:

///
/// Bind the user list
///
Private void BindGridView ()
{
StrWhere = this. lblStrWhere. Text;
HDHG. BLL. Accounts. Accounts_Users blluser = new HDHG. BLL. Accounts. Accounts_Users ();
DataSet ds = blluser. GetList (this. AspNetPager. PageSize, this. AspNetPager. CurrentPageIndex, strWhere, "0 ");
This. myGridView. DataSource = ds;
This. myGridView. DataBind ();
}

Bind data by page:

[Copy to clipboard]

CODE:

///
/// Bind data by PAGE
///
///
///
Protected void AspNetPager_PageChanged (object sender, EventArgs e)
{
This. BindGridView ();
}

In this way, the page is displayed.

Note: blluser. GetCount () is the method for obtaining the data quantity.
LblStrWhere is a hidden Label that stores query conditions. You can also disable this control.
DataList can also be bound using this method. It is very convenient to use it.