按照常规,先准备一张表和一些存储过程,如下:
Table
:MyUsers
UserID(
int
,
primary
key
,
identity
)
--
自动增加字段,用户ID标识列
UserName(
nvarchar
(
50
),
not
null
)
--
用户名
Description(
nvarchar
(
50
),
not
null
)
--
备注
Procedure
:
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
CREATE
PROCEDURE
[
dbo
]
.
[
AddUser
]
--
增加一个用户
@UserName
nvarchar
(
50
),
@Description
nvarchar
(
50
)
AS
BEGIN
SET
NOCOUNT
ON
;
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
transaction
t
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
insert
into
MyUsers(UserName,Description)
values
(
@UserName
,
@Description
)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
if
@@error
<>
0
begin
rollback
transaction
t
end
else
begin
commit
transaction
end
END
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
CREATE
PROCEDURE
[
dbo
]
.
[
DeleteUserByUserID
]
--
删除一个用户
@UserID
int
AS
BEGIN
SET
NOCOUNT
ON
;
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
Begin
Transaction
t
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
delete
from
MyUsers
where
UserID
=
@UserID
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
if
@@error
<>
0
begin
rollback
transaction
t
end
else
begin
commit
transaction
end
END
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
CREATE
PROCEDURE
[
dbo
]
.
[
UpdateUser
]
--
修改用户姓名或备注
@UserID
int
,
@UserName
nvarchar
(
50
),
@Description
nvarchar
(
50
)
AS
BEGIN
SET
NOCOUNT
ON
;
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
transaction
t
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
update
MyUsers
set
UserName
=
@UserName
,
Description
=
@Description
where
UserID
=
@UserID
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
if
@@error
<>
0
begin
rollback
transaction
t
end
else
begin
commit
transaction
t
end
END
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
CREATE
PROCEDURE
[
dbo
]
.
[
GetUsers
]
--
得到用户列表,注意这里的参数
@RowIndex
int
,
@RecordCount
int
AS
BEGIN
SET
NOCOUNT
ON
;
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
With
VUsers
as
(
select
*
,row_number()
over
(
order
by
UserID
desc
)
as
RowNum
from
MyUsers
)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
select
*
from
VUsers
where
RowNum
>
@RowIndex
and
RowNum
<=
(
@RowIndex
+
@RecordCount
)
END
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.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;
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**/
///
///DataAccessLayer
///
public
class
DataManager
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
privateSqlConnectioncon=null;//连接对象
privateSqlCommandcmd=null;//command执行对象
privateSqlDataAdapterda=null;//适配器对象
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////
///DAL对象构造
///
publicDataManager()
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
//
//TODO:Addconstructorlogichere
//
}
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////
///打开数据库连接
///
privatevoidOpenConnection()
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
try
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
stringconString=ConfigurationManager.ConnectionStrings["localDB"].ConnectionString;
con=newSqlConnection(conString);
if(ConnectionState.Closed==con.State)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
con.Open();
}
}
catch(SqlExceptionex)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
thrownewException("数据库无法访问",ex);
}
}
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////
///关闭数据库连接
///
privatevoidCloseConnection()
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
if(ConnectionState.Open==con.State)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
try
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
con.Close();
}
catch(SqlExceptionex)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
thrownewException("数据库无法关闭",ex);
}
}
}
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////
///取得用户列表
///
///行索引
///页显示量(增量)
///
用户列表数据集
publicDataSetGetUsers(introwIndex,intrecordCount)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
OpenConnection();
try
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
cmd=newSqlCommand();
cmd.Connection=con;
cmd.CommandType=CommandType.StoredProcedure;
cmd.CommandText="GetUsers";
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
SqlParameterspRowIndex=newSqlParameter("@RowIndex",SqlDbType.Int,4);
spRowIndex.Direction=ParameterDirection.Input;
SqlParameterspRecordCount=newSqlParameter("@RecordCount",SqlDbType.Int,4);
spRecordCount.Direction=ParameterDirection.Input;
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
cmd.Parameters.Add(spRowIndex);
cmd.Parameters.Add(spRecordCount);
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
spRowIndex.Value=rowIndex;
spRecordCount.Value=recordCount;
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
da=newSqlDataAdapter(cmd);
DataSetds=newDataSet();
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
da.Fill(ds,"MyUsers");
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
returnds;
}
catch(SqlExceptionex)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
thrownewException("无法取得有效数据",ex);
}
finally
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
CloseConnection();
}
}
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////
///取得用户总数
///
///
用户总数
publicintGetUsersCount()
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
OpenConnection();
try
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
cmd=newSqlCommand();
cmd.Connection=con;
cmd.CommandType=CommandType.StoredProcedure;
cmd.CommandText="GetUsersCount";
intcount=Convert.ToInt32(cmd.ExecuteScalar().ToString());
returncount;
}
catch(SqlExceptionex)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
thrownewException("无法取得有效数据",ex);
}
finally
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.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:ObjectDataSourceID="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
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
![](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方法,不能增加别的参数,形成矛盾怎么办??欲知解决之道,且等下回分解.....(快跑 啊.....鸡蛋来啦!!)
Posted on 2006-04-17 18:43
笑看千秋_R
书接上回,我们提到,如果我们分页的数据,在查询的存储过程中需要更多参数,在使用ObjectDataSource作为数据源完成界面级分页,我们必须 做一些其他的事情.为了让示例更为易懂,我们将上一次的MyUsers表变化一下,增加一个用户状态列.并增加两个存储过程.
Table
:MyUsers
@UserID
(
int
,
primary
key
,
identity
),
@UserName
(
nvarchar
(
50
),
not
null
),
@Description
(
nvarchar
(
50
),
not
null
),
@Status
(
bit
,
not
null
)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
Procedure
:
create
procedure
[
dbo
]
.
[
GetUsersByStatus
]
--
通过状态得到用户列表
@RowIndex
int
,
@RecordCount
int
,
@Status
bit
AS
BEGIN
SET
NOCOUNT
ON
;
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
With
VUsers
as
(
select
*
,row_number()
over
(
order
by
UserID
desc
)
as
RowNum
from
MyUsers
where
Status
=
@Status
)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
select
*
from
VUsers
where
RowNum
>
@RowIndex
and
RowNum
<=
(
@RowIndex
+
@RecordCount
)
END
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.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
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
get
{return_Status;}
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
set
{_Status=values;}
}
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
2
.增加一个方法
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
public
DataSetGetUsers(
int
rowIndex,
int
recordCount)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////
///取得用户列表
///
///行索引
///页显示量(增量)
///
用户列表数据集
publicDataSetGetUsers(introwIndex,intrecordCount)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
OpenConnection();
try
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
cmd=newSqlCommand();
cmd.Connection=con;
cmd.CommandType=CommandType.StoredProcedure;
cmd.CommandText="GetUsersByStatus";
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
SqlParameterspRowIndex=newSqlParameter("@RowIndex",SqlDbType.Int,4);
spRowIndex.Direction=ParameterDirection.Input;
SqlParameterspRecordCount=newSqlParameter("@RecordCount",SqlDbType.Int,4);
spRecordCount.Direction=ParameterDirection.Input;
SqlParameterspStatus=newSqlParameter("@Status",SqlDbType.Bit,1);
spStatus.Direction=ParameterDirection.Input;
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
cmd.Parameters.Add(spRowIndex);
cmd.Parameters.Add(spRecordCount);
cmd.Parameters.Add(spStatus);//在这里将增加的参数值隐式加入到查询
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
spRowIndex.Value=rowIndex;
spRecordCount.Value=recordCount;
spStatus.Value=_Status;
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
da=newSqlDataAdapter(cmd);
DataSetds=newDataSet();
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
da.Fill(ds,"MyUsers");
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
returnds;
}
catch(SqlExceptionex)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
thrownewException("无法取得有效数据",ex);
}
finally
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
CloseConnection();
}
}
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////
///根据用户状态取得用户总数
///
///
用户总数
publicintGetUsersCount()
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
OpenConnection();
try
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
cmd=newSqlCommand();
cmd.Connection=con;
cmd.CommandType=CommandType.StoredProcedure;
cmd.CommandText="GetUsersCountByStatus";
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
SqlParameterspStatus=newSqlParameter("@Status",SqlDbType.Bit,1);
spStatus.Direction=ParameterDirection.Input;
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
cmd.Parameters.Add(spStatus);
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
spStatus.Value=_Status;
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
intcount=Convert.ToInt32(cmd.ExecuteScalar().ToString());
returncount;
}
catch(SqlExceptionex)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
thrownewException("无法取得有效数据",ex);
}
finally
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
CloseConnection();
}
}
}
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
3
.在页面增加一个DropDownList,设置如下
<
asp:DropDownListID
=
"
ddShowUsersByStaus
"
runat
=
"
server
"
AutoPostBack
=
"
True
"
>
<
asp:ListItemSelected
=
"
True
"
Value
=
"
all
"
>
全部
asp:ListItem>
<asp:ListItemValue="false">禁用
asp:ListItem>
<asp:ListItemValue="true">激活
asp:ListItem>
asp:DropDownList>
div>
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
4.在WebSite增加一个事件
protectedObjectDataSource_Created(objectsender,ObjectDataSourceEventArgse)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
if(!ddShowUsersByStaus.SelectedValue.Equals("all"))
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
DataManagerdm=(DataManager)e.GetInstance();
dm.Status=ddShowUsersByStaus.SelectedValue;
}
}
这样,在选择了用户状态之后,ObjectDataSource会首先将选择的值赋给Status属性,然后才会去调用GetUsers()方法取得用户列表至此,我们可以在不必改变原有方法接口的前提下,解决增加了参数的问题.
然而,这样似乎还是很麻烦,无谓在DataManager类中增加属性,使得Web和DataManager成为紧耦合应用,同时也非"懒人"所为.再介绍一种更为灵活的方式供大家参考
(2)
ObjectDataSource类的SelectParameters是可以动态改变的,据此,我们按下面的顺序来更改应用.
1
.Datamanager中重载GetUsers()和GetUsersCount()两个方法.代码如下
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
public
DataSetGetUsers(
int
rowIndex,
int
recordCount,
bool
status)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
OpenConnection();
try
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
cmd=newSqlCommand();
cmd.Connection=con;
cmd.CommandType=CommandType.StoredProcedure;
cmd.CommandText="GetUsersByStatus";
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
SqlParameterspRowIndex=newSqlParameter("@RowIndex",SqlDbType.Int,4);
spRowIndex.Direction=ParameterDirection.Input;
SqlParameterspRecordCount=newSqlParameter("@RecordCount",SqlDbType.Int,4);
spRecordCount.Direction=ParameterDirection.Input;
SqlParameterspStatus=newSqlParameter("@Status",SqlDbType.Bit,1);
spStatus.Direction=ParameterDirection.Input;
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
cmd.Parameters.Add(spRowIndex);
cmd.Parameters.Add(spRecordCount);
cmd.Parameters.Add(spStatus);
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
spRowIndex.Value=rowIndex;
spRecordCount.Value=recordCount;
spStatus.Value=status;
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
da=newSqlDataAdapter(cmd);
DataSetds=newDataSet();
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
da.Fill(ds,"MyUsers");
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
returnds;
}
catch(SqlExceptionex)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
thrownewException("无法取得有效数据",ex);
}
finally
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
CloseConnection();
}
}
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
public
int
GetUsersCount(
bool
status)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
OpenConnection();
try
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
cmd=newSqlCommand();
cmd.Connection=con;
cmd.CommandType=CommandType.StoredProcedure;
cmd.CommandText="GetUsersCountByStatus";
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
SqlParameterspStatus=newSqlParameter("@Status",SqlDbType.Bit,1);
spStatus.Direction=ParameterDirection.Input;
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
cmd.Parameters.Add(spStatus);
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
spStatus.Value=status;
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
intcount=Convert.ToInt32(cmd.ExecuteScalar().ToString());
returncount;
}
catch(SqlExceptionex)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
thrownewException("无法取得有效数据",ex);
}
finally
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
CloseConnection();
}
}
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
2
.在页面增加DropDownList设置如下:
<
asp:DropDownListID
=
"
ddShowUsersByStaus
"
runat
=
"
server
"
AutoPostBack
=
"
True
"
OnSelectedIndexChanged
=
"
ddShowUsersByStaus_SelectedIndexChanged
"
>
<
asp:ListItemSelected
=
"
True
"
Value
=
"
all
"
>
全部
asp:ListItem>
<asp:ListItemValue="false">禁用
asp:ListItem>
<asp:ListItemValue="true">激活
asp:ListItem>
asp:DropDownList>
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
3.在DropDownList的SelectedIndexChanged事件增加代码:
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/None.gif)
protectedvoidddShowUsersByStaus_SelectedIndexChanged(objectsender,EventArgse)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
objMyUsers.SelectParameters.Clear();
gvMyUsers.PageIndex=0;
if("all".Equals(ddShowUsersByStaus.SelectedValue))
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
else
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
ParameterpStatus=newParameter("status",TypeCode.Boolean);
![](http://shouzheng.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
objMyUsers.SelectParameters.Add(pStatus);
pStatus.DefaultValue=ddShowUsersByStaus.SelectedValue;
}
}
如何?这样看上去只是给ObjectDataSource的参数集合动态增加了一个参数,它自己就会去调用重载后的分页方法,省得我们在专门指定 SelectMethod.编码简洁多了,维护起来也更容易,增加分页的条件无非是增加重载的方法和变更动态增加参数的代码,无意间发现这就是"开-闭" 原则的小应用.(估计没人在乎这个吧...)
采用GridView作为数据绑定控件,好处在于ObjectDataSource会根据GridView的变化自动向数据库取出当前需要的数据,而不用 我们为它担忧一丝一毫.至少不用为取得空数据和页码不正确睡不着觉了.数据操作应该是完整的增删查改.我们把查询做得很彻底了,后面将介绍增加,删除,修 改记录的操作,我想,GridView&ObjectDataSource
不会让大家失望的.未完待续.......(To Be Continue!!)