public void Number_Conditions_Overloads_1()
{
var query = new MockQuery(_connection, "SELECT * FROM TABLE1 {WHERE {COLUMN1 [Criteria1]}}");
Int32 n1 = 123;
Int32 n2 = 456;
// IS
query.SetCondition("Criteria1", n1);
var cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 = :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(DbType.Int32, cmd.Parameters[0].DbType);
Assert.AreEqual(n1, cmd.Parameters[0].Value);
// IS NULL
query.SetCondition("Criteria1", (int?)null);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 IS NULL", cmd.CommandText);
Assert.AreEqual(0, cmd.Parameters.Count);
// IS LESS THAN
query.SetConditionRange("Criteria1", to: n1, inclusive: false);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 < :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(n1, cmd.Parameters[0].Value);
// Overload 2
query.SetCondition("Criteria1", n1, NumericOperator.IsLessThan);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 < :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(n1, cmd.Parameters[0].Value);
// IS LESS THAN OR EQ
query.SetConditionRange("Criteria1", to: n1);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 <= :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(n1, cmd.Parameters[0].Value);
// Overload 2
query.SetCondition("Criteria1", n1, NumericOperator.IsLessThanOrEqualTo);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 <= :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(n1, cmd.Parameters[0].Value);
// IS GR THAN
query.SetConditionRange("Criteria1", from: n1, inclusive: false);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 > :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(n1, cmd.Parameters[0].Value);
// Overload 2
query.SetCondition("Criteria1", n1, NumericOperator.IsGreaterThan);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 > :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(n1, cmd.Parameters[0].Value);
// IS GR THAN OR EQ
query.SetConditionRange("Criteria1", from: n1);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 >= :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(n1, cmd.Parameters[0].Value);
// Overload 2
query.SetCondition("Criteria1", n1, NumericOperator.IsGreaterThanOrEqualTo);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 >= :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(n1, cmd.Parameters[0].Value);
// IS NOT
query.SetCondition("Criteria1", n1, NumericOperator.IsNot);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 <> :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(n1, cmd.Parameters[0].Value);
// IS NOT NULL
query.SetCondition("Criteria1", (int?)null, NumericOperator.IsNot);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 IS NOT NULL", cmd.CommandText);
Assert.AreEqual(0, cmd.Parameters.Count);
// IS BETWEEN
query.SetConditionRange("Criteria1", n1, n2);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 BETWEEN :pCriteria1_1 AND :pCriteria1_2", cmd.CommandText);
Assert.AreEqual(2, cmd.Parameters.Count);
Assert.AreEqual(n1, cmd.Parameters[0].Value);
Assert.AreEqual(n2, cmd.Parameters[1].Value);
// IS NOT BETWEEN
query.SetConditionRange("Criteria1", n1, n2, isNot: true);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 NOT BETWEEN :pCriteria1_1 AND :pCriteria1_2", cmd.CommandText);
Assert.AreEqual(2, cmd.Parameters.Count);
Assert.AreEqual(n1, cmd.Parameters[0].Value);
Assert.AreEqual(n2, cmd.Parameters[1].Value);
// IN
query.SetCondition("Criteria1", new[] { n1, n2 });
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 IN (:pCriteria1_1, :pCriteria1_2)", cmd.CommandText);
Assert.AreEqual(2, cmd.Parameters.Count);
Assert.AreEqual(n1, cmd.Parameters[0].Value);
Assert.AreEqual(n2, cmd.Parameters[1].Value);
// NOT IN
query.SetCondition("Criteria1", new[] { n1, n2 }, isNot: true);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 NOT IN (:pCriteria1_1, :pCriteria1_2)", cmd.CommandText);
Assert.AreEqual(2, cmd.Parameters.Count);
Assert.AreEqual(n1, cmd.Parameters[0].Value);
Assert.AreEqual(n2, cmd.Parameters[1].Value);
}
public void Date_Conditions_Overloads_1()
{
var query = new MockQuery(_connection, "SELECT * FROM TABLE1 {WHERE {COLUMN1 [Criteria1]}}");
var dt1 = DateTime.Now.AddDays(-10);
var dt2 = DateTime.Now;
// IS
query.SetCondition("Criteria1", dt1);
var cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 = :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(DbType.DateTime, cmd.Parameters[0].DbType);
Assert.AreEqual(dt1, cmd.Parameters[0].Value);
// IS LESS THAN
query.SetConditionRange("Criteria1", to: dt1, inclusive: false);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 < :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(dt1, cmd.Parameters[0].Value);
// IS LESS THAN OR EQ
query.SetConditionRange("Criteria1", to: dt1);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 <= :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(dt1, cmd.Parameters[0].Value);
// IS GR THAN
query.SetConditionRange("Criteria1", from: dt1, inclusive: false);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 > :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(dt1, cmd.Parameters[0].Value);
// IS GR THAN OR EQ
query.SetConditionRange("Criteria1", from: dt1);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 >= :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(dt1, cmd.Parameters[0].Value);
// IS NOT
query.SetCondition("Criteria1", dt1, NumericOperator.IsNot);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 <> :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(dt1, cmd.Parameters[0].Value);
// IS BETWEEN
query.SetConditionRange("Criteria1", dt1, dt2);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 BETWEEN :pCriteria1_1 AND :pCriteria1_2", cmd.CommandText);
Assert.AreEqual(2, cmd.Parameters.Count);
Assert.AreEqual(dt1, cmd.Parameters[0].Value);
Assert.AreEqual(dt2, cmd.Parameters[1].Value);
// IS NOT BETWEEN
query.SetConditionRange("Criteria1", dt1, dt2, isNot: true);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 NOT BETWEEN :pCriteria1_1 AND :pCriteria1_2", cmd.CommandText);
Assert.AreEqual(2, cmd.Parameters.Count);
Assert.AreEqual(dt1, cmd.Parameters[0].Value);
Assert.AreEqual(dt2, cmd.Parameters[1].Value);
// IN
query.SetCondition("Criteria1", new[] { dt1, dt2 });
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 IN (:pCriteria1_1, :pCriteria1_2)", cmd.CommandText);
Assert.AreEqual(2, cmd.Parameters.Count);
Assert.AreEqual(dt1, cmd.Parameters[0].Value);
Assert.AreEqual(dt2, cmd.Parameters[1].Value);
// NOT IN
query.SetCondition("Criteria1", new[] { dt1, dt2 }, isNot: true);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 NOT IN (:pCriteria1_1, :pCriteria1_2)", cmd.CommandText);
Assert.AreEqual(2, cmd.Parameters.Count);
Assert.AreEqual(dt1, cmd.Parameters[0].Value);
Assert.AreEqual(dt2, cmd.Parameters[1].Value);
}