Example #1
0
            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));
                }
            }
Example #2
0
            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);
            }
Example #3
0
            public void Invalid_Script()
            {
                var query = new MockQuery(_connection, "SELECT * FROM TABLE1");

                query.SetCondition("condition1", 0);
                AssertCommand(query.CreateCommand());
            }
Example #4
0
            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);
            }
Example #5
0
            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);
            }
Example #6
0
            public void Variables_1()
            {
                var query    = new MockQuery(_connection, "SELECT * FROM TABLE1 {WHERE {COLUMN1 [Variable1]}}");
                var variable = "= 'Some Value'";

                query.DefineVariable("Variable1", variable);

                var cmd = query.CreateCommand();

                Assert.IsNotNull(cmd);
                Assert.AreEqual(CommandType.Text, cmd.CommandType);
                Assert.AreEqual($"SELECT * FROM TABLE1 WHERE COLUMN1 {variable}", cmd.CommandText);
            }
Example #7
0
            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);
            }
Example #8
0
 public void InitializeTest()
 {
     _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.OrderID [OrderID]}" +
                            "@{({Orders.OrderDate [OrderDate]} {Orders.ShippedDate [ShippedDate]} {Orders.RequiredDate [RequiredDate]})}\n" +
                            "{Orders.EmployeeID IN (SELECT EmployeeID FROM Employees WHERE {FirstName [EmployeeFirstName]} {LastName [EmployeeLastName]})}\n" +
                            "{Orders.OrderID IN (SELECT OrderID FROM [[Order Details]] WHERE ProductID IN (SELECT ProductID FROM Products WHERE {ProductName [ProductName]} {SupplierID IN (SELECT SupplierID FROM Suppliers WHERE {CompanyName [SupplierCompanyName]})}))}\n" +
                            "{Orders.Freight [Freight]}}");
 }
Example #9
0
            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);
            }
Example #10
0
            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);
            }
Example #11
0
            private void AssertParserException(string script)
            {
                var query = new MockQuery(_connection, script);

                // Set the condition
                try
                {
                    query.CreateCommand();
                }
                catch (Exception ex)
                {
                    Assert.IsInstanceOfType(ex, typeof(Parsing.ParserException));
                    return;
                }
                Assert.Fail();
            }
Example #12
0
            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);
            }
Example #13
0
            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);
            }
Example #14
0
            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));
                }
            }
Example #15
0
            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);
            }
Example #16
0
            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);
            }
Example #17
0
            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);
            }
Example #18
0
            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);
            }
Example #19
0
            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);
            }
Example #20
0
            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);
            }
Example #21
0
            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);
            }
Example #22
0
            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);
            }
Example #23
0
            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);
            }
Example #24
0
            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);
            }
Example #25
0
            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);
            }
Example #26
0
            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);
            }
Example #27
0
            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);
            }