用了我一天的时间,才把这个问题搞明白,其中还参考了许多网友的示例,唉,失败呀~!
先贴一下源码~!
页面的:
<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