Beispiel #1
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);
        }
Beispiel #2
0
        /// <summary>
        /// 建立分组条件
        /// </summary>
        /// <returns></returns>
        private string BuildGroupCondiction(DatabaseType dbType)
        {
            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;
                    }

                    TypeCode typeCode = Type.GetTypeCode(searchInfo.FieldValue.GetType());

                    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 (searchInfo.SqlOperator == SqlOperator.NotLike)
                        {
                            sb.AppendFormat(" OR {0} {1} '{2}'", searchInfo.FieldName,
                                            this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("%{0}%", searchInfo.FieldValue));
                        }
                        else if (searchInfo.SqlOperator == SqlOperator.LikeStartAt)
                        {
                            sb.AppendFormat(" OR {0} {1} '{2}'", searchInfo.FieldName,
                                            this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("{0}%", searchInfo.FieldValue));
                        }
                        else
                        {
                            if (dbType == DatabaseType.Oracle)
                            {
                                #region Oracle分组
                                if (typeCode == TypeCode.DateTime)
                                {
                                    DateTime dt = Convert.ToDateTime(searchInfo.FieldValue);
                                    if (dt.Hour > 0 || dt.Minute > 0)
                                    {
                                        sb.AppendFormat(" OR {0} {1} to_date('{2}','YYYY-MM-dd HH:mi')", searchInfo.FieldName,
                                                        this.ConvertSqlOperator(searchInfo.SqlOperator), dt.ToString("yyyy-MM-dd HH:mm"));
                                    }
                                    else
                                    {
                                        sb.AppendFormat(" OR {0} {1} to_date('{2}','YYYY-MM-dd')", searchInfo.FieldName,
                                                        this.ConvertSqlOperator(searchInfo.SqlOperator), dt.ToString("yyyy-MM-dd"));
                                    }
                                }
                                else if (!searchInfo.ExcludeIfEmpty)
                                {
                                    //如果要进行空值查询的时候
                                    if (searchInfo.SqlOperator == SqlOperator.Equal)
                                    {
                                        sb.AppendFormat(" OR ({0} is null or {0}='')", searchInfo.FieldName);
                                    }
                                    else if (searchInfo.SqlOperator == SqlOperator.NotEqual)
                                    {
                                        sb.AppendFormat(" OR {0} is not null", searchInfo.FieldName);
                                    }
                                }
                                else
                                {
                                    sb.AppendFormat(" OR {0} {1} '{2}'", searchInfo.FieldName,
                                                    this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
                                }
                                #endregion
                            }
                            else if (dbType == DatabaseType.Access)
                            {
                                #region Access分组
                                if (typeCode == TypeCode.DateTime)
                                {
                                    sb.AppendFormat(" OR {0} {1} #{2}#", searchInfo.FieldName,
                                                    this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
                                }
                                else if (typeCode == TypeCode.Byte || typeCode == TypeCode.Decimal || typeCode == TypeCode.Double ||
                                         typeCode == TypeCode.Int16 || typeCode == TypeCode.Int32 || typeCode == TypeCode.Int64 ||
                                         typeCode == TypeCode.SByte || typeCode == TypeCode.Single || typeCode == TypeCode.UInt16 ||
                                         typeCode == TypeCode.UInt32 || typeCode == TypeCode.UInt64)
                                {
                                    //数值类型操作
                                    sb.AppendFormat(" OR {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);
                                }
                                #endregion
                            }
                            else if (dbType == DatabaseType.SQLite)
                            {
                                #region Sqlite分组
                                if (typeCode == TypeCode.DateTime)
                                {
                                    //日期用date函数,日期时间用datetime函数
                                    DateTime dt = Convert.ToDateTime(searchInfo.FieldValue);
                                    sb.AppendFormat(" OR {0} {1} datetime('{2}') ", searchInfo.FieldName,
                                                    this.ConvertSqlOperator(searchInfo.SqlOperator), dt.ToString("yyyy-MM-dd HH:mm:ss"));
                                }
                                else
                                {
                                    sb.AppendFormat(" OR {0} {1} '{2}'", searchInfo.FieldName,
                                                    this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
                                }
                                #endregion
                            }
                            else //if (dbType == DatabaseType.SqlServer)
                            {
                                #region SqlServer分组
                                if (typeCode == TypeCode.DateTime)
                                {
                                    //日期时间特别处理,防止时间格式变化导致查询错误
                                    DateTime dt = Convert.ToDateTime(searchInfo.FieldValue);
                                    sb.AppendFormat(" OR {0} {1} '{2}'", searchInfo.FieldName,
                                                    this.ConvertSqlOperator(searchInfo.SqlOperator), dt.ToString("yyyy-MM-dd HH:mm:ss"));
                                }
                                else
                                {
                                    if (searchInfo.SqlOperator == SqlOperator.Like)
                                    {
                                        sb.AppendFormat(" OR {0} {1} '{2}'", searchInfo.FieldName,
                                                        this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("%{0}%", searchInfo.FieldValue));
                                    }
                                    else
                                    {
                                        sb.AppendFormat(" OR {0} {1} '{2}'", searchInfo.FieldName,
                                                        this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
                                    }
                                }
                                #endregion
                            }
                        }
                    }
                }

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

            return(sql);
        }
Beispiel #3
0
        /// <summary>
        /// 根据对象构造相关的条件语句(指定数据库类型),如返回的语句是:
        /// <para>
        /// Where (1=1)  AND Test4  &lt;  'Value4' AND Test6  >=  'Value6' AND Test7  &lt;=  'value7' AND Test  &lt;>  '1' AND Test5  >  'Value5' AND Test2  Like  '%Value2%' AND Test3  =  'Value3'
        /// </para>
        /// </summary>
        /// <returns></returns>
        public string BuildConditionSql(DatabaseType dbType)
        {
            string     sql        = " Where (1=1) ";
            string     fieldName  = string.Empty;
            SearchInfo searchInfo = null;

            StringBuilder sb = new StringBuilder();

            sql += BuildGroupCondiction(dbType);

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

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

                TypeCode typeCode = Type.GetTypeCode(searchInfo.FieldValue.GetType());

                //只有组别名称为空才继续,即正常的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.NotLike)
                    {
                        sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,
                                        this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("%{0}%", searchInfo.FieldValue));
                    }
                    else if (searchInfo.SqlOperator == SqlOperator.LikeStartAt)
                    {
                        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 (dbType == DatabaseType.Oracle)
                        {
                            #region 特殊Oracle操作
                            if (typeCode == TypeCode.DateTime)
                            {
                                DateTime dt = Convert.ToDateTime(searchInfo.FieldValue);
                                if (dt.Hour > 0 || dt.Minute > 0)
                                {
                                    sb.AppendFormat(" AND {0} {1} to_date('{2}','YYYY-MM-dd HH:mi')", searchInfo.FieldName,
                                                    this.ConvertSqlOperator(searchInfo.SqlOperator), dt.ToString("yyyy-MM-dd HH:mm"));
                                }
                                else
                                {
                                    sb.AppendFormat(" AND {0} {1} to_date('{2}','YYYY-MM-dd')", searchInfo.FieldName,
                                                    this.ConvertSqlOperator(searchInfo.SqlOperator), dt.ToString("yyyy-MM-dd"));
                                }
                            }
                            else if (!searchInfo.ExcludeIfEmpty)
                            {
                                //如果要进行空值查询的时候
                                if (searchInfo.SqlOperator == SqlOperator.Equal)
                                {
                                    sb.AppendFormat(" AND ({0} is null or {0}='')", searchInfo.FieldName);
                                }
                                else if (searchInfo.SqlOperator == SqlOperator.NotEqual)
                                {
                                    sb.AppendFormat(" AND {0} is not null", searchInfo.FieldName);
                                }
                            }
                            else
                            {
                                sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,
                                                this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
                            }
                            #endregion
                        }
                        else if (dbType == DatabaseType.Access)
                        {
                            #region 特殊Access操作
                            if (searchInfo.SqlOperator == SqlOperator.Equal &&
                                typeCode == TypeCode.String &&
                                string.IsNullOrEmpty(searchInfo.FieldValue.ToString()))
                            {
                                sb.AppendFormat(" AND ({0} {1} '{2}' OR {0} IS NULL)", searchInfo.FieldName,
                                                this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
                            }
                            else
                            {
                                if (typeCode == TypeCode.DateTime)
                                {
                                    sb.AppendFormat(" AND {0} {1} #{2}#", searchInfo.FieldName,
                                                    this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
                                }
                                else if (typeCode == TypeCode.Byte || typeCode == TypeCode.Decimal || typeCode == TypeCode.Double ||
                                         typeCode == TypeCode.Int16 || typeCode == TypeCode.Int32 || typeCode == TypeCode.Int64 ||
                                         typeCode == TypeCode.SByte || typeCode == TypeCode.Single || typeCode == TypeCode.UInt16 ||
                                         typeCode == TypeCode.UInt32 || typeCode == TypeCode.UInt64)
                                {
                                    //数值类型操作
                                    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);
                                }
                            }
                            #endregion
                        }
                        else if (dbType == DatabaseType.SQLite)
                        {
                            #region MyRegion

                            if (searchInfo.SqlOperator == SqlOperator.Equal &&
                                typeCode == TypeCode.String && string.IsNullOrEmpty(searchInfo.FieldValue.ToString()))
                            {
                                sb.AppendFormat(" AND ({0} {1} '{2}' OR {0} IS NULL)", searchInfo.FieldName,
                                                this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
                            }
                            else
                            {
                                if (typeCode == TypeCode.DateTime)
                                {
                                    //日期用date函数,日期时间用datetime函数
                                    DateTime dt = Convert.ToDateTime(searchInfo.FieldValue);
                                    sb.AppendFormat(" AND {0} {1} datetime('{2}') ", searchInfo.FieldName,
                                                    this.ConvertSqlOperator(searchInfo.SqlOperator), dt.ToString("yyyy-MM-dd HH:mm:ss"));
                                }
                                else
                                {
                                    sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,
                                                    this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
                                }
                            }

                            #endregion
                        }
                        else //if (dbType == DatabaseType.SqlServer)
                        {
                            if (typeCode == TypeCode.DateTime)
                            {
                                //日期时间特别处理,防止时间格式变化导致查询错误
                                DateTime dt = Convert.ToDateTime(searchInfo.FieldValue);
                                sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,
                                                this.ConvertSqlOperator(searchInfo.SqlOperator), dt.ToString("yyyy-MM-dd HH:mm:ss"));
                            }
                            else
                            {
                                sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,
                                                this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
                            }
                        }
                    }
                }
            }

            sql += sb.ToString();

            return(sql);
        }