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("");
}
public static List <qClauses> Ft4Clauses(qFieldType ColType, bool CaseSensitive = false)
{
List <qClauses> aClauses = null;
switch (ColType)
{
case qFieldType.iText:
aClauses = new List <qClauses>(new qClauses[] {
qClauses.Contains,
qClauses.BeginsWith,
qClauses.EndsWith,
qClauses.Equal,
qClauses.NotContains,
qClauses.NotEqual,
qClauses.IsNull,
qClauses.NotIsNull,
qClauses.iIn,
qClauses.NotIn,
qClauses.Custom,
qClauses.RegularExpression
});
break;
case qFieldType.iNumber:
aClauses = new List <qClauses>(new qClauses[] {
qClauses.Equal,
qClauses.GreaterThan,
qClauses.LessThan,
qClauses.GreaterThanOrEqualTo,
qClauses.LessThanOrEqualTo,
qClauses.NotBetween,
qClauses.Between,
qClauses.NotEqual,
qClauses.IsNull,
qClauses.NotIsNull,
qClauses.iIn,
qClauses.NotIn,
qClauses.Custom,
qClauses.RegularExpression
});
break;
case qFieldType.iBoolean:
aClauses = new List <qClauses>(new qClauses[] {
qClauses.Equal,
qClauses.iTrue,
qClauses.iFalse,
qClauses.IsNull,
qClauses.NotIsNull,
qClauses.Custom
});
break;
case qFieldType.iDateTime:
aClauses = new List <qClauses>(new qClauses[] {
qClauses.Equal,
qClauses.GreaterThan,
qClauses.LessThan,
qClauses.GreaterThanOrEqualTo,
qClauses.LessThanOrEqualTo,
qClauses.Between,
qClauses.NotBetween,
qClauses.Contains,
qClauses.BeginsWith,
qClauses.EndsWith,
qClauses.NotContains,
qClauses.NotEqual,
qClauses.IsNull,
qClauses.NotIsNull,
qClauses.iIn,
qClauses.NotIn,
qClauses.Custom
});
break;
}
return(aClauses);
}
