protected string HandleStringComparison(string col, string val, string verb, TextColumnType columnType)
{
switch (verb)
{
case "SqlWildcard":
return SqlSyntaxContext.SqlSyntaxProvider.GetStringColumnWildcardComparison(col, RemoveQuote(val), columnType);
case "Equals":
return SqlSyntaxContext.SqlSyntaxProvider.GetStringColumnEqualComparison(col, RemoveQuote(val), columnType);
case "StartsWith":
return SqlSyntaxContext.SqlSyntaxProvider.GetStringColumnStartsWithComparison(col, RemoveQuote(val), columnType);
case "EndsWith":
return SqlSyntaxContext.SqlSyntaxProvider.GetStringColumnEndsWithComparison(col, RemoveQuote(val), columnType);
case "Contains":
return SqlSyntaxContext.SqlSyntaxProvider.GetStringColumnContainsComparison(col, RemoveQuote(val), columnType);
case "InvariantEquals":
case "SqlEquals":
//recurse
return HandleStringComparison(col, val, "Equals", columnType);
case "InvariantStartsWith":
case "SqlStartsWith":
//recurse
return HandleStringComparison(col, val, "StartsWith", columnType);
case "InvariantEndsWith":
case "SqlEndsWith":
//recurse
return HandleStringComparison(col, val, "EndsWith", columnType);
case "InvariantContains":
case "SqlContains":
//recurse
return HandleStringComparison(col, val, "Contains", columnType);
default:
throw new ArgumentOutOfRangeException("verb");
}
}
public static bool SqlWildcard(this string str, string txt, TextColumnType columnType)
{
var wildcardmatch = new Regex("^" + Regex.Escape(txt).
//deal with any wildcard chars %
Replace(@"\%", ".*") + "$");
return(wildcardmatch.IsMatch(str));
}
private string HandleStringComparison(string col, string val, string verb, TextColumnType columnType)
{
switch (verb)
{
case "SqlWildcard":
return(SqlSyntaxContext.SqlSyntaxProvider.GetStringColumnWildcardComparison(col, EscapeAtArgument(RemoveQuote(val)), columnType));
case "Equals":
return(SqlSyntaxContext.SqlSyntaxProvider.GetStringColumnEqualComparison(col, EscapeAtArgument(RemoveQuote(val)), columnType));
case "StartsWith":
return(SqlSyntaxContext.SqlSyntaxProvider.GetStringColumnStartsWithComparison(col, EscapeAtArgument(RemoveQuote(val)), columnType));
case "EndsWith":
return(SqlSyntaxContext.SqlSyntaxProvider.GetStringColumnEndsWithComparison(col, EscapeAtArgument(RemoveQuote(val)), columnType));
case "Contains":
return(SqlSyntaxContext.SqlSyntaxProvider.GetStringColumnContainsComparison(col, EscapeAtArgument(RemoveQuote(val)), columnType));
case "InvariantEquals":
case "SqlEquals":
//recurse
return(HandleStringComparison(col, val, "Equals", columnType));
case "InvariantStartsWith":
case "SqlStartsWith":
//recurse
return(HandleStringComparison(col, val, "StartsWith", columnType));
case "InvariantEndsWith":
case "SqlEndsWith":
//recurse
return(HandleStringComparison(col, val, "EndsWith", columnType));
case "InvariantContains":
case "SqlContains":
//recurse
return(HandleStringComparison(col, val, "Contains", columnType));
default:
throw new ArgumentOutOfRangeException("verb");
}
}
public virtual string GetStringColumnWildcardComparison(string column, int paramIndex, TextColumnType columnType)
{
//use the 'upper' method to always ensure strings are matched without case sensitivity no matter what the db setting.
return($"upper({column}) LIKE upper(@{paramIndex})");
}
public override string GetStringColumnWildcardComparison(string column, int paramIndex, TextColumnType columnType)
{
switch (columnType)
{
case TextColumnType.NVarchar:
return(base.GetStringColumnWildcardComparison(column, paramIndex, columnType));
case TextColumnType.NText:
//MSSQL doesn't allow for upper methods with NText columns
return($"{column} LIKE @{paramIndex}");
default:
throw new ArgumentOutOfRangeException(nameof(columnType));
}
}
public virtual string GetStringColumnWildcardComparison(string column, string value, TextColumnType columnType)
{
//use the 'upper' method to always ensure strings are matched without case sensitivity no matter what the db setting.
return(string.Format("upper({0}) LIKE '{1}'", column, value.ToUpper()));
}
public virtual string GetStringColumnEqualComparison(string column, int paramIndex, TextColumnType columnType)
{
//use the 'upper' method to always ensure strings are matched without case sensitivity no matter what the db setting.
return(string.Format("upper({0}) = upper(@{1})", column, paramIndex));
}
public static bool SqlEndsWith(this string str, string txt, TextColumnType columnType) => str.InvariantEndsWith(txt);
#pragma warning disable IDE0060 // Remove unused parameter
public static bool SqlContains(this string str, string txt, TextColumnType columnType) => str.InvariantContains(txt);
public static bool SqlStartsWith(this string str, string txt, TextColumnType columnType)
{
return(str.InvariantStartsWith(txt));
}
public static bool SqlEquals(this string str, string txt, TextColumnType columnType)
{
return(str.InvariantEquals(txt));
}
public override string GetStringColumnEqualComparison(string column, string value, TextColumnType columnType)
{
switch (columnType)
{
case TextColumnType.NVarchar:
return(base.GetStringColumnEqualComparison(column, value, columnType));
case TextColumnType.NText:
//MSSQL doesn't allow for = comparison with NText columns but allows this syntax
return(string.Format("{0} LIKE '{1}'", column, value));
default:
throw new ArgumentOutOfRangeException("columnType");
}
}
public override string GetStringColumnEndsWithComparison(string column, string value, TextColumnType columnType)
{
switch (columnType)
{
case TextColumnType.NVarchar:
return(base.GetStringColumnEndsWithComparison(column, value, columnType));
case TextColumnType.NText:
//MSSQL doesn't allow for upper methods with NText columns
return(string.Format("{0} LIKE '%{1}'", column, value));
default:
throw new ArgumentOutOfRangeException("columnType");
}
}
public virtual string GetStringColumnEqualComparison(string column, int paramIndex, TextColumnType columnType) =>
// use the 'upper' method to always ensure strings are matched without case sensitivity no matter what the db setting.
$"upper({column}) = upper(@{paramIndex})";
public static bool SqlStartsWith(this string? str, string txt, TextColumnType columnType) => str?.InvariantStartsWith(txt) ?? false;
protected string HandleStringComparison(string col, string val, string verb, TextColumnType columnType)
{
switch (verb)
{
case "SqlWildcard":
SqlParameters.Add(RemoveQuote(val));
//don't execute if compiled
if (Visited == false)
{
return(SqlSyntax.GetStringColumnWildcardComparison(col, SqlParameters.Count - 1, columnType));
}
//already compiled, return
return(string.Empty);
case "Equals":
SqlParameters.Add(RemoveQuote(val));
//don't execute if compiled
if (Visited == false)
{
return(SqlSyntax.GetStringColumnEqualComparison(col, SqlParameters.Count - 1, columnType));
}
//already compiled, return
return(string.Empty);
case "StartsWith":
SqlParameters.Add(string.Format("{0}{1}",
RemoveQuote(val),
SqlSyntax.GetWildcardPlaceholder()));
//don't execute if compiled
if (Visited == false)
{
return(SqlSyntax.GetStringColumnWildcardComparison(col, SqlParameters.Count - 1, columnType));
}
//already compiled, return
return(string.Empty);
case "EndsWith":
SqlParameters.Add(string.Format("{0}{1}",
SqlSyntax.GetWildcardPlaceholder(),
RemoveQuote(val)));
//don't execute if compiled
if (Visited == false)
{
return(SqlSyntax.GetStringColumnWildcardComparison(col, SqlParameters.Count - 1, columnType));
}
//already compiled, return
return(string.Empty);
case "Contains":
SqlParameters.Add(string.Format("{0}{1}{0}",
SqlSyntax.GetWildcardPlaceholder(),
RemoveQuote(val)));
//don't execute if compiled
if (Visited == false)
{
return(SqlSyntax.GetStringColumnWildcardComparison(col, SqlParameters.Count - 1, columnType));
}
//already compiled, return
return(string.Empty);
case "InvariantEquals":
case "SqlEquals":
//recurse
return(HandleStringComparison(col, val, "Equals", columnType));
case "InvariantStartsWith":
case "SqlStartsWith":
//recurse
return(HandleStringComparison(col, val, "StartsWith", columnType));
case "InvariantEndsWith":
case "SqlEndsWith":
//recurse
return(HandleStringComparison(col, val, "EndsWith", columnType));
case "InvariantContains":
case "SqlContains":
//recurse
return(HandleStringComparison(col, val, "Contains", columnType));
default:
throw new ArgumentOutOfRangeException("verb");
}
}
