public void Complex_Sql_4()
{
// Compile the query
_query.SetCondition("OrderDate", DateTime.Parse("12/30/1995", System.Globalization.CultureInfo.InvariantCulture), NumericOperator.IsLessThanOrEqualTo);
_query.SetCondition("ShippedDate",
new[]
{
DateTime.Parse("11/20/1995", System.Globalization.CultureInfo.InvariantCulture),
DateTime.Parse("11/21/1995", System.Globalization.CultureInfo.InvariantCulture),
DateTime.Parse("11/25/1995", System.Globalization.CultureInfo.InvariantCulture)
});
_query.SetCondition("ProductName", new[] { "Tofu", "Chai", "Chocolade" });
_query.SetCondition("Freight", 200m, NumericOperator.IsGreaterThanOrEqualTo);
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.OrderDate <= :pOrderDate_1 OR Orders.ShippedDate IN (:pShippedDate_1, :pShippedDate_2, :pShippedDate_3))\n" +
"AND Orders.OrderID IN (SELECT OrderID FROM [Order Details] WHERE ProductID IN (SELECT ProductID FROM Products WHERE ProductName IN (:pProductName_1, :pProductName_2, :pProductName_3)))\n" +
"AND Orders.Freight >= :pFreight_1",
cmd.CommandText);
Assert.AreEqual(8, cmd.Parameters.Count);
Assert.AreEqual(DateTime.Parse("12/30/1995", System.Globalization.CultureInfo.InvariantCulture), cmd.Parameters[0].Value);
Assert.AreEqual(DateTime.Parse("11/20/1995", System.Globalization.CultureInfo.InvariantCulture), cmd.Parameters[1].Value);
Assert.AreEqual(DateTime.Parse("11/21/1995", System.Globalization.CultureInfo.InvariantCulture), cmd.Parameters[2].Value);
Assert.AreEqual(DateTime.Parse("11/25/1995", System.Globalization.CultureInfo.InvariantCulture), cmd.Parameters[3].Value);
Assert.AreEqual("Tofu", cmd.Parameters[4].Value);
Assert.AreEqual("Chai", cmd.Parameters[5].Value);
Assert.AreEqual("Chocolade", cmd.Parameters[6].Value);
Assert.AreEqual(200m, cmd.Parameters[7].Value);
// Add extra condition
_query.SetCondition("SupplierCompanyName", "Tokyo Traders");
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.OrderDate <= :pOrderDate_1 OR Orders.ShippedDate IN (:pShippedDate_1, :pShippedDate_2, :pShippedDate_3))\n" +
"AND Orders.OrderID IN (SELECT OrderID FROM [Order Details] WHERE ProductID IN (SELECT ProductID FROM Products WHERE ProductName IN (:pProductName_1, :pProductName_2, :pProductName_3) AND SupplierID IN (SELECT SupplierID FROM Suppliers WHERE CompanyName = :pSupplierCompanyName_1)))\n" +
"AND Orders.Freight >= :pFreight_1",
cmd.CommandText);
Assert.AreEqual(9, cmd.Parameters.Count);
Assert.AreEqual(DateTime.Parse("12/30/1995", System.Globalization.CultureInfo.InvariantCulture), cmd.Parameters[0].Value);
Assert.AreEqual(DateTime.Parse("11/20/1995", System.Globalization.CultureInfo.InvariantCulture), cmd.Parameters[1].Value);
Assert.AreEqual(DateTime.Parse("11/21/1995", System.Globalization.CultureInfo.InvariantCulture), cmd.Parameters[2].Value);
Assert.AreEqual(DateTime.Parse("11/25/1995", System.Globalization.CultureInfo.InvariantCulture), cmd.Parameters[3].Value);
Assert.AreEqual("Tofu", cmd.Parameters[4].Value);
Assert.AreEqual("Chai", cmd.Parameters[5].Value);
Assert.AreEqual("Chocolade", cmd.Parameters[6].Value);
Assert.AreEqual("Tokyo Traders", cmd.Parameters[7].Value);
Assert.AreEqual(200m, cmd.Parameters[8].Value);
// Remove two conditions
_query.RemoveCondition("ProductName");
_query.RemoveCondition("OrderDate");
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.ShippedDate IN (:pShippedDate_1, :pShippedDate_2, :pShippedDate_3))\n" +
"AND Orders.OrderID IN (SELECT OrderID FROM [Order Details] WHERE ProductID IN (SELECT ProductID FROM Products WHERE SupplierID IN (SELECT SupplierID FROM Suppliers WHERE CompanyName = :pSupplierCompanyName_1)))\n" +
"AND Orders.Freight >= :pFreight_1",
cmd.CommandText);
Assert.AreEqual(5, cmd.Parameters.Count);
Assert.AreEqual(DateTime.Parse("11/20/1995", System.Globalization.CultureInfo.InvariantCulture), cmd.Parameters[0].Value);
Assert.AreEqual(DateTime.Parse("11/21/1995", System.Globalization.CultureInfo.InvariantCulture), cmd.Parameters[1].Value);
Assert.AreEqual(DateTime.Parse("11/25/1995", System.Globalization.CultureInfo.InvariantCulture), cmd.Parameters[2].Value);
Assert.AreEqual("Tokyo Traders", cmd.Parameters[3].Value);
Assert.AreEqual(200m, cmd.Parameters[4].Value);
}