控件代码及测试例子:http://files.cnblogs.com/wuhuacong/CommonSearch.rar
使用场景: 在列表页面中,一般有好几个条件, 用户进行查询时候,需要根据这几个条件进行过滤查询.但在组装这些过滤条件的时候,代码比较烦琐臃肿,本组件代码为解决该问题而设计。
使用目的: 1.减少对参数非空的条件判断 2. 可以构造出参数化的DbCommand对象,简化操作. 3.适当修改后可以用于其他数据访问的参数化参数生成.4.构造Sql语句或者参数化条件更加易读
1. 生成SQL条件语句
如有几个字段,需要根据不同的字段进行过滤,想生成的SQL语句如下:
Where (1=1) AND AA2 Like '%AA2Value%' AND AA6 >= 'Value6' AND AA7 <= 'value7' AND AA3 = 'Value3' AND AA4 < 'Value4' AND AA5 > 'Value5' AND AA <> '1'
那么代码如下:
SearchCondition search
=
new
SearchCondition();
search.AddCondition(
"
AA
"
,
1
, SqlOperator.NotEqual)
.AddCondition(
"
AA2
"
,
"
AA2Value
"
, SqlOperator.Like)
.AddCondition(
"
AA3
"
,
"
Value3
"
, SqlOperator.Equal)
.AddCondition(
"
AA4
"
,
"
Value4
"
, SqlOperator.LessThan)
.AddCondition(
"
AA5
"
,
"
Value5
"
, SqlOperator.MoreThan)
.AddCondition(
"
AA6
"
,
"
Value6
"
, SqlOperator.MoreThanOrEqual)
.AddCondition(
"
AA7
"
,
"
value7
"
, SqlOperator.LessThanOrEqual);
string
conditionSql
=
search.BuildConditionSql();
2. 生成基于Enterprise Library的DbCommand对象
Database db
=
DatabaseFactory.CreateDatabase();
SearchCondition search
=
new
SearchCondition();
search.AddCondition(
"
Name
"
,
"
测试
"
, SqlOperator.Like)
.AddCondition(
"
ID
"
,
1
, SqlOperator.MoreThanOrEqual);
DbCommand dbComand
=
search.BuildDbCommand(db,
"
select Comments from Test
"
,
"
Order by Name
"
);
using
(IDataReader dr
=
db.ExecuteReader(dbComand))
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
while (dr.Read())
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.txtSql.Text += "\r\n" + dr["Comments"].ToString();
}
}
下面是该控件的类对象图解
![Search.jpg](https://i-blog.csdnimg.cn/blog_migrate/4126d4163585e6d859c812069060edb1.jpeg)
下面我们比较一下使用该控件和不使用在列表查询页面中的代码,可以看出使用了控件后的代码大大较少了,并且可读性也增强了
1. 使用该控件, 列表查询页面中的代码
private
string
GetCondition()
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
SearchCondition search = new SearchCondition();
search.AddCondition("GroupID", this.ddlUserGroup.SelectedValue, SqlOperator.Equal, true)//班组ID
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
.AddCondition("DealGroupName", this.ddlDealGroup.SelectedValue, SqlOperator.Equal, true)/**//*消缺单位*/
.AddCondition("VisioStationID", this.ddlStation.SelectedValue, SqlOperator.Like, true)//变电站
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
.AddCondition("VisioImageID", this.ddlLine.SelectedValue, SqlOperator.Like, true)/**//*馈线*/
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
.AddCondition("BugNo", this.txtBugNo.Text.Trim(), SqlOperator.Like, true)/**//*编号*/
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
.AddCondition("Finder", this.ddlFindUser.SelectedValue, SqlOperator.Like, true)/**//*发现人*/
.AddCondition("CheckUser", this.ddlCheckUser.SelectedValue, SqlOperator.Like, true)//验收人
.AddCondition("DeviceBug.BugType", this.ddlBugType.SelectedValue, SqlOperator.Equal, true)//缺陷类别
.AddCondition("CurrentState", this.ddlCurrentState.SelectedValue, SqlOperator.Equal, true)//处理状态
.AddCondition("FindDate", this.txtFindBeginDate.Text.Trim(), SqlOperator.MoreThanOrEqual, true)//发现日期
.AddCondition("FindDate", this.txtFindEndDate.Text.Trim(), SqlOperator.LessThanOrEqual, true)//发现日期
.AddCondition("EndDate", this.txtEndBeginDate.Text.Trim(), SqlOperator.MoreThanOrEqual, true)//消缺日期
.AddCondition("EndDate", this.txtEndEndDate.Text.Trim(), SqlOperator.LessThanOrEqual, true);//消缺日期
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
return search.BuildConditionSql();
}
2. 普通做法,不使用控件,列表查询页面中的代码
Code
private string GetCondition()
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
string condition = "";
if ( this.ddlUserGroup.SelectedValue != "0")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format( " GroupID = {0}" , this.ddlUserGroup.SelectedValue.ToString() );
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
//消缺单位
if ( this.ddlDealGroup.SelectedValue != "0")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (condition == "")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format( " DealGroupName = '{0}'" , this.ddlDealGroup.SelectedItem.Text );
}
else
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format( " And DealGroupName = '{0}'" , this.ddlDealGroup.SelectedItem.Text );
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
if (this.txtStation.Text.Trim() != "")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (condition == "")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format(" Station like '%{0}%'",this.txtStation.Text.Trim() );
}
else
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format(" And Station like '%{0}%' ",this.txtStation.Text.Trim() );
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
if (this.txtLineName.Text.Trim() != "")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (condition == "")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format(" LineName like '%{0}%'",this.txtLineName.Text.Trim() );
}
else
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format(" And LineName like '%{0}%' ",this.txtLineName.Text.Trim() );
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
//编号
if (this.txtBugNo.Text.Trim() != "")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (condition == "")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format("BugNo like '%{0}%'",this.txtBugNo.Text.Trim() );
}
else
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format(" And BugNo like '%{0}%'",this.txtBugNo.Text.Trim() );
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
//发现人
if ( ddlFindUser.SelectedIndex >= 1 )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (condition == "")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format("Finder like '%{0}%'",ddlFindUser.SelectedValue );
}
else
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format(" And Finder like '%{0}%'",ddlFindUser.SelectedValue );
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
//验收人
if ( this.ddlCheckUser.SelectedIndex >= 1 )
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (condition == "")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format("CheckUser like '%{0}%'",this.ddlCheckUser.SelectedValue );
}
else
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format(" And CheckUser like '%{0}%'",this.ddlCheckUser.SelectedValue );
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
//缺陷类别
if (this.ddlBugType.SelectedValue.Trim() != "#")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (condition == "")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format("DeviceBug.BugType={0}",this.ddlBugType.SelectedValue.Trim() );
}
else
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format(" And DeviceBug.BugType={0}",this.ddlBugType.SelectedValue.Trim() );
}
}
//处理状态
if (this.ddlCurrentState.SelectedValue.Trim() != "#")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (condition == "")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format("CurrentState='{0}'",this.ddlCurrentState.SelectedValue.Trim() );
}
else
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format(" And CurrentState='{0}'",this.ddlCurrentState.SelectedValue.Trim() );
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
//发现日期
if (this.txtFindBeginDate.Text.Trim() != "")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (condition == "")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format("FindDate>='{0}'",this.txtFindBeginDate.Text.Trim() );
}
else
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format(" And FindDate>='{0}'",this.txtFindBeginDate.Text.Trim() );
}
}
if (this.txtFindEndDate.Text.Trim() != "")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (condition == "")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format("FindDate<='{0}'",this.txtFindEndDate.Text.Trim() );
}
else
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format(" And FindDate<='{0}'",this.txtFindEndDate.Text.Trim() );
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
//消缺日期
if (this.txtEndBeginDate.Text.Trim() != "")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (condition == "")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format("EndDate>='{0}'",this.txtEndBeginDate.Text.Trim() );
}
else
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format(" And EndDate>='{0}'",this.txtEndBeginDate.Text.Trim() );
}
}
if (this.txtEndEndDate.Text.Trim() != "")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (condition == "")
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format("EndDate<='{0}'",this.txtEndEndDate.Text.Trim() );
}
else
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
condition += string.Format(" And EndDate<='{0}'",this.txtEndEndDate.Text.Trim() );
}
}
return condition;
}