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