/// <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
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("");
}