完整的ASP.NET存储过程分页，排序，鼠标移至变色

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

首先建立一个存储过程如下（MySQL数据库）：
CREATE DEFINER=`root`@`localhost` PROCEDURE `pagination`(
       in tbName varchar(100),   /*表名*/
          fldName varchar(100), /*排序关键字*/
          pageSize int,          /*每页显示行数*/
          pageIndex int,         /*当前显示第几页*/
          orderType int,         /*排序规则 0升序，1降序*/
          strWhere varchar(2000),/*查询条件例如where 1=1*/
       out allPages int          /*传出参数返回总行数*/
)
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;

建立一个排序类
using System;
using System.Data;
using MySql.Data.MySqlClient;

namespace TestMySQL
{
    public class Procedure
    {
        public Procedure()
        {
        }

/// <summary>
        /// 根据条件查询,返回一页记录
        /// </summary>
        /// <param name="tbName">要查询的表名多个表之间用","分隔</param>
        /// <param name="fldName">排序关键字段</param>
        /// <param name="pageSize">每页显示行数</param>
        /// <param name="pageIndex">显示第几页</param>
        /// <param name="orderType">排序规则 0为升序,1为降序</param>
        /// <param name="strWhere">查询条件例如"where 1=1'</param>
        /// <param name="allNum">返回总行数</param>
        /// <returns>一页的记录</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;

//添加参数列表
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"];
        }
    }
}

使用排序类
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";//表名
        private string sortKey = "id";//排序关键字
        private int pageSize = 20;//每页显示行数
        private int CurrentPageIndex = 1;//当前页数
        private int sortRule = 0;//排序规则 0升,1降
        private string selectCondition = "where 1=1";//查询条件
        private int countRow;//返回的总行数
        private int countPage;//总页数

        private void Page_Load(object sender, System.EventArgs e)
        {
            if(!Page.IsPostBack)
            {
                this.BingDataGird();
            }
        }

private void BingDataGird()
        {
            //排序关键字段首次加载是按id排
            if(ViewState["sortKey"] == null)
            {
                ViewState["sortKey"] = "id";
            }
            sortKey = (string)ViewState["sortKey"];

//排序规则 0为升序,1为降序,首次加载时按升序
            if(ViewState["sortRule"] == null)
            {
                ViewState["sortRule"] = 0;
            }
            sortRule = (int)ViewState["sortRule"];

this.DataGrid1.PageSize = pageSize;//DataGird默认的每页显示行数等于自己设定的每页显示行数
CurrentPageIndex = this.DataGrid1.CurrentPageIndex + 1;
DataTable dt = Procedure.Query(tbName,sortKey,pageSize,CurrentPageIndex,sortRule,selectCondition,out countRow);

this.DataGrid1.VirtualItemCount = countRow;
this.DataGrid1.DataSource = dt;
this.DataGrid1.DataBind();

//计算总页数
            if(countRow%pageSize == 0)
            {
                countPage = countRow/pageSize;
            }
            else
            {
                countPage = countRow/pageSize +1;
            }

            Response.Write("总记录数为:"+countRow+"<br>"
            +"总页数为:"+countPage+"<br>"
            +"当前页数:"+CurrentPageIndex);
        }

private void DataGrid1_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
        {
            this.DataGrid1.CurrentPageIndex = e.NewPageIndex;
            this.BingDataGird();
        }

private void DataGrid1_SortCommand(object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
        {
            //设定双向排序
            if(ViewState["sortRule"] == null)
            {
                ViewState["sortRule"] = 0;
            }
            else
            {
                if( (int)ViewState["sortRule"] == 0)
                {
                    ViewState["sortRule"] = 1;
                }
                else
                {
                    ViewState["sortRule"] = 0;
                }
            }

sortRule = (int)ViewState["sortRule"];//排序规则
this.DataGrid1.CurrentPageIndex = 0;//每次点击关键字排序时返回第一页

ViewState["sortKey"] = e.SortExpression;//排序关键字
sortKey = (string)ViewState["sortKey"];

this.DataGrid1.VirtualItemCount = countRow;
this.DataGrid1.DataSource = dt;
this.DataGrid1.DataBind();

//计算总页数
            if(countRow%pageSize == 0)
            {
                countPage = countRow/pageSize;
            }
            else
            {
                countPage = countRow/pageSize +1;
            }

            Response.Write("总记录数为:"+countRow+"<br>"
                +"总页数为:"+countPage+"<br>"
                +"当前页数:"+CurrentPageIndex);
        }

private void DataGrid1_ItemDataBound(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");
            }
        }

#region Web 窗体设计器生成的代码
        override protected void OnInit(EventArgs e)
        {
            //
            // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
            //
            InitializeComponent();
            base.OnInit(e);
        }

        /// <summary>
        /// 设计器支持所需的方法 - 不要使用代码编辑器修改
        /// 此方法的内容。
        /// </summary>
        private void InitializeComponent()
        {
            this.DataGrid1.PageIndexChanged += new System.Web.UI.WebControls.DataGridPageChangedEventHandler(this.DataGrid1_PageIndexChanged);
            this.DataGrid1.SortCommand += new System.Web.UI.WebControls.DataGridSortCommandEventHandler(this.DataGrid1_SortCommand);
            this.DataGrid1.ItemDataBound += new System.Web.UI.WebControls.DataGridItemEventHandler(this.DataGrid1_ItemDataBound);
            this.Load += new System.EventHandler(this.Page_Load);