用扩展方法优化多条件查询(不定条件查询)
更新时间：2021年9月22日 10:17 点击：2283
在我们开发过程中，特别是管理系统的开发，经常会遇到多条件查询(或者叫不定条件查询)的案例，就是提供给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)
{
}
}
对于其他的一下扩展方法，我写了一个类文件，有兴趣的可以点此下载。
第一次正正经经的写博文，累死我了。由于自己也是个菜鸟，想把一个问题讲清楚让更多的“菜鸟”也能看懂，难免有些啰嗦，有不足的地方还请大家多多指教。
相关文章
- 关于mysql效率优化一般通过以下两种方式定位执行效率较低的sql语句。通过慢查询日志定位那些执行效率较低的 SQL 语句，用 --log-slow-queries[=file_name] 选项启动时， mysqld 会 写一个包含所有执行时间超过 long_quer...2015-11-08
- 过了这么久，discuz论坛的问题还是困扰着很多网友，其实从各论坛里看到的问题总结出来，很关键的一点都是因为没有将数据表引擎转成InnoDB导致的，discuz在并发稍微高一点的环境下就表现的非常糟糕，产生大量的锁等待，这时候如果...2015-11-24
- java开发的Android应用，性能一直是一个大问题，，或许是Java语言本身比较消耗内存。本文我们来谈谈Android 性能优化之MemoryFile文件读写。 Android匿名共享内存对外A...2016-09-20
- MySQL是一个功能强大的开源数据库。随着越来越多的数据库驱动的应用程序，人们一直在推动MySQL发展到它的极限。这里是101条调节和优化 MySQL安装的技巧。一些技巧是针对特定的安装环境的，但这些思路是通用的。我已经把...2013-09-11
- 这篇文章主要介绍了Angular性能优化之第三方组件和懒加载技术，对性能优化感兴趣的同学，可以参考下...2021-05-11
- 本文给大家介绍的是C#程序优化的小技巧，通过此方法可以有效的降低CPU的占用率，十分的简单实用，有需要的小伙伴可以参考下。...2020-06-25
利用 Chrome Dev Tools 进行页面性能分析的步骤说明(前端性能优化)
这篇文章主要介绍了利用 Chrome Dev Tools 进行页面性能分析的步骤说明(前端性能优化),本文给大家介绍的非常想详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下...2021-02-24网站广告怎么投放最好?首屏广告投放类型优化和广告位布局优化的案例
网站广告怎么投放最好?一个网站中广告位置最好的是哪几个地方呢，许多的朋友都不知道如何让自己的网站广告收效最好了，今天我们就一起来看看吧。 在说到联盟优化前,...2016-10-10- 这篇文章主要介绍了JavaScript提高网站性能优化的建议(二)的相关资料,需要的朋友可以参考下...2016-07-29
- 这篇文章主要介绍了Vue开发网站seo优化方法，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧...2021-05-07
CocosCreator ScrollView优化系列之分帧加载
这篇文章主要介绍了CocosCreator ScrollView的优化，从分帧加载进行了讲解，对性能优化感兴趣的同学，一定要看一下...2021-04-15- 昨天在《js 正则学习小记之匹配字符串字面量》谈到 /"(?:\\.|[^"])*"/ 是个不错的表达式，因为可以满足我们的要求，所以这个表达式可用，但不一定是最好的...2021-05-07
- 经过代码优化升级的文件下载与页面显示速度更快;内容能够被更多的用户所访问(包括残障人士等);当浏览器版本更新，或者出现新的网络交互设备时，确保所有应用能够继续正确...2017-07-06
什么是INDEX, NOINDEX, FOLLOW和 NOFOLLOW及优化和特点
什么是INDEX, NOINDEX, FOLLOW和 NOFOLLOW及优化和特点 The Robots META tag放于后台HTML源代码的标头区(HEAD区)中。 我举一个例子： <HTML> <HEAD>...2017-07-06- 这篇文章主要介绍了Java优化for循环嵌套的高效率方法，帮助大家更好的提升java程序性能，感兴趣的朋友可以了解下...2020-09-14
- 这篇文章主要为大家详细介绍了MySQL分页优化，内容思路很详细，有意对MySQL分页优化的朋友可以参考一下...2016-04-22
- 这篇文章介绍了网站中图片日常使用以及优化手法，非常实用，有需要的同学不妨来看看吧 前言： 最近新到一个团队，才意识到基础的薄弱，牛人遍地，还是好好学习，天天向上。 一...2017-01-22
- 一．Join语法概述join 用于多表中字段之间的联系，语法如下：复制代码 代码如下:... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditionatable1:左表；table2:右表。JOIN 按照功能大致分为如下三类：INNER JOIN（内连接,或...2014-05-31
- 这篇文章主要给大家介绍了关于C# SQLite执行效率优化的相关资料，文中通过示例代码介绍的非常详细，对大家学习或者使用C# SQLite具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧...2020-06-25
- 这篇文章主要介绍了javascript性能优化之DOM交互操作技巧,结合实例形式总结分析了JavaScript针对DOM操作过程中的各种常见优化操作技巧,需要的朋友可以参考下...2015-12-14