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

[mcluzhi]

刚学没DataList没多久，不知道如何分页。老师说用存储过程写，或自己写控件后台分页。可是迷茫呀。从网上搜索了N个实力，结果发现全都是错的，差点哭了，现在的版主都这么的不负责任，给就给了，干嘛给个错的，浪费了这么多时间编辑！
求高手指点江山。。。。。在此谢过

database: server=.;uid=sa;pwd=;database=Test
表：(Student)id primary key,name,age,addre

DataList:

<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> 
<asp:LinkButton ID="EndLB" runat="server" CommandName="end"
OnCommand="LinkButton_Click">最后一页</asp:LinkButton>

总<asp:Label ID="TotalLbl" runat="server"> </asp:Label>页  
当前第<asp:Label ID="CurrentLbl" runat="server"></asp:Label>页     

<asp:LinkButton ID="JumpLB" runat="server" CommandName="jump"
OnCommand="LinkButton_Click">跳到</asp:LinkButton>第
<asp:TextBox ID="TextBox1" runat="server" Width="90px"></asp:TextBox>页</div>

[编程有钱人了]

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

[子夜__]

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();
    }

[lightning0218]

万能分页存储过程：
--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

[mcluzhi]

[Quote=引用 2 楼 wxr0323 的回复:]
DataSource 分页

C# code
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
bind();
}
}
void bind()
{
int c……
[/Quote]
我这是引用DataSource 不需要再查询了吧？

[insus]

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

[mcluzhi]

[Quote=引用 5 楼 insus 的回复:]
不知这个是否有参考地方：
http://www.cnblogs.com/insus/articles/1418224.html
[/Quote]
都是你自己写的东西，不是远代码。我想知道源代码是什么样的，不然不用你写的，以后怎么办

[am_so_fat]

。。。去下个动态软件生存器！。。直接可以生成出来！。。什么代码都有！深入的研究下！也许对你有不错的帮助！。。

[mcluzhi]

23132213

[mcluzhi]

<%@ 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>
         <asp:LinkButton ID="PreviousLB" runat="server" onclick="PreviousLB_Click">上一页</asp:LinkButton>
        <asp:LinkButton   ID="NextLB" runat="server" onclick="NextLB_Click">下一页</asp:LinkButton> 
    <asp:LinkButton ID="EndLB" runat="server"   onclick="EndLB_Click">最后一页</asp:LinkButton>
       
    总<asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>页  
    当前第<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();
    }
}

[子夜__]

[Quote=引用 4 楼 mcluzhi 的回复:]

引用 2 楼 wxr0323 的回复:
DataSource 分页

C# code
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
bind();
}
}
void bind()
{
int c……

我这是引用DataSource 不需要再查询了吧？
[/Quote]

PagedDataSource 分页。