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