Posted on 2006-04-17 18:43
笑看千秋_R
然而,这样似乎还是很麻烦,无谓在DataManager类中增加属性,使得Web和DataManager成为紧耦合应用,同时也非"懒人"所为.再介绍 一种更为灵活的方式供大家参考
Table
: MyUsers
@UserID ( int , primary key , identity ),
@UserName ( nvarchar ( 50 ), not null ),
@Description ( nvarchar ( 50 ), not null ),
@Status ( bit , not null )
Procedure :
create procedure [ dbo ] . [ GetUsersByStatus ] -- 通过状态得到用户列表
@RowIndex int ,
@RecordCount int ,
@Status bit
AS
BEGIN
SET NOCOUNT ON ;
With VUsers as (
select * ,row_number() over ( order by UserID desc ) as RowNum
from MyUsers where Status = @Status
)
select * from VUsers
where RowNum > @RowIndex and RowNum <= ( @RowIndex + @RecordCount )
END
CREATE PROCEDURE [ dbo ] . [ GetUsersCountByStatus ] -- 取得通过状态得到用户列表的总数
@Status bit
AS
BEGIN
SET NOCOUNT ON ;
select count (UserID) from MyUsers where Status = @Status
END
@UserID ( int , primary key , identity ),
@UserName ( nvarchar ( 50 ), not null ),
@Description ( nvarchar ( 50 ), not null ),
@Status ( bit , not null )
Procedure :
create procedure [ dbo ] . [ GetUsersByStatus ] -- 通过状态得到用户列表
@RowIndex int ,
@RecordCount int ,
@Status bit
AS
BEGIN
SET NOCOUNT ON ;
With VUsers as (
select * ,row_number() over ( order by UserID desc ) as RowNum
from MyUsers where Status = @Status
)
select * from VUsers
where RowNum > @RowIndex and RowNum <= ( @RowIndex + @RecordCount )
END
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
{
get{return _Status;}
set{_Status = values;}
}
2 .增加一个方法
public DataSet GetUsers( int rowIndex, int recordCount)
{
/**//// <summary>
/// 取得用户列表
/// </summary>
/// <param name="rowIndex">行索引</param>
/// <param name="recordCount">页显示量(增量)</param>
/// <returns>用户列表数据集</returns>
public DataSet GetUsers(int rowIndex, int recordCount)
{
OpenConnection();
try
{
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;
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");
return ds;
}
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 = "GetUsersCountByStatus";
SqlParameter spStatus = new SqlParameter("@Status", SqlDbType.Bit, 1);
spStatus.Direction = ParameterDirection.Input;
cmd.Parameters.Add(spStatus);
spStatus.Value = _Status;
int count = Convert.ToInt32(cmd.ExecuteScalar().ToString());
return count;
}
catch (SqlException ex)
{
throw new Exception("无法取得有效数据", ex);
}
finally
{
CloseConnection();
}
}
}
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 >
4 .在WebSite增加一个事件
protected ObjectDataSource_Created( object sender, ObjectDataSourceEventArgs e)
{
if(!ddShowUsersByStaus.SelectedValue.Equals("all"))
{
DataManager dm = (DataManager)e.GetInstance();
dm.Status = ddShowUsersByStaus.SelectedValue;
}
}
这样,在选择了用户状态之后,ObjectDataSource会首先将选择的值赋给Status属性,然后才会去调用GetUsers()方法取得用户列表 至此,我们可以在不必改变原有方法接口的前提下,解决增加了参数的问题.
private bool _Status;
public bool Status
{
get{return _Status;}
set{_Status = values;}
}
2 .增加一个方法
public DataSet GetUsers( int rowIndex, int recordCount)
{
/**//// <summary>
/// 取得用户列表
/// </summary>
/// <param name="rowIndex">行索引</param>
/// <param name="recordCount">页显示量(增量)</param>
/// <returns>用户列表数据集</returns>
public DataSet GetUsers(int rowIndex, int recordCount)
{
OpenConnection();
try
{
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;
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");
return ds;
}
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 = "GetUsersCountByStatus";
SqlParameter spStatus = new SqlParameter("@Status", SqlDbType.Bit, 1);
spStatus.Direction = ParameterDirection.Input;
cmd.Parameters.Add(spStatus);
spStatus.Value = _Status;
int count = Convert.ToInt32(cmd.ExecuteScalar().ToString());
return count;
}
catch (SqlException ex)
{
throw new Exception("无法取得有效数据", ex);
}
finally
{
CloseConnection();
}
}
}
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 >
4 .在WebSite增加一个事件
protected ObjectDataSource_Created( object sender, ObjectDataSourceEventArgs e)
{
if(!ddShowUsersByStaus.SelectedValue.Equals("all"))
{
DataManager dm = (DataManager)e.GetInstance();
dm.Status = ddShowUsersByStaus.SelectedValue;
}
}
然而,这样似乎还是很麻烦,无谓在DataManager类中增加属性,使得Web和DataManager成为紧耦合应用,同时也非"懒人"所为.再介绍 一种更为灵活的方式供大家参考
(2)
ObjectDataSource类的SelectParameters是可以动态改变的,据此,我们按下面的顺序来更改应用.
1
.Datamanager中重载GetUsers()和GetUsersCount()两个方法.代码如下
public DataSet GetUsers( int rowIndex, int recordCount, bool status)
{
OpenConnection();
try
{
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;
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");
return ds;
}
catch (SqlException ex)
{
throw new Exception("无法取得有效数据", ex);
}
finally
{
CloseConnection();
}
}
public int GetUsersCount( bool status)
{
OpenConnection();
try
{
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetUsersCountByStatus";
SqlParameter spStatus = new SqlParameter("@Status", SqlDbType.Bit, 1);
spStatus.Direction = ParameterDirection.Input;
cmd.Parameters.Add(spStatus);
spStatus.Value = status;
int count = Convert.ToInt32(cmd.ExecuteScalar().ToString());
return count;
}
catch (SqlException ex)
{
throw new Exception("无法取得有效数据", ex);
}
finally
{
CloseConnection();
}
}
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 >
3 .在DropDownList的SelectedIndexChanged事件增加代码:
protected void ddShowUsersByStaus_SelectedIndexChanged( object sender, EventArgs e)
{
objMyUsers.SelectParameters.Clear();
gvMyUsers.PageIndex = 0;
if ("all".Equals(ddShowUsersByStaus.SelectedValue))
{
}
else
{
Parameter pStatus = new Parameter("status", TypeCode.Boolean);
objMyUsers.SelectParameters.Add(pStatus);
pStatus.DefaultValue = ddShowUsersByStaus.SelectedValue;
}
}
public DataSet GetUsers( int rowIndex, int recordCount, bool status)
{
OpenConnection();
try
{
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;
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");
return ds;
}
catch (SqlException ex)
{
throw new Exception("无法取得有效数据", ex);
}
finally
{
CloseConnection();
}
}
public int GetUsersCount( bool status)
{
OpenConnection();
try
{
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetUsersCountByStatus";
SqlParameter spStatus = new SqlParameter("@Status", SqlDbType.Bit, 1);
spStatus.Direction = ParameterDirection.Input;
cmd.Parameters.Add(spStatus);
spStatus.Value = status;
int count = Convert.ToInt32(cmd.ExecuteScalar().ToString());
return count;
}
catch (SqlException ex)
{
throw new Exception("无法取得有效数据", ex);
}
finally
{
CloseConnection();
}
}
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 >
3 .在DropDownList的SelectedIndexChanged事件增加代码:
protected void ddShowUsersByStaus_SelectedIndexChanged( object sender, EventArgs e)
{
objMyUsers.SelectParameters.Clear();
gvMyUsers.PageIndex = 0;
if ("all".Equals(ddShowUsersByStaus.SelectedValue))
{
}
else
{
Parameter pStatus = new Parameter("status", TypeCode.Boolean);
objMyUsers.SelectParameters.Add(pStatus);
pStatus.DefaultValue = ddShowUsersByStaus.SelectedValue;
}
}
如何?这样看上去只是给ObjectDataSource的参数集合动态增加了一个参数,它自己就会去调用重载后的分页方法,省得我们在专门指定 SelectMethod.编码简洁多了,维护起来也更容易,增加分页的条件无非是增加重载的方法和变更动态增加参数的代码,无意间发现这就是"开-闭" 原则的小应用.(估计没人在乎这个吧...)
采用GridView作为数据绑定控件,好处在于ObjectDataSource会根据GridView的变化自动向数据库取出当前需要的数据,而不用 我们为它担忧一丝一毫.至少不用为取得空数据和页码不正确睡不着觉了.数据操作应该是完整的增删查改.我们把查询做得很彻底了,后面将介绍增加,删除,修 改记录的操作,我想,GridView&ObjectDataSource