http://www.cnblogs.com/eavia/archive/2011/01/21/1940963.html
public DataTable GetDataTable(KeyValuePair<string, object>[] KeyValue)
{
StringBuilder strSql = new StringBuilder();
string languageTag = SYS.Cmn.SysCmnVar.LanguageTag;
strSql.Append(@"SELECT BT.*,warn.DATANAME AS 'ProductsWarning' FROM ({0}) BT INNER JOIN (SELECT DISTINCT DATAVALUE,DATANAME FROM tab_datafunctionstatus datawry WHERE (datawry.FunctionStatus='StorageProductsMinWarning' OR datawry.FunctionStatus='StorageProductsMaxWarning') AND datawry.dataLanguage='" + languageTag + "') warn");
strSql.Append(" On BT.WarningStatus=warn.dataValue ");
if (KeyValue.Length != 0)
{
strSql.Append(" Where 1=1 ");
for (int i = 0; i < KeyValue.Length; i++)
{
KeyValuePair<string, object> obj = KeyValue[i];
string [] metaDataArray=((string)FMap[obj.Key]).Split('|');
StringBuilder temp = new StringBuilder(CondinOuterText);
temp.Replace("#Null$", metaDataArray[0])
.Replace("#FiledName#","BT." + metaDataArray[2])
.Replace("#Option#", metaDataArray[1])
.Replace("#Value$", string.Format(metaDataArray[3], obj.Value.ToString()));
strSql.Append(temp.ToString());
}
}
string sqlcomplete = string.Format(strSql.ToString(), BaseSqlString);
return SYS.Cmn.MySqlControl.SQLServerHelper.Query_DataTable(sqlcomplete);
}
static Hashtable FMap = null;
static DACStorageProductsQuery()
{
FMap = new Hashtable();
FMap.Add("products_name", "''|=|products_name|'{0}'");
FMap.Add("storage_name", "''|=|storage_name|'{0}'");
FMap.Add("manufacturers_company", "''|=|manufacturers_company|'{0}'");
FMap.Add("ProductsWarning", "''|=|ProductsWarning|'{0}'");
FMap.Add("productiondatebegin", "''|<=|productiondate|'{0}'");
FMap.Add("productiondateend", "''|=>|productiondate|'{0}'");
FMap.Add("products_price_summin", "0|<=|products_price_sum|{0}");
FMap.Add("products_price_summax", "0|=>|products_price_sum|{0}");
FMap.Add("mafuproductiondatebegin", "''|<=|mafuproductiondate|'{0}'");
FMap.Add("mafuproductiondateend", "''|=>|mafuproductiondate|'{0}'");
FMap.Add("products_amount_summin", "0|<=|products_amount_sum|{0}");
FMap.Add("products_amount_summax", "0|=>|products_amount_sum|{0}");
}
const string CondinOuterText = @" AND ( #Null$ = #FiledName# OR #Value$ #Option# #FiledName# ) ";