Paging control ASPNETPAGET

Recently, the paging control ASPNETPAGET was used in the development project.
1. Add a stored procedure

-- The second Stored Procedure
/*
**************************************** ***********************
** Tens of millions of paging stored procedures **
**************************************** ***********************
Parameter description:
1. Tables: Table Name, View
2. PrimaryKey: Primary Key
3. Sort: Sorting statement without Order By such as NewsID Desc and OrderRows Asc
4. CurrentPage: Current page number
5. PageSize: page size
6. Filter: Filter statement without Where
7. Group: Group statement without Group

**************************************** ***********************/
Create procedure ComPagerData (
@ Tables varchar (1000 ),
PrimaryKey varchar (100 ),
@ Sort varchar (200) = NULL,
@ CurrentPage int = 1,
@ PageSize int = 10,
@ Fields varchar (1000) = '*',
@ Filter varchar (1000) = NULL,
@ Group varchar (1000) = NULL
)
AS
/* Default sorting */
IF @ Sort is null or @ Sort =''
SET @ Sort = @ PrimaryKey

DECLARE @ SortTable varchar (100)
DECLARE @ SortName varchar (100)
DECLARE @ strSortColumn varchar (200)
DECLARE @ operator char (2)
DECLARE @ type varchar (100)
DECLARE @ prec int

/* Set the sorting statement .*/
If charindex ('desc', @ Sort)> 0
BEGIN
SET @ strSortColumn = REPLACE (@ Sort, 'desc ','')
SET @ operator = '<='
END
ELSE
BEGIN
If charindex ('asc ', @ Sort)> 0
SET @ strSortColumn = REPLACE (@ Sort, 'asc ','')
SET @ operator = '> ='
END

If charindex ('.', @ strSortColumn)> 0
BEGIN
SET @ SortTable = SUBSTRING (@ strSortColumn, 0, CHARINDEX ('.', @ strSortColumn ))
SET @ SortName = SUBSTRING (@ strSortColumn, CHARINDEX ('.', @ strSortColumn) + 1, LEN (@ strSortColumn ))
END
ELSE
BEGIN
SET @ SortTable = @ Tables
SET @ SortName = @ strSortColumn
END

SELECT @ type = t. name, @ prec = c. prec
FROM sysobjects o
JOIN syscolumns c on o. id = c. id
JOIN policypes t on c. xusertype = t. xusertype
WHERE o. name = @ SortTable AND c. name = @ SortName

If charindex ('Char ', @ type)> 0
SET @ type = @ type + '(' + CAST (@ prec AS varchar) + ')'

DECLARE @ strPageSize varchar (50)
DECLARE @ strStartRow varchar (50)
DECLARE @ strFilter varchar (1000)
DECLARE @ strSimpleFilter varchar (1000)
DECLARE @ strGroup varchar (1000)

/* Default current page */
IF @ CurrentPage <1
SET @ CurrentPage = 1

/* Set paging parameters .*/
SET @ strPageSize = CAST (@ PageSize AS varchar (50 ))
SET @ strStartRow = CAST (@ CurrentPage-1) * @ PageSize + 1) AS varchar (50 ))

/* Filter and group statement .*/
IF @ Filter is not null and @ Filter! =''
BEGIN
SET @ strFilter = 'where' + @ Filter +''
SET @ strSimpleFilter = 'and' + @ Filter +''
END
ELSE
BEGIN
SET @ strSimpleFilter =''
SET @ strFilter =''
END
IF @ Group is not null and @ Group! =''
SET @ strGroup = 'group by' + @ GROUP +''
ELSE
SET @ strGroup =''

/* Execute the query statement */
EXEC (
'
DECLARE @ SortColumn '+ @ type +'
Set rowcount '+ @ strStartRow +'
SELECT @ SortColumn = '+ @ strSortColumn + 'from' + @ Tables + @ strFilter + ''+ @ strGroup + 'ORDER BY' + @ Sort +'
Set rowcount '+ @ strPageSize +'
SELECT '+ @ Fields + 'from' + @ Tables + 'where' + @ strSortColumn + @ operator +' @ SortColumn '+ @ strSimpleFilter + ''+ @ strGroup + 'order '+ @ Sort +'
'
)

GO

And stored procedures
-- Add the parameters for the stored procedure here
-- ===================================================== ======
-- Author: <caizhenfang>
-- ALTER date: <2007-05-30>
-- Description: <number of data records read>
-- ===================================================== ======
Create procedure ComPagerCount (
@ Tables varchar (50), -- table
@ PrimaryKey varchar (50), -- primary key
@ Filter varchar (800) = NULL, --: Condition Filter without Where
@ Sort varchar (200) = Null, --: Sorting without order
@ PageSize int, -- page size
@ CurrentPage int, -- paging Index
@ Docount bit -- if true, only the total number of records is read.
)
As
Declare @ sCondit varchar (800)
Declare @ sOrder varchar (200)
If @ Filter is not null and @ Filter! =''
SET @ sCondit = 'where' + @ Filter
Else
SET @ sCondit =''
If @ Sort IS Null And @ Sort =''
Set @ sOrder = 'ORDER BY' + @ PrimaryKey
Else
Set @ sOrder = 'ORDER BY' + @ Sort
BEGIN
Begin Tran
Set nocount on
IF (@ docount = 1)
Exec ('select count ('+ @ PrimaryKey +') from '+ @ Tables + @ sCondit)
ELSE
Begin
Declare @ PageLowerBound int
Declare @ PageUpperBound int
Set @ PageLowerBound = (@ CurrentPage-1) * @ PageSize
Set @ PageUpperBound = @ PageLowerBound + @ PageSize
Create table # pageindex (id int identity (1, 1) not null, nid int)
Set rowcount @ PageUpperBound
Exec ('insert into # pageindex (nid)
Select '+ @ PrimaryKey + 'from' + @ Tables + @ sCondit + @ sOrder)

Exec ('select O. * from' + @ Tables + 'o, # pageindex p where o .'
+ @ PrimaryKey + '= p. nid and p. id>' + @ PageLowerBound + 'and p. id <=' + @ PageUpperBound + 'order by p. id ')
END
Set nocount off
If (@ Error = 0) Commit Tran
Else ROLLBACK Tran
END

GO
2. Add a class library
//////////////////////////////////////// ///////////////////
// Name: SelectPager. cs
// Purpose: Paging Function
// Author: Cai zhenfang

// Time: 2007-5-22
// Modify:
// Description: All list paging functions.
//////////////////////////////////////// ////////////////////
Using System;
Using System. Collections;
Using System. Collections. Generic;
Using System. Text;
Using System. Data;
Using System. Data. Common;

Using Microsoft. Practices. EnterpriseLibrary. Data;
Using Microsoft. Practices. EnterpriseLibrary. Common;

Namespace Dinge. CnGameAD. DataAccess
{
Public class SelectPager
{
/// <Summary>
/// Execute the stored procedure and return the number of data records
/// </Summary>
/// <Param name = "PageIndex"> Start index </param>
/// <Param name = "hashtable"> List of stored procedure parameters, which must include Key1: @ Tables, Value1: Table name, Key2: @ Filter, Value2: query string </param>
/// <Param name = "DataBaseSelect"> select 1: CnGameAD_Base basic database, 2: CnGameAD_Report report database, and 3: CnGameAD_Log log database. </param>
/// <Returns> return integer </returns>
Public static int ReadRowsCount (Hashtable hashtable, int DataBaseSelect)
{
Int row = 0;
Database DBAdmin = DatabaseFactory. CreateDatabase ("EipString"); // category of the connected Database
DataSet DSAdmin = null;

Try
{

DbCommand ComAdmin = DBAdmin. GetStoredProcCommand ("ComPagerCount"); // paging Stored Procedure
DBAdmin. AddInParameter (ComAdmin, "@ PrimaryKey", DbType. String, "ID"); // Table Primary Key
DBAdmin. AddInParameter (ComAdmin, "@ PageSize", DbType. Int32, 10); // page size
DBAdmin. AddInParameter (ComAdmin, "@ CurrentPage", DbType. Int32, 1); // current page
DBAdmin. AddInParameter (ComAdmin, "@ docount", DbType. Int32, 1); // only statistics are collected.

Foreach (DictionaryEntry fn in hashtable)
{
DBAdmin. AddInParameter (ComAdmin, (string) fn. Key, DbType. String, (string) fn. Value );
}
// Obtain the value of the output parameter. Note the type of the converted return value.
Row = (int) DBAdmin. ExecuteScalar (ComAdmin );
}
Catch (Exception ex)
{

Return 0;
}
Return row;
}

/// <Summary>
/// Execute the stored procedure and return the paging Dataset
/// </Summary>
/// <Param name = "PrimaryKey"> stored procedure name </param>
/// <Param name = "CurrentPage"> table primary key </param>
/// <Param name = "PageSize"> page size </param>
/// <Param name = "oValue"> an array of stored procedure parameters. The parameters must include Key1: @ Tables, Value1: Table name, Key2: @ Filter, Value2: query string </param>
/// Key3: @ Fields, Value3: select the field name, Key4: @ Sort, Value4: sorting method, without Order By, it can be''
/// Key3: @ Group, Value3: grouping method, without Group By, can be''
///
/// <Param name = "DataBaseSelect"> select 1: CnGameAD_Base basic database, 2: CnGameAD_Report report database, and 3: CnGameAD_Log log database. </param>
/// <Returns> DataSet </returns>
Public static DataSet ReadPageData (int PageIndex, int PageSize, Hashtable hashtable, int DataBaseSelect)
{
Database DBAdmin = DatabaseFactory. CreateDatabase ("EipString"); // category of the connected Database
DataSet DSAdmin = null;

Try
{
// DBAdmin = DatabaseFactory. CreateDatabase ("CnGameAD_Base ");
DbCommand ComAdmin = DBAdmin. GetStoredProcCommand ("ComPagerData"); // paging Stored Procedure
DBAdmin. AddInParameter (ComAdmin, "@ PrimaryKey", DbType. String, "ID"); // Table Primary Key
DBAdmin. AddInParameter (ComAdmin, "@ PageSize", DbType. Int32, PageSize); // page size
DBAdmin. AddInParameter (ComAdmin, "@ CurrentPage", DbType. Int32, PageIndex); // the current page

Foreach (DictionaryEntry fn in hashtable)
{
DBAdmin. AddInParameter (ComAdmin, (string) fn. Key, DbType. String, (string) fn. Value );
}
DSAdmin = new DataSet ();
DSAdmin = DBAdmin. ExecuteDataSet (ComAdmin );
}
Catch (Exception ex)
{

Return null;
}
Return DSAdmin;
}
}
}
3. Introduce the Enterprise Library namespace in step 1
4 implement paging Program
Using System;
Using System. Data;
Using System. Configuration;
Using System. Collections;
Using System. Web;
Using System. Web. Security;
Using System. Web. UI;
Using System. Web. UI. WebControls;
Using System. Web. UI. WebControls. WebParts;
Using System. Web. UI. HtmlControls;

Using Dinge. CnGameAD. DataAccess;

Public partial class TestAspNetPager: System. Web. UI. Page
{
Protected void Page_Load (object sender, EventArgs e)
{

If (! Page. IsPostBack)
{
LoadDataBind ();
}
}

/// <Summary>
/// Data Query and binding
/// </Summary>
/// <Param name = "PageIndex"> start page </param>
Private void LoadDataBind ()
{

Hashtable hsTableCount = new Hashtable (); // record the number of queried data
Hashtable hsTableData = new Hashtable (); // record the query result Dataset

/// Create a logical object
String keywords = "ID ";
String Fields = string. Empty; // query a field
String Filter = string. Empty; // query Condition
String Group = string. Empty; // grouping method without Group
String Sort = string. Empty; // sorting method without Order
Sort = "id DESC ";
Fields = "ID ";

Fields = "ID, reportowner, MtSucceedNum, MtErorNum, MoNum ";

// Filter = Filter + "reportowner" + "= 1" + "and ";

Filter = Filter + "reportowner =" + Convert. ToInt64 (Session ["ID"]) + "and ";

Filter = Filter + "1 = 1 ";

HsTableCount. Add ("@ Tables", "eip_reportNote"); // name of the operation table
HsTableCount. Add ("@ Filter", Filter );
// Obtain the total number of qualified records
Pager. RecordCount = SelectPager. ReadRowsCount (hsTableCount, 1 );

HsTableData. Add ("@ Tables", "eip_reportNote"); // name of the operation table
HsTableData. Add ("@ Group", Group );
HsTableData. Add ("@ Sort", Sort );
HsTableData. Add ("@ Fields", Fields );
HsTableData. Add ("@ Filter", Filter );
// Retrieve and bind qualified records by PAGE
GvData. DataSource = SelectPager. ReadPageData (Pager. CurrentPageIndex, Pager. PageSize, hsTableData, 1 );
GvData. DataBind ();

// Clear ();
}
Public int MtNum (object container, string mtok, string mtero)
{
String Smtok = DataBinder. GetPropertyValue (container, mtok). ToString ();
String Smtero = DataBinder. GetPropertyValue (container, mtero). ToString ();

Int32 Return = 0;
Return = Convert. ToInt32 (Smtok) + Convert. ToInt32 (Smtero );
Return Return;


}

Protected void gvData_RowDataBound (object sender, GridViewRowEventArgs e)
{
If (e. Row. RowType. Equals (DataControlRowType. DataRow ))
{
E. Row. Attributes. Add ("onmouseover", "c = this. style. backgroundColor; this. style. backgroundColor = '# ffff '");
E. Row. Attributes. Add ("onmouseout", "this. style. backgroundColor = c ");
}
}

/// <Summary>
/// Grid deletion event
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "e"> </param>
Protected void gvData_RowDeleting (object sender, GridViewDeleteEventArgs e)
{

}

 

/// <Summary>
/// Paging event
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "e"> </param>
Protected void pager_PageChanged (object sender, EventArgs e)
{
LoadDataBind ();
}

// Public string MtNum (object container, string MtSucceedNum, string MtErorNum)
//{
// String Return;

// Int mtall, mtsucc, mter;
// Mtsucc = Convert. ToInt32 (MtSucceedNum );
// Mter = Convert. ToInt32 (MtErorNum );
// Mtall = mtsucc + mter;
// Return = mtall. ToString ();
// Return Return;
//}

/// <Summary>
/// Batch Review
/// </Summary>
// Private void CheckMultiple ()
//{
// String ID = string. Empty;
// String State = string. Empty;
// Hashtable hsTable = new Hashtable ();

/// Create a logical object
/// DAABExternal _ DAABExternal = new DAABExternal ();
//}

/// <Summary>
/// Query
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "e"> </param>

/// <Summary>
/// Grid color event
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "e"> </param>
}

These codes are combined to solve the problem. I will sort out my ideas and rewrite this article.