存储过程分页 及其排序 完整代码 .net C#

 

 

ALTER proc CP_Page_Control_RowNumber

 @PageSize int,
 @PageIndex int,
 @OrderBy nvarchar(100),
 @SelectSQl nvarchar(MAX),
 @recordCount INT OUTPUT
as

DECLARE @SQL nvarchar(MAX)
DECLARE @sqlcount nvarchar(MAX)

DECLARE @UP int
SET @UP=@PageSize*(@PageIndex-1)

SET @sqlcount = N'SELECT @Count = COUNT(*)  FROM (' + @SelectSQl + ') PageA '
EXEC SP_EXECUTESQL @sqlcount,N'@Count INT OUTPUT',@Count=@recordCount OUTPUT


SET @SQL='
WITH OrderedOrders AS
(SELECT *,ROW_NUMBER() OVER (ORDER BY '+@OrderBy+') as RowNumber FROM ('+@SelectSQl+') as T )
SELECT *
FROM OrderedOrders
WHERE RowNumber between '+str(@UP)+' and '+str(@UP+@PageSize-1)

EXEC SP_EXECUTESQL @SQL

 

 

存储过程

ALTER  PROCEDURE [dbo].[ListPage](
@tblName  nvarchar(500),           ----要显示的表或多个表的连接
@fldName  nvarchar(400) = '*',          ----要显示的字段列表
@pageSize int = 8,                    ----每页显示的记录个数
@page  int = 1,           ----要显示那一页的记录
@pageCount int = 1 output,          ----查询结果分页后的总页数
@Counts int = 1 output,           ----查询到的记录数
@fldSort nvarchar(100) = null,          ----排序字段列表或条件
@Sort  bit = 0,           ----排序方法，0为升序，1为降序
@strCondition nvarchar(400) = null, ----查询条件,不需where
@ID  nvarchar(50)  ----主表的主键
)
AS
SET NOCOUNT ON
Declare @sqlTmp nvarchar(1000)  ----存放动态生成的SQL语句
Declare @strTmp nvarchar(1000)  ----存放取得查询结果总数的查询语句
Declare @strID  nvarchar(1000)  ----存放取得查询开头或结尾ID的查询语句
Declare @sqlSort nvarchar(200)  ----存放临时生成的排序条件
Declare @intCounts int   ----要移动的记录数
Declare @BeginID varchar(50)   ----开始的ID

 

--------首先生成排序方法---------
if @Sort=0  --升序
begin
   if not(@fldSort is null)
  set @sqlSort = ' Order by ' + @fldSort 
   else
  set @sqlSort = ' Order by ' + @ID 
end
else   --降序
begin
 if not(@fldSort is null)
  set @sqlSort = ' Order by ' + @fldSort + ' DESC '  
 else
  set @sqlSort = ' Order by ' + @ID + ' DESC '
end

--------生成查询语句--------
--此处@strTmp为取得查询结果数量的语句
if @strCondition is null --没有设置显示条件
 begin
 set @sqlTmp =  @fldName + ' From ' + @tblName
 set @strTmp = 'select @Counts=Count(' + @ID + ') FROM '+@tblName
 set @strID = ' From ' + @tblName +' '+@sqlSort
 end
else
 begin
 set @sqlTmp = + @fldName + ' From  ' + @tblName + ' where ' + @strCondition
 set @strTmp = 'select @Counts=Count(' + @ID + ') FROM '+@tblName + ' where ' + @strCondition
 set @strID = ' From ' + @tblName + ' where ' + @strCondition+' '+@sqlSort
 end

----取得查询结果总数量-----
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out

--取得分页总数
if @Counts <= @pageSize
 set @pageCount = 1
else
 set @pageCount = (@Counts / @pageSize) + 1

--计算要移动的记录数
if @page = 1
 set @intCounts = @pageSize
else
begin
 set @intCounts = (@page) * @pageSize
end

-----取得分页后此页的第一条记录的ID
set @strID = 'select @BeginID=' + @fldSort + ' ' + @strID

 

set @intCounts = @intCounts - @pageSize +1
set rowcount  @intCounts
exec sp_executesql @strID,N'@BeginID varchar(50) out ',@BeginID out

 

------恢复系统设置-----
set rowcount  0
SET NOCOUNT OFF

------返回查询结果-----
if @strCondition is null
begin
if @Sort=1
    set @strTmp = '  select top 8 ' + @sqlTmp + ' where ' + @fldSort + ' <=''' + @BeginID + '''  '
else
    set @strTmp = '  select top 8 ' + @sqlTmp + ' where ' + @fldSort + ' >=''' + @BeginID + '''  '
end
else
begin
if @Sort=1
    set @strTmp = '  select top 8 ' + @sqlTmp + ' and ' + @fldSort + ' <='''  + @BeginID + ' ''  '
else
    set @strTmp = '  select top 8 ' + @sqlTmp + ' and ' + @fldSort + ' >=''' + @BeginID + '''  '
end
 
if not(@sqlSort is null)
set @strTmp = @strTmp + @sqlSort
exec sp_executesql @strTmp

 

 

 

 

//----------d 层

  public System.Collections.ArrayList PageStoreFillDataTable(string cmdText, SqlParameter[] inputParameters, SqlParameter[] outputParameters)
        {
            System.Data.DataTable dt = new DataTable();
            System.Collections.ArrayList ilist = new System.Collections.ArrayList();
              SqlCommand cmd = new SqlCommand();
              using (SqlConnection conn = new SqlConnection(DataAccess.GetConnString()))
              {
                  PrepareCommand(cmd, conn, null, CommandType.StoredProcedure, cmdText, inputParameters, outputParameters);
                  SqlDataAdapter adp = new SqlDataAdapter(cmd);
                  adp.Fill(dt);
                  ilist.Add(dt);
                  if (outputParameters != null)
                  {
                      foreach (SqlParameter parm in outputParameters)
                        ilist.Add(parm.Value.ToString());
                  }
                  cmd.Parameters.Clear();  

              }
              return ilist;
           
          
        }
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = cmdType;
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                cmd.Parameters.Add(parm);
            }
        } 

    public static System.Collections.ArrayList PageStore(string tblName, string fldName, int pageSize, int page, string strCondition, string fldSort, int Sort, string ID)
        {
          
            SqlParameter[] inputparameters = new SqlParameter[8];
            inputparameters[0] = new SqlParameter("@tblName", tblName);
            inputparameters[1] = new SqlParameter("@fldName", fldName);
            inputparameters[2] = new SqlParameter("@pageSize", pageSize);
            inputparameters[3] = new SqlParameter("@page", page);
            inputparameters[4] = new SqlParameter("@strCondition", strCondition);
            inputparameters[5] = new SqlParameter("@fldSort", fldSort);
            inputparameters[6] = new SqlParameter("@Sort", Sort);
            inputparameters[7] = new SqlParameter("@ID", ID);
            SqlParameter[] outputparameters = new SqlParameter[1];
            outputparameters[0] = new SqlParameter("@Counts", SqlDbType.Int);
            DataSet ds=new DataSet();
            return GetDataAccess().PageStoreFillDataTable("ListPage",inputparameters,outputparameters);
           
        }

 

//web---层

 /// <summary>
    /// 数据绑定
    /// </summary>
    private void databind()
    {

           System.Collections.ArrayList list = bg.GetdataBudgetCollectSct(int.Parse(DSState.SelectedValue), 39, AllocateDateFir.Text, AllocateDateEnd.Text, UserAddress.Text, SLProcessNo.Text, PageControl1.GetPageCurrent(), int.Parse(Sort), GridViewSortExpression);
 
            GRProjectlist.DataSource = (DataTable)list[0];
            GRProjectlist.DataBind();
 
           PageControl1.getViewState(int.Parse(list[1].ToString()));
          PageControl1.WritePageNo(PageControl1.GetPageCurrent(), int.Parse(list[1].ToString()));

      
    }

 

    #region 排序

 protected void GRProjectlist_Sorting(object sender, GridViewSortEventArgs e)
    {

        GridViewSortExpression = e.SortExpression.ToString();
        GridViewSortDirection();
        databind();
    }
    private string Sort
    {
        get { return ViewState["SortDirection"] as string ?? "1"; }
        set { ViewState["SortDirection"] = value; }
    }
    private string GridViewSortDirection()
    {
        if (Sort == "1")
        {
            Sort = "0";
        }
        else
        {
            Sort = "1";
        }
        return Sort;
    }
    private string GridViewSortExpression
    {
        get { return ViewState["SortExpression"] as string ?? "NodeBeginTime"; }
        set { ViewState["SortExpression"] = value; }
    }
    #endregion

 

//用户控件

<%@ Control Language="C#" AutoEventWireup="true"   CodeFile="PageControl.ascx.cs" Inherits="UserAscx_PageControl" %>

<div id="LinkButtonCss"><div id='pagenumber' style='text-align:center; '  runat="server"> </div></div>

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;

public partial class UserAscx_PageControl : System.Web.UI.UserControl
{

    public delegate void PageClick();
    public PageClick pageclick = null;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack)
        {
            if (ViewState["Count"] != null)
            {
                WritePageNo(GetPageCurrent(), int.Parse(ViewState["Count"].ToString()));
             }
        }

    }
    protected void LinkButton_Click(object sender, EventArgs e)//委托按钮时间 delegate button EventArgs
    {
        int Currentpage;
        if (pageclick != null)
        {
            switch (((LinkButton)sender).Text)
            {
                case "首页":
                    Currentpage = 1;
                    break;
                case "末页":
                    Currentpage = (int.Parse(ViewState["Count"].ToString())-1)/8+1;
                    break;
                case "上一页":
                    Currentpage = GetPageCurrent() - 1;
                    break;
                case "下一页":
                    Currentpage = GetPageCurrent() + 1; ;
                    break;
                default:
                    Currentpage = int.Parse(((LinkButton)sender).Text);
                    break;
            }
            ViewState["ButtonPage"] = Currentpage;
            pagenumber.Controls.RemoveAt(0);
         
       
            pageclick();

        }
    }
    public void getViewState(int Count)//获取页面总页 get pagecount
    {
        ViewState["Count"] = Count;
    }
    public int GetPageCurrent()//获取当前页面页数  Get PageCurrent
    {
        int PageCurrent;
        if (ViewState["ButtonPage"] == null)
        {
            PageCurrent = 1;
        }
        else
        {
            PageCurrent = int.Parse(ViewState["ButtonPage"].ToString());
        }
        return PageCurrent;
    }
    private void BaseWrite(int ReturnPage, int Count)//写下数字页数  writebase number page
    {
        Unit u = new Unit(12);
        for (int i = ReturnPage; i <= Count; i++)
        {
            LinkButton link = new LinkButton();
            link.Text = i.ToString();
            link.ID = "LinkButton_" + i.ToString();
            link.Width = u;
            link.Click += new EventHandler(LinkButton_Click);
          
            pagenumber.Controls.Add(link);
        }
    }
    private void FristPage()//首页
    {
        Unit u = new Unit(40);
        LinkButton link = new LinkButton();
        link.Text = "首页";
        link.ID = "FristPageLink_1";
        link.Width = u;
        link.Click += new EventHandler(LinkButton_Click);
        pagenumber.Controls.Add(link);

    }
    private void EndPage(int Count)//最后一页码
    {
        Unit u = new Unit(40);
        LinkButton link = new LinkButton();
        link.Text = "末页";
        link.ID = "EndPageLink_" + Count;
        link.Width = u;
        link.Click += new EventHandler(LinkButton_Click);
        pagenumber.Controls.Add(link);
    }
    private void UpPage()//首页
    {
        Unit u = new Unit(40);
        LinkButton link = new LinkButton();
        link.Text = "上一页";
        link.ID = "UpPage_1";
        link.Width = u;
        link.Click += new EventHandler(LinkButton_Click);
        pagenumber.Controls.Add(link);

    }
    private void DownPage()//最后一页码
    {
        Unit u = new Unit(40);
        LinkButton link = new LinkButton();
        link.Text = "下一页";
        link.ID = "DownPage_1";
        link.Width = u;
        link.Click += new EventHandler(LinkButton_Click);
        pagenumber.Controls.Add(link);
    }
    private void WriteTotalPage(int allpagenumber)//最后一页码
    {

        Label label = new Label();
        label.Text = "共有" + allpagenumber.ToString() + "条记录,每页8条,共" + ((allpagenumber-1) / 8 + 1) + "页";
        label.ID = "PageLabel";
        pagenumber.Controls.Add(label);
    }
    private void UpDownPage(int pagecout, int requestcout)
    {
        if (requestcout <= pagecout && requestcout > 1)
        {
            UpPage(); EndPage(pagecout);
        }
        if (requestcout < pagecout)
        {
            DownPage(); FristPage();
        }
    }

    public void WritePageNo(int requestcout, int allpagenumber)
    {
        int pagecout = (allpagenumber-1) / 8 + 1;
        pagenumber.Controls.Clear();
        if (pagecout > 1)
        {
          
            if (pagecout <= 8)
            {
                if (requestcout <= pagecout && requestcout > 1)
                {
                    FristPage(); UpPage();
                }
                BaseWrite(1, pagecout);
                if (requestcout < pagecout)
                {
                    DownPage(); EndPage(pagecout);
                }
            }
            else
            {
                // 最后一排
                if (requestcout + 4 > pagecout)
                {
                    FristPage(); UpPage();
                    BaseWrite(pagecout - 7, pagecout);
                    if (requestcout < pagecout)
                    {
                        DownPage(); EndPage(pagecout);
                    }
                }
                else if (requestcout / 4 > 0)
                {
                    FristPage(); UpPage();
                    BaseWrite(requestcout - 3, requestcout + 4);
                    DownPage(); EndPage(pagecout);
                }
                else
                {
                    if (requestcout > 1)
                    { FristPage(); UpPage(); }
                    BaseWrite(1, 8);
                    DownPage(); EndPage(pagecout);
                }

            }
    
            ((LinkButton)pagenumber.FindControl("LinkButton_" + GetPageCurrent().ToString())).ForeColor = System.Drawing.Color.Red;

        }
        WriteTotalPage(allpagenumber);
    }

}