/// <summary>
/// Merges multiple parameters into this list. <br />
/// Checks for name clashes, and will rename parameters if necessary. <br />
/// If some parameter is renamed the returned Sql statement will containg the original sql replaced with new names, else (if nothing changed) returns null. <br />
/// </summary>
public string MergeParameters(ParameterInfos parameters, string sql)
{
Dictionary <string, string> renamedParameters = new Dictionary <string, string>();
foreach (var parameter in parameters.Values)
{
string newParameterName = MergeParameter(parameter);
if (newParameterName != null && parameter.Name != newParameterName)
{
renamedParameters.Add(DapperQueryBuilderOptions.DatabaseParameterSymbol + parameter.Name, DapperQueryBuilderOptions.DatabaseParameterSymbol + newParameterName);
}
}
if (renamedParameters.Any())
{
Regex matchParametersRegex = new Regex("(?:[,~=<>*/%+&|^-]|\\s|\\b|^)* (" + string.Join("|", renamedParameters.Select(p => p.Key)) + ") (?:[,~=<>*/%+&|^-]|\\s|\\b|$)",
RegexOptions.CultureInvariant | RegexOptions.IgnorePatternWhitespace | RegexOptions.Compiled);
string newSql = matchParametersRegex.Replace(sql, match => {
Group group = match.Groups[match.Groups.Count - 1]; // last match is the inner parameter
string replace = renamedParameters[group.Value];
return(String.Format("{0}{1}{2}", match.Value.Substring(0, group.Index - match.Index), replace, match.Value.Substring(group.Index - match.Index + group.Length)));
});
return(newSql);
}
return(null);
}
/// <inheritdoc/>
public void MergeParameters(ParameterInfos target)
{
foreach (IFilter filter in this)
{
filter.MergeParameters(target);
}
}
/// <summary>
/// New Filter statement. <br />
/// Example: $"[CategoryId] = {categoryId}" <br />
/// Example: $"[Name] LIKE {productName}"
/// </summary>
public Filter(FormattableString filter)
{
var parsedStatement = new InterpolatedStatementParser(filter);
Sql = parsedStatement.Sql;
Parameters = parsedStatement.Parameters;
_parametersStr = string.Join(", ", Parameters.ParameterNames.ToList().Select(n => "@" + n + "='" + Convert.ToString(Parameters.Get <dynamic>(n)) + "'"));
}
/// <summary>
/// New Filter statement. <br />
/// Example: $"[CategoryId] = {categoryId}" <br />
/// Example: $"[Name] LIKE {productName}"
/// </summary>
public Filter(FormattableString filter)
{
var parsedStatement = new InterpolatedStatementParser(filter);
Sql = parsedStatement.Sql;
Parameters = parsedStatement.Parameters;
_parametersStr = string.Join(", ", Parameters.ParameterNames.ToList().Select(n => DapperQueryBuilderOptions.DatabaseParameterSymbol + n + "='" + Convert.ToString(Parameters.Get <dynamic>(n)) + "'"));
}
private InterpolatedStatementParser(string format, params object[] arguments)
{
Parameters = new ParameterInfos();
StringBuilder sb = new StringBuilder();
if (string.IsNullOrEmpty(format))
{
return;
}
var matches = _formattableArgumentRegex.Matches(format);
int lastPos = 0;
for (int i = 0; i < matches.Count; i++)
{
// unescape escaped curly braces
string sql = format.Substring(lastPos, matches[i].Index - lastPos).Replace("{{", "{").Replace("}}", "}");
lastPos = matches[i].Index + matches[i].Length;
// arguments[i] may not work because same argument can be used multiple times
int argPos = int.Parse(matches[i].Groups["ArgPos"].Value);
string argFormat = matches[i].Groups["Format"].Value;
object arg = arguments[argPos];
if (arg is string && argFormat == "raw") // example: {nameof(Product.Name):raw} -> won't be parametrized, we just emit raw string!
{
sb.Append(sql);
sb.Append(arg);
continue;
}
// If user passes " column LIKE '{variable}' ", we assume that he used single quotes incorrectly as if interpolated string was a sql literal
if (quotedVariableStart.IsMatch(sql) && quotedVariableEnd.IsMatch(format.Substring(lastPos)))
{
sql = sql.Substring(0, sql.Length - 1); // skip starting quote
lastPos++; // skip closing quote
}
sb.Append(sql);
string parmName = AutoGeneratedParameterPrefix + _autoNamedParametersCount.ToString();
string parmObjectName = AutoGeneratedParameterObjectPrefix + _autoNamedParametersCount.ToString();
_autoNamedParametersCount++;
//var direction = System.Data.ParameterDirection.Input;
//if (argFormat == "out")
// direction = System.Data.ParameterDirection.Output;
Parameters.Add(new ParameterInfo(parmObjectName, arg));
sb.Append(parmName);
}
string lastPart = format.Substring(lastPos).Replace("{{", "{").Replace("}}", "}");
sb.Append(lastPart);
Sql = sb.ToString();
_parametersStr = string.Join(", ", Parameters.ParameterNames.ToList().Select(n => "@" + n + "='" + Convert.ToString(Parameters.Get <dynamic>(n)) + "'"));
}
/// <summary>
/// Merges parameters from this query/statement into a CommandBuilder. <br />
/// Checks for name clashes, and will rename parameters (in CommandBuilder) if necessary. <br />
/// If some parameter is renamed the underlying Sql statement will have the new parameter names replaced by their new names.<br />
/// This method does NOT append Parser SQL to CommandBuilder SQL (you may want to save this SQL statement elsewhere)
/// </summary>
public void MergeParameters(ParameterInfos target)
{
string newSql = target.MergeParameters(Parameters, Sql);
if (newSql != null)
{
Sql = newSql;
_parametersStr = string.Join(", ", Parameters.ParameterNames.ToList().Select(n => "@" + n + "='" + Convert.ToString(Parameters.Get<dynamic>(n)) + "'"));
// filter parameters in Sql were renamed and won't match the previous passed filters - discard original parameters to avoid reusing wrong values
Parameters = null;
}
}
/// <summary>
/// Merges multiple parameters into this list. <br />
/// Checks for name clashes, and will rename parameters if necessary. <br />
/// If some parameter is renamed the returned Sql statement will containg the original sql replaced with new names, else (if nothing changed) returns null. <br />
/// </summary>
public string MergeParameters(ParameterInfos parameters, string sql)
{
string newSql = sql;
foreach (var parameter in parameters.Values)
{
string newParameterName = MergeParameter(parameter);
if (newParameterName != null)
{
newSql = newSql.Replace("@" + parameter.Name, "@" + newParameterName);
}
}
if (newSql != sql)
{
return(newSql);
}
return(null);
}
/// <summary>
/// If you're using Filters in standalone structure (without QueryBuilder), <br />
/// you can just "build" the filters over a ParameterInfos and get the string for the filters (with leading WHERE)
/// </summary>
/// <param name="target"></param>
/// <returns></returns>
public string BuildFilters(DynamicParameters target)
{
ParameterInfos parameters = new ParameterInfos();
foreach (IFilter filter in this)
{
filter.MergeParameters(parameters);
}
foreach (var parameter in parameters.Values)
{
target.Add(parameter.Name, parameter.Value, parameter.DbType, parameter.ParameterDirection, parameter.Size);
}
StringBuilder sb = new StringBuilder();
WriteFilter(sb);
if (sb.Length > 0)
{
return("WHERE " + sb.ToString());
}
return("");
}
/// <summary>
/// New CommandBuilder.
/// </summary>
/// <param name="cnn"></param>
public CommandBuilder(IDbConnection cnn)
{
_cnn = cnn;
_command = new StringBuilder();
_parameters = new ParameterInfos();
}
private InterpolatedStatementParser(string format, params object[] arguments)
{
Parameters = new ParameterInfos();
StringBuilder sb = new StringBuilder();
if (string.IsNullOrEmpty(format))
{
return;
}
var matches = _formattableArgumentRegex.Matches(format);
int lastPos = 0;
for (int i = 0; i < matches.Count; i++)
{
// unescape escaped curly braces
string sql = format.Substring(lastPos, matches[i].Index - lastPos).Replace("{{", "{").Replace("}}", "}");
lastPos = matches[i].Index + matches[i].Length;
// arguments[i] may not work because same argument can be used multiple times
int argPos = int.Parse(matches[i].Groups["ArgPos"].Value);
string argFormat = matches[i].Groups["Format"].Value;
List <string> argFormats = argFormat.Split(new char[] { ',', '|' }, StringSplitOptions.RemoveEmptyEntries).Select(f => f.Trim()).ToList();
object arg = arguments[argPos];
if (argFormats.Contains("raw")) // example: {nameof(Product.Name):raw} -> won't be parametrized, we just emit raw string!
{
sb.Append(sql);
sb.Append(arg);
continue;
}
else if (arg is FormattableString fsArg) //Support nested FormattableString
{
sb.Append(sql);
var nestedStatement = new InterpolatedStatementParser(fsArg);
string subSql = nestedStatement.Sql;
if (nestedStatement.Parameters.Any())
{
subSql = (Parameters.MergeParameters(nestedStatement.Parameters, nestedStatement.Sql) ?? subSql);
}
sb.Append(nestedStatement.Sql);
continue;
}
else if (arg is ICompleteCommand innerQuery) //Support nested QueryBuilder, CommandBuilder or FluentQueryBuilder
{
sb.Append(sql);
string innerSql = innerQuery.Sql;
// Convert back from @p0 @p1 etc to {0} {1} etc (FormattableString standard)
Regex matchParametersRegex = new Regex("(?:[,~=<>*/%+&|^-]|\\s|\\b|^)* " + "(" + DapperQueryBuilderOptions.DatabaseParameterSymbol + DapperQueryBuilderOptions.AutoGeneratedParameterName + "(?<Name>\\d*)" + ")" + " (?:[,~=<>*/%+&|^-]|\\s|\\b|$)",
RegexOptions.CultureInvariant | RegexOptions.IgnorePatternWhitespace | RegexOptions.Compiled);
innerSql = matchParametersRegex.Replace(innerSql, match => {
Group parm = match.Groups[1];
int parmNum = int.Parse(match.Groups[2].Value);
string replace = "{" + parmNum + "}";
string ret = string.Format("{0}{1}{2}", match.Value.Substring(0, parm.Index - match.Index), replace, match.Value.Substring(parm.Index - match.Index + parm.Length));
return(ret);
});
var nestedStatement = new InterpolatedStatementParser(innerSql, innerQuery.Parameters.Select(p => p.Value.Value).ToArray());
string subSql = nestedStatement.Sql;
if (nestedStatement.Parameters.Any())
{
subSql = (Parameters.MergeParameters(nestedStatement.Parameters, nestedStatement.Sql) ?? subSql);
}
sb.Append(nestedStatement.Sql);
continue;
}
// If user passes " column LIKE '{variable}' ", we assume that he used single quotes incorrectly as if interpolated string was a sql literal
if (quotedVariableStart.IsMatch(sql) && quotedVariableEnd.IsMatch(format.Substring(lastPos)))
{
sql = sql.Substring(0, sql.Length - 1); // skip starting quote
lastPos++; // skip closing quote
}
sb.Append(sql);
var direction = System.Data.ParameterDirection.Input;
System.Data.DbType?dbType = null;
if (argFormats.Contains("out"))
{
direction = System.Data.ParameterDirection.Output;
}
System.Data.DbType parsedDbType;
Match m;
foreach (var f in argFormats)
{
if (arg is string && (m = regexDbTypeString.Match(f)) != null && m.Success) // String(maxlength) / nvarchar(maxlength) / String / nvarchar
{
arg = new DbString()
{
IsAnsi = false,
IsFixedLength = false,
Value = (string)arg,
Length = (string.IsNullOrEmpty(m.Groups["maxlength"].Value) ? Math.Max(DbString.DefaultLength, ((string)arg).Length) : int.Parse(m.Groups["maxlength"].Value))
}
}
;
else if (arg is string && (m = regexDbTypeAnsiString.Match(f)) != null && m.Success) // AnsiString(maxlength) / varchar(maxlength) / AnsiString / varchar
{
arg = new DbString()
{
IsAnsi = true,
IsFixedLength = false,
Value = (string)arg,
Length = (string.IsNullOrEmpty(m.Groups["maxlength"].Value) ? Math.Max(DbString.DefaultLength, ((string)arg).Length) : int.Parse(m.Groups["maxlength"].Value))
}
}
;
else if (arg is string && (m = regexDbTypeStringFixedLength.Match(f)) != null && m.Success) // StringFixedLength(length) / nchar(length) / StringFixedLength / nchar
{
arg = new DbString()
{
IsAnsi = false,
IsFixedLength = true,
Value = (string)arg,
Length = (string.IsNullOrEmpty(m.Groups["length"].Value) ? ((string)arg).Length : int.Parse(m.Groups["length"].Value))
}
}
;
else if (arg is string && (m = regexDbTypeAnsiStringFixedLength.Match(f)) != null && m.Success) // AnsiStringFixedLength(length) / char(length) / AnsiStringFixedLength / char
{
arg = new DbString()
{
IsAnsi = true,
IsFixedLength = true,
Value = (string)arg,
Length = (string.IsNullOrEmpty(m.Groups["length"].Value) ? ((string)arg).Length : int.Parse(m.Groups["length"].Value))
}
}
;
else if (arg is string && (m = regexDbTypeText.Match(f)) != null && m.Success) // text / varchar(MAX) / varchar(-1)
{
arg = new DbString()
{
IsAnsi = false,
IsFixedLength = true,
Value = (string)arg,
Length = int.MaxValue
}
}
;
else if (arg is string && (m = regexDbTypeNText.Match(f)) != null && m.Success) // ntext / nvarchar(MAX) / nvarchar(-1)
{
arg = new DbString()
{
IsAnsi = true,
IsFixedLength = true,
Value = (string)arg,
Length = int.MaxValue
}
}
;
else if (!(arg is DbString) && dbType == null && Enum.TryParse <System.Data.DbType>(value: f, ignoreCase: true, result: out parsedDbType))
{
dbType = parsedDbType;
}
//TODO: parse SqlDbTypes?
// https://stackoverflow.com/questions/35745226/net-system-type-to-sqldbtype
// https://gist.github.com/tecmaverick/858392/53ddaaa6418b943fa3a230eac49a9efe05c2d0ba
}
sb.Append(DapperQueryBuilderOptions.DatabaseParameterSymbol + Parameters.Add(arg, dbType, direction));
}
string lastPart = format.Substring(lastPos).Replace("{{", "{").Replace("}}", "}");
sb.Append(lastPart);
Sql = sb.ToString();
_parametersStr = string.Join(", ", Parameters.ParameterNames.ToList().Select(n => DapperQueryBuilderOptions.DatabaseParameterSymbol + n + "='" + Convert.ToString(Parameters.Get <dynamic>(n)) + "'"));
}
private InterpolatedStatementParser(string format, params object[] arguments)
{
Parameters = new ParameterInfos();
StringBuilder sb = new StringBuilder();
if (string.IsNullOrEmpty(format))
{
return;
}
var matches = _formattableArgumentRegex.Matches(format);
int lastPos = 0;
for (int i = 0; i < matches.Count; i++)
{
// unescape escaped curly braces
string sql = format.Substring(lastPos, matches[i].Index - lastPos).Replace("{{", "{").Replace("}}", "}");
lastPos = matches[i].Index + matches[i].Length;
// arguments[i] may not work because same argument can be used multiple times
int argPos = int.Parse(matches[i].Groups["ArgPos"].Value);
string argFormat = matches[i].Groups["Format"].Value;
List <string> argFormats = argFormat.Split(new char[] { ',', '|' }, StringSplitOptions.RemoveEmptyEntries).Select(f => f.Trim()).ToList();
object arg = arguments[argPos];
if (argFormats.Contains("raw")) // example: {nameof(Product.Name):raw} -> won't be parametrized, we just emit raw string!
{
sb.Append(sql);
sb.Append(arg);
continue;
}
// If user passes " column LIKE '{variable}' ", we assume that he used single quotes incorrectly as if interpolated string was a sql literal
if (quotedVariableStart.IsMatch(sql) && quotedVariableEnd.IsMatch(format.Substring(lastPos)))
{
sql = sql.Substring(0, sql.Length - 1); // skip starting quote
lastPos++; // skip closing quote
}
sb.Append(sql);
var direction = System.Data.ParameterDirection.Input;
System.Data.DbType?dbType = null;
if (argFormats.Contains("out"))
{
direction = System.Data.ParameterDirection.Output;
}
System.Data.DbType parsedDbType;
Match m;
foreach (var f in argFormats)
{
if (arg is string && (m = regexDbTypeString.Match(f)) != null && m.Success) // String(maxlength) / nvarchar(maxlength) / String / nvarchar
{
arg = new DbString()
{
IsAnsi = false,
IsFixedLength = false,
Value = (string)arg,
Length = (string.IsNullOrEmpty(m.Groups["maxlength"].Value) ? Math.Max(DbString.DefaultLength, ((string)arg).Length) : int.Parse(m.Groups["maxlength"].Value))
}
}
;
else if (arg is string && (m = regexDbTypeAnsiString.Match(f)) != null && m.Success) // AnsiString(maxlength) / varchar(maxlength) / AnsiString / varchar
{
arg = new DbString()
{
IsAnsi = true,
IsFixedLength = false,
Value = (string)arg,
Length = (string.IsNullOrEmpty(m.Groups["maxlength"].Value) ? Math.Max(DbString.DefaultLength, ((string)arg).Length) : int.Parse(m.Groups["maxlength"].Value))
}
}
;
else if (arg is string && (m = regexDbTypeStringFixedLength.Match(f)) != null && m.Success) // StringFixedLength(length) / nchar(length) / StringFixedLength / nchar
{
arg = new DbString()
{
IsAnsi = false,
IsFixedLength = true,
Value = (string)arg,
Length = (string.IsNullOrEmpty(m.Groups["length"].Value) ? ((string)arg).Length : int.Parse(m.Groups["length"].Value))
}
}
;
else if (arg is string && (m = regexDbTypeAnsiStringFixedLength.Match(f)) != null && m.Success) // AnsiStringFixedLength(length) / char(length) / AnsiStringFixedLength / char
{
arg = new DbString()
{
IsAnsi = true,
IsFixedLength = true,
Value = (string)arg,
Length = (string.IsNullOrEmpty(m.Groups["length"].Value) ? ((string)arg).Length : int.Parse(m.Groups["length"].Value))
}
}
;
else if (arg is string && (m = regexDbTypeText.Match(f)) != null && m.Success) // text / varchar(MAX) / varchar(-1)
{
arg = new DbString()
{
IsAnsi = false,
IsFixedLength = true,
Value = (string)arg,
Length = int.MaxValue
}
}
;
else if (arg is string && (m = regexDbTypeNText.Match(f)) != null && m.Success) // ntext / nvarchar(MAX) / nvarchar(-1)
{
arg = new DbString()
{
IsAnsi = true,
IsFixedLength = true,
Value = (string)arg,
Length = int.MaxValue
}
}
;
else if (!(arg is DbString) && dbType == null && Enum.TryParse <System.Data.DbType>(value: f, ignoreCase: true, result: out parsedDbType))
{
dbType = parsedDbType;
}
//TODO: parse SqlDbTypes?
// https://stackoverflow.com/questions/35745226/net-system-type-to-sqldbtype
// https://gist.github.com/tecmaverick/858392/53ddaaa6418b943fa3a230eac49a9efe05c2d0ba
}
sb.Append(Parameters.Add(arg, dbType, direction));
}
string lastPart = format.Substring(lastPos).Replace("{{", "{").Replace("}}", "}");
sb.Append(lastPart);
Sql = sb.ToString();
_parametersStr = string.Join(", ", Parameters.ParameterNames.ToList().Select(n => "@" + n + "='" + Convert.ToString(Parameters.Get <dynamic>(n)) + "'"));
}