public static void AppendParameter(IDbCommand cmd, string sValue, int nSize, SqlFilterMode mode, string[] arrField)
{
if ( !IsEmptyString(sValue) )
{
SearchBuilder sb = new SearchBuilder(sValue, cmd);
cmd.CommandText += " and (1 = 0" + ControlChars.CrLf;
// 08/15/2005 Paul. Oracle uses || to concatenate strings.
// Also use upper() to make the compares case insignificant.
// 02/05/2006 Paul. DB2 use || to concatenate strings.
// Also use upper() to make the compares case insignificant.
// 07/18/2006 Paul. SqlFilterMode.Contains behavior has be deprecated. It is now the same as SqlFilterMode.StartsWith.
if ( mode == SqlFilterMode.Contains )
mode = SqlFilterMode.StartsWith ;
if ( IsOracle(cmd) || Sql.IsDB2(cmd) )
{
switch ( mode )
{
case SqlFilterMode.Exact:
foreach ( string sField in arrField )
{
cmd.CommandText += " or upper(" + sField + ") = upper(@" + sField + ")" + ControlChars.CrLf;
Sql.AddParameter(cmd, "@" + sField, sValue, nSize);
}
break;
case SqlFilterMode.StartsWith:
// 07/18/2006 Paul. We need to use SearchBuilder even when searching multiple fields, such as the PHONE fields.
foreach ( string sField in arrField )
{
cmd.CommandText += sb.BuildQuery(" or ", sField);
}
break;
/*
case SqlFilterMode.Contains:
sValue = EscapeSQLLike(sValue);
if ( IsMySQL(cmd) )
sValue = sValue.Replace("\\", "\\\\");
foreach ( string sField in arrField )
{
cmd.CommandText += " or upper(" + sField + ") like '%' || upper(@" + sField + ") || '%'" + ControlChars.CrLf;
// 07/16/2006 Paul. MySQL requires that slashes be escaped, even in the escape clause.
if ( IsMySQL(cmd) )
cmd.CommandText += " escape '\\\\'";
else
cmd.CommandText += " escape '\\'";
Sql.AddParameter(cmd, "@" + sField, sValue, nSize);
}
break;
*/
}
}
else
{
switch ( mode )
{
case SqlFilterMode.Exact:
foreach ( string sField in arrField )
{
cmd.CommandText += " or " + sField + " = @" + sField + ControlChars.CrLf;
Sql.AddParameter(cmd, "@" + sField, sValue, nSize);
}
break;
case SqlFilterMode.StartsWith:
// 07/18/2006 Paul. We need to use SearchBuilder even when searching multiple fields, such as the PHONE fields.
foreach ( string sField in arrField )
{
cmd.CommandText += sb.BuildQuery(" or ", sField);
}
break;
/*
case SqlFilterMode.Contains:
sValue = EscapeSQLLike(sValue);
if ( IsMySQL(cmd) )
sValue = sValue.Replace("\\", "\\\\");
foreach ( string sField in arrField )
{
cmd.CommandText += " or " + sField + " like '%' + @" + sField + " + '%'" + ControlChars.CrLf;
// 07/16/2006 Paul. MySQL requires that slashes be escaped, even in the escape clause.
if ( IsMySQL(cmd) )
cmd.CommandText += " escape '\\\\'";
else
cmd.CommandText += " escape '\\'";
Sql.AddParameter(cmd, "@" + sField, sValue, nSize);
}
break;
*/
}
cmd.CommandText += " )" + ControlChars.CrLf;
}
}
}
public static void AppendParameter(IDbCommand cmd, string sValue, SqlFilterMode mode, string sField)
{
if ( !IsEmptyString(sValue) )
{
if ( IsOracle(cmd) || Sql.IsDB2(cmd) )
{
switch ( mode )
{
case SqlFilterMode.Exact:
cmd.CommandText += " and upper(" + sField + ") = upper(@" + sField + ")" + ControlChars.CrLf;
Sql.AddParameter(cmd, "@" + sField, sValue, sValue.Length);
break;
case SqlFilterMode.StartsWith:
// 08/29/2005 Paul. Oracle uses || to concatenate strings.
cmd.CommandText += " and upper(" + sField + ") like upper(@" + sField + ") || '%'" + ControlChars.CrLf;
sValue = EscapeSQLLike(sValue);
// 07/16/2006 Paul. MySQL requires that slashes be escaped, even in the escape clause.
if ( IsMySQL(cmd) )
{
sValue = sValue.Replace("\\", "\\\\");
cmd.CommandText += " escape '\\\\'";
}
else
cmd.CommandText += " escape '\\'";
Sql.AddParameter(cmd, "@" + sField, sValue, sValue.Length);
break;
case SqlFilterMode.Contains:
// 08/29/2005 Paul. Oracle uses || to concatenate strings.
cmd.CommandText += " and upper(" + sField + ") like '%' || upper(@" + sField + ") || '%'" + ControlChars.CrLf;
sValue = EscapeSQLLike(sValue);
// 07/16/2006 Paul. MySQL requires that slashes be escaped, even in the escape clause.
if ( IsMySQL(cmd) )
{
sValue = sValue.Replace("\\", "\\\\");
cmd.CommandText += " escape '\\\\'";
}
else
cmd.CommandText += " escape '\\'";
Sql.AddParameter(cmd, "@" + sField, sValue, sValue.Length);
break;
}
}
else
{
switch ( mode )
{
case SqlFilterMode.Exact:
cmd.CommandText += " and " + sField + " = @" + sField + ControlChars.CrLf;
Sql.AddParameter(cmd, "@" + sField, sValue, sValue.Length);
break;
case SqlFilterMode.StartsWith:
// 08/29/2005 Paul. SQL Server uses + to concatenate strings.
cmd.CommandText += " and " + sField + " like @" + sField + " + '%'" + ControlChars.CrLf;
sValue = EscapeSQLLike(sValue);
// 07/16/2006 Paul. MySQL requires that slashes be escaped, even in the escape clause.
if ( IsMySQL(cmd) )
{
sValue = sValue.Replace("\\", "\\\\");
cmd.CommandText += " escape '\\\\'";
}
else
cmd.CommandText += " escape '\\'";
Sql.AddParameter(cmd, "@" + sField, sValue, sValue.Length);
break;
case SqlFilterMode.Contains:
// 08/29/2005 Paul. SQL Server uses + to concatenate strings.
cmd.CommandText += " and " + sField + " like '%' + @" + sField + " + '%'" + ControlChars.CrLf;
sValue = EscapeSQLLike(sValue);
// 07/16/2006 Paul. MySQL requires that slashes be escaped, even in the escape clause.
if ( IsMySQL(cmd) )
{
sValue = sValue.Replace("\\", "\\\\");
cmd.CommandText += " escape '\\\\'";
}
else
cmd.CommandText += " escape '\\'";
Sql.AddParameter(cmd, "@" + sField, sValue, sValue.Length);
break;
}
}
}
}