跪求DataList基于DataSource分页或者如何写存储过程

Question

刚学没DataList没多久，不知道如何分页。老师说用存储过程写，或自己写控件后台分页。可是迷茫呀。从网上搜索了N个实力，结果发现全都是错的，差点哭了，现在的版主都这么的不负责任，给就给了，干嘛给个错的，浪费了这么多时间编辑！
求高手指点江山。。在此谢过database:  server=.;uid=sa;pwd=;database=Test
表：(Student)id  primary key,name,age,addreDataList: <asp:DataList ID="DataList1" runat="server" DataKeyField="id"  ForeColor="#333333"
             Width="784px" DataSourceID="SqlDataSource1">
            <FooterStyle BackColor="#990000" Font-Bold="true" ForeColor="White" />
            <AlternatingItemStyle BackColor="White" />
            <ItemStyle BackColor="#FFFBD6" ForeColor="#333333" />
            <SeparatorStyle BorderStyle="Dashed" />
            <SelectedItemStyle BackColor="#FFCC66" Font-Bold="true" ForeColor="Navy" />
            <HeaderTemplate>
            学生信息<br />
             <table class="style1" style="background-color:#808000">
             <tr class="style1">
             <td class="style2">编号</td>
             <td class="style2">姓名</td>
             <td class="style2">年龄</td>
              <td class="style2">地址</td>
             </tr>
                </table>
            </HeaderTemplate>
            <HeaderStyle BackColor="#990000" Font-Bold="true" ForeColor="White" />
            <ItemTemplate>
                id:
                <asp:Label ID="idLabel" runat="server" Text='<%# Eval("id") %>' />
                <br />
                name:
                <asp:Label ID="nameLabel" runat="server" Text='<%# Eval("name") %>' />
                <br />
                age:
                <asp:Label ID="ageLabel" runat="server" Text='<%# Eval("age") %>' />
                <br />
                matter:
                <asp:Label ID="matterLabel" runat="server" Text='<%# Eval("matter") %>' />
                <br />
                <br />
             </ItemTemplate>
        </asp:DataList>
_____________________________________________________________________________________________________________  DataSource:       <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:TestConnectionString %>" 
            SelectCommand="SELECT * FROM [Student]"></asp:SqlDataSource>
    </div>
__________________________________________________________________________________________________________
分页控件：      <div align="center">
        <asp:LinkButton ID="FirstLB" runat="server" OnCommand="LinkButton_Click" 
                                                         CommandName="first">第一页</asp:LinkButton>
       <asp:LinkButton ID="PreviousLB" runat="server" CommandName="prev" 
                                          OnCommand="LinkButton_Click">上一页</asp:LinkButton>
        <asp:LinkButton ID="NextLB" runat="server" CommandName="next" 
                                           OnCommand="LinkButton_Click">下一页</asp:LinkButton>&nbsp;
    <asp:LinkButton ID="EndLB" runat="server" CommandName="end" 
                                                OnCommand="LinkButton_Click">最后一页</asp:LinkButton>
    
    总<asp:Label ID="TotalLbl" runat="server"> </asp:Label>页&nbsp;&nbsp;
    当前第<asp:Label ID="CurrentLbl" runat="server"></asp:Label>页&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    
    <asp:LinkButton ID="JumpLB" runat="server" CommandName="jump" 
        OnCommand="LinkButton_Click">跳到</asp:LinkButton>第
    <asp:TextBox ID="TextBox1" runat="server" Width="90px"></asp:TextBox>页</div>

Answer 1

http://www.cnblogs.com/wangjun8868/archive/2009/07/10/1520336.html

Answer 2

DataSource 分页
protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            bind();
        }
    }
    void bind()
    {
        int curpage = Convert.ToInt32(this.Label1.Text);
        PagedDataSource ps = new PagedDataSource();
        pagebind(ps);
        ps.AllowPaging = true;
        ps.PageSize = 5;
        ps.CurrentPageIndex = curpage -1;
        this.LinkButton1.Enabled = true;
        this.LinkButton2.Enabled = true;
        this.LinkButton3.Enabled = true;
        this.LinkButton4.Enabled = true;
        if (curpage == 1)
        {
            this.LinkButton1.Enabled = false;
            this.LinkButton2.Enabled = false;
        }
        if (curpage == ps.PageCount)
        {
            this.LinkButton3.Enabled = false;
            this.LinkButton4.Enabled = false;
        }
        this.Label2.Text = ps.PageCount.ToString();
        this.DataList1.DataSource = ps;
        this.DataList1.DataBind();
    }
    void pagebind(PagedDataSource pd)
    { 
        string strcon="server=.;database=pubs;uid=sa;pwd=sa";
        SqlConnection cn = new SqlConnection(strcon);
        SqlDataAdapter da = new SqlDataAdapter("select title_id,title,price from titles", cn);
        DataSet ds = new DataSet();
        da.Fill(ds, "titles");
        pd.DataSource = ds.Tables["titles"].DefaultView;
    }
    protected void LinkButton1_Click(object sender, EventArgs e)
    {
        this.Label1.Text = "1";
        bind();
    }
    protected void LinkButton2_Click(object sender, EventArgs e)
    {
        this.Label1.Text = (Convert.ToInt32(this.Label1.Text) - 1).ToString();
        bind();
    }
    protected void LinkButton3_Click(object sender, EventArgs e)
    {
        this.Label1.Text=(Convert.ToInt32(this.Label1.Text)+1).ToString();
        bind();
    }
    protected void LinkButton4_Click(object sender, EventArgs e)
    {
        this.Label1.Text = Label2.Text;
        bind();
    }

Answer 3

万能分页存储过程：
--TOP n 实现的通用分页存储过程
CREATE PROC sp_PageView
@tbname   sysname,               --要分页显示的表名
@FieldKey   nvarchar(1000),      --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent int=1,               --要显示的页码
@PageSize   int=10,                --每页的大小(记录数)
@FieldShow nvarchar(1000)='',      --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar(1000)='',      --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC,用于指定排序顺序                                          
@Where    nvarchar(1000)='',     --查询条件
@PageCount int OUTPUT             --总页数
AS
SET NOCOUNT ON
--检查对象是否有效   (这是原作者的内容，我注释掉，因为当使用表值函数时无法通过)
--IF OBJECT_ID(@tbname) IS NULL
--BEGIN
    --RAISERROR(N'对象"%s"不存在',1,16,@tbname)     
    --RETURN
--END
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
    AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
    AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
BEGIN
    RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname) 
    RETURN
END--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
    RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)
    RETURN
END--其他参数检查及规范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
    SET @FieldOrder=N''
ELSE
    SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@Where,N'')=N''
    SET @Where=N''
ELSE
    SET @Where=N'WHERE ('+@Where+N')'--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
    DECLARE @sql nvarchar(4000)
    SET @sql=N'SELECT @PageCount=COUNT(*)'
        +N' FROM '+@tbname
        +N' '+@Where
    EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT
    SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
END--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
    @TopN1=(@PageCurrent-1)*@PageSize--第一页直接显示
IF @PageCurrent=1
    EXEC(N'SELECT TOP '+@TopN
        +N' '+@FieldShow
        +N' FROM '+@tbname
        +N' '+@Where
        +N' '+@FieldOrder)
ELSE
BEGIN
    --处理别名
    IF @FieldShow=N'*'
        SET @FieldShow=N'a.*'    --生成主键(惟一键)处理条件
    DECLARE @Where1 nvarchar(4000),@Where2 nvarchar(4000),
        @s nvarchar(1000),@Field sysname
    SELECT @Where1=N'',@Where2=N'',@s=@FieldKey
    WHILE CHARINDEX(N',',@s)>0
        SELECT @Field=LEFT(@s,CHARINDEX(N',',@s)-1),
            @s=STUFF(@s,1,CHARINDEX(N',',@s),N''),
            @Where1=@Where1+N' AND a.'+@Field+N'=b.'+@Field,
            @Where2=@Where2+N' AND b.'+@Field+N' IS NULL',
            @Where=REPLACE(@Where,@Field,N'a.'+@Field),
            @FieldOrder=REPLACE(@FieldOrder,@Field,N'a.'+@Field),
            @FieldShow=REPLACE(@FieldShow,@Field,N'a.'+@Field)
    SELECT @Where=REPLACE(@Where,@s,N'a.'+@s),
        @FieldOrder=REPLACE(@FieldOrder,@s,N'a.'+@s),
        @FieldShow=REPLACE(@FieldShow,@s,N'a.'+@s),
        @Where1=STUFF(@Where1+N' AND a.'+@s+N'=b.'+@s,1,5,N''),    
        @Where2=CASE
            WHEN @Where='' THEN N'WHERE ('
            ELSE @Where+N' AND ('
            END+N'b.'+@s+N' IS NULL'+@Where2+N')'    --执行查询
    EXEC(N'SELECT TOP '+@TopN
        +N' '+@FieldShow
        +N' FROM '+@tbname
        +N' a LEFT JOIN(SELECT TOP '+@TopN1
        +N' '+@FieldKey
        +N' FROM '+@tbname
        +N' a '+@Where
        +N' '+@FieldOrder
        +N')b ON '+@Where1
        +N' '+@Where2
        +N' '+@FieldOrder)   -- select @PageCount
END
GO

Answer 4

我这是引用DataSource 不需要再查询了吧？

Answer 5

不知这个是否有参考地方：
http://www.cnblogs.com/insus/articles/1418224.html

Answer 6

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %><!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>
    <style type="text/css">
    .style1{
        width:784px;
    }
    .style2
    {
     width:126px;
     }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div align="center">
        <asp:DataList ID="DataList1" runat="server" ForeColor="#333333"
             Width="784px">
            <FooterStyle BackColor="#990000" Font-Bold="true" ForeColor="White" />
            <AlternatingItemStyle BackColor="White" />
            <ItemStyle BackColor="#FFFBD6" ForeColor="#333333" />
            <SeparatorStyle BorderStyle="Dashed" />
            <SelectedItemStyle BackColor="#FFCC66" Font-Bold="true" ForeColor="Navy" />
            <HeaderTemplate>
           <center>学生信息</center><br />
             <table class="style1" style="background-color:#808000">
             <tr class="style1">
             <td class="style2">编号</td>
             <td class="style2">姓名</td>
             <td class="style2">年龄</td>
              <td class="style2">地址</td>
             </tr>
                </table>
            </HeaderTemplate>
            <HeaderStyle BackColor="#990000" Font-Bold="true" ForeColor="White" />
            <ItemTemplate><table class="style1" border="0">
            <tr>
                <td class="style2">
                <asp:Label ID="idLabel" runat="server" Text='<%# Eval("id") %>' />
                </td>
                <td class="style2">
                <asp:Label ID="nameLabel" runat="server" Text='<%# Eval("name") %>' />
                </td>
                <td class="style2"><center>
                <asp:Label ID="ageLabel" runat="server" Text='<%# Eval("age") %>' /></center>
                </td>
               <td class="style2">
                <asp:Label ID="matterLabel" runat="server" Text='<%# Eval("matter") %>' />
                </td></tr></table>
                
             </ItemTemplate>
        </asp:DataList>
       
    </div>
      <div align="center">
        <asp:LinkButton ID="FirstLB" runat="server"  onclick="FirstLB_Click" >第一页</asp:LinkButton>
        &nbsp;<asp:LinkButton ID="PreviousLB" runat="server" onclick="PreviousLB_Click">上一页</asp:LinkButton>
        <asp:LinkButton   ID="NextLB" runat="server" onclick="NextLB_Click">下一页</asp:LinkButton>&nbsp;
    <asp:LinkButton ID="EndLB" runat="server"   onclick="EndLB_Click">最后一页</asp:LinkButton>
    &nbsp;&nbsp;&nbsp;
    总<asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>页&nbsp;&nbsp;
    当前第<asp:Label ID="Label1" runat="server" Text="1"></asp:Label>页    
     </form>
</body>
</html>using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;public partial class _Default : System.Web.UI.Page 
{
    int CurrentIndex;//当前页数
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            DataBinds();
        }
    }
    protected void DataBinds() {
        SqlConnection cn = new SqlConnection("Data Source=.;Initial Catalog=Test;Integrated Security=True");//连接并实例化数据库
        string sql = "select * from Student";//定义查询语句
        SqlDataAdapter da = new SqlDataAdapter(sql,cn);//实例化对象Adapter
        DataSet ds = new DataSet();//实例化DataSet
        da.Fill(ds,"Student");//填充        PagedDataSource pds = new PagedDataSource();//初始化分页事例
        pds.DataSource = ds.Tables["Student"].DefaultView;
        pds.AllowPaging = true;//启动分页
        pds.PageSize = 5;//每页显示的个数
        CurrentIndex = int.Parse(this.Label1.Text) - 1;//获取当前页数索引
        pds.CurrentPageIndex = CurrentIndex;
        if (CurrentIndex == 0)
        {//如果是第一页，上一页和第一页的控件不可点击
            this.PreviousLB.Enabled = false;
            this.FirstLB.Enabled = false;
            this.NextLB.Enabled = true;
            this.EndLB.Enabled = true;
        }
        else if (CurrentIndex == pds.PageCount - 1)
        {
            //如果是最后一页，下一页和最后一页空间不可点击
            this.PreviousLB.Enabled = true;
            this.FirstLB.Enabled = true;
            this.NextLB.Enabled = false;
            this.EndLB.Enabled = false;        }
        else {
            this.PreviousLB.Enabled = true;
            this.FirstLB.Enabled = true;
            this.NextLB.Enabled = true;
            this.EndLB.Enabled = true;
            
        }
        this.Label2.Text = pds.PageCount.ToString();//获取总页数
        DataList1.DataSource = pds;//绑定DataList数据
        DataList1.DataBind();
    }
    protected void FirstLB_Click(object sender, EventArgs e)//首页
    {
        this.Label1.Text = "1";//页数为1
        DataBinds();
    }
    protected void PreviousLB_Click(object sender, EventArgs e)
    {
        this.Label1.Text = (int.Parse(Label1.Text) -1).ToString();//页数减1
        DataBinds();
    }
    protected void NextLB_Click(object sender, EventArgs e)//下一页
    {
        this.Label1.Text = (int.Parse(this.Label1.Text) + 1).ToString();//页数加1
        DataBinds();    }
    protected void EndLB_Click(object sender, EventArgs e)//末页
    {
        this.Label1.Text = Label2.Text;//页数为最后一页
        DataBinds();
    }
}