Posted on 2006-04-13 19:34
笑看千秋_R
懒"归正传:
最近开场废话是多了点(可能是参加会议太多闹的.. ^_^!!).马上请出GridView & ObjectDataSource.
下面我将逐步展现一个分页的案例.
案例环境: WindowsXP SP2, VS2005 Team Suite,SqlServer2005(没有比这更糟的了....)
要求: 了解数据绑定控件的结构,数据源的运行机制,以及简单的绑定控件设计实现(那些内容足够再写一篇教程了,这里不再多叙.)
参 考: MSDN中提到,ObjectDataSource是唯一支持界面分页的数据源(除非自己来继承造一个,汗!),但示例仍 然是用SqlDataSource做数据源介绍的GirdView绑定.难道MS这么小气???(ps:打死俺也不说).
那好,就从那句介绍开始,咱们自己动手整出一个来.
按照常规,先准备一张表和一些存储过程,如下:
Table
:MyUsers
UserID (
int
,
primary
key
,
identity
)
--
自动增加字段,用户ID标识列
UserName (
nvarchar
(
50
),
not
null
)
--
用户名
Description (
nvarchar
(
50
),
not
null
)
--
备注
Procedure
:
![](https://i-blog.csdnimg.cn/blog_migrate/3ded721644b58dd96e5245c56be261e9.gif)
CREATE
PROCEDURE
[
dbo
]
.
[
AddUser
]
--
增加一个用户
@UserName
nvarchar
(
50
),
@Description
nvarchar
(
50
)
AS
BEGIN
SET
NOCOUNT
ON
;
![](https://i-blog.csdnimg.cn/blog_migrate/3ded721644b58dd96e5245c56be261e9.gif)
begin
transaction
t
insert
into
MyUsers(UserName,Description)
values
(
@UserName
,
@Description
)
if
@@error
<>
0
begin
rollback
transaction
t
end
else
begin
commit
transaction
end
END
![](https://i-blog.csdnimg.cn/blog_migrate/3ded721644b58dd96e5245c56be261e9.gif)
CREATE
PROCEDURE
[
dbo
]
.
[
DeleteUserByUserID
]
--
删除一个用户
@UserID
int
AS
BEGIN
SET
NOCOUNT
ON
;
![](https://i-blog.csdnimg.cn/blog_migrate/3ded721644b58dd96e5245c56be261e9.gif)
Begin
Transaction
t
![](https://i-blog.csdnimg.cn/blog_migrate/3ded721644b58dd96e5245c56be261e9.gif)
delete
from
MyUsers
where
UserID
=
@UserID
![](https://i-blog.csdnimg.cn/blog_migrate/3ded721644b58dd96e5245c56be261e9.gif)
if
@@error
<>
0
begin
rollback
transaction
t
end
else
begin
commit
transaction
end
END
![](https://i-blog.csdnimg.cn/blog_migrate/3ded721644b58dd96e5245c56be261e9.gif)
CREATE
PROCEDURE
[
dbo
]
.
[
UpdateUser
]
--
修改用户姓名或备注
@UserID
int
,
@UserName
nvarchar
(
50
),
@Description
nvarchar
(
50
)
AS
BEGIN
SET
NOCOUNT
ON
;
![](https://i-blog.csdnimg.cn/blog_migrate/3ded721644b58dd96e5245c56be261e9.gif)
begin
transaction
t
update
MyUsers
set
UserName
=
@UserName
,
Description
=
@Description
where
UserID
=
@UserID
![](https://i-blog.csdnimg.cn/blog_migrate/3ded721644b58dd96e5245c56be261e9.gif)
if
@@error
<>
0
begin
rollback
transaction
t
end
else
begin
commit
transaction
t
end
END
![](https://i-blog.csdnimg.cn/blog_migrate/3ded721644b58dd96e5245c56be261e9.gif)
CREATE
PROCEDURE
[
dbo
]
.
[
GetUsers
]
--
得到用户列表,注意这里的参数
@RowIndex
int
,
@RecordCount
int
AS
BEGIN
SET
NOCOUNT
ON
;
![](https://i-blog.csdnimg.cn/blog_migrate/3ded721644b58dd96e5245c56be261e9.gif)
With
VUsers
as
(
select
*
,row_number()
over
(
order
by
UserID
desc
)
as
RowNum
from
MyUsers
)
![](https://i-blog.csdnimg.cn/blog_migrate/3ded721644b58dd96e5245c56be261e9.gif)
select
*
from
VUsers
where
RowNum
>
@RowIndex
and
RowNum
<=
(
@RowIndex
+
@RecordCount
)
END
![](https://i-blog.csdnimg.cn/blog_migrate/3ded721644b58dd96e5245c56be261e9.gif)
CREATE
PROCEDURE
[
dbo
]
.
[
GetUsersCount
]
AS
BEGIN
SET
NOCOUNT
ON
;
select
count
(UserID)
from
MyUsers
END
这里详细说明一下倒数两个存储过程,GetUsers的参数RowIndex,不是页码(传说中的PageIndex),而是行索引,且从 0开始.这点特别要注意,因为要让ObjectDataSource自动取得当前需要的数据(界面级分页意味着当前需要显示多少数据只向数据库取多少,并 不会像其他数据源全总取出消耗性能.),依据也就是行索引和增量.GetUsersCount这个存储过程是为ObjectDataSource分页提供 总数的,它与分页的存储过程要保持一致.特别指的是有分页条件的情况,本人经常忘记(^_^||).否则GridView显示会不正常.
在WebSite新建一个DAL,假定类名为DataManager,给出代码片段如下:
using
System;
using
System.Data;
using
System.Data.SqlClient;
using
System.Configuration;
using
System.Web;
using
System.Web.Security;
using
System.Web.UI;
using
System.Web.UI.WebControls;
using
System.Web.UI.WebControls.WebParts;
using
System.Web.UI.HtmlControls;
using
System.Collections;
using
System.Collections.Generic;
![](https://i-blog.csdnimg.cn/blog_migrate/3ded721644b58dd96e5245c56be261e9.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/502ee85f91c5d92ab66ed888d7a04fa0.gif)
/**/
/// <summary>
/// DataAccessLayer
/// </summary>
public
class
DataManager
![](https://i-blog.csdnimg.cn/blog_migrate/502ee85f91c5d92ab66ed888d7a04fa0.gif)
{
private SqlConnection con = null; //连接对象
private SqlCommand cmd = null; //command执行对象
private SqlDataAdapter da = null; //适配器对象
![](https://i-blog.csdnimg.cn/blog_migrate/21a33d1eda8ad922abd5a0f20a772ac3.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/51e1f491e927fc2c44c55d28756ea5ea.gif)
/**//// <summary>
/// DAL对象构造
/// </summary>
public DataManager()
![](https://i-blog.csdnimg.cn/blog_migrate/51e1f491e927fc2c44c55d28756ea5ea.gif)
{
//
// TODO: Add constructor logic here
//
}
![](https://i-blog.csdnimg.cn/blog_migrate/21a33d1eda8ad922abd5a0f20a772ac3.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/51e1f491e927fc2c44c55d28756ea5ea.gif)
/**//// <summary>
/// 打开数据库连接
/// </summary>
private void OpenConnection()
![](https://i-blog.csdnimg.cn/blog_migrate/51e1f491e927fc2c44c55d28756ea5ea.gif)
{
try
![](https://i-blog.csdnimg.cn/blog_migrate/51e1f491e927fc2c44c55d28756ea5ea.gif)
{
string conString = ConfigurationManager.ConnectionStrings["localDB"].ConnectionString;
con = new SqlConnection(conString);
if (ConnectionState.Closed == con.State)
![](https://i-blog.csdnimg.cn/blog_migrate/51e1f491e927fc2c44c55d28756ea5ea.gif)
{
con.Open();
}
}
catch (SqlException ex)
![](https://i-blog.csdnimg.cn/blog_migrate/51e1f491e927fc2c44c55d28756ea5ea.gif)
{
throw new Exception("数据库无法访问", ex);
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/21a33d1eda8ad922abd5a0f20a772ac3.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/51e1f491e927fc2c44c55d28756ea5ea.gif)
/**//// <summary>
/// 关闭数据库连接
/// </summary>
private void CloseConnection()
![](https://i-blog.csdnimg.cn/blog_migrate/51e1f491e927fc2c44c55d28756ea5ea.gif)
{
if (ConnectionState.Open == con.State)
![](https://i-blog.csdnimg.cn/blog_migrate/51e1f491e927fc2c44c55d28756ea5ea.gif)
{
try
![](https://i-blog.csdnimg.cn/blog_migrate/51e1f491e927fc2c44c55d28756ea5ea.gif)
{
con.Close();
}
catch (SqlException ex)
![](https://i-blog.csdnimg.cn/blog_migrate/51e1f491e927fc2c44c55d28756ea5ea.gif)
{
throw new Exception("数据库无法关闭", ex);
}
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/21a33d1eda8ad922abd5a0f20a772ac3.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/51e1f491e927fc2c44c55d28756ea5ea.gif)
/**//// <summary>
/// 取得用户列表
/// </summary>
/// <param name="rowIndex">行索引</param>
/// <param name="recordCount">页显示量(增量)</param>
/// <returns>用户列表数据集</returns>
public DataSet GetUsers(int rowIndex, int recordCount)
![](https://i-blog.csdnimg.cn/blog_migrate/51e1f491e927fc2c44c55d28756ea5ea.gif)
{
OpenConnection();
try
![](https://i-blog.csdnimg.cn/blog_migrate/51e1f491e927fc2c44c55d28756ea5ea.gif)
{
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetUsers";
SqlParameter spRowIndex = new SqlParameter("@RowIndex",SqlDbType.Int,4);
spRowIndex.Direction = ParameterDirection.Input;
SqlParameter spRecordCount = new SqlParameter("@RecordCount",SqlDbType.Int,4);
spRecordCount.Direction = ParameterDirection.Input;
![](https://i-blog.csdnimg.cn/blog_migrate/21a33d1eda8ad922abd5a0f20a772ac3.gif)
cmd.Parameters.Add(spRowIndex);
cmd.Parameters.Add(spRecordCount);
![](https://i-blog.csdnimg.cn/blog_migrate/21a33d1eda8ad922abd5a0f20a772ac3.gif)
spRowIndex.Value = rowIndex;
spRecordCount.Value = recordCount;
![](https://i-blog.csdnimg.cn/blog_migrate/21a33d1eda8ad922abd5a0f20a772ac3.gif)
da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "MyUsers");
![](https://i-blog.csdnimg.cn/blog_migrate/21a33d1eda8ad922abd5a0f20a772ac3.gif)
return ds;
}
catch (SqlException ex)
![](https://i-blog.csdnimg.cn/blog_migrate/51e1f491e927fc2c44c55d28756ea5ea.gif)
{
throw new Exception("无法取得有效数据", ex);
}
finally
![](https://i-blog.csdnimg.cn/blog_migrate/51e1f491e927fc2c44c55d28756ea5ea.gif)
{
CloseConnection();
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/21a33d1eda8ad922abd5a0f20a772ac3.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/51e1f491e927fc2c44c55d28756ea5ea.gif)
/**//// <summary>
/// 取得用户总数
/// </summary>
/// <returns>用户总数</returns>
public int GetUsersCount()
![](https://i-blog.csdnimg.cn/blog_migrate/51e1f491e927fc2c44c55d28756ea5ea.gif)
{
OpenConnection();
try
![](https://i-blog.csdnimg.cn/blog_migrate/51e1f491e927fc2c44c55d28756ea5ea.gif)
{
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetUsersCount";
int count = Convert.ToInt32(cmd.ExecuteScalar().ToString());
return count;
}
catch (SqlException ex)
![](https://i-blog.csdnimg.cn/blog_migrate/51e1f491e927fc2c44c55d28756ea5ea.gif)
{
throw new Exception("无法取得有效数据", ex);
}
finally
![](https://i-blog.csdnimg.cn/blog_migrate/51e1f491e927fc2c44c55d28756ea5ea.gif)
{
CloseConnection();
}
}
对DAL也有几项说明,DataManager.GetUsers(int rowIndex, int recordCount)方法的参数只能有两个,
参数名可以随意,但意义却要与调用的存储过程GetUsers要一致,rowIndex必须是行索引,recordCount必须是页显示量.
OK!接下来在页面上放上一个GridView和一个ObjectDataSource,设置代码片段如下:
<
asp:GridView
ID
="gvMyUsers"
runat
="server"
AllowPaging
="True"
CellPadding
="4"
DataSourceID
="objMyUsers"
ForeColor
="#333333"
GridLines
="None"
AutoGenerateDeleteButton
="True"
AutoGenerateEditButton
="True"
AutoGenerateColumns
="False"
DataKeyNames
="UserID"
PageSize
="10"
>
<
FooterStyle
BackColor
="#507CD1"
Font-Bold
="True"
ForeColor
="White"
/>
<
RowStyle
BackColor
="#EFF3FB"
/>
<
EditRowStyle
BackColor
="#2461BF"
/>
<
SelectedRowStyle
BackColor
="#D1DDF1"
Font-Bold
="True"
ForeColor
="#333333"
/>
<
PagerStyle
BackColor
="#2461BF"
ForeColor
="White"
HorizontalAlign
="Center"
/>
<
HeaderStyle
BackColor
="#507CD1"
Font-Bold
="True"
ForeColor
="White"
/>
<
AlternatingRowStyle
BackColor
="White"
/>
</
asp:GridView
>
<
asp:ObjectDataSource
ID
="objMyUsers"
runat
="server"
EnablePaging
="True"
MaximumRowsParameterName
="recordCount"
SelectCountMethod
="GetUsersCount"
SelectMethod
="GetUsers"
StartRowIndexParameterName
="rowIndex"
TypeName
="DataManager"
>
</
asp:ObjectDataSource
>
指定每页显示最大数据量(页显示量)的参数:
MaximumRowsParameterName="recordCount"
指定取得数据总数方法:
SelectCountMethod="GetUsersCount"
指定分页方法:
SelectMethod="GetUsers"
指定起始行参数:
StartRowIndexParameterName="rowIndex"
指定前面方法所属类名(全限定名):
TypeName="DataManager
这几个设置是必须的,一定要对应.
现在轻轻的点击一下运行吧(什么什么?还没写事件?...我说老铁,都啥年月了,懒人还有用那玩儿吗?).到这里,一个最快速的分页其实已经做完了.
前面的步骤都是必须的,但仍然可以有一些变化.比如,若你已经按以往的办法写好了一个类似于
Create
Procedure
GetUsers
@PageIndex
int
,
@PageSize
int
as
begin
![](https://i-blog.csdnimg.cn/blog_migrate/b854634c0904529d4018c4c3336be836.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/b854634c0904529d4018c4c3336be836.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/b854634c0904529d4018c4c3336be836.gif)
end
这样的存储过程了,没关系,你在DAL的DataManager.GetUsers(int rowIndex, int recordCount)这里,
把rowIndex,recordCount在调用存储过程前转换成PageIndex,PageSize就好了,也不过是
PageIndex = rowIndex/recordCount +1; PageSize =recordCount;这样的语句就能搞定啦.再或者改存储过程里面也行(虽然有点不值得),不多说了.自己耍一把就知道了.
你一定心里还有疑问,没错,DataManager.GetUsers(int rowIndex, int recordCount)这个方法,更多时候需要多一些参数,也就是说,我们分页取数据的过程,还需要一些筛选条件,但这个方法是要给 ObjectDataSource的selectMethod方法,不能增加别的参数,形成矛盾怎么办??欲知解决之道,且等下回分解.....(快跑 啊.....鸡蛋来啦!!)