Пример #1
0
        /// <summary>
        /// 建立分组条件
        /// </summary>
        /// <returns></returns>
        private string BuildGroupCondiction()
        {
            Hashtable     ht         = GetGroupNames();
            SearchInfo    searchInfo = null;
            StringBuilder sb         = new StringBuilder();
            string        sql        = string.Empty;
            string        tempSql    = string.Empty;

            foreach (string groupName in ht.Keys)
            {
                sb      = new StringBuilder();
                tempSql = " AND ({0})";
                foreach (DictionaryEntry de in this.conditionTable)
                {
                    searchInfo = (SearchInfo)de.Value;

                    //如果选择ExcludeIfEmpty为True,并且该字段为空值的话,跳过
                    if (searchInfo.ExcludeIfEmpty &&
                        (searchInfo.FieldValue == null || string.IsNullOrEmpty(searchInfo.FieldValue.ToString())))
                    {
                        continue;
                    }

                    if (groupName.Equals(searchInfo.GroupName, StringComparison.OrdinalIgnoreCase))
                    {
                        if (searchInfo.SqlOperator == SqlOperator.Like)
                        {
                            sb.AppendFormat(" OR {0} {1} '{2}'", searchInfo.FieldName,
                                            this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("%{0}%", searchInfo.FieldValue));
                        }
                        else
                        {
                            if (ValidateUtil.IsNumber(searchInfo.FieldValue.ToString()))
                            {
                                sb.AppendFormat(" AND {0} {1} {2}", searchInfo.FieldName,
                                                this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
                            }
                            else
                            {
                                sb.AppendFormat(" OR {0} {1} '{2}'", searchInfo.FieldName,
                                                this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
                            }
                        }
                    }
                }

                if (!string.IsNullOrEmpty(sb.ToString()))
                {
                    tempSql = string.Format(tempSql, sb.ToString().Substring(3));//从第一个Or开始位置
                    sql    += tempSql;
                }
            }

            return(sql);
        }
Пример #2
0
        /// <summary>
        /// 根据对象构造相关的条件语句(不使用参数),如返回的语句是:
        /// <![CDATA[
        /// Where (1=1)  AND Test4  <  'Value4' AND Test6  >=  'Value6' AND Test7  <=  'value7' AND Test  <>  '1' AND Test5  >  'Value5' AND Test2  Like  '%Value2%' AND Test3  =  'Value3'
        /// ]]>
        /// </summary>
        /// <returns></returns>
        public string BuildConditionSql()
        {
            string     sql        = " Where (1=1) ";
            string     fieldName  = string.Empty;
            SearchInfo searchInfo = null;

            StringBuilder sb = new StringBuilder();

            sql += BuildGroupCondiction();

            foreach (DictionaryEntry de in this.conditionTable)
            {
                searchInfo = (SearchInfo)de.Value;

                //如果选择ExcludeIfEmpty为True,并且该字段为空值的话,跳过
                if (searchInfo.ExcludeIfEmpty &&
                    (searchInfo.FieldValue == null || string.IsNullOrEmpty(searchInfo.FieldValue.ToString())))
                {
                    continue;
                }

                //只有组别名称为空才继续,即正常的sql条件
                if (string.IsNullOrEmpty(searchInfo.GroupName))
                {
                    if (searchInfo.SqlOperator == SqlOperator.Like)
                    {
                        sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,
                                        this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("%{0}%", searchInfo.FieldValue));
                    }
                    else if (searchInfo.SqlOperator == SqlOperator.In)
                    {
                        sb.AppendFormat(" AND {0} {1} {2}", searchInfo.FieldName,
                                        this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("({0})", searchInfo.FieldValue));
                    }
                    else
                    {
                        if (ValidateUtil.IsNumber(searchInfo.FieldValue.ToString()))
                        {
                            sb.AppendFormat(" AND {0} {1} {2}", searchInfo.FieldName,
                                            this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
                        }
                        else
                        {
                            sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,
                                            this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
                        }
                    }
                }
            }

            sql += sb.ToString();

            return(sql);
        }
Пример #3
0
        /// <summary>
        /// 获取给定条件集合的组别对象集合
        /// </summary>
        /// <returns></returns>
        private Hashtable GetGroupNames()
        {
            Hashtable  htGroupNames = new Hashtable();
            SearchInfo searchInfo   = null;

            foreach (DictionaryEntry de in this.conditionTable)
            {
                searchInfo = (SearchInfo)de.Value;
                if (!string.IsNullOrEmpty(searchInfo.GroupName) && !htGroupNames.Contains(searchInfo.GroupName))
                {
                    htGroupNames.Add(searchInfo.GroupName, searchInfo.GroupName);
                }
            }

            return(htGroupNames);
        }