书接上回,我们提到,如果我们分页的数据,在查询的存储过程中需要更多参数,在使用ObjectDataSource作为数据源完成界面级分页,我们必须做一些其他的事情.为了让示例更为易懂,我们将上一次的MyUsers表变化一下,增加一个用户状态列.并增加两个存储过程.
Table
: MyUsers
@UserID
(
int
,
primary
key
,
identity
),
@UserName
(
nvarchar
(
50
),
not
null
),
@Description
(
nvarchar
(
50
),
not
null
),
@Status
(
bit
,
not
null
)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
Procedure
:
create
procedure
[
dbo
]
.
[
GetUsersByStatus
]
--
通过状态得到用户列表
@RowIndex
int
,
@RecordCount
int
,
@Status
bit
AS
BEGIN
SET
NOCOUNT
ON
;
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
With
VUsers
as
(
select
*
,row_number()
over
(
order
by
UserID
desc
)
as
RowNum
from
MyUsers
where
Status
=
@Status
)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
select
*
from
VUsers
where
RowNum
>
@RowIndex
and
RowNum
<=
(
@RowIndex
+
@RecordCount
)
END
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
CREATE
PROCEDURE
[
dbo
]
.
[
GetUsersCountByStatus
]
--
取得通过状态得到用户列表的总数
@Status
bit
AS
BEGIN
SET
NOCOUNT
ON
;
select
count
(UserID)
from
MyUsers
where
Status
=
@Status
END
在WebSite的DataManager中,再增加两个带参数的分页查询的方法.下面介绍两种带参的分页查询方法的写法(原因是和ObjectDataSource的运行机制有关):
(1)
MSDN中提到,ObjectDataSource有这样一个事件:ObjectDataSources_Created,它将在每次ObjectDataSource
初始化TypeName指定的类时触发.也就是说,如果分页的方法需要除RowIndex,RecordCount外的其他参数的话可以
借助此事件来增加参数.由此,可以按下面方式增加分页方法:
1
.在DataManager类中,增加一个属性
private
bool
_Status;
public
bool
Status
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
{
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
get
{
return
_Status;}
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
set
{_Status
=
values;}
}
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
2
.增加一个方法
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
public
DataSet GetUsers(
int
rowIndex,
int
recordCount)
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
{
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.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/3112b7b6526db5bc83e275260ae60525.gif)
{
OpenConnection();
try
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
cmd
=
new
SqlCommand();
cmd.Connection
=
con;
cmd.CommandType
=
CommandType.StoredProcedure;
cmd.CommandText
=
"
GetUsersByStatus
"
;
SqlParameter spRowIndex
=
new
SqlParameter(
"
@RowIndex
"
,SqlDbType.Int,
4
);
spRowIndex.Direction
=
ParameterDirection.Input;
SqlParameter spRecordCount
=
new
SqlParameter(
"
@RecordCount
"
,SqlDbType.Int,
4
);
spRecordCount.Direction
=
ParameterDirection.Input;
SqlParameter spStatus
=
new
SqlParameter(
"
@Status
"
, SqlDbType.Bit,
1
);
spStatus.Direction
=
ParameterDirection.Input;
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
cmd.Parameters.Add(spRowIndex);
cmd.Parameters.Add(spRecordCount);
cmd.Parameters.Add(spStatus);
//
在这里将增加的参数值隐式加入到查询
spRowIndex.Value
=
rowIndex;
spRecordCount.Value
=
recordCount;
spStatus.Value
=
_Status;
da
=
new
SqlDataAdapter(cmd);
DataSet ds
=
new
DataSet();
da.Fill(ds,
"
MyUsers
"
);
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
return
ds;
}
catch
(SqlException ex)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
throw
new
Exception(
"
无法取得有效数据
"
, ex);
}
finally
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
CloseConnection();
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
/**/
///
<summary>
///
根据用户状态取得用户总数
///
</summary>
///
<returns>
用户总数
</returns>
public
int
GetUsersCount()
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
OpenConnection();
try
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
cmd
=
new
SqlCommand();
cmd.Connection
=
con;
cmd.CommandType
=
CommandType.StoredProcedure;
cmd.CommandText
=
"
GetUsersCountByStatus
"
;
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
SqlParameter spStatus
=
new
SqlParameter(
"
@Status
"
, SqlDbType.Bit,
1
);
spStatus.Direction
=
ParameterDirection.Input;
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
cmd.Parameters.Add(spStatus);
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
spStatus.Value
=
_Status;
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
int
count
=
Convert.ToInt32(cmd.ExecuteScalar().ToString());
return
count;
}
catch
(SqlException ex)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
throw
new
Exception(
"
无法取得有效数据
"
, ex);
}
finally
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
CloseConnection();
}
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
3
.在页面增加一个DropDownList,设置如下
<
asp:DropDownList ID
=
"
ddShowUsersByStaus
"
runat
=
"
server
"
AutoPostBack
=
"
True
"
>
<
asp:ListItem Selected
=
"
True
"
Value
=
"
all
"
>
全部
</
asp:ListItem
>
<
asp:ListItem Value
=
"
false
"
>
禁用
</
asp:ListItem
>
<
asp:ListItem Value
=
"
true
"
>
激活
</
asp:ListItem
>
</
asp:DropDownList
></
div
>
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
4
.在WebSite增加一个事件
protected
ObjectDataSource_Created(
object
sender, ObjectDataSourceEventArgs e)
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
{
if
(
!
ddShowUsersByStaus.SelectedValue.Equals(
"
all
"
))
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
DataManager dm
=
(DataManager)e.GetInstance();
dm.Status
=
ddShowUsersByStaus.SelectedValue;
}
}
这样,在选择了用户状态之后,ObjectDataSource会首先将选择的值赋给Status属性,然后才会去调用GetUsers()方法取得用户列表 至此,我们可以在不必改变原有方法接口的前提下,解决增加了参数的问题.
然而,这样似乎还是很麻烦,无谓在DataManager类中增加属性,使得Web和DataManager成为紧耦合应用,同时也非"懒人"所为.再介绍 一种更为灵活的方式供大家参考
(2)
ObjectDataSource类的SelectParameters是可以动态改变的,据此,我们按下面的顺序来更改应用.
1
.Datamanager中重载GetUsers()和GetUsersCount()两个方法.代码如下
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
public
DataSet GetUsers(
int
rowIndex,
int
recordCount,
bool
status)
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
{
OpenConnection();
try
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
cmd
=
new
SqlCommand();
cmd.Connection
=
con;
cmd.CommandType
=
CommandType.StoredProcedure;
cmd.CommandText
=
"
GetUsersByStatus
"
;
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
SqlParameter spRowIndex
=
new
SqlParameter(
"
@RowIndex
"
, SqlDbType.Int,
4
);
spRowIndex.Direction
=
ParameterDirection.Input;
SqlParameter spRecordCount
=
new
SqlParameter(
"
@RecordCount
"
, SqlDbType.Int,
4
);
spRecordCount.Direction
=
ParameterDirection.Input;
SqlParameter spStatus
=
new
SqlParameter(
"
@Status
"
, SqlDbType.Bit,
1
);
spStatus.Direction
=
ParameterDirection.Input;
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
cmd.Parameters.Add(spRowIndex);
cmd.Parameters.Add(spRecordCount);
cmd.Parameters.Add(spStatus);
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
spRowIndex.Value
=
rowIndex;
spRecordCount.Value
=
recordCount;
spStatus.Value
=
status;
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
da
=
new
SqlDataAdapter(cmd);
DataSet ds
=
new
DataSet();
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
da.Fill(ds,
"
MyUsers
"
);
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
return
ds;
}
catch
(SqlException ex)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
throw
new
Exception(
"
无法取得有效数据
"
, ex);
}
finally
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
CloseConnection();
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
public
int
GetUsersCount(
bool
status)
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
{
OpenConnection();
try
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
cmd
=
new
SqlCommand();
cmd.Connection
=
con;
cmd.CommandType
=
CommandType.StoredProcedure;
cmd.CommandText
=
"
GetUsersCountByStatus
"
;
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
SqlParameter spStatus
=
new
SqlParameter(
"
@Status
"
, SqlDbType.Bit,
1
);
spStatus.Direction
=
ParameterDirection.Input;
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
cmd.Parameters.Add(spStatus);
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
spStatus.Value
=
status;
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
int
count
=
Convert.ToInt32(cmd.ExecuteScalar().ToString());
return
count;
}
catch
(SqlException ex)
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
throw
new
Exception(
"
无法取得有效数据
"
, ex);
}
finally
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
CloseConnection();
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
2
.在页面增加DropDownList设置如下:
<
asp:DropDownList ID
=
"
ddShowUsersByStaus
"
runat
=
"
server
"
AutoPostBack
=
"
True
"
OnSelectedIndexChanged
=
"
ddShowUsersByStaus_SelectedIndexChanged
"
>
<
asp:ListItem Selected
=
"
True
"
Value
=
"
all
"
>
全部
</
asp:ListItem
>
<
asp:ListItem Value
=
"
false
"
>
禁用
</
asp:ListItem
>
<
asp:ListItem Value
=
"
true
"
>
激活
</
asp:ListItem
>
</
asp:DropDownList
>
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
3
.在DropDownList的SelectedIndexChanged事件增加代码:
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
protected
void
ddShowUsersByStaus_SelectedIndexChanged(
object
sender, EventArgs e)
![](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
{
objMyUsers.SelectParameters.Clear();
gvMyUsers.PageIndex
=
0
;
if
(
"
all
"
.Equals(ddShowUsersByStaus.SelectedValue))
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
}
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
else
![](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
{
Parameter pStatus
=
new
Parameter(
"
status
"
, TypeCode.Boolean);
objMyUsers.SelectParameters.Add(pStatus);
pStatus.DefaultValue
=
ddShowUsersByStaus.SelectedValue;
}
}
如何?这样看上去只是给ObjectDataSource的参数集合动态增加了一个参数,它自己就会去调用重载后的分页方法,省得我们在专门指定SelectMethod.编码简洁多了,维护起来也更容易,增加分页的条件无非是增加重载的方法和变更动态增加参数的代码,无意间发现这就是"开-闭"原则的小应用.(估计没人在乎这个吧...)
采用GridView作为数据绑定控件,好处在于ObjectDataSource会根据GridView的变化自动向数据库取出当前需要的数据,而不用我们为它担忧一丝一毫.至少不用为取得空数据和页码不正确睡不着觉了.数据操作应该是完整的增删查改.我们把查询做得很彻底了,后面将介绍增加,删除,修改记录的操作,我想,GridView&ObjectDataSource
不会让大家失望的.