在我們開發過程中，特別是管理系統的開發，經常會遇到多條件查詢(或者叫不定條件查詢)的案例，就是提供給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)
{
}
}
對于其他的一下擴展方法，我寫了一個類文件，有興趣的可以點此下載。
第一次正正經經的寫博文，累死我了。由于自己也是個菜鳥，想把一個問題講清楚讓更多的“菜鳥”也能看懂，難免有些啰嗦，有不足的地方還請大家多多指教。
