public void Null_Sql_1()
{
var query = new MockQuery(_connection, "SELECT * FROM TABLE1 {WHERE {COLUMN1 [Criteria1]}}");
// Set the condition
query.SetCondition("Criteria1", new StringValue((string)null));
var cmd = query.CreateCommand();
Assert.IsNotNull(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 IS NULL", cmd.CommandText);
Assert.AreEqual(0, cmd.Parameters.Count);
// Now try the overload
query.SetCondition("Criteria1", (string)null);
cmd = query.CreateCommand();
Assert.IsNotNull(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 IS NULL", cmd.CommandText);
Assert.AreEqual(0, cmd.Parameters.Count);
// Now try IS NOT
query.SetCondition("Criteria1", null, StringOperator.Is, isNot: true);
cmd = query.CreateCommand();
Assert.IsNotNull(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 IS NOT NULL", cmd.CommandText);
Assert.AreEqual(0, cmd.Parameters.Count);
}
public void Negative_Test_1()
{
var query = new MockQuery(_connection, "SELECT * FROM TABLE1 {WHERE {COLUMN1 [Criteria1]}}");
// Set the condition
query.SetCondition("Criteria1", new StringValue(new [] { null, "A", "B" }));
try
{
query.CreateCommand();
Assert.Fail();
}
catch (Exception ex)
{
Assert.IsInstanceOfType(ex, typeof(InvalidConditionException));
}
// Set the condition
query.SetCondition("Criteria1", new NumberValue(new[] { 1, 2, 3 }));
try
{
query.CreateCommand();
Assert.Fail();
}
catch (Exception ex)
{
Assert.IsInstanceOfType(ex, typeof(InvalidConditionException));
}
}
public void Custom_Conditions_1()
{
var query = new MockQuery(_connection, "SELECT * FROM TABLE1 {WHERE {COLUMN1 [Criteria1]} {COLUMN2 [Criteria2]} {COLUMN3 [Criteria3]}};");
query.SetCondition("Criteria1", new CustomConditionValue(1, 2, 3));
query.SetCondition("Criteria2", new CustomParameterlessConditionValue("test"));
query.SetCondition("Criteria3", new CustomConditionValue(4, 5, 6));
var cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 = sillyProcedure(:pCriteria1_1, :pCriteria1_2, :pCriteria1_3) " +
"AND COLUMN2 = 'test' /*hint*/ AND COLUMN3 = sillyProcedure(:pCriteria3_1, :pCriteria3_2, :pCriteria3_3);", cmd.CommandText);
Assert.AreEqual(6, cmd.Parameters.Count);
Assert.AreEqual("pCriteria1_1", cmd.Parameters[0].ParameterName);
Assert.AreEqual("pCriteria1_2", cmd.Parameters[1].ParameterName);
Assert.AreEqual("pCriteria1_3", cmd.Parameters[2].ParameterName);
Assert.AreEqual("pCriteria3_1", cmd.Parameters[3].ParameterName);
Assert.AreEqual("pCriteria3_2", cmd.Parameters[4].ParameterName);
Assert.AreEqual("pCriteria3_3", cmd.Parameters[5].ParameterName);
Assert.AreEqual(1, cmd.Parameters[0].Value);
Assert.AreEqual(2, cmd.Parameters[1].Value);
Assert.AreEqual(3, cmd.Parameters[2].Value);
Assert.AreEqual(4, cmd.Parameters[3].Value);
Assert.AreEqual(5, cmd.Parameters[4].Value);
Assert.AreEqual(6, cmd.Parameters[5].Value);
}
public void Common_Sql_3()
{
var query = new MockQuery(_connection, "SELECT * FROM TABLE1 {WHERE {COLUMN1 :Criteria1} {COLUMN2 :Criteria2}}");
query.ParserHints = Parsing.ParserHints.None;
// No columns
var cmd = query.CreateCommand();
Assert.IsNotNull(cmd);
Assert.AreEqual(CommandType.Text, cmd.CommandType);
Assert.AreEqual("SELECT * FROM TABLE1", cmd.CommandText);
Assert.AreEqual(0, cmd.Parameters.Count);
// Both columns
query.SetCondition("Criteria1", new BoolValue(true));
query.SetCondition("Criteria2", new NumberValue(13));
cmd = query.CreateCommand();
Assert.IsNotNull(cmd);
Assert.AreEqual(CommandType.Text, cmd.CommandType);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 = :pCriteria1_1 AND COLUMN2 = :pCriteria2_1", cmd.CommandText);
Assert.AreEqual(2, cmd.Parameters.Count);
Assert.AreEqual("pCriteria1_1", cmd.Parameters[0].ParameterName);
Assert.AreEqual("pCriteria2_1", cmd.Parameters[1].ParameterName);
Assert.AreEqual(true, cmd.Parameters[0].Value);
Assert.AreEqual(13, cmd.Parameters[1].Value);
// First column
query.Conditions.Clear();
query.SetCondition("Criteria1", new BoolValue(true));
cmd = query.CreateCommand();
Assert.IsNotNull(cmd);
Assert.AreEqual(CommandType.Text, cmd.CommandType);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 = :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual("pCriteria1_1", cmd.Parameters[0].ParameterName);
Assert.AreEqual(true, cmd.Parameters[0].Value);
// Second column
query.Conditions.Clear();
query.SetCondition("Criteria2", new NumberValue(13));
cmd = query.CreateCommand();
Assert.IsNotNull(cmd);
Assert.AreEqual(CommandType.Text, cmd.CommandType);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN2 = :pCriteria2_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual("pCriteria2_1", cmd.Parameters[0].ParameterName);
Assert.AreEqual(13, cmd.Parameters[0].Value);
}
public void Escape_Strings()
{
// Escaping square brackets with dollar sign, a potentially common scenario in OLEDB queries
var sql = "SELECT * FROM TABLE1 {WHERE {[[Some Column 1]] [SomeCriteria1]} {[[Some Column 2]] [SomeCriteria2]} {[[Some Column 3]] [SomeCriteria3]}}";
var expected = "SELECT * FROM TABLE1 WHERE [Some Column 1] = :pSomeCriteria1_1 AND [Some Column 3] = :pSomeCriteria3_1";
var query = new MockQuery(_connection, sql);
query.SetCondition("SomeCriteria1", 123);
query.SetCondition("SomeCriteria3", 456);
AssertCommand(query.CreateCommand(), expected);
}
public void Join_Sql_2()
{
// More complex query. Similar to previous one except now we're looking for customer whose name either: begins with Thomas, ends with Hardy or has " John " in the middle.
// This means we have 3 different conditions clustered inside a single group that generates an OR query (condition1 OR condition2). Also, now, we're looking at
// the shipment date rather than order date.
var query = new MockQuery(_connection, "SELECT Orders.OrderID, Customers.ContactName, Orders.OrderDate, Orders.ShippedDate \n" +
"FROM Orders, Customers \n" +
"WHERE Customers.CustomerID = Orders.CustomerID \n" +
"{AND @{({Customers.ContactName [ContactNameFirst]} {Customers.ContactName [ContactNameMiddle]} {Customers.ContactName [ContactNameLast]})} \n" +
"{Orders.OrderDate [OrderDate]} \n" +
"{Orders.ShippedDate [ShippedDate]}}");
query.SetCondition("ContactNameFirst", Operator.Contains,
new StringValue("Thomas", StringValue.MatchOption.BeginsWith));
query.SetCondition("ContactNameMiddle", Operator.Contains,
new StringValue(" John ", StringValue.MatchOption.OccursAnywhere));
query.SetCondition("ContactNameLast", Operator.Contains,
new StringValue("Hardy", StringValue.MatchOption.EndsWith));
query.SetCondition("ShippedDate", Operator.IsGreaterThanOrEqualTo,
new DateValue(DateTime.Parse("11/20/1995", System.Globalization.CultureInfo.InvariantCulture)));
var cmd = query.CreateCommand();
AssertCommand(cmd);
// Note that linefeeds differ between the original and the output. Some are removed. When outputting scopes, text before the scope is written but with reduced whitespace
// as to clean up any junk that was potentially left by any scopes that were previously removed. Generator maintains the original formatting but some lines might end up
// trimmed. This is expected behavior and this test covers it.
Assert.AreEqual(
"SELECT Orders.OrderID, Customers.ContactName, Orders.OrderDate, Orders.ShippedDate \n" +
"FROM Orders, Customers \n" +
"WHERE Customers.CustomerID = Orders.CustomerID \n" +
"AND (Customers.ContactName LIKE :pContactNameFirst_1 OR Customers.ContactName LIKE :pContactNameMiddle_1 OR Customers.ContactName LIKE :pContactNameLast_1) \n" +
"AND Orders.ShippedDate >= :pShippedDate_1"
, cmd.CommandText);
Assert.AreEqual(4, cmd.Parameters.Count);
Assert.AreEqual("pContactNameFirst_1", cmd.Parameters[0].ParameterName);
Assert.AreEqual("pContactNameMiddle_1", cmd.Parameters[1].ParameterName);
Assert.AreEqual("pContactNameLast_1", cmd.Parameters[2].ParameterName);
Assert.AreEqual("pShippedDate_1", cmd.Parameters[3].ParameterName);
Assert.AreEqual("Thomas%", cmd.Parameters[0].Value);
Assert.AreEqual("% John %", cmd.Parameters[1].Value);
Assert.AreEqual("%Hardy", cmd.Parameters[2].Value);
Assert.AreEqual(DateTime.Parse("11/20/1995", System.Globalization.CultureInfo.InvariantCulture), cmd.Parameters[3].Value);
}
public void Invalid_Script()
{
var query = new MockQuery(_connection, "SELECT * FROM TABLE1");
query.SetCondition("condition1", 0);
AssertCommand(query.CreateCommand());
}
public void Common_Sql_4()
{
var query = new MockQuery(_connection, "SELECT * FROM TABLE1 {WHERE {COLUMN1 [Criteria1]} {JUNK 1} {COLUMN2 [Criteria2]}} {JUNK 2}");
query.SetCondition("Criteria1", new BoolValue(true));
query.SetCondition("Criteria2", new NumberValue(13));
var cmd = query.CreateCommand();
AssertCommand(cmd);
// Note that now there is expected extra space before the AND resulting from the JUNK 1.
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 = :pCriteria1_1 AND COLUMN2 = :pCriteria2_1", cmd.CommandText);
Assert.AreEqual(2, cmd.Parameters.Count);
Assert.AreEqual("pCriteria1_1", cmd.Parameters[0].ParameterName);
Assert.AreEqual("pCriteria2_1", cmd.Parameters[1].ParameterName);
Assert.AreEqual(true, cmd.Parameters[0].Value);
Assert.AreEqual(13, cmd.Parameters[1].Value);
}
public void Common_Sql_5()
{
var query = new MockQuery(_connection, "SELECT * FROM TABLE1 {WHERE {COLUMN1 [Criteria1]}};");
query.SetCondition("Criteria1", new CustomParameterlessConditionValue("test"));
var cmd = query.CreateCommand();
Assert.IsNotNull(cmd);
Assert.AreEqual(0, cmd.Parameters.Count);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 = 'test' /*hint*/;", cmd.CommandText);
query.SetCondition("Criteria1", Operator.IsNot, new CustomParameterlessConditionValue("test"));
cmd = query.CreateCommand();
Assert.IsNotNull(cmd);
Assert.AreEqual(0, cmd.Parameters.Count);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 <> 'test';", cmd.CommandText);
}
public void Negative_Test_2()
{
var query = new MockQuery(_connection, "SELECT * FROM TABLE1 {WHERE {COLUMN1 [Criteria1]}}");
// Set the condition
try
{
query.SetCondition("Criteria1", new NumberValue((IEnumerable <decimal>)null));
query.CreateCommand();
Assert.Fail();
}
catch (Exception ex)
{
Assert.IsInstanceOfType(ex, typeof(ArgumentException));
}
// Set the condition
try
{
query.SetCondition("Criteria1", Operator.Contains, new NumberValue(1));
query.CreateCommand();
Assert.Fail();
}
catch (Exception ex)
{
Assert.IsInstanceOfType(ex, typeof(InvalidConditionException));
}
// Set the condition
try
{
query.SetCondition("Criteria1", new StringValue(null));
query.CreateCommand();
Assert.Fail();
}
catch (Exception ex)
{
Assert.IsInstanceOfType(ex, typeof(ArgumentException));
}
}
public void Bool_Conditions_Overloads_1()
{
var query = new MockQuery(_connection, "SELECT * FROM TABLE1 {WHERE {COLUMN1 [Criteria1]}}");
// IS
query.SetCondition("Criteria1", true);
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.Boolean, cmd.Parameters[0].DbType);
Assert.AreEqual(true, cmd.Parameters[0].Value);
// IS NULL
query.SetCondition("Criteria1", (bool?)null);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 IS NULL", cmd.CommandText);
Assert.AreEqual(0, cmd.Parameters.Count);
}
public void Null_Sql_2()
{
var query = new MockQuery(_connection, "SELECT * FROM TABLE1 {WHERE {COLUMN1 [Criteria1]}}");
// Set the DateTime condition
query.SetCondition("Criteria1", new DateValue((DateTime?)null));
var cmd = query.CreateCommand();
Assert.IsNotNull(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 IS NULL", cmd.CommandText);
Assert.AreEqual(0, cmd.Parameters.Count);
// Now try IS NOT
query.SetCondition("Criteria1", Operator.IsNot, new DateValue((DateTime?)null));
cmd = query.CreateCommand();
Assert.IsNotNull(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 IS NOT NULL", cmd.CommandText);
Assert.AreEqual(0, cmd.Parameters.Count);
// Set the Bool condition
query.SetCondition("Criteria1", new BoolValue(null));
cmd = query.CreateCommand();
Assert.IsNotNull(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 IS NULL", cmd.CommandText);
Assert.AreEqual(0, cmd.Parameters.Count);
// Now try IS NOT
query.SetCondition("Criteria1", Operator.IsNot, new BoolValue(null));
cmd = query.CreateCommand();
Assert.IsNotNull(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 IS NOT NULL", cmd.CommandText);
Assert.AreEqual(0, cmd.Parameters.Count);
}
public void Bool_Conditions_1()
{
var query = new MockQuery(_connection, "SELECT * FROM TABLE1 {WHERE {COLUMN1 [Criteria1]}}");
// IS
query.SetCondition("Criteria1", new BoolValue(true));
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.Boolean, cmd.Parameters[0].DbType);
Assert.AreEqual(true, cmd.Parameters[0].Value);
// IS NOT
query.SetCondition("Criteria1", Operator.IsNot, new BoolValue(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(false, cmd.Parameters[0].Value);
}
public void Join_Sql_1()
{
// Slightly more complex query. We're getting Orders by Customer's name and OrderDate but we also have a possibility to filter by ShippedDate
// which we will omit in this test. We'll looking for orders shipped in November '95 from a customer whose name begins with Thomas.
var query = new MockQuery(_connection, "SELECT Orders.OrderID, Customers.ContactName, Orders.OrderDate, Orders.ShippedDate " +
"FROM Orders, Customers " +
"WHERE Customers.CustomerID = Orders.CustomerID " +
"{AND {Customers.ContactName [ContactName]} " +
"{Orders.OrderDate [OrderDate]} " +
"{Orders.ShippedDate [ShippedDate]}}");
query.SetCondition("ContactName", Operator.Contains,
new StringValue("Thomas", StringValue.MatchOption.BeginsWith));
query.SetCondition("OrderDate", Operator.IsBetween, new DateValue(
DateTime.Parse("11/1/1995", System.Globalization.CultureInfo.InvariantCulture),
DateTime.Parse("11/30/1995", System.Globalization.CultureInfo.InvariantCulture)));
var cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual(
"SELECT Orders.OrderID, Customers.ContactName, Orders.OrderDate, Orders.ShippedDate " +
"FROM Orders, Customers " +
"WHERE Customers.CustomerID = Orders.CustomerID " +
"AND Customers.ContactName LIKE :pContactName_1 " +
"AND Orders.OrderDate BETWEEN :pOrderDate_1 AND :pOrderDate_2"
, cmd.CommandText);
Assert.AreEqual(3, cmd.Parameters.Count);
Assert.AreEqual("pContactName_1", cmd.Parameters[0].ParameterName);
Assert.AreEqual("pOrderDate_1", cmd.Parameters[1].ParameterName);
Assert.AreEqual("pOrderDate_2", cmd.Parameters[2].ParameterName);
Assert.AreEqual("Thomas%", cmd.Parameters[0].Value);
Assert.AreEqual(DateTime.Parse("11/1/1995", System.Globalization.CultureInfo.InvariantCulture), cmd.Parameters[1].Value);
Assert.AreEqual(DateTime.Parse("11/30/1995", System.Globalization.CultureInfo.InvariantCulture), cmd.Parameters[2].Value);
}
public void Empty_String_Sql()
{
var query = new MockQuery(_connection, "SELECT * FROM TABLE1 {WHERE {COLUMN1 [Criteria1]}}");
// Set the condition
query.SetCondition("Criteria1", new StringValue(""));
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("", cmd.Parameters[0].Value);
// Now try IS NOT
query.SetCondition("Criteria1", "", StringOperator.Is, isNot: true);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 <> :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual("", cmd.Parameters[0].Value);
// Set the condition
query.SetCondition("Criteria1", Operator.IsAnyOf, new StringValue(new[] { null, "A", "B" }));
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 IN (:pCriteria1_1, :pCriteria1_2, :pCriteria1_3)", cmd.CommandText);
Assert.AreEqual(3, cmd.Parameters.Count);
Assert.AreEqual(DBNull.Value, cmd.Parameters[0].Value);
Assert.AreEqual("A", cmd.Parameters[1].Value);
Assert.AreEqual("B", cmd.Parameters[2].Value);
}
public void Common_Sql_2()
{
var query = new MockQuery(_connection, "SELECT * FROM TABLE1 {WHERE {JUNK 1} {COLUMN1 [Criteria1]} {JUNK 2}}");
query.SetCondition("Criteria1", new BoolValue(true));
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("pCriteria1_1", cmd.Parameters[0].ParameterName);
Assert.AreEqual(true, cmd.Parameters[0].Value);
}
public void Complex_Sql_1()
{
_query.SetCondition("OrderID", Operator.Is, new NumberValue(10));
var cmd = _query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual(
"SELECT Orders.OrderID, Customers.ContactName, Orders.OrderDate, Orders.ShippedDate\n" +
"FROM Orders, Customers\n" +
"WHERE Customers.CustomerID = Orders.CustomerID\n" +
"AND Orders.OrderID = :pOrderID_1",
cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(10, cmd.Parameters[0].Value);
}
public void String_Conditions_Overloads_1()
{
var query = new MockQuery(_connection, "SELECT * FROM TABLE1 {WHERE {COLUMN1 [Criteria1]}}");
var s1 = "Value 1";
var s2 = "Value 2";
// IS
query.SetCondition("Criteria1", s1);
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.String, cmd.Parameters[0].DbType);
Assert.AreEqual(s1, cmd.Parameters[0].Value);
// LIKE
query.SetCondition("Criteria1", s1, StringOperator.IsLike);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 LIKE :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(s1, cmd.Parameters[0].Value);
// NOT LIKE
query.SetCondition("Criteria1", s1, StringOperator.IsLike, isNot: true);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 NOT LIKE :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(s1, cmd.Parameters[0].Value);
// BEGINS WITH
query.SetCondition("Criteria1", s1, StringOperator.BeginsWith);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 LIKE :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual($"{s1}%", cmd.Parameters[0].Value);
// CONTAINS
query.SetCondition("Criteria1", s1, StringOperator.Contains);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 LIKE :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual($"%{s1}%", cmd.Parameters[0].Value);
// ENDS WITH
query.SetCondition("Criteria1", s1, StringOperator.EndsWith);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 LIKE :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual($"%{s1}", cmd.Parameters[0].Value);
// IS NOT
query.SetCondition("Criteria1", s1, isNot: true);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 <> :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(s1, cmd.Parameters[0].Value);
// IN
query.SetCondition("Criteria1", new[] { s1, s2 });
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(s1, cmd.Parameters[0].Value);
Assert.AreEqual(s2, cmd.Parameters[1].Value);
// NOT IN
query.SetCondition("Criteria1", new[] { s1, s2 }, 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(s1, cmd.Parameters[0].Value);
Assert.AreEqual(s2, cmd.Parameters[1].Value);
}
public void Date_Conditions_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", new DateValue(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.SetCondition("Criteria1", Operator.IsLessThan, new DateValue(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 LESS THAN OR EQ
query.SetCondition("Criteria1", Operator.IsLessThanOrEqualTo, new DateValue(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.SetCondition("Criteria1", Operator.IsGreaterThan, new DateValue(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 OR EQ
query.SetCondition("Criteria1", Operator.IsGreaterThanOrEqualTo, new DateValue(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", Operator.IsNot, new DateValue(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 BETWEEN
query.SetCondition("Criteria1", Operator.IsBetween, new DateValue(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.SetCondition("Criteria1", Operator.IsNotBetween, new DateValue(dt1, dt2));
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", Operator.IsAnyOf, new DateValue(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", Operator.IsNotAnyOf, new DateValue(new[] { dt1, dt2 }));
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);
}
public void String_Conditions_1()
{
var query = new MockQuery(_connection, "SELECT * FROM TABLE1 {WHERE {COLUMN1 [Criteria1]}}");
var s1 = "Value 1";
var s2 = "Value 2";
// IS
query.SetCondition("Criteria1", new StringValue(s1));
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.String, cmd.Parameters[0].DbType);
Assert.AreEqual(s1, cmd.Parameters[0].Value);
// LIKE
query.SetCondition("Criteria1", Operator.Contains, new StringValue(s1));
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 LIKE :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(s1, cmd.Parameters[0].Value);
// NOT LIKE
query.SetCondition("Criteria1", Operator.DoesNotContain, new StringValue(s1));
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 NOT LIKE :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(s1, cmd.Parameters[0].Value);
// BEGINS WITH
query.SetCondition("Criteria1", Operator.Contains, new StringValue(s1, StringValue.MatchOption.BeginsWith, "*"));
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 LIKE :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual($"{s1}*", cmd.Parameters[0].Value);
// CONTAINS
query.SetCondition("Criteria1", Operator.Contains, new StringValue(s1, StringValue.MatchOption.OccursAnywhere, "*"));
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 LIKE :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual($"*{s1}*", cmd.Parameters[0].Value);
// ENDS WITH
query.SetCondition("Criteria1", Operator.Contains, new StringValue(s1, StringValue.MatchOption.EndsWith, "*"));
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 LIKE :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual($"*{s1}", cmd.Parameters[0].Value);
// IS NOT
query.SetCondition("Criteria1", Operator.IsNot, new StringValue(s1));
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 <> :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual(s1, cmd.Parameters[0].Value);
// IS BETWEEN
query.SetCondition("Criteria1", Operator.IsBetween, new StringValue(s1, s2));
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(s1, cmd.Parameters[0].Value);
Assert.AreEqual(s2, cmd.Parameters[1].Value);
// IS NOT BETWEEN
query.SetCondition("Criteria1", Operator.IsNotBetween, new StringValue(s1, s2));
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(s1, cmd.Parameters[0].Value);
Assert.AreEqual(s2, cmd.Parameters[1].Value);
// IN
query.SetCondition("Criteria1", Operator.IsAnyOf, new StringValue(new[] { s1, s2 }));
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(s1, cmd.Parameters[0].Value);
Assert.AreEqual(s2, cmd.Parameters[1].Value);
// NOT IN
query.SetCondition("Criteria1", Operator.IsNotAnyOf, new StringValue(new[] { s1, s2 }));
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(s1, cmd.Parameters[0].Value);
Assert.AreEqual(s2, cmd.Parameters[1].Value);
}
public void Number_Conditions_2()
{
var query = new MockQuery(_connection, "SELECT * FROM TABLE1 {WHERE {COLUMN1 [Criteria1]}}");
decimal nDec = 123;
double nDbl = 123;
float nFlt = 123;
UInt32 nUInt32 = 123;
UInt64 nUInt64 = 123;
Int32 nInt32 = 123;
Int64 nInt64 = 123;
byte nByte = 123;
sbyte nSByte = 123;
char nChar = 'A';
// DECIMAL
query.SetCondition("Criteria1", new NumberValue(nDec));
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.Decimal, cmd.Parameters[0].DbType);
Assert.AreEqual(nDec, cmd.Parameters[0].Value);
// DOUBLE
query.SetCondition("Criteria1", new NumberValue(nDbl));
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.Double, cmd.Parameters[0].DbType);
Assert.AreEqual(nDbl, cmd.Parameters[0].Value);
// FLOAT
query.SetCondition("Criteria1", new NumberValue(nFlt));
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.Single, cmd.Parameters[0].DbType);
Assert.AreEqual(nFlt, cmd.Parameters[0].Value);
// UINT32
query.SetCondition("Criteria1", new NumberValue(nUInt32));
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.UInt32, cmd.Parameters[0].DbType);
Assert.AreEqual(nUInt32, cmd.Parameters[0].Value);
// UINT64
query.SetCondition("Criteria1", new NumberValue(nUInt64));
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.UInt64, cmd.Parameters[0].DbType);
Assert.AreEqual(nUInt64, cmd.Parameters[0].Value);
// INT32
query.SetCondition("Criteria1", new NumberValue(nInt32));
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(nInt32, cmd.Parameters[0].Value);
// INT64
query.SetCondition("Criteria1", new NumberValue(nInt64));
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.Int64, cmd.Parameters[0].DbType);
Assert.AreEqual(nInt64, cmd.Parameters[0].Value);
// BYTE
query.SetCondition("Criteria1", new NumberValue(nByte));
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.Byte, cmd.Parameters[0].DbType);
Assert.AreEqual(nByte, cmd.Parameters[0].Value);
// SBYTE
query.SetCondition("Criteria1", new NumberValue(nSByte));
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.SByte, cmd.Parameters[0].DbType);
Assert.AreEqual(nSByte, cmd.Parameters[0].Value);
// CHAR
query.SetCondition("Criteria1", new NumberValue(nChar));
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.StringFixedLength, cmd.Parameters[0].DbType);
Assert.AreEqual(1, cmd.Parameters[0].Size);
Assert.AreEqual(nChar, cmd.Parameters[0].Value);
}
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 Number_Conditions_1()
{
var query = new MockQuery(_connection, "SELECT * FROM TABLE1 {WHERE {COLUMN1 [Criteria1]}}");
Int32 n1 = 123;
Int32 n2 = 456;
// IS
query.SetCondition("Criteria1", new NumberValue(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 LESS THAN
query.SetCondition("Criteria1", Operator.IsLessThan, new NumberValue(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);
// IS LESS THAN OR EQ
query.SetCondition("Criteria1", Operator.IsLessThanOrEqualTo, new NumberValue(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);
// IS GR THAN
query.SetCondition("Criteria1", Operator.IsGreaterThan, new NumberValue(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);
// IS GR THAN OR EQ
query.SetCondition("Criteria1", Operator.IsGreaterThanOrEqualTo, new NumberValue(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);
// IS NOT
query.SetCondition("Criteria1", Operator.IsNot, new NumberValue(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);
// IS BETWEEN
query.SetCondition("Criteria1", Operator.IsBetween, new NumberValue(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.SetCondition("Criteria1", Operator.IsNotBetween, new NumberValue(n1, n2));
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", Operator.IsAnyOf, new NumberValue(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", Operator.IsNotAnyOf, new NumberValue(new[] { n1, n2 }));
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);
}
public void Convert_In_To_Equality_1()
{
// Tests the scenario where IN('A') or IN(123) should be automatically converted by the = 'A' and = 123 etc. This happens
// in the ConditionValue classes
var query = new MockQuery(_connection, "SELECT * FROM TABLE1 {WHERE {COLUMN1 [Criteria1]}}");
// Set conditions, string short overload
query.SetCondition("Criteria1", new [] { "A" });
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("pCriteria1_1", cmd.Parameters[0].ParameterName);
Assert.AreEqual("A", cmd.Parameters[0].Value);
// Set conditions, string short overload, negative
query.SetCondition("Criteria1", new[] { "A" }, true);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 != :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual("pCriteria1_1", cmd.Parameters[0].ParameterName);
Assert.AreEqual("A", cmd.Parameters[0].Value);
// Set conditions, string
query.SetCondition("Criteria1", Operator.IsAnyOf, new StringValue(new[] { "A" }));
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 = :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual("pCriteria1_1", cmd.Parameters[0].ParameterName);
Assert.AreEqual("A", cmd.Parameters[0].Value);
// Set conditions, number short overload
query.SetCondition("Criteria1", new[] { 123 });
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 = :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual("pCriteria1_1", cmd.Parameters[0].ParameterName);
Assert.AreEqual(123, cmd.Parameters[0].Value);
// Set conditions, number short overload, negative
query.SetCondition("Criteria1", new[] { 123 }, true);
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 <> :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual("pCriteria1_1", cmd.Parameters[0].ParameterName);
Assert.AreEqual(123, cmd.Parameters[0].Value);
// Set conditions, number
query.SetCondition("Criteria1", Operator.IsAnyOf, new NumberValue(new[] { 123 }));
cmd = query.CreateCommand();
AssertCommand(cmd);
Assert.AreEqual("SELECT * FROM TABLE1 WHERE COLUMN1 = :pCriteria1_1", cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreEqual("pCriteria1_1", cmd.Parameters[0].ParameterName);
Assert.AreEqual(123, cmd.Parameters[0].Value);
}