关闭

关于GridView和ObjectDataSource结合的分页问题

850人阅读 评论(0) 收藏 举报

用了我一天的时间,才把这个问题搞明白,其中还参考了许多网友的示例,唉,失败呀~!

先贴一下源码~!

页面的:

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetUsers"
            TypeName="DAL.DataManager" EnablePaging="True" MaximumRowsParameterName="maxRows" SelectCountMethod="GetUsersCount" StartRowIndexParameterName="RowIndex">
           <!--注意此处要把参数信息删除-->
        </asp:ObjectDataSource>
        
        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"
            DataSourceID="ObjectDataSource1">
            <Columns>
                <asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" />
                <asp:BoundField DataField="UserID" HeaderText="UserID" SortExpression="UserID" />
                <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
            </Columns>
        </asp:GridView>

后台代码:

UserInfo类:

using System;
using System.Collections.Generic;
using System.Text;

namespace DAL
{
   public  class UserInfo
    {
       private int _userid;
       private string _username;
       private string _description;

       public UserInfo()
       { }

       public UserInfo( int userid , string username , string desc )
       {
           _userid = userid;
           _username = username;
           _description = desc;
       }

       public int UserID
       {
           get
           {
               return _userid;
           }
           set
           {
               _userid = value;
           }
       }

       public string UserName
       {
           get { return _username; }
           set { _username = value; }
       }
       public string Description
       {
           get { return _description; }
           set { _description = value; }
       }
    }
}

DataManager类:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
  
/// <summary>
/// DataManager 的摘要说明
/// </summary>
    public class DataManager
    {
        private SqlConnection con = null; //连接对象
        private SqlCommand cmd = null;  //command执行对象
        private SqlDataAdapter da = null; //适配器对象

        /**/
        /// <summary>
        /// DAL对象构造
        /// </summary>
        public DataManager()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        /**/
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        private void OpenConnection()
        {
            try
            {
                string conString = "server=。;uid=sa;pwd=sa;database=account"; //换成你的数据库
                con = new SqlConnection( conString );
                if ( ConnectionState.Closed == con.State )
                {
                    con.Open();
                }
            }
            catch ( SqlException ex )
            {
                throw new Exception( "数据库无法访问" , ex );
            }
        }

        /**/
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        private void CloseConnection()
        {
            if ( ConnectionState.Open == con.State )
            {
                try
                {
                    con.Close();
                }
                catch ( SqlException ex )
                {
                    throw new Exception( "数据库无法关闭" , ex );
                }
            }
        }

        /**/
        /// <summary>
        /// 取得用户列表
        /// </summary>
        /// <param name="rowIndex">行索引</param>
        /// <param name="recordCount">页显示量(增量)</param>
        /// <returns>用户列表数据集</returns>
        public IList<UserInfo> GetUsers( int RowIndex , int maxRows )
        {
            OpenConnection();
            IList<UserInfo> user = new List<UserInfo>();
            try
            {
                cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "GetUsers";

                SqlParameter spRowIndex = new SqlParameter( "@pageindex" , SqlDbType.Int , 4 );
                spRowIndex.Direction = ParameterDirection.Input;
                SqlParameter spRecordCount = new SqlParameter( "@pagesize" , SqlDbType.Int , 4 );
                spRecordCount.Direction = ParameterDirection.Input;
                SqlParameter spDocount = new SqlParameter( "@docount" , SqlDbType.Bit );
                spDocount.Direction = ParameterDirection.Input;

                cmd.Parameters.Add( spRowIndex );
                cmd.Parameters.Add( spRecordCount );
                cmd.Parameters.Add( spDocount );

                spRowIndex.Value = RowIndex/maxRows ;    //注意此处GridView传入的不是页码,而是第多少记录,比如第二页的话,他会传入10,第三页传入20,以此...
                spRecordCount.Value = maxRows;
                spDocount.Value = false;

                SqlDataReader dr = cmd.ExecuteReader();

                while ( dr.Read() )
                {
                    UserInfo userinfo = new UserInfo( dr.GetInt32( 0 ) , dr.GetString( 1 ) , dr.GetString( 2 ) );

                    user.Add( userinfo );
                }


              

                return user;
            }
            catch ( SqlException ex )
            {
                throw new Exception( "无法取得有效数据" , ex );
            }
            finally
            {
                CloseConnection();
            }
        }

        /**/
        /// <summary>
        /// 取得用户总数
        /// </summary>
        /// <returns>用户总数</returns>
        public int GetUsersCount( )
        {
            OpenConnection();
            try
            {
                cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "GetUsers";

                SqlParameter spRowIndex = new SqlParameter( "@pageindex" , SqlDbType.Int , 4 );
                spRowIndex.Direction = ParameterDirection.Input;
                SqlParameter spRecordCount = new SqlParameter( "@pagesize" , SqlDbType.Int , 4 );
                spRecordCount.Direction = ParameterDirection.Input;
                SqlParameter spDocount = new SqlParameter( "@docount" , SqlDbType.Bit );
                spDocount.Direction = ParameterDirection.Input;

                cmd.Parameters.Add( spRowIndex );
                cmd.Parameters.Add( spRecordCount );
                cmd.Parameters.Add( spDocount );

                spRowIndex.Value = 1;
                spRecordCount.Value = 10;
                spDocount.Value = true;

                da = new SqlDataAdapter( cmd );

                int count = Convert.ToInt32( cmd.ExecuteScalar().ToString() );
                return count;
            }
            catch ( SqlException ex )
            {
                throw new Exception( "无法取得有效数据" , ex );
            }
            finally
            {
                CloseConnection();
            }
        }
    }
}

存储过程:

CREATE procedure GetUsers
(@pagesize int,
@pageindex int,
@docount bit)
as
set nocount on
if(@docount=1)
select count(UserID) from MyUsers
else
begin
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
create table #pageindex(id int identity(1,1) not null,nid int)
set rowcount @PageUpperBound
insert into #pageindex(nid)
select UserID from MyUsers order by UserID desc
select O.*
from MyUsers O,#pageindex p
where O.UserID=p.nid and p.id>@PageLowerBound and p.id<=@PageUpperBound order by p.id
end
set nocount off
GO

好了,表结构不用贴了吧,相信大家已经清楚了。有啥问题可以和我联系:wengnet@gmail.com

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:3542次
    • 积分:64
    • 等级:
    • 排名:千里之外
    • 原创:3篇
    • 转载:1篇
    • 译文:0篇
    • 评论:0条
    文章分类
    文章存档