public void ManualWhereOr()
{
ProductQuery pq = new ProductQuery("p");
pq.es2.Connection.Name = "ForeignKeyTest";
List<int> inList = new List<int>();
inList.Add(8);
inList.Add(9);
esComparison comp = null;
comp = pq.ManualWhere("ProductName", "LIKE", "W%", null, "OR");
comp = pq.ManualWhere("UnitPrice", "LESSTHAN", 10.0, null, "OR");
comp = pq.ManualWhere("ProductID", "IN", inList, null, "OR");
pq.Where(comp);
ProductCollection coll = new ProductCollection();
coll.es.Connection.Name = "ForeignKeyTest";
Assert.IsTrue(coll.Load(pq));
Assert.AreEqual(7, coll.Count);
}
public void ManualWhereAnd()
{
ProductQuery pq = new ProductQuery("p");
pq.es2.Connection.Name = "ForeignKeyTest";
esComparison comp = null;
comp = pq.ManualWhere("Discontinued", "EQUAL", false, null, "AND");
comp = pq.ManualWhere("UnitPrice", "BETWEEN", 0.15, 0.20, "AND");
comp = pq.ManualWhere("ProductID", "GREATERTHAN", 2, null, "AND");
pq.Where(comp);
ProductCollection coll = new ProductCollection();
coll.es.Connection.Name = "ForeignKeyTest";
Assert.IsTrue(coll.Load(pq));
Assert.AreEqual(1, coll.Count);
}
public static void RefreshForeignKeyTest(string connectionName)
{
OrderItemCollection oiColl = new OrderItemCollection();
oiColl.es.Connection.Name = "ForeignKeyTest";
if (connectionName.Length != 0)
{
oiColl.es.Connection.Name = connectionName;
}
oiColl.Query.Where(oiColl.Query.OrderID > 11 |
oiColl.Query.ProductID > 9);
oiColl.Query.Load();
oiColl.MarkAllAsDeleted();
oiColl.Save();
OrderCollection oColl = new OrderCollection();
oColl.es.Connection.Name = "ForeignKeyTest";
if (connectionName.Length != 0)
{
oColl.es.Connection.Name = connectionName;
}
oColl.Query.Where(oColl.Query.OrderID > 11);
oColl.Query.Load();
oColl.MarkAllAsDeleted();
oColl.Save();
EmployeeTerritoryCollection etColl = new EmployeeTerritoryCollection();
etColl.es.Connection.Name = "ForeignKeyTest";
if (connectionName.Length != 0)
{
etColl.es.Connection.Name = connectionName;
}
etColl.Query.Where(etColl.Query.EmpID > 4 |
etColl.Query.TerrID > 4);
etColl.Query.Load();
etColl.MarkAllAsDeleted();
etColl.Save();
CustomerCollection cColl = new CustomerCollection();
cColl.es.Connection.Name = "ForeignKeyTest";
if (connectionName.Length != 0)
{
cColl.es.Connection.Name = connectionName;
}
cColl.Query.Where(cColl.Query.CustomerID > "99999" &
cColl.Query.CustomerSub > "001");
cColl.Query.Load();
cColl.MarkAllAsDeleted();
cColl.Save();
TerritoryExCollection tExColl = new TerritoryExCollection();
tExColl.es.Connection.Name = "ForeignKeyTest";
if (connectionName.Length != 0)
{
tExColl.es.Connection.Name = connectionName;
}
tExColl.Query.Where(tExColl.Query.TerritoryID > 1);
tExColl.Query.Load();
tExColl.MarkAllAsDeleted();
tExColl.Save();
TerritoryCollection tColl = new TerritoryCollection();
tColl.es.Connection.Name = "ForeignKeyTest";
if (connectionName.Length != 0)
{
tColl.es.Connection.Name = connectionName;
}
tColl.Query.Where(tColl.Query.TerritoryID > 5);
tColl.Query.Load();
tColl.MarkAllAsDeleted();
tColl.Save();
ReferredEmployeeCollection reColl = new ReferredEmployeeCollection();
reColl.es.Connection.Name = "ForeignKeyTest";
if (connectionName.Length != 0)
{
reColl.es.Connection.Name = connectionName;
}
reColl.Query.Where(reColl.Query.EmployeeID > 4 |
reColl.Query.ReferredID > 5);
reColl.Query.Load();
reColl.MarkAllAsDeleted();
reColl.Save();
ProductCollection pColl = new ProductCollection();
pColl.es.Connection.Name = "ForeignKeyTest";
if (connectionName.Length != 0)
{
pColl.es.Connection.Name = connectionName;
}
pColl.Query.Where(pColl.Query.ProductID > 10);
pColl.Query.Load();
pColl.MarkAllAsDeleted();
pColl.Save();
GroupCollection gColl = new GroupCollection();
gColl.es.Connection.Name = "ForeignKeyTest";
if (connectionName.Length != 0)
{
gColl.es.Connection.Name = connectionName;
}
gColl.Query.Where(gColl.Query.Id > "15001");
gColl.Query.Load();
gColl.MarkAllAsDeleted();
gColl.Save();
EmployeeCollection eColl = new EmployeeCollection();
eColl.es.Connection.Name = "ForeignKeyTest";
if (connectionName.Length != 0)
{
eColl.es.Connection.Name = connectionName;
}
eColl.Query.Where(eColl.Query.EmployeeID > 5);
eColl.Query.Load();
eColl.MarkAllAsDeleted();
eColl.Save();
CustomerGroupCollection cgColl = new CustomerGroupCollection();
cgColl.es.Connection.Name = "ForeignKeyTest";
if (connectionName.Length != 0)
{
cgColl.es.Connection.Name = connectionName;
}
cgColl.Query.Where(cgColl.Query.GroupID > "99999" |
cgColl.Query.GroupID < "00001");
cgColl.Query.Load();
cgColl.MarkAllAsDeleted();
cgColl.Save();
}
public void CustomMultiInnerWithDistinct()
{
int empId = 1;
ProductCollection collection = new ProductCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
Assert.IsTrue(collection.GetActiveProductIds(empId));
Assert.AreEqual(2, collection.Count);
}
public void MixedANDAndORInOn()
{
ProductCollection collection = new ProductCollection();
collection.es.Connection.ConnectionString =
UnitTestBase.GetFktString(collection.es.Connection);
int empId = 1;
ProductQuery prd = new ProductQuery("pq");
OrderItemQuery item = new OrderItemQuery("oiq");
OrderQuery ord = new OrderQuery("oq");
CustomerQuery cust = new CustomerQuery("cq");
EmployeeQuery emp = new EmployeeQuery("eq");
prd.Select(prd.ProductID);
prd.InnerJoin(item).On(prd.ProductID == item.ProductID);
prd.InnerJoin(ord).On(item.OrderID == ord.OrderID);
prd.InnerJoin(cust).On(ord.CustID == cust.CustomerID &
(ord.CustSub == cust.CustomerSub |
ord.EmployeeID == cust.StaffAssigned));
prd.InnerJoin(emp).On(cust.Manager == emp.EmployeeID);
prd.Where(emp.EmployeeID == empId);
prd.Where(prd.Discontinued == false);
prd.OrderBy(prd.ProductID.Ascending);
Assert.IsTrue(collection.Load(prd));
Assert.AreEqual(9, collection.Count);
}
public void TestCollectionDeleteAll()
{
int prdId = -1;
Product prd = new Product();
prd.es.Connection.Name = "ForeignKeyTest";
try
{
using (esTransactionScope scope = new esTransactionScope())
{
prd.ProductName = "UnitTest";
prd.UnitPrice = 1;
prd.Discontinued = false;
for (int i = 0; i < 3; i++)
{
OrderItem oi = prd.OrderItemCollectionByProductID.AddNew();
oi.OrderID = i + 1;
oi.UnitPrice = prd.UnitPrice;
oi.Quantity = Convert.ToInt16(i);
oi.Discount = 0;
}
prd.Save();
prdId = prd.ProductID.Value;
// Test
ProductCollection collection = new ProductCollection();
collection.es.Connection.Name = "ForeignKeyTest";
Assert.IsTrue(collection.LoadAll());
Product entity = collection.FindByPrimaryKey(prdId);
Assert.AreEqual(3, entity.OrderItemCollectionByProductID.Count);
entity.OrderItemCollectionByProductID.MarkAllAsDeleted();
entity.MarkAsDeleted();
collection.Save();
prd = new Product();
prd.es.Connection.Name = "ForeignKeyTest";
Assert.IsFalse(prd.LoadByPrimaryKey(prdId));
OrderItemCollection oic = new OrderItemCollection();
oic.es.Connection.Name = "ForeignKeyTest";
oic.Query.Where(oic.Query.ProductID == prdId);
Assert.IsFalse(oic.Query.Load());
}
}
finally
{
prd = new Product();
prd.es.Connection.Name = "ForeignKeyTest";
if (prd.LoadByPrimaryKey(prdId))
{
prd.OrderItemCollectionByProductID.MarkAllAsDeleted();
prd.MarkAsDeleted();
prd.Save();
}
}
}