控件代码及测试例子：/wuhuacong/CommonSearch.rar 使用场景:
使用目的:1.减少对参数非空的条件判断 2. 可以构造出参数化的DbCommand对象,简化操作. 3.适当修改后可以用于其他数据访问的参数化参数生成.4.构造Sql语句或者参数化条件更加易读
1. 生成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'
那么代码如下：
SearchConditionsearch=newSearchCondition();
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);
stringconditionSql=search.BuildConditionSql();
2. 生成基于Enterprise Library的DbCommand对象
Databasedb=DatabaseFactory.CreateDatabase();
SearchConditionsearch=newSearchCondition();
search.AddCondition("Name","测试",SqlOperator.Like)
.AddCondition("ID",1,SqlOperator.MoreThanOrEqual);
DbCommanddbComand=search.BuildDbCommand(db,"selectCommentsfromTest","OrderbyName");
using(IDataReaderdr=db.ExecuteReader(dbComand))
{
while(dr.Read())
{
this.txtSql.Text+="\r\n"+dr["Comments"].ToString();
}
}
下面是该控件的类对象图解
下面我们比较一下使用该控件和不使用在列表查询页面中的代码，可以看出使用了控件后的代码大大较少了，并且可读性也增强了
1. 使用该控件, 列表查询页面中的代码
privatestringGetCondition()
{
SearchConditionsearch=newSearchCondition();
search.AddCondition("GroupID",this.ddlUserGroup.SelectedValue,SqlOperator.Equal,true)//班组ID
.AddCondition("DealGroupName",this.ddlDealGroup.SelectedValue,SqlOperator.Equal,true)/**//*消缺单位*/
.AddCondition("VisioStationID",this.ddlStation.SelectedValue,SqlOperator.Like,true)//变电站
.AddCondition("VisioImageID",this.ddlLine.SelectedValue,SqlOperator.Like,true)/**//*馈线*/
.AddCondition("BugNo",this.txtBugNo.Text.Trim(),SqlOperator.Like,true)/**//*编号*/
.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);//消缺日期
returnsearch.BuildConditionSql();
}
2. 普通做法，不使用控件，列表查询页面中的代码
Code
privatestringGetCondition()
{
stringcondition="";
if(this.ddlUserGroup.SelectedValue!="0")
{
condition+=string.Format("GroupID={0}",this.ddlUserGroup.SelectedValue.ToString());
}
//消缺单位
if(this.ddlDealGroup.SelectedValue!="0")
{
if(condition=="")
{
condition+=string.Format("DealGroupName='{0}'",this.ddlDealGroup.SelectedItem.Text);
}
else
{
condition+=string.Format("AndDealGroupName='{0}'",this.ddlDealGroup.SelectedItem.Text);
}
}
if(this.txtStation.Text.Trim()!="")
{
if(condition=="")
{
condition+=string.Format("Stationlike'%{0}%'",this.txtStation.Text.Trim());
}
else
{
condition+=string.Format("AndStationlike'%{0}%'",this.txtStation.Text.Trim());
}
}
if(this.txtLineName.Text.Trim()!="")
{
if(condition=="")
{
condition+=string.Format("LineNamelike'%{0}%'",this.txtLineName.Text.Trim());
}
else
{
condition+=string.Format("AndLineNamelike'%{0}%'",this.txtLineName.Text.Trim());
}
}
//编号
if(this.txtBugNo.Text.Trim()!="")
{
if(condition=="")
{
condition+=string.Format("BugNolike'%{0}%'",this.txtBugNo.Text.Trim());
}
else
{
condition+=string.Format("AndBugNolike'%{0}%'",this.txtBugNo.Text.Trim());
}
}
//发现人
if(ddlFindUser.SelectedIndex>=1)
{
if(condition=="")
{
condition+=string.Format("Finderlike'%{0}%'",ddlFindUser.SelectedValue);
}
else
{
condition+=string.Format("AndFinderlike'%{0}%'",ddlFindUser.SelectedValue);
}
}
//验收人
if(this.ddlCheckUser.SelectedIndex>=1)
{
if(condition=="")
{
condition+=string.Format("CheckUserlike'%{0}%'",this.ddlCheckUser.SelectedValue);
}
else
{
condition+=string.Format("AndCheckUserlike'%{0}%'",this.ddlCheckUser.SelectedValue);
}
}
//缺陷类别
if(this.ddlBugType.SelectedValue.Trim()!="#")
{
if(condition=="")
{
condition+=string.Format("DeviceBug.BugType={0}",this.ddlBugType.SelectedValue.Trim());
}
else
{
condition+=string.Format("AndDeviceBug.BugType={0}",this.ddlBugType.SelectedValue.Trim());
}
}
//处理状态
if(this.ddlCurrentState.SelectedValue.Trim()!="#")
{
if(condition=="")
{
condition+=string.Format("CurrentState='{0}'",this.ddlCurrentState.SelectedValue.Trim());
}
else
{
condition+=string.Format("AndCurrentState='{0}'",this.ddlCurrentState.SelectedValue.Trim());
}
}
//发现日期
if(this.txtFindBeginDate.Text.Trim()!="")
{
if(condition=="")
{
condition+=string.Format("FindDate>='{0}'",this.txtFindBeginDate.Text.Trim());
}
else
{
condition+=string.Format("AndFindDate>='{0}'",this.txtFindBeginDate.Text.Trim());
}
}
if(this.txtFindEndDate.Text.Trim()!="")
{
if(condition=="")
{
condition+=string.Format("FindDate<='{0}'",this.txtFindEndDate.Text.Trim());
}
else
{
condition+=string.Format("AndFindDate<='{0}'",this.txtFindEndDate.Text.Trim());
}
}
//消缺日期
if(this.txtEndBeginDate.Text.Trim()!="")
{
if(condition=="")
{
condition+=string.Format("EndDate>='{0}'",this.txtEndBeginDate.Text.Trim());
}
else
{
condition+=string.Format("AndEndDate>='{0}'",this.txtEndBeginDate.Text.Trim());
}
}
if(this.txtEndEndDate.Text.Trim()!="")
{
if(condition=="")
{
condition+=string.Format("EndDate<='{0}'",this.txtEndEndDate.Text.Trim());
}
else
{
condition+=string.Format("AndEndDate<='{0}'",this.txtEndEndDate.Text.Trim());
}
}
returncondition;
}
