Пример #1
0
        /// <summary>
        /// return a string value
        /// </summary>
        /// <param name="Clause"></param>
        /// <returns>string</returns>
        public static string sClause(qClauses Clause)
        {
            switch (Clause)
            {
            case qClauses.BeginsWith: return("Begins With");

            case qClauses.NotBeginsWith: return("not Begins With");

            case qClauses.Between: return("Between");

            case qClauses.Contains: return("Contains");

            case qClauses.EndsWith: return("Ends With");

            case qClauses.NotEndsWith: return("not Ends With");

            case qClauses.Equal: return("Equal");

            case qClauses.GreaterThan: return("GreaterThan");

            case qClauses.GreaterThanOrEqualTo: return("Greater Than Or Equal To");

            case qClauses.iFalse: return("False");

            case qClauses.iIn: return("In");

            case qClauses.IsEmpty: return("is Empty");

            case qClauses.IsNull: return("is Null");

            case qClauses.iTrue: return("True");

            case qClauses.LessThan: return("Less Than");

            case qClauses.LessThanOrEqualTo: return("Less Than Or Equal To");

            case qClauses.NotBetween: return("Not Between");

            case qClauses.NotContains: return("Not Contains");

            case qClauses.NotEqual: return("Not Equal");

            case qClauses.NotIn: return("Not In");

            case qClauses.NotIsEmpty: return("Not Is Empty");

            case qClauses.NotIsNull: return("Not Is Null");

            case qClauses.Custom: return("Custom");

            case qClauses.RegularExpression: return("Matches Regular Expression");
            }
            return("");
        }   //a FieldType Text will have a <> set vs. Number or Boolean
Пример #2
0
        public static string sqlClauses(qClauses Clause, bool CaseSensitive, qFieldType at,
                                        MyDb.Common.DataBaseType _cnnType = MyDb.Common.DataBaseType.ORACLE)
        {
            if (CaseSensitive)
            {
                switch (Clause)
                {
                case qClauses.BeginsWith:
                    return("\"??_Attr_Name\" like 'XX_VALUE_FROM%' ");

                case qClauses.Between:
                    if (_cnnType == MyDb.Common.DataBaseType.ORACLE)
                    {
                        return(at == qFieldType.iDateTime ? "\"??_Attr_Name\" between to_date('XX_VALUE_FROM','MM/DD/YYYY') and to_date('YY_VALUE_TO','MM/DD/YYYY') "
                            : "\"??_Attr_Name\" between XX_VALUE_FROM and YY_VALUE_TO ");
                    }
                    else if (_cnnType == MyDb.Common.DataBaseType.SQLSERVER)
                    {
                        return(at == qFieldType.iDateTime ? "\"??_Attr_Name\" between CONVERT(DATETIME,'XX_VALUE_FROM') and CONVERT(DATETIME,'YY_VALUE_TO') "
                           : "\"??_Attr_Name\" between XX_VALUE_FROM and YY_VALUE_TO ");
                    }
                    else
                    {
                        return("\"??_Attr_Name\" between 'XX_VALUE_FROM' and 'YY_VALUE_TO' ");
                    }

                case qClauses.Contains:
                    return("\"??_Attr_Name\" like '%XX_VALUE_FROM%' ");

                case qClauses.EndsWith:
                    return("\"??_Attr_Name\" like '%XX_VALUE_FROM' ");

                case qClauses.Equal:
                    if (_cnnType == MyDb.Common.DataBaseType.ORACLE)
                    {
                        return(at == qFieldType.iDateTime ? "\"??_Attr_Name\" = to_date('XX_VALUE_FROM','MM/DD/YYYY') " : "\"??_Attr_Name\" = 'XX_VALUE_FROM' ");
                    }
                    if (_cnnType == MyDb.Common.DataBaseType.SQLSERVER)
                    {
                        return(at == qFieldType.iDateTime ? "\"??_Attr_Name\" = CONVERT(DATETIME,'XX_VALUE_FROM') " : "\"??_Attr_Name\" = 'XX_VALUE_FROM' ");
                    }
                    return("\"??_Attr_Name\" = 'XX_VALUE_FROM' ");

                case qClauses.GreaterThan:
                    if (_cnnType == MyDb.Common.DataBaseType.ORACLE)
                    {
                        return(at == qFieldType.iDateTime ? "\"??_Attr_Name\" > to_date('XX_VALUE_FROM','MM/DD/YYYY') " : "\"??_Attr_Name\" > 'XX_VALUE_FROM' ");
                    }
                    if (_cnnType == MyDb.Common.DataBaseType.SQLSERVER)
                    {
                        return(at == qFieldType.iDateTime ? "\"??_Attr_Name\" > CONVERT(DATETIME,'XX_VALUE_FROM') " : "\"??_Attr_Name\" > 'XX_VALUE_FROM' ");
                    }
                    return("\"??_Attr_Name\" > 'XX_VALUE_FROM' ");

                case qClauses.GreaterThanOrEqualTo:
                    if (_cnnType == MyDb.Common.DataBaseType.ORACLE)
                    {
                        return(at == qFieldType.iDateTime ? "\"??_Attr_Name\" >= to_date('XX_VALUE_FROM','MM/DD/YYYY') " : "\"??_Attr_Name\" >= 'XX_VALUE_FROM' ");
                    }
                    if (_cnnType == MyDb.Common.DataBaseType.SQLSERVER)
                    {
                        return(at == qFieldType.iDateTime ? "\"??_Attr_Name\" >= CONVERT(DATETIME,'XX_VALUE_FROM') " : "\"??_Attr_Name\" >= 'XX_VALUE_FROM' ");
                    }
                    return("\"??_Attr_Name\" >= 'XX_VALUE_FROM' ");

                case qClauses.iFalse:
                    //= 0 "
                    return("\"??_Attr_Name\" = (1=2)");

                case qClauses.iIn:
                    return("\"??_Attr_Name\" IN (XX_VALUE_FROM) ");

                case qClauses.IsEmpty:
                    return("\"??_Attr_Name\" = '' ");

                case qClauses.IsNull:
                    return("\"??_Attr_Name\" IS NULL");

                case qClauses.iTrue:
                    //<> 0"
                    return("\"??_Attr_Name\" = (1=1)");

                case qClauses.LessThan:
                    if (_cnnType == MyDb.Common.DataBaseType.ORACLE)
                    {
                        return(at == qFieldType.iDateTime ? "\"??_Attr_Name\" < to_date('XX_VALUE_FROM','MM/DD/YYYY') " : "\"??_Attr_Name\" < 'XX_VALUE_FROM' ");
                    }
                    if (_cnnType == MyDb.Common.DataBaseType.SQLSERVER)
                    {
                        return(at == qFieldType.iDateTime ? "\"??_Attr_Name\" < CONVERT(DATETIME,'XX_VALUE_FROM') " : "\"??_Attr_Name\" < 'XX_VALUE_FROM' ");
                    }
                    return("\"??_Attr_Name\" < 'XX_VALUE_FROM' ");

                case qClauses.LessThanOrEqualTo:
                    if (_cnnType == MyDb.Common.DataBaseType.ORACLE)
                    {
                        return(at == qFieldType.iDateTime ? "\"??_Attr_Name\" <= to_date('XX_VALUE_FROM','MM/DD/YYYY') " : "\"??_Attr_Name\" <= 'XX_VALUE_FROM' ");
                    }
                    if (_cnnType == MyDb.Common.DataBaseType.SQLSERVER)
                    {
                        return(at == qFieldType.iDateTime ? "\"??_Attr_Name\" <= CONVERT(DATETIME,'XX_VALUE_FROM') " : "\"??_Attr_Name\" <= 'XX_VALUE_FROM' ");
                    }
                    return("\"??_Attr_Name\" <= 'XX_VALUE_FROM' ");

                case qClauses.NotBetween:
                    if (_cnnType == MyDb.Common.DataBaseType.ORACLE)
                    {
                        return(at == qFieldType.iDateTime ? "NOT \"??_Attr_Name\" between to_date('XX_VALUE_FROM','MM/DD/YYYY') and to_date('YY_VALUE_TO','MM/DD/YYYY') "
                            : "NOT \"??_Attr_Name\" between XX_VALUE_FROM and YY_VALUE_TO ");
                    }
                    ;
                    if (_cnnType == MyDb.Common.DataBaseType.SQLSERVER)
                    {
                        return(at == qFieldType.iDateTime ? "NOT \"??_Attr_Name\" between CONVERT(DATETIME,'XX_VALUE_FROM') and CONVERT(DATETIME,'YY_VALUE_TO') "
                            : "NOT \"??_Attr_Name\" between XX_VALUE_FROM and YY_VALUE_TO ");
                    }
                    return("NOT \"??_Attr_Name\" between XX_VALUE_FROM and YY_VALUE_TO ");;

                case qClauses.NotContains:
                    return(" NOT \"??_Attr_Name\" like '%XX_VALUE_FROM%' ");

                case qClauses.NotEqual:
                    if (_cnnType == MyDb.Common.DataBaseType.ORACLE)
                    {
                        return(at == qFieldType.iDateTime ? "\"??_Attr_Name\" <> to_date('XX_VALUE_FROM','MM/DD/YYYY') " : "\"??_Attr_Name\" <> 'XX_VALUE_FROM' ");
                    }
                    if (_cnnType == MyDb.Common.DataBaseType.SQLSERVER)
                    {
                        return(at == qFieldType.iDateTime ? "\"??_Attr_Name\" <> CONVERT(DATETIME,'XX_VALUE_FROM') " : "\"??_Attr_Name\" <> 'XX_VALUE_FROM' ");
                    }
                    return("\"??_Attr_Name\" <> 'XX_VALUE_FROM' ");

                case qClauses.NotIn:
                    return("\"??_Attr_Name\" Not In (XX_VALUE_FROM)");

                case qClauses.NotIsEmpty:
                    return("\"??_Attr_Name\" <> ''");

                case qClauses.NotIsNull:
                    return(" Not \"??_Attr_Name\" is null");

                case qClauses.Custom:
                    return("XX_VALUE_FROM");

                case qClauses.RegularExpression:
                    return("REGEXP_LIKE(??_Attr_Name, 'XX_VALUE_FROM','c') ");
                }
            }
            else
            {
                switch (Clause)
                {
                case qClauses.BeginsWith:
                    return("??_Attr_Name like 'XX_VALUE_FROM%' ");

                case qClauses.Between:
                    //return "??_Attr_Name between 'XX_VALUE_FROM' and 'YY_VALUE_TO' ";
                    //return at == qFieldType.iDateTime ? "??_Attr_Name between to_date('XX_VALUE_FROM','MM/DD/YYYY') and to_date('YY_VALUE_TO','MM/DD/YYYY') " : "??_Attr_Name between XX_VALUE_FROM and YY_VALUE_TO ";
                    if (at == qFieldType.iDateTime && _cnnType == MyDb.Common.DataBaseType.ORACLE)
                    {
                        return("  ??_Attr_Name between to_date('XX_VALUE_FROM','MM/DD/YYYY') and to_date('YY_VALUE_TO','MM/DD/YYYY') ");
                    }
                    if (at == qFieldType.iDateTime && _cnnType == MyDb.Common.DataBaseType.SQLSERVER)
                    {
                        return("  ??_Attr_Name between CONVERT(DATETIME,'XX_VALUE_FROM') and CONVERT(DATETIME,'YY_VALUE_TO') ");
                    }
                    return(" ??_Attr_Name between XX_VALUE_FROM and YY_VALUE_TO ");

                case qClauses.Contains:
                    return("??_Attr_Name like '%XX_VALUE_FROM%' ");

                case qClauses.EndsWith:
                    return("??_Attr_Name like '%XX_VALUE_FROM' ");

                case qClauses.Equal:
                    // return "??_Attr_Name = 'XX_VALUE_FROM' ";
                    if (_cnnType == MyDb.Common.DataBaseType.ORACLE)
                    {
                        return(at == qFieldType.iDateTime ? "??_Attr_Name = to_date('XX_VALUE_FROM','MM/DD/YYYY') " : "??_Attr_Name = 'XX_VALUE_FROM' ");
                    }
                    if (_cnnType == MyDb.Common.DataBaseType.SQLSERVER)
                    {
                        return(at == qFieldType.iDateTime ? "??_Attr_Name = CONVERT(DATETIME,'XX_VALUE_FROM') " : "??_Attr_Name = 'XX_VALUE_FROM' ");
                    }
                    return("upper(??_Attr_Name) = upper('XX_VALUE_FROM') ");

                case qClauses.GreaterThan:
                    //return "??_Attr_Name > XX_VALUE_FROM ";
                    if (_cnnType == MyDb.Common.DataBaseType.ORACLE)
                    {
                        return(at == qFieldType.iDateTime ? "??_Attr_Name > to_date('XX_VALUE_FROM','MM/DD/YYYY') " : "??_Attr_Name > XX_VALUE_FROM ");
                    }
                    if (_cnnType == MyDb.Common.DataBaseType.SQLSERVER)
                    {
                        return(at == qFieldType.iDateTime ? "??_Attr_Name > CONVERT(DATETIME,'XX_VALUE_FROM') " : "??_Attr_Name > XX_VALUE_FROM ");
                    }
                    return("??_Attr_Name > XX_VALUE_FROM ");

                case qClauses.GreaterThanOrEqualTo:
                    //return "??_Attr_Name >= XX_VALUE_FROM ";
                    if (_cnnType == MyDb.Common.DataBaseType.ORACLE)
                    {
                        return(at == qFieldType.iDateTime ? "??_Attr_Name >= to_date('XX_VALUE_FROM','MM/DD/YYYY') " : "??_Attr_Name >= 'XX_VALUE_FROM' ");
                    }
                    if (_cnnType == MyDb.Common.DataBaseType.SQLSERVER)
                    {
                        return(at == qFieldType.iDateTime ? "??_Attr_Name >=  CONVERT(DATETIME,'XX_VALUE_FROM') " : "??_Attr_Name >= 'XX_VALUE_FROM' ");
                    }
                    return("??_Attr_Name >= 'XX_VALUE_FROM' ");

                case qClauses.iFalse:
                    //0 "
                    return("??_Attr_Name = (1=2)");

                case qClauses.iIn:
                    return("??_Attr_Name IN (XX_VALUE_FROM) ");

                case qClauses.IsEmpty:
                    return("??_Attr_Name = '' ");

                case qClauses.IsNull:
                    return("??_Attr_Name IS NULL");

                case qClauses.iTrue:
                    //<> 0"
                    return("??_Attr_Name =(1=1)");

                case qClauses.LessThan:
                    //return "??_Attr_Name < XX_VALUE_FROM ";
                    //return at == qFieldType.iDateTime ? "??_Attr_Name < to_date('XX_VALUE_FROM','MM/DD/YYYY') " : "??_Attr_Name < 'XX_VALUE_FROM' ";
                    if (at == qFieldType.iDateTime && _cnnType == MyDb.Common.DataBaseType.ORACLE)
                    {
                        return("??_Attr_Name < to_date('XX_VALUE_FROM','MM/DD/YYYY') ");
                    }
                    if (at == qFieldType.iDateTime && _cnnType == MyDb.Common.DataBaseType.SQLSERVER)
                    {
                        return("??_Attr_Name < CONVERT(DATETIME,'XX_VALUE_FROM') ");
                    }
                    return("??_Attr_Name < XX_VALUE_FROM ");

                case qClauses.LessThanOrEqualTo:
                    //return "??_Attr_Name <= XX_VALUE_FROM ";
                    //return at == qFieldType.iDateTime ? "??_Attr_Name <= to_date('XX_VALUE_FROM','MM/DD/YYYY') " : "??_Attr_Name <= 'XX_VALUE_FROM' ";
                    if (at == qFieldType.iDateTime && _cnnType == MyDb.Common.DataBaseType.ORACLE)
                    {
                        return("??_Attr_Name <= to_date('XX_VALUE_FROM','MM/DD/YYYY') ");
                    }
                    if (at == qFieldType.iDateTime && _cnnType == MyDb.Common.DataBaseType.SQLSERVER)
                    {
                        return("??_Attr_Name <= CONVERT(DATETIME,'XX_VALUE_FROM') ");
                    }
                    return("??_Attr_Name <= XX_VALUE_FROM ");

                case qClauses.NotBetween:
                    //return " NOT ??_Attr_Name between 'XX_VALUE_FROM' and 'YY_VALUE_TO' ";
                    //return at == qFieldType.iDateTime ? " NOT ??_Attr_Name between to_date('XX_VALUE_FROM','MM/DD/YYYY') and to_date('YY_VALUE_TO','MM/DD/YYYY') " : "??_Attr_Name between XX_VALUE_FROM and YY_VALUE_TO ";
                    if (at == qFieldType.iDateTime && _cnnType == MyDb.Common.DataBaseType.ORACLE)
                    {
                        return(" NOT ??_Attr_Name between to_date('XX_VALUE_FROM','MM/DD/YYYY') and to_date('YY_VALUE_TO','MM/DD/YYYY') ");
                    }
                    if (at == qFieldType.iDateTime && _cnnType == MyDb.Common.DataBaseType.SQLSERVER)
                    {
                        return(" NOT ??_Attr_Name between CONVERT(DATETIME,'XX_VALUE_FROM') and CONVERT(DATETIME,'YY_VALUE_TO') ");
                    }
                    return(" NOT ??_Attr_Name between XX_VALUE_FROM and YY_VALUE_TO ");

                case qClauses.NotContains:
                    return(" NOT ??_Attr_Name like '%XX_VALUE_FROM%' ");

                case qClauses.NotEqual:
                    //return "??_Attr_Name <> 'XX_VALUE_FROM' ";
                    //return at == qFieldType.iDateTime ? "??_Attr_Name <> to_date('XX_VALUE_FROM','MM/DD/YYYY') " : "upper(??_Attr_Name) <> upper('XX_VALUE_FROM') ";
                    if (at == qFieldType.iDateTime && _cnnType == MyDb.Common.DataBaseType.ORACLE)
                    {
                        return("??_Attr_Name <> to_date('XX_VALUE_FROM','MM/DD/YYYY') ");
                    }
                    if (at == qFieldType.iDateTime && _cnnType == MyDb.Common.DataBaseType.SQLSERVER)
                    {
                        return("??_Attr_Name <> CONVERT(DATETIME,'XX_VALUE_FROM') ");
                    }
                    return("??_Attr_Name <> 'XX_VALUE_FROM' ");

                case qClauses.NotIn:
                    return("??_Attr_Name Not In (XX_VALUE_FROM)");

                case qClauses.NotIsEmpty:
                    return("??_Attr_Name <> ''");

                case qClauses.NotIsNull:
                    return(" Not ??_Attr_Name is null");

                case qClauses.Custom:
                    return("XX_VALUE_FROM");

                case qClauses.RegularExpression:
                    return("REGEXP_LIKE(??_Attr_Name, 'XX_VALUE_FROM','i') ");       //http://www.regular-expressions.info/oracle.html
                }
            }
            return("");
        }