巧拼SQL语句

来源：互联网发布：北京知象科技龙白滔编辑：程序博客网时间：2024/04/27 20:59

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# ) ";