public void ConsecutiveOrderBy()
{
var sql = new Sql()
.Append("SELECT * FROM blah");
sql.Append("ORDER BY x");
sql.Append("ORDER BY y");
Assert.AreEqual(sql.SQL, "SELECT * FROM blah\nORDER BY x\n, y");
}
public async Task<bool> TitleExistsAsync(string title, long? id)
{
var sql = new Sql("SELECT COUNT(*) FROM Book");
if (id.HasValue) sql = sql.Where("Id <> @0", id.Value);
sql = sql.Where("Title = @0", title);
var total = await _database.ExecuteScalarAsync<long>(sql);
return total > 0;
}
public void append_with_args2()
{
var sql = new Sql();
sql.Append("l1");
sql.Append("l2 @0 @1", "a1", "a2");
sql.Append("l3 @0", "a3");
Assert.AreEqual(sql.SQL, "l1\nl2 @0 @1\nl3 @2");
Assert.AreEqual(sql.Arguments.Length, 3);
Assert.AreEqual(sql.Arguments[0], "a1");
Assert.AreEqual(sql.Arguments[1], "a2");
Assert.AreEqual(sql.Arguments[2], "a3");
}
public void append_instances()
{
var sql = new Sql("l0 @0", "a0");
var sql1 = new Sql("l1 @0", "a1");
var sql2 = new Sql("l2 @0", "a2");
Assert.AreSame(sql.Append(sql1), sql);
Assert.AreSame(sql.Append(sql2), sql);
Assert.AreEqual(sql.SQL, "l0 @0\nl1 @1\nl2 @2");
Assert.AreEqual(sql.Arguments.Length, 3);
Assert.AreEqual(sql.Arguments[0], "a0");
Assert.AreEqual(sql.Arguments[1], "a1");
Assert.AreEqual(sql.Arguments[2], "a2");
}
public void ConsecutiveWhere()
{
var sql = new Sql()
.Append("SELECT * FROM blah");
sql.Append("WHERE x");
sql.Append("WHERE y");
Assert.AreEqual(sql.SQL, "SELECT * FROM blah\nWHERE x\nAND y");
}
public void unused_args()
{
var sql = new Sql();
sql.Append("arg @0 @2", "a1", "a2", "a3");
Assert.AreEqual(sql.SQL, "arg @0 @1");
Assert.AreEqual(sql.Arguments.Length, 2);
Assert.AreEqual(sql.Arguments[0], "a1");
Assert.AreEqual(sql.Arguments[1], "a3");
}
public void single_arg()
{
var sql = new Sql();
sql.Append("arg @0", "a1");
Assert.AreEqual(sql.SQL, "arg @0");
Assert.AreEqual(sql.Arguments.Length, 1);
Assert.AreEqual(sql.Arguments[0], "a1");
}
private void Build(StringBuilder sb, List<object> args, Sql lhs)
{
if (!String.IsNullOrEmpty(_sql))
{
// Add SQL to the string
if (sb.Length > 0)
{
sb.Append("\n");
}
var sql = ParametersHelper.ProcessParams(_sql, _args, args);
if (Is(lhs, "WHERE ") && Is(this, "WHERE "))
sql = "AND " + sql.Substring(6);
if (Is(lhs, "ORDER BY ") && Is(this, "ORDER BY "))
sql = ", " + sql.Substring(9);
sb.Append(sql);
}
// Now do rhs
if (_rhs != null)
_rhs.Build(sb, args, this);
}
public void repeated_args()
{
var sql = new Sql();
sql.Append("arg @0 @1 @0 @1", "a1", "a2");
Assert.AreEqual(sql.SQL, "arg @0 @1 @2 @3");
Assert.AreEqual(sql.Arguments.Length, 4);
Assert.AreEqual(sql.Arguments[0], "a1");
Assert.AreEqual(sql.Arguments[1], "a2");
Assert.AreEqual(sql.Arguments[2], "a1");
Assert.AreEqual(sql.Arguments[3], "a2");
}
public void named_args()
{
var sql = new Sql();
sql.Append("arg @name @password", new { name = "n", password = "******" });
Assert.AreEqual(sql.SQL, "arg @0 @1");
Assert.AreEqual(sql.Arguments.Length, 2);
Assert.AreEqual(sql.Arguments[0], "n");
Assert.AreEqual(sql.Arguments[1], "p");
}
public void mysql_user_vars()
{
var sql = new Sql();
sql.Append("arg @@user1 @2 @1 @@@system1", "a1", "a2", "a3");
Assert.AreEqual(sql.SQL, "arg @@user1 @0 @1 @@@system1");
Assert.AreEqual(sql.Arguments.Length, 2);
Assert.AreEqual(sql.Arguments[0], "a3");
Assert.AreEqual(sql.Arguments[1], "a2");
}
public void mixed_named_and_numbered_args()
{
var sql = new Sql();
sql.Append("arg @0 @name @1 @password @2", "a1", "a2", "a3", new { name = "n", password = "******" });
Assert.AreEqual(sql.SQL, "arg @0 @1 @2 @3 @4");
Assert.AreEqual(sql.Arguments.Length, 5);
Assert.AreEqual(sql.Arguments[0], "a1");
Assert.AreEqual(sql.Arguments[1], "n");
Assert.AreEqual(sql.Arguments[2], "a2");
Assert.AreEqual(sql.Arguments[3], "p");
Assert.AreEqual(sql.Arguments[4], "a3");
}
public void invalid_arg_name()
{
Assert.Throws<ArgumentException>(() =>
{
var sql = new Sql();
sql.Append("arg @name1 @name2", new { x = 1, y = 2 });
Assert.AreEqual(sql.SQL, "arg @0 @1");
});
}
/// <summary>
/// Append another SQL builder instance to the right-hand-side of this SQL builder
/// </summary>
/// <param name="sql">A reference to another SQL builder instance</param>
/// <returns>A reference to this builder, allowing for fluent style concatenation</returns>
public Sql Append(Sql sql)
{
// Ensure the sql is rebuilt if changed
_sqlFinal = null;
if (_rhs != null)
_rhs.Append(sql);
else
_rhs = sql;
return this;
}
public SqlJoinClause(Sql sql)
{
_sql = sql;
}
public void invalid_arg_index()
{
Assert.Throws<ArgumentOutOfRangeException>(()=>{
var sql = new Sql();
sql.Append("arg @0 @1", "a0");
Assert.AreEqual(sql.SQL, "arg @0 @1");
});
}
public void simple_append()
{
var sql = new Sql();
sql.Append("LINE 1");
sql.Append("LINE 2");
sql.Append("LINE 3");
Assert.AreEqual(sql.SQL, "LINE 1\nLINE 2\nLINE 3");
Assert.AreEqual(sql.Arguments.Length, 0);
}
static bool Is(Sql sql, string sqltype)
{
return sql != null && sql._sql != null && sql._sql.StartsWith(sqltype, StringComparison.InvariantCultureIgnoreCase);
}