用扩展方法优化多条件查询(不定条件查询)_asp.net教程-查字典教程网
用扩展方法优化多条件查询(不定条件查询)
用扩展方法优化多条件查询(不定条件查询)
发布时间:2016-12-29 来源:查字典编辑
摘要:在我们开发过程中,特别是管理系统的开发,经常会遇到多条件查询(或者叫不定条件查询)的案例,就是提供给User输入的查询条件有多个不同的查询栏...

在我们开发过程中,特别是管理系统的开发,经常会遇到多条件查询(或者叫不定条件查询)的案例,就是提供给User输入的查询条件有多个不同的查询栏位,而且,在实际使用中并不能确定User会使用哪些条件来当做搜索条件。

下图就是我们实际项目中一个查询页面的截图,

User在实际操作中,有可能会只根据[扣帐编号]查询,那么,只要在[扣帐编号]栏位输入号码,其他栏位留空即可,那么查询语句就只卡[扣帐编号]这条条件也有可能直接根据日前范围查询,只要输入起始日期即可。当然,在实际开发的时候我们是不能预判User的行为的,因此,正常情况下我们都是用Sql拼接的方法来应对这个问题:

复制代码 代码如下:

StringBulider sbSql=new StringBulider();

sbSql.Append("select * from V_view1 where 1=1 ");

/*"注意,这里为了确保拼接Sql语句的语法正确,要加上“1=1”,因为可能后面所有的查询条件都为空,这个语句 要以 "where 1=1" 结尾。 以前也有在园子里看到文章说加上“1=1”对查询效率有一定影响,这个没有深入研究过,对此持保留态度鉴于我们这里只针对一般开发,数据量不是很大,所以对于这个问题暂且不做讨论*/

if(!string.IsNullorEmpty(varGRNO))

sbSql.AppendFormat(" and BOLNR = '{0}' ",varGRNO);

这样,就在生成Sql语句之前对User的输入行为做了判断:对于某个查询条件,如果User有输入,则加入Sql的Where条件中,有个没有输入,则不予考虑。

对于日期范围的判断,可以这样写:

复制代码 代码如下:

StringBulider sbSql=new StringBulider();

sbSql.Append("select * from V_view1 where 1=1 ");

if(!string.IsNullorEmpty(varGRNO))

sbSql.AppendFormat(" and BOLNR = '{0}' ",varGRNO);

if(!string.IsNullorEmpty(vardtFrom))

{

sbSql.AppendFormat(" and CRDate >= '{0}' ",Convert.ToDateTime(vardtFrom));

if(!string.IsNullorEmpty(vardtTo))

{

sbSql.AppendFormat(" and CRDate <= '{0}' ",Convert.ToDateTime(vardtTo));

}

}

下面是我们实际开发中的完整代码(省略了一些无关的逻辑):

复制代码 代码如下:

public DataTable GetGRCollections(string varShipto, string varGRNO, string varGRNOto, string varMaterialNO, string varPL, string varPLto, string varCustomerID, string varCustomerID1, string varCustomerPN, string varDateFrom, string varDateTo, string varChecked,string varSupplierPN)

{

try

{

#region Code Here................

DataTable dtResult = new DataTable();

StringBuilder sbSql = new StringBuilder();

sbSql.Append(" SELECT * ")

.Append(" FROM V_QueryGR")

.Append(" WHERE (GRTime>= '" + varDateFrom + " 'and GRTime<='" + varDateTo + "')");

if (!string.IsNullOrEmpty(varShipto))

{

sbSql.Append(" and Plant='"+varShipto+"'");

}

if (!string.IsNullOrEmpty(varGRNO))

{

if (!string.IsNullOrEmpty(varGRNOto))

sbSql.Append(" and (GRNO>='" + varGRNO +"' and GRNO<='"+varGRNOto+ "')");

else

sbSql.Append(" and GRNO='" + varGRNO + "'");

}

if (!string.IsNullOrEmpty(varMaterialNO))

{

sbSql.Append(" and MaterialNO='"+varMaterialNO+"'");

}

if (!string.IsNullOrEmpty(varPL))

{

if (!string.IsNullOrEmpty(varPLto))

sbSql.Append(" and (PackingNO>='" + varPL + "' and PackingNO<='"+varPLto+"')");

else

sbSql.Append(" and PackingNO='" + varPL + "'");

}

if (!string.IsNullOrEmpty(varCustomerID))

{

sbSql.Append(" and CustomID='" + varCustomerID + "'");

}

if (string.IsNullOrEmpty(varCustomerID))

{

ClsCommon ObjCommon = new ClsCommon(userData);

sbSql.Append(" and CustomID in (" + ObjCommon.GetVendorPermissionString() + ")");

}

if (!string.IsNullOrEmpty(varCustomerID1))

{

sbSql.Append(" and CustomID2='" + varCustomerID1 + "'");

}

if (!string.IsNullOrEmpty(varCustomerPN))

{

sbSql.Append(" and CustomerPN='" + varCustomerPN + "'");

}

if (!string.IsNullOrEmpty(varDateFrom))

{

if (!string.IsNullOrEmpty(varDateTo))

sbSql.Append(" and (GRTime>= '" + varDateFrom + "' and GRTime<='" + varDateTo + "')");

else

sbSql.Append(" and PackingNO='" + varDateFrom + "'");

}

if (varChecked == "Checked")

{

sbSql.Append(" and CheckPrice=1 ");

}

if (varChecked == "UnChecked")

{

sbSql.Append(" and CheckPrice=0");

}

if (!string.IsNullOrEmpty(varSupplierPN))

{

sbSql.Append(" and SuplierPN='" + varSupplierPN + "'");

}

try

{

ControlHandleDB();

dtResult = ControlSqlAccess.GetDataTable(sbSql.ToString());

}

catch

{

throw;

}

finally

{

ControlSqlAccess.CloseConnection();

}

return dtResult;

#endregion

}

catch (CommonObjectsException ex)

{

}

catch (Exception ex)

{

}

}

这样一来,如果参数多一点的话,一个简单的Get方法就要写50行以上的代码,虽然不能以代码的行数来评定开发效率,但这种方法无疑增加了代码量,

也降低的代码的可读性和可维护性。

以前,为了给这种情况找到一种更“优雅”,更简洁的方法,也有在网上找了一些资料,发现其他人的方法也是大同小异,差不多都是这样按条件拼接。

园子里有一位同学(现在忘记是哪位了O(∩_∩)O哈!)提出了一种解决方案就是把判断的逻辑直接写到Sql语句或者存储过程中:

复制代码 代码如下:

select * from V_view1 where ((ISNULL(@varGRNO,'')<>'' and BOLNR=@varGRNO ) or (1=1))

这个方法虽然一定程度上减少了代码量,但是把业务逻辑混杂在Sql语句中,个人感觉不是太好的方法,而且大大增加了维护的难度。当然,有兴趣的同学可以

自己去研究。

既然,以上方法都有弊端,那么有没有更好一点的解放方案呢?答案是肯定的,上次用EF的时候突然想到.Net中的扩展方法能够对这个问题进行优化。

首先,来看一下什么事扩展方法,一下是来做MSDN的解释:

扩展方法使您能够向现有类型“添加”方法,而无需创建新的派生类型、重新编译或以其他方式修改原始类型。 扩展方法是一种特殊的静态方法,但可以像扩展类型上的实例方法一样进行调用。

我们常用的Linq中引用的 using System.Linq 其实就是一个扩展方法库,更详细的内容可以参照MSDN和c# 扩展方法奇思妙用(鹤冲天)。在这里,我只举一个简单的例子:

比如,正常情况下判断一个字符串是否为空是这样写:

复制代码 代码如下:

string.IsNullOrEmpty(str);

如果加上一个我们自己扩展的方法:

/// <summary>

/// 检查字符串是否是空(IsNullOrEmpty)

/// </summary>

/// <param name="str"></param>

/// <returns></returns>

public static bool IsNullOrEmpty(this string str)

{

return string.IsNullOrEmpty(str);

}

那么以后判断字符串是否为空就可以这样:

复制代码 代码如下:

str.IsNullOrEmpty();

是不是简洁、优雅了许多呢?

好,回到正题,看看如何用扩展方法的特性来优化Sql语句的拼接问题。既然扩展方法允许我们以实例方法的方式来调用静态方法,那么我们是否可以给Sql语句的字符串实例扩展一个方法来对其操作呢?

比如这个Sql:

复制代码 代码如下:

StringBulider sbSql=new StringBulider();

sbSql.Append("select * from V_view1 where 1=1 ");

if(!string.IsNullorEmpty(varGRNO))

sbSql.AppendFormat(" and BOLNR = '{0}' ",varGRNO);

实际上就是对一个变量进行判断,然后操作字符串实例。

那么,我们就加行一个这样的扩展:

复制代码 代码如下:

public static string strEquals(this string strSql, string strValue, string ColName)

{

if (!string.IsNullOrEmpty(strValue))

return string.Format(strSql + " and {0}='{1}' ", ColName, strValue);

else

return strSql;

}

看到没有,在方法内部进行参数的非空判断,那么,上面的代码就可以这样写:

复制代码 代码如下:

String strSql="select * from V_view1 where 1=1"

strSql=strSql.strEquals(varGRNO,BOLNR)

是不是少了很多代码?

如果有更多的参数,我们可以写的想Linq一样优雅:

复制代码 代码如下:

String strSql="select * from V_view1 where 1=1"

.strEquals(varGRNO,BOLNR)

.strEquals(varPLNO,VBELN)

.strEquals(varPONO,EBELN)

对于like语句,进行下面的扩展

复制代码 代码如下:

public static string strLike(this string strSql, string strValue, string ColName)

{

if (!string.IsNullOrEmpty(strValue))

return string.Format(strSql + " and {0} like '%{1}%' ", ColName, strValue);

else

return strSql;

}

和范围的扩展:

复制代码 代码如下:

public static string strEqualsOrBetween(this string strSql, string strStart, string strEnd, string ColName)

{

if (string.IsNullOrEmpty(strStart) && string.IsNullOrEmpty(strEnd))

return strSql;

else if (!string.IsNullOrEmpty(strStart) && !string.IsNullOrEmpty(strEnd))

{

return strSql.strBigger(strStart, ColName).strSmaller(strEnd, ColName);

}

else if (string.IsNullOrEmpty(strStart) && !string.IsNullOrEmpty(strEnd))

return strSql.strEquals(strEnd, ColName);

else

return strSql.strEquals(strStart, ColName);

}

这样一来,上面一大段的代码就可以写成这样:

复制代码 代码如下:

public DataTable GetGRCollections(string varShipto, string varGRNO, string varGRNOto, string varMaterialNO, string varPL, string varPLto, string varCustomerID, string varCustomerID1, string varCustomerPN, string varDateFrom, string varDateTo, string varChecked,string varSupplierPN)

{

try

{

#region Code Here................

DataTable dtResult = new DataTable();

String strSql="select * from V_QueryGR where 1=1"

.DtEqualsOrBetween(varDateFrom,varDateTo,GRTime)

.strEquals(varShipto,Plant)

.strEqualsOrBetween(varGRNO,GRNO)

.strEquals(varMaterialNO,MaterialNO)

.strEqualsOrBetween(varPL,PackingNO)

.strEquals(varCustomerID,CustomID)

.strEquals(varCustomerID1,CustomID2)

.strEquals(varCustomerPN,CustomerPN)

.DtEqualsOrBetween(varDateFrom,varDateTo,GRTime)

.strEquals(varSupplierPN,SuplierPN)

try

{

ControlHandleDB();

dtResult = ControlSqlAccess.GetDataTable(sbSql.ToString());

}

catch

{

throw;

}

finally

{

ControlSqlAccess.CloseConnection();

}

return dtResult;

#endregion

}

catch (CommonObjectsException ex)

{

}

catch (Exception ex)

{

}

}

对于其他的一下扩展方法,我写了一个类文件,有兴趣的可以点此下载。

第一次正正经经的写博文,累死我了。由于自己也是个菜鸟,想把一个问题讲清楚让更多的“菜鸟”也能看懂,难免有些啰嗦,有不足的地方还请大家多多指教。

相关阅读
推荐文章
猜你喜欢
附近的人在看
推荐阅读
拓展阅读
  • 大家都在看
  • 小编推荐
  • 猜你喜欢
  • 最新asp.net教程学习
    热门asp.net教程学习
    编程开发子分类