.net三层架构调用分页存储过程

Question

private void PageUp()
        {
            string id = Request.QueryString["id"];
            string where = "s_id=" + id;
            AspNetPager1.PageSize = 5;
            this.Repeater1.DataSource = new TopicManager().getPage(this.AspNetPager1.PageSize, this.AspNetPager1.CurrentPageIndex, where, 4); 
            this.DataBind();
        }请问各位高手哪里出问题了  运行后什么都没有
<asp:Content ID="Content3" ContentPlaceHolderID="ContentBody" runat="server">   
      <div id="main" style="width:100%;  margin:0 auto;"> 
        <asp:Repeater ID="Repeater1" runat="server" ViewStateMode="Enabled">
         <ItemTemplate>   
           <div class="div_moudle">
                <div class="div_m_title">
                <%# Eval("S_name")%>
                </div>
             <asp:Datalist ID="DataList1" runat="server" DataSource='<%# Eval("TopicList") %>'> 
                <ItemTemplate>
                <table id="table1" style="width:100%;padding-top:0px; height:40px; text-align:center; ">
                     <tr>
                        <td style="width:100px;padding-top:0px;text-align:center;" > 
                            <tt><%# GetReplyCount(Convert.ToInt32(Eval("T_id").ToString()))%></tt>
                            <tt></tt>
                        </td>
                        <td style="width:600px;text-align:left;padding-top:0px;"><a href='<%# Eval("T_id","TopicReply.aspx?id={0}") %>'  target="_blank"> <%# Eval("T_title")%></a></td>
                        <td style="width:100px;padding-top:0px;">
                            <tt style="width:250px;">
                                <a href="#" target="_blank"><%# GetEditer( Convert.ToInt32( Eval("U_id")))%></a>
                            </tt>
                            <br />
                            <tt style="text-align:center">
                                 <%# GetLastReplyAuthor(Eval("T_id").ToString())%>
                            </tt>
                       </td>
                     </tr> 
                    
                </table>
                </ItemTemplate>
           </asp:Datalist>
           
          </div>
          </ItemTemplate>
        </asp:Repeater>
        
    </div>
   <%-- <asp:Button ID="BtnFirst" runat="server" Text="第一页" onclick="BtnFirst_Click" />
    <asp:Button ID="BtnNext" runat="server" Text="下一页" onclick="BtnNext_Click" />
    <asp:Button ID="BtnUp" runat="server" Text="上一页" onclick="BtnUp_Click" />
  <asp:Button ID="BtnEnd" runat="server" Text="最后一页" onclick="BtnEnd_Click" />
      当前第<asp:Label ID="Lb_pageindex" runat="server" Text="1"></asp:Label>页--%>
      <webdiyer:AspNetPager ID="AspNetPager1" runat="server" UrlPaging="True">
                    </webdiyer:AspNetPager>
     
</asp:Content>

Answer 1

 public SqlDataReader getTopicsByWherePage(int PageSize, int PageIndex, string strWhere, int Counts)
        {            string sql = "[dbo].[PageCut]";
            SqlParameter[] parameters = {
                                             new SqlParameter("@tblName",SqlDbType.VarChar,225),
                                             new SqlParameter("@strGetFields",SqlDbType.VarChar,1000),
                                             new SqlParameter("@fldName",SqlDbType.VarChar,225),
                                             new SqlParameter("@PageSize",SqlDbType.Int),
                                             new SqlParameter("@PageIndex",SqlDbType.Int),
                                             new SqlParameter("@OrderType",SqlDbType.Bit),
                                             new SqlParameter("@strWhere",SqlDbType.VarChar,1500),
                                             new SqlParameter("@Counts",SqlDbType.Int)
                                             
                                          };
            parameters[0].Value = "xh_topic_tb";
            parameters[1].Value = "*";
            parameters[2].Value = "t_id";
            parameters[3].Value = PageSize;
            parameters[4].Value = PageIndex;
            parameters[5].Value = 1;
            parameters[6].Value = strWhere;
            parameters[7].Value = Counts;
            return new DataAccess().ExcuteDataReader(CommandType.StoredProcedure, sql, parameters);
        
        }

Answer 2

public SqlDataReader getPage(int PageSize, int PageIndex, string strWhere, int Counts)
        {
            return new TopicAccess().getTopicsByWherePage(PageSize, PageIndex, strWhere,Counts);        }

Answer 3

存储过程：USE [xhforum]
GO
/****** Object:  StoredProcedure [dbo].[PageCut]    Script Date: 12/05/2012 16:53:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PageCut]
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '' ,-- 查询条件 (注意: 不要加 where)
@Counts    int    out -- 返回记录总数, 非 0 值则返回 这里作运行后返回的记录总数分页用
AS
set nocount on
declare @strSQL varchar(5000) -- 主语句
declare @CountSQL nVarchar(4000)
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
--如果@OrderType不是0，就执行降序，这句很重要！
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
endif @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +@strGetFields+ ' from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrderelse
set @strSQL = 'select top ' + str(@PageSize) +@strGetFields+ ' from ['+ @tblName + '] '+ @strOrder
--如果是第一页就执行以上代码，这样会加快执行速度
endelse
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = 'select top ' + str(@PageSize) +@strGetFields+ ' from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + '] where ' + @strWhere + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
endif @strWhere != ''
set @CountSQL='select    @Counts=count(*) from    [' + @tblName + '] where ' + @strWhere +''
else
set @CountSQL='select    @Counts=count(*) from    [' + @tblName + '] '

Answer 4

public SqlDataReader getPage(int PageSize, int PageIndex, string strWhere, int Counts)
SqlDataReader 能绑定到 this.Repeater1.DataSource吗？我目前还没有发现直接用SqlDataReader绑定到DataSource的，一般都是返回一个List的实体类集合绑定SqlDataReader不及时关闭，当用户访问量过大后，会出现很多问题。SqlDataReader sdr=new DataAccess().ExcuteDataReader(CommandType.StoredProcedure, sql, parameters);
List<实体类> list=new List<实体类>();
while(sdr.Read())
{
     实体类 data=new 实体类();
     赋值
     list.Add(data);
}
return list;

Answer 5

你设置AspNetPager的RecordCount属性了吗？ http://www.webdiyer.com/controls/aspnetpager

Answer 6

是不是这样赋值的啊
 Topic top = new Topic();
                top.T_id = sdr.GetInt32(0);
                top.S_id = sdr.GetInt32(1);
                top.U_id = sdr.GetInt32(2);
                top.T_title = sdr.GetString(3);
                top.T_content = sdr.GetString(4);
                top.T_time = sdr.GetDateTime(5);
                top.T_readCount = sdr.GetInt32(6);
                top.T_IsDistribute = sdr.GetBoolean(7);
                top.T_isTop = sdr.GetBoolean(8);
                top.T_recommend = sdr.GetBoolean(9);
                list.Add(top);

Answer 7

while(sdr.Read()){
              Topic top = new Topic();
                top.T_id = sdr.GetInt32(0);//也可以Convert.ToInt32(sdr["T_id"]);,T_id为数据库列名称
                top.S_id = sdr.GetInt32(1);
                top.U_id = sdr.GetInt32(2);
                top.T_title = sdr.GetString(3);
                top.T_content = sdr.GetString(4);
                top.T_time = sdr.GetDateTime(5);
                top.T_readCount = sdr.GetInt32(6);
                top.T_IsDistribute = sdr.GetBoolean(7);
                top.T_isTop = sdr.GetBoolean(8);
                top.T_recommend = sdr.GetBoolean(9);
                list.Add(top);
}
return list;

Answer 8

    private void PageUp()
        {
            string id = Request.QueryString["id"];
            string strWhere = "s_id=" + id;
            this.AspNetPager1.AlwaysShow = true;
            this.AspNetPager1.PageSize = 5;
            this.AspNetPager1.RecordCount= (new TopicManager().getPage(AspNetPager1.PageSize,AspNetPager1.CurrentPageIndex,strWhere,0)).Count;
            this.Repeater1.DataSource = new TopicManager().getPage(AspNetPager1.PageSize, AspNetPager1.CurrentPageIndex, strWhere, 0);
            
           
        }这样有啥问题？

Answer 9

List<Topic> list=new TopicManager().getPage(AspNetPager1.PageSize, AspNetPager1.CurrentPageIndex, strWhere, 0);
this.AspNetPager1.RecordCount= list.Count;
this.Repeater1.DataSource = list;
this.Repeater1.DataBind();

Answer 10

只有最后一次回复了，你先调试看看list是否有值，给你一个我的用于分页的辅助类，也是结合AspNetPager一起使用的，你看下能不能用到，用这个方便许多
使用DataPage dataPage = new DataPage();
dataPage.TableName = "User";
dataPage.CurrentPage = AspNetPager1.CurrentPageIndex;
dataPage.PageSize = AspNetPager1.PageSize;
dataPage.OrderBy = "UserId desc";
dataPage.Where = "UserId=" + userId;
/// <summary>
    /// 用于数据分页的辅助类，结合分页存储过程一起使用
    /// </summary>
    [Serializable]
    public class DataPage
    {
        #region 实例私有成员        private string _tableName = string.Empty;  //表名
        private string _fields = "*";    //查询字段
        private string _orderBy = string.Empty;       //排序字段
        private string _where = string.Empty;     //筛选条件
        private int _pageSize = 10;       //每页记录数
        private int _currentPage = 0;    //当前页码
        private int _recordSum = 0;      //总记录数        #endregion        #region 实例公共属性        /// <summary>
        /// 查询表名
        /// </summary>
        public string TableName
        {
            get { return _tableName; }
            set
            {
                //表名排除dbo.前缀
                _tableName = value.ToUpper().Replace("DBO.", string.Empty);
            }
        }        /// <summary>
        /// 查询字段,如ID,Name,Title或*,默认为*
        /// </summary>
        public string Fields
        {
            get { return _fields; }
            set
            {
                if (this._orderBy.Trim() == string.Empty)
                    _fields = value;
                else
                {
                    CheckSortFields(value, this._orderBy);
                    _fields = value;
                }
            }
        }        /// <summary>
        /// 检查排序字段必须包含在查询字段中
        /// </summary>
        /// <param name="fields">查询字段</param>
        /// <param name="sort">排序字段</param>
        private void CheckSortFields(string fields, string sort)
        {
            string[] sortFields = sort.ToLower().Replace("asc", string.Empty).Replace("desc", string.Empty).Replace(" ", string.Empty).Split(',');
            foreach (string str in sortFields)
                if (fields.ToLower().IndexOf(str) < 0)
                    throw new ArgumentException("排序字段必须包含在查询字段中。");
        }        /// <summary>
        /// 排序字段,如id,name desc或空,默认为空(主键排序)
        /// </summary>
        public string OrderBy
        {
            get { return _orderBy; }
            set
            {
                if (this._fields == string.Empty || this._fields.Contains("*"))
                    _orderBy = value;
                else
                {
                    CheckSortFields(this._fields, value);
                    _orderBy = value;
                }
            }
        }        /// <summary>
        /// 筛选字段,如ID=12 or name like '%accp'或空字符串
        /// </summary>
        public string Where
        {
            get { return _where; }
            set { _where = value; }
        }        /// <summary>
        /// 每页记录数,默认值为10
        /// </summary>
        public int PageSize
        {
            get { return _pageSize; }
            set
            {
                if (value <= 0)
                    throw new ArgumentException("每页记录数必须大于0");
                _pageSize = value;
            }
        }        /// <summary>
        /// 当前页码,默认值为0
        /// </summary>
        public int CurrentPage
        {
            get
            {
                //在存储过程中从1开始
                return _currentPage;
            }
            set
            {
                if (value < 0)
                    throw new ArgumentException("当前页码必须大等于0");
                _currentPage = value;
            }
        }        /// <summary>
        /// 总记录数,从存储过程返回
        /// </summary>
        public int RecordSum
        {
            get { return _recordSum; }
            set { _recordSum = value; }
        }        #endregion        #region 补助属性        /// <summary>
        /// 总页数
        /// </summary>
        public int PageSum
        {
            get
            {
                if (_recordSum % _pageSize == 0)
                    return _recordSum / _pageSize;
                else
                    return _recordSum / _pageSize + 1;
            }
        }        /// <summary>
        /// 本页记录数
        /// </summary>
        public int RecordThisPage
        {
            get
            {
                if (CurrentPage == PageSum) // 最后一页
                    return RecordSum - _pageSize * (CurrentPage - 1);
                else
                    return _pageSize;
            }
        }        /// <summary>
        /// 取得起始记录号
        /// </summary>
        /// <returns>起始记录号</returns>
        public int First
        {
            get
            {
                return (CurrentPage - 1) * _pageSize + 1;
            }
        }        /// <summary>
        /// 取得终止记录号
        /// </summary>
        /// <returns>终止记录号</returns>
        public int Last
        {
            get
            {
                if (_recordSum == 0) // 无记录
                    return 0;
                else if (CurrentPage == PageSum) // 最后一页
                    return RecordSum;
                else
                    return _pageSize * CurrentPage;
            }
        }        /// <summary>
        /// 是否是第一页
        /// </summary>
        public bool IsFirstPage
        {
            get { return CurrentPage == 1; }
        }        /// <summary>
        /// 是否是最后一页
        /// </summary>
        public bool IsLastPage
        {
            get
            {
                if (PageSum == 0) return true;
                return CurrentPage == PageSum;
            }
        }        #endregion        
    }

Answer 11

        AspNetPager1.RecordCount = dataPage.RecordSum;
        AspNetPager1.CustomInfoHTML = "共：<font color='0099ff'>" + AspNetPager1.PageCount + "</font>页";
        AspNetPager1.CustomInfoHTML += "，<font color='0099ff'>" + AspNetPager1.RecordCount + "</font>条数据";
        AspNetPager1.CustomInfoHTML += "，本页<font color='0099ff'>" + Repeater1.Items.Count + "</font>条数据";