SQL2005+asp.net 自定义“真分页”

最新推荐文章于 2024-05-06 15:39:50 发布

dengmou1877

最新推荐文章于 2024-05-06 15:39:50 发布

阅读量133

点赞数

文章标签： xhtml ui c#

原文链接：http://www.cnblogs.com/dmhai/p/3740807.html

版权

这方法MS SQL2000不能用，因为用到row_number()，这是MS SQL2005中提供的，在2000中没有，这个函数可以获取数据结果集行的索引。

【在SQLSERVER中处理分页，必须使用存储过程或自定义函数】

--存储过程方式

CREATE PROC sp_Demo_GetPaging
    @startIndex INT ,
    @endIndex INT
AS
    BEGIN
        SELECT  *
        FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY id ) AS rowIndex, *
                  FROM      dbo.demo
                ) AS tb
        WHERE   rowIndex BETWEEN @startIndex AND @endIndex
    END
GO

--函数方式

CREATE FUNCTION Fun_Demo_Paging
    (
      @startIndex INT ,
      @endIndex INT
    )
RETURNS TABLE
AS
RETURN
    SELECT  *
    FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY id ) AS rowIndex, *
              FROM      dbo.demo
            ) AS tb
    WHERE   rowIndex BETWEEN @startIndex AND @endIndex

GO

调用操作：

存储过程版本：

EXEC dbo.sp_Demo_GetPaging 1, 5

函数版本：

SELECT  *  FROM    dbo.Fun_Demo_Paging(1, 3)

下面新建个空的Web应用程序演示下：

1.新建Demo.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Demo.aspx.cs" Inherits="dmhaiDemo_GetPaging.Demo" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <table style="width: 30%;">
        <tr>
            <th width="30%">
                id
            </th>
            <th width="30%">
                name
            </th>
            <th width="40%">
                age
            </th>
        </tr>
        <asp:Repeater ID="rptDemo" runat="server">
            <ItemTemplate >
                <tr align="center">
                    <td>
                        <%#Eval("id") %>
                    </td>
                    <td>
                        <%#Eval("name") %>
                    </td>
                    <td>
                        <%#Eval("age") %>
                    </td>
                </tr>
            </ItemTemplate>
        </asp:Repeater>
    </table>
    <table border="0" cellpadding="0" cellspacing="0">
        <tr>
            <td>
                共<asp:Label ID="lblPageCount" runat="server" ForeColor="Red"></asp:Label>页, 当前第<asp:Label
                    ID="lblCurrPage" runat="server" ForeColor="Red"></asp:Label>页&nbsp;&nbsp; 共<asp:Label
                        ID="lblTotalCount" runat="server" ForeColor="Red"></asp:Label>条记录, 每页显示<asp:Label
                            ID="lblPageSize" runat="server" Text="12" ForeColor="Red"></asp:Label>条&nbsp;&nbsp;
            </td>
            <td>
                <asp:LinkButton ID="btnFirst" runat="server" OnClick="btnFirst_Click">首页</asp:LinkButton>&nbsp;
                <asp:LinkButton ID="btnPrev" runat="server" OnClick="btnPrev_Click">上一页</asp:LinkButton>&nbsp;
                <asp:LinkButton ID="btnNext" runat="server" OnClick="btnNext_Click">下一页</asp:LinkButton>&nbsp;
                <asp:LinkButton ID="btnLast" runat="server" OnClick="btnLast_Click">尾页</asp:LinkButton>&nbsp;
                跳到<asp:DropDownList ID="ddlPager" runat="server" Width="50" OnSelectedIndexChanged="ddlPager_SelectedIndexChanged"
                    AutoPostBack="true" />
                页
            </td>
        </tr>
    </table>
    </form>
</body>
</html>

View Code

2.Demo.aspx.cs

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;

namespace dmhaiDemo_GetPaging
{
    public partial class Demo : System.Web.UI.Page
    {
        //页面加载时默认显示第一页
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindPaging("1");
            }
        }

        //首页
        protected void btnFirst_Click(object sender, EventArgs e)
        {
            BindPaging("1");
        }

        //上一页
        protected void btnPrev_Click(object sender, EventArgs e)
        {
            BindPaging("-1");
        }

        //下一页
        protected void btnNext_Click(object sender, EventArgs e)
        {
            BindPaging("+1");
        }
        
        //尾页
        protected void btnLast_Click(object sender, EventArgs e)
        {
            BindPaging("0");
        }

        //下拉列表框选项发生改变
        protected void ddlPager_SelectedIndexChanged(object sender, EventArgs e)
        {
            BindPaging(ddlPager.Text);
        }

        private void BindPaging(string page)
        {
            SqlDataSource sqlds = new SqlDataSource();
            sqlds.ConnectionString = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
            sqlds.SelectCommand = "SELECT count(1) FROM [demo]";
            sqlds.SelectCommandType = SqlDataSourceCommandType.Text;
            DataView dv = sqlds.Select(DataSourceSelectArguments.Empty) as DataView;
            int totalCount = int.Parse(dv[0][0].ToString());//总条数
            int pageSize = 5;//每页显示条数
            int pageCount = (totalCount % pageSize == 0) ? (totalCount / pageSize) : (totalCount / pageSize + 1);//总页数
            int currPage; //当前页数
            //设置当前页
            switch (page)
            {
                //首页
                case "1":
                    currPage = 1;
                    break;
                //下一页
                case "+1":
                    currPage = int.Parse(lblCurrPage.Text) + 1;
                    break;
                //上一页
                case "-1":
                    currPage = int.Parse(lblCurrPage.Text) - 1;
                    break;
                //尾页
                case "0":
                    currPage = pageCount;
                    break;
                //下拉列表框直接传值时
                default:
                    currPage = int.Parse(page);
                    break;
            }
            //加载DropDownList项
            if (!IsPostBack)
            {
                for (int i = 1; i <= pageCount; i++)
                {
                    ddlPager.Items.Add(i.ToString());
                }
            }
            //设置分页信息的显示
            lblCurrPage.Text = currPage.ToString();
            lblPageCount.Text = pageCount.ToString();
            lblPageSize.Text = pageSize.ToString();
            lblTotalCount.Text = totalCount.ToString();
            //设置翻页按钮的可用性
            btnFirst.Enabled = btnPrev.Enabled = btnNext.Enabled = btnLast.Enabled = true;
            if (currPage == 1)
            {
                btnFirst.Enabled = btnPrev.Enabled = false;
            }
            if (currPage == pageCount)
            {
                btnNext.Enabled = btnLast.Enabled = false;
            }
            ddlPager.Text = currPage.ToString();
            //通过开始行和结束行的索引获取当前页要显示的数据集
            sqlds.SelectCommand = "sp_Demo_GetPaging";
            sqlds.SelectParameters.Add("startIndex", ((currPage - 1) * pageSize + 1).ToString());
            sqlds.SelectParameters.Add("endIndex", (currPage * pageSize).ToString());
            sqlds.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
            dv = sqlds.Select(DataSourceSelectArguments.Empty) as DataView;


            rptDemo.DataSource = dv;
            rptDemo.DataBind();
        }
    }
}

View Code

运行结果：

转载于:https://www.cnblogs.com/dmhai/p/3740807.html

dengmou1877

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL2005+asp.net 自定义“真分页”

这方法MS SQL2000不能用，因为用到row_number()，这是MSSQL2005中提供的，在2000中没有，这个函数可以获取数据结果集行的索引。【在SQLSERVER中处理分页，必须使用存储过程或自定义函数】--存储过程方式CREATE PROC sp_Demo_GetPaging @startIndex INT , @endIndex I...
复制链接

扫一扫