/// <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 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); }
/// <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 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> /// 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); }
//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 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 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> /// 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; }