Complete ASP. NET Stored Procedure paging, sorting, mouse movement to color

First, create a stored procedure as follows (MySQL database ):
Create definer = 'root' @ 'localhost' PROCEDURE 'pagination '(
In tbName varchar (100),/* Table name */
FldName varchar (100),/* sort keyword */
PageSize int,/* number of lines per page */
PageIndex int,/* Current page number */
OrderType int,/* sorting rule 0 ascending, 1 descending */
StrWhere varchar (2000),/* query conditions such as where 1 = 1 */
Out allPages int/* Total Number of outgoing parameters */
)
Begin

Declare beginRow int;
Declare sqlStr varchar (1000 );
Declare limitTemp varchar (1000 );
Declare orderTemp varchar (1000 );
Declare allPageNum int;

Set allPageNum = 0;
Set beginRow = (pageIndex-1) * pageSize;
Set sqlStr = CONCAT ('select * from', tbName );
Set limitTemp = CONCAT ('limit', beginRow, ',', pageSize );
Set orderTemp = CONCAT ('ORDER BY', fldName );
If orderType = 0 then
Set orderTemp = CONCAT (orderTemp, 'asc ');
Else
Set orderTemp = CONCAT (orderTemp, 'desc ');
End if;

Set @ sqlSelect = CONCAT (sqlStr, '', strWhere, orderTemp, limitTemp );

Set @ sqlCountRow = CONCAT ('select count (*) into @ countRow from ', tbName );

Prepare sqlstmt from @ sqlSelect;
Execute sqlstmt;
Deallocate prepare sqlstmt;

Prepare sqlstmt from @ sqlCountRow;
EXECUTE sqlstmt;
Set allPages = @ countRow;
Deallocate prepare sqlstmt;

End;

 

Create a sorting class
Using System;
Using System. Data;
Using MySql. Data. MySqlClient;

Namespace TestMySQL
{
Public class Procedure
{
Public Procedure ()
{
}

/// <Summary>
/// Based on the condition query, a page of records is returned
/// </Summary>
/// <Param name = "tbName"> multiple tables to be queried are separated by "," </param>
/// <Param name = "fldName"> sort key fields </param>
/// <Param name = "pageSize"> Number of lines per page </param>
/// <Param name = "pageIndex"> page number </param>
/// <Param name = "orderType"> sorting rule 0 is in ascending order and 1 is in descending order. </param>
/// <Param name = "strWhere"> query conditions such as "where 1 = 1' </param>
/// <Param name = "allNum"> total number of returned rows </param>
/// <Returns> one-page record </returns>
Public static DataTable Query (string tbName, string fldName, int pageSize, int pageIndex, int orderType, string strWhere, out int allNum)
{

// MySqlConnection con = DB. CreateCon ();
MySqlConnection con = new MySqlConnection ("server = localhost; database = testprocedure; user id = root; password = 123456 ");
MySqlCommand mySqlCmd = new MySqlCommand ();
MySqlCmd. Connection = con;
MySqlCmd. CommandText = "pagination ";
MySqlCmd. CommandType = CommandType. StoredProcedure;

// Add a parameter list
MySqlCmd. Parameters. Add (new MySqlParameter ("tbName", MySql. Data. MySqlClient. MySqlDbType. VarChar ));
MySqlCmd. Parameters ["tbName"]. Value = tbName;

MySqlCmd. Parameters. Add (new MySqlParameter ("fldName", MySql. Data. MySqlClient. MySqlDbType. VarChar ));
MySqlCmd. Parameters ["fldName"]. Value = fldName;

MySqlCmd. Parameters. Add (new MySqlParameter ("pageSize", MySql. Data. MySqlClient. MySqlDbType. Int32 ));
MySqlCmd. Parameters ["pageSize"]. Value = pageSize;

MySqlCmd. Parameters. Add (new MySqlParameter ("pageIndex", MySql. Data. MySqlClient. MySqlDbType. Int32 ));
MySqlCmd. Parameters ["pageIndex"]. Value = pageIndex;

MySqlCmd. Parameters. Add (new MySqlParameter ("orderType", MySql. Data. MySqlClient. MySqlDbType. Int32 ));
MySqlCmd. Parameters ["orderType"]. Value = orderType;

MySqlCmd. Parameters. Add (new MySqlParameter ("strWhere", MySql. Data. MySqlClient. MySqlDbType. VarChar ));
MySqlCmd. Parameters ["strWhere"]. Value = strWhere;

MySqlCmd. Parameters. Add (new MySqlParameter ("allPages", MySql. Data. MySqlClient. MySqlDbType. Int32 ));
// MySqlCmd. Parameters ["@ allNum"]. Value = allNum;
MySqlCmd. Parameters ["allPages"]. Direction = ParameterDirection. Output;

MySqlDataAdapter msda = new MySqlDataAdapter (mySqlCmd );
DataSet ds = new DataSet ();
Msda. Fill (ds, "temp ");

AllNum = Convert. ToInt32 (mySqlCmd. Parameters ["allPages"]. Value );

Return ds. Tables ["temp"];
}
}
}

 

 

Use sorting class
Using System;
Using System. Collections;
Using System. ComponentModel;
Using System. Data;
Using System. Drawing;
Using System. Web;
Using System. Web. SessionState;
Using System. Web. UI;
Using System. Web. UI. WebControls;
Using System. Web. UI. HtmlControls;

Namespace TestMySQL
{
Public class WebForm3: System. Web. UI. Page
{
Protected System. Web. UI. WebControls. DataGrid DataGrid1;

Private string tbName = "table1"; // table name
Private string sortKey = "id"; // sorting keyword
Private int pageSize = 20; // number of lines per page
Private int CurrentPageIndex = 1; // current page number
Private int sortRule = 0; // The sorting rules are 0 liters and 1 drop.
Private string selectCondition = "where 1 = 1"; // query Condition
Private int countRow; // The total number of returned rows.
Private int countPage; // the total number of pages.

Private void Page_Load (object sender, System. EventArgs e)
{
If (! Page. IsPostBack)
{
This. bingtransferird ();
}
}

Private void bingtransferird ()
{
// Sort key fields by id when loading for the first time
If (ViewState ["sortKey"] = null)
{
ViewState ["sortKey"] = "id ";
}
SortKey = (string) ViewState ["sortKey"];

// Sorting rules 0 are in ascending order, 1 is in descending order, and the first loading is in ascending order
If (ViewState ["sortRule"] = null)
{
ViewState ["sortRule"] = 0;
}
SortRule = (int) ViewState ["sortRule"];

This. DataGrid1.PageSize = pageSize; // by default, the number of lines displayed on each page is the same as the number of lines displayed on each page.
CurrentPageIndex = this. DataGrid1.CurrentPageIndex + 1;
DataTable dt = Procedure. Query (tbName, sortKey, pageSize, CurrentPageIndex, sortRule, selectCondition, out countRow );

This. DataGrid1.VirtualItemCount = countRow;
This. Maid = dt;
This. DataGrid1.DataBind ();

// Calculate the total number of pages
If (countRow % pageSize = 0)
{
CountPage = countRow/pageSize;
}
Else
{
CountPage = countRow/pageSize + 1;
}

Response. Write ("Total number of records:" + countRow + "<br>"
+ "Total page number:" + countPage + "<br>"
+ "Current page number:" + CurrentPageIndex );
}

Private void DataGrid1_PageIndexChanged (object source, System. Web. UI. WebControls. DataGridPageChangedEventArgs e)
{
This. DataGrid1.CurrentPageIndex = e. NewPageIndex;
This. bingtransferird ();
}

Private void DataGrid1_SortCommand (object source, System. Web. UI. WebControls. DataGridSortCommandEventArgs e)
{
// Set bidirectional sorting
If (ViewState ["sortRule"] = null)
{
ViewState ["sortRule"] = 0;
}
Else
{
If (int) ViewState ["sortRule"] = 0)
{
ViewState ["sortRule"] = 1;
}
Else
{
ViewState ["sortRule"] = 0;
}
}

SortRule = (int) ViewState ["sortRule"]; // sorting rule
This. DataGrid1.CurrentPageIndex = 0; // the first page is returned when you click the sort keyword.

ViewState ["sortKey"] = e. SortExpression; // sorting keyword
SortKey = (string) ViewState ["sortKey"];

This. DataGrid1.PageSize = pageSize; // by default, the number of lines displayed on each page is the same as the number of lines displayed on each page.
CurrentPageIndex = this. DataGrid1.CurrentPageIndex + 1;
DataTable dt = Procedure. Query (tbName, sortKey, pageSize, CurrentPageIndex, sortRule, selectCondition, out countRow );

This. DataGrid1.VirtualItemCount = countRow;
This. Maid = dt;
This. DataGrid1.DataBind ();

// Calculate the total number of pages
If (countRow % pageSize = 0)
{
CountPage = countRow/pageSize;
}
Else
{
CountPage = countRow/pageSize + 1;
}

Response. Write ("Total number of records:" + countRow + "<br>"
+ "Total page number:" + countPage + "<br>"
+ "Current page number:" + CurrentPageIndex );
}

Private void maid (object sender, System. Web. UI. WebControls. DataGridItemEventArgs e)
{
If (e. Item. ItemType = ListItemType. Item | e. Item. ItemType = ListItemType. AlternatingItem)
{
E. Item. Attributes. Add ("onmouseover", "c = this. style. backgroundColor, this. style. backgroundColor = '# fff111 '");
E. Item. Attributes. Add ("onmouseout", "this. style. backgroundColor = c ");
}
}

# Code generated by region Web Form Designer
Override protected void OnInit (EventArgs e)
{
//
// CODEGEN: This call is required by the ASP. NET Web form designer.
//
InitializeComponent ();
Base. OnInit (e );
}

/// <Summary>
/// The designer supports the required methods-do not use the code editor to modify
/// Content of this method.
/// </Summary>
Private void InitializeComponent ()
{
This. Maid + = new System. Web. UI. WebControls. Maid pageindexchanged );
This. DataGrid1.SortCommand + = new System. Web. UI. WebControls. DataGridSortCommandEventHandler (this. DataGrid1_SortCommand );
This. Maid + = new System. Web. UI. WebControls. Maid (this. Maid );
This. Load + = new System. EventHandler (this. Page_Load );

}
# Endregion

}
}