/// <summary> /// New CommandBuilder based on an initial command. <br /> /// Parameters embedded using string-interpolation will be automatically converted into Dapper parameters. /// </summary> /// <param name="cnn"></param> /// <param name="command">SQL command</param> public CommandBuilder(IDbConnection cnn, FormattableString command) : this(cnn) { var parsedStatement = new InterpolatedStatementParser(command); parsedStatement.MergeParameters(this.Parameters); string sql = AdjustMultilineString(parsedStatement.Sql); _command.Append(sql); }
/// <summary> /// Adds a new condition to having clauses. /// </summary> public IGroupByHavingBuilder Having(FormattableString having) { var parsedStatement = new InterpolatedStatementParser(having); parsedStatement.MergeParameters(this.Parameters); _having.Add(parsedStatement.Sql); return(this); }
/// <summary> /// Replaces a text by a replacement text<br /> /// </summary> public CommandBuilder 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 one column to the select clauses /// </summary> public ISelectBuilder Select(FormattableString column) { var parsedStatement = new InterpolatedStatementParser(column); parsedStatement.MergeParameters(this.Parameters); _selectColumns.Add(parsedStatement.Sql); return(this); }
//TODO: create options with InnerJoin, LeftJoin, RightJoin, FullJoin, CrossJoin? Create overloads with table alias? /// <summary> /// Adds a new column to orderby clauses. /// </summary> public IOrderByBuilder OrderBy(FormattableString orderBy) { var parsedStatement = new InterpolatedStatementParser(orderBy); parsedStatement.MergeParameters(this.Parameters); _orderBy.Add(parsedStatement.Sql); return(this); }
/// <summary> /// Adds a new column to groupby clauses. /// </summary> public IGroupByBuilder GroupBy(FormattableString groupBy) { var parsedStatement = new InterpolatedStatementParser(groupBy); parsedStatement.MergeParameters(this.Parameters); _groupBy.Add(parsedStatement.Sql); return(this); }
/// <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)) + "'")); }
/// <summary> /// Adds one column to the select clauses, and defines that query is a SELECT DISTINCT type /// </summary> public ISelectDistinctBuilder SelectDistinct(FormattableString select) { _isSelectDistinct = true; var parsedStatement = new InterpolatedStatementParser(select); parsedStatement.MergeParameters(this.Parameters); _selectColumns.Add(parsedStatement.Sql); return(this); }
/// <summary> /// New QueryBuilder based on an initial query. <br /> /// Query can be modified using .Append(), .AppendLine(), .Where(). <br /> /// Parameters embedded using string-interpolation will be automatically converted into Dapper parameters. /// Where filters will later replace /**where**/ keyword /// </summary> /// <param name="cnn"></param> /// <param name="query">You can use "{where}" or "/**where**/" in your query, and it will be replaced by "WHERE + filters" (if any filter is defined). <br /> /// You can use "{filters}" or "/**filters**/" in your query, and it will be replaced by "AND filters" (without where) (if any filter is defined). /// </param> public QueryBuilder(IDbConnection cnn, FormattableString query) { _commandBuilder = new CommandBuilder(cnn); var parsedStatement = new InterpolatedStatementParser(query); parsedStatement.MergeParameters(_commandBuilder.Parameters); _queryTemplate = parsedStatement.Sql; }
/// <summary> /// Adds a new join to the FROM clause. /// </summary> public virtual QueryBuilder From(FormattableString fromString) { var parsedStatement = new InterpolatedStatementParser(fromString); string sql = parsedStatement.Sql; if (parsedStatement.Parameters.Any()) { sql = (Parameters.MergeParameters(parsedStatement.Parameters, parsedStatement.Sql) ?? sql); } _froms.Add(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 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> /// Adds a new join to the FROM clause. /// </summary> public virtual QueryBuilder 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> /// Appends a statement to the current command. <br /> /// Parameters embedded using string-interpolation will be automatically converted into Dapper parameters. /// </summary> /// <param name="statement">SQL command</param> public CommandBuilder 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(); 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; } 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(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)) + "'")); }