/// <summary>
/// Add a parameter to this dynamic parameter list (reusing existing parameter if possible)
/// </summary>
public string Add(object value, DbType?dbType = null, ParameterDirection?direction = null)
{
//TODO
ParameterInfo existingParam = null;
//_parms.FirstOrDefault(p =>
// p.DbType == dbType
// && p.ParameterDirection == direction
// && direction == ParameterDirection.Input
// && ((p.Value == null && value == null) || (p.Value!=null && p.Value.Equals(value)))
// );
if (existingParam != null)
{
throw new NotImplementedException(); // check this.
//return InterpolatedStatementParser.AutoGeneratedParameterFactory + existingParam.Name.Substring( InterpolatedStatementParser.AutoGeneratedParameterObjectPrefix.Length );
}
else
{
var parmCount = _parms.Count;
var parmName = InterpolatedStatementParser.AutoGeneratedParameterNameFactory(parmCount.ToString());
var parmObjectName = InterpolatedStatementParser.AutoGeneratedParameterObjectNameFn(parmCount.ToString());
var parameter = new ParameterInfo(parmObjectName, value, dbType, direction);
_parms.Add(parameter);
_nameToParm[parameter.Name] = parameter;
_parmToName[parameter] = parameter.Name;
return(parmName);
}
}
/// <summary>
/// Merges single parameter into this list. <br />
/// Checks for name clashes, and will rename parameter if necessary. <br />
/// If parameter is renamed the new name will be returned, else returns null.
/// </summary>
public string MergeParameter(ParameterInfo parameter)
{
//TODO
ParameterInfo existingParam = null;
//_parms.FirstOrDefault(p =>
// p.DbType == parameter.DbType
// && p.ParameterDirection == parameter.ParameterDirection
// && parameter.ParameterDirection == ParameterDirection.Input
// && ((p.Value == null && parameter.Value == null) || (p.Value!=null && p.Value.Equals(parameter.Value)))
// );
if (existingParam != null)
{
return(existingParam.Name);
}
string newParameterName = InterpolatedStatementParser.AutoGeneratedParameterObjectNameFn(ParameterNames.Count.ToString());
// Create a copy, it's safer
ParameterInfo newParameter = new ParameterInfo(
name: newParameterName,
value: parameter.Value,
dbType: parameter.DbType,
direction: parameter.ParameterDirection,
size: parameter.Size,
precision: parameter.Precision,
scale: parameter.Scale
);
newParameter.OutputCallback = parameter.OutputCallback;
Add(newParameter);
return(newParameterName);
}
/// <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._parms)
{
string newParameterName = MergeParameter(parameter);
if (newParameterName != null)
{
string oldName = InterpolatedStatementParser.AutoGeneratedParameterNameFactory(parameter.Name.Substring(InterpolatedStatementParser.AutoGeneratedParameterObjectNameFn("0").Length - 1));
string newName = InterpolatedStatementParser.AutoGeneratedParameterNameFactory(newParameterName.Substring(InterpolatedStatementParser.AutoGeneratedParameterObjectNameFn("0").Length - 1));
renamedParameters.Add(oldName, newName);
}
}
if (renamedParameters.Any())
{
Regex matchParametersRegex = new Regex("(?:[a-zA-Z0-9~=<>*/%+&|^-]|\\s|\\b|^) (" + string.Join("|", renamedParameters.Select(p => p.Key.Replace("{", "\\{").Replace("}", "\\}"))) + ") (?:[a-zA-Z0-9~=<>*/%+&|^-]|\\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);
}
/// <summary>
/// New CommandBuilder based on an initial command. <br />
/// Parameters embedded using string-interpolation will be automatically converted into EF parameters.
/// </summary>
/// <param name="dbSet"></param>
/// <param name="command">SQL command</param>
public CommandBuilder(DbSet <TEntity> dbSet, FormattableString command) : this(dbSet)
{
var parsedStatement = new InterpolatedStatementParser(command);
parsedStatement.MergeParameters(this.Parameters);
string sql = AdjustMultilineString(parsedStatement.Sql);
_command.Append(sql);
}
/// <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>
/// Adds one column to the select clauses
/// </summary>
public ISelectBuilder <TEntity> Select(FormattableString column)
{
var parsedStatement = new InterpolatedStatementParser(column);
parsedStatement.MergeParameters(this.Parameters);
_selectColumns.Add(parsedStatement.Sql);
return(this);
}
/// <summary>
/// Adds a new condition to having clauses.
/// </summary>
public IGroupByHavingBuilder <TEntity> Having(FormattableString having)
{
var parsedStatement = new InterpolatedStatementParser(having);
parsedStatement.MergeParameters(this.Parameters);
_having.Add(parsedStatement.Sql);
return(this);
}
/// <summary>
/// Adds a new column to groupby clauses.
/// </summary>
public IGroupByBuilder <TEntity> GroupBy(FormattableString groupBy)
{
var parsedStatement = new InterpolatedStatementParser(groupBy);
parsedStatement.MergeParameters(this.Parameters);
_groupBy.Add(parsedStatement.Sql);
return(this);
}
/// <summary>
/// Adds one column to the select clauses, and defines that query is a SELECT DISTINCT type
/// </summary>
public ISelectDistinctBuilder <TEntity> SelectDistinct(FormattableString select)
{
_isSelectDistinct = true;
var parsedStatement = new InterpolatedStatementParser(select);
parsedStatement.MergeParameters(this.Parameters);
_selectColumns.Add(parsedStatement.Sql);
return(this);
}
/// <summary>
/// Replaces a text by a replacement text<br />
/// </summary>
public CommandBuilder <TEntity> Replace(string oldValue, FormattableString newValue)
{
var parsedStatement = new InterpolatedStatementParser(newValue);
parsedStatement.MergeParameters(this.Parameters);
string sql = AdjustMultilineString(parsedStatement.Sql);
_command.Replace(oldValue, sql);
return(this);
}
/// <summary>
/// Adds a new join to the FROM clause.
/// </summary>
public virtual QueryBuilder <TEntity> From(FormattableString fromString)
{
var parsedStatement = new InterpolatedStatementParser(fromString);
if (parsedStatement.Parameters.Any())
{
_froms.Add(Parameters.MergeParameters(parsedStatement.Parameters, parsedStatement.Sql));
}
else
{
_froms.Add(parsedStatement.Sql);
}
return(this);
}
/// <summary>
/// Adds a new table to from clauses. <br />
/// "FROM" word is optional. <br />
/// You can add an alias after table name. <br />
/// You can also add INNER JOIN, LEFT JOIN, etc (with the matching conditions).
/// </summary>
public IFromBuilder <TEntity> From(FormattableString from)
{
var parsedStatement = new InterpolatedStatementParser(from);
parsedStatement.MergeParameters(this.Parameters);
string sql = parsedStatement.Sql;
if (!_fromTables.Any() && !Regex.IsMatch(sql, "\\b FROM \\b", RegexOptions.IgnoreCase | RegexOptions.IgnorePatternWhitespace))
{
sql = "FROM " + sql;
}
_fromTables.Add(sql);
return(this);
}
/// <summary>
/// Appends a statement to the current command. <br />
/// Parameters embedded using string-interpolation will be automatically converted into EF parameters.
/// </summary>
/// <param name="statement">SQL command</param>
public CommandBuilder <TEntity> Append(FormattableString statement)
{
var parsedStatement = new InterpolatedStatementParser(statement);
parsedStatement.MergeParameters(this.Parameters);
string sql = AdjustMultilineString(parsedStatement.Sql);
if (!string.IsNullOrWhiteSpace(sql))
{
// we assume that a single word will always be appended in a single statement (why would anyone split a single sql word in 2 appends?!),
// so if there is no whitespace (or line break) between last text and new text, we add a space betwen them
string currentLine = _command.ToString().Split(new[] { "\r\n", "\r", "\n" }, StringSplitOptions.None).LastOrDefault();
if (currentLine != null && currentLine.Length > 0 && !char.IsWhiteSpace(currentLine.Last()) && !char.IsWhiteSpace(sql[0]))
{
_command.Append(" ");
}
}
_command.Append(sql);
return(this);
}
private InterpolatedStatementParser(string format, params object[] arguments)
{
Parameters = new ParameterInfos();
if (string.IsNullOrEmpty(format))
{
return;
}
StringBuilder sb = new StringBuilder();
var matches = _formattableArgumentRegex.Matches(format);
int currentBlockEndPos = 0;
int previousBlockEndPos = 0;
for (int i = 0; i < matches.Count; i++)
{
previousBlockEndPos = currentBlockEndPos;
currentBlockEndPos = matches[i].Index + matches[i].Length;
// unescape escaped curly braces
string sql = format.Substring(previousBlockEndPos, matches[i].Index - previousBlockEndPos).Replace("{{", "{").Replace("}}", "}");
// 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);
if (nestedStatement.Parameters.Any())
{
sb.Append(Parameters.MergeParameters(nestedStatement.Parameters, nestedStatement.Sql));
}
else
{
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(currentBlockEndPos)))
{
sql = sql.Substring(0, sql.Length - 1); // skip starting quote
currentBlockEndPos++; // 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;
* }
*/
if (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(currentBlockEndPos).Replace("{{", "{").Replace("}}", "}");
sb.Append(lastPart);
Sql = sb.ToString();
_parametersStr = string.Join(", ", Parameters.ParameterNames.ToList().Select(n => "@" + n + "='" + Convert.ToString(Parameters.Get <dynamic>(n)) + "'"));
}