static public void Delete(System.Int32 employeeID, esSqlAccessType sqlAccessType)
{
var obj = new Employee();
obj.EmployeeID = employeeID;
obj.AcceptChanges();
obj.MarkAsDeleted();
obj.Save(sqlAccessType);
}
public void CastToDateTime()
{
int empId = 0;
try
{
using (esTransactionScope scope = new esTransactionScope())
{
// Create an employee with a date in the LastName column
Employee emp = new Employee();
emp.es.Connection.Name = "ForeignKeyTest";
switch (emp.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.OracleClientProvider":
emp.LastName = "31-DEC-2008 01:01:01";
break;
default:
emp.LastName = "2008-12-31";
break;
}
emp.FirstName = "required";
emp.Save();
empId = emp.EmployeeID.Value;
emp = new Employee();
emp.es.Connection.Name = "ForeignKeyTest";
emp.Query.Select((esDateTime)emp.Query.LastName.As("CastColumn"));
emp.Query.Where(emp.Query.EmployeeID == empId);
switch (emp.es.Connection.ProviderSignature.DataProviderName)
{
//case "EntitySpaces.MySqlClientProvider":
//case "EntitySpaces.NpgsqlProvider":
//case "EntitySpaces.Npgsql2Provider":
//case "EntitySpaces.VistaDBProvider":
case "EntitySpaces.MSAccessProvider":
case "EntitySpaces.SQLiteProvider":
//case "EntitySpaces.SqlServerCeProvider":
Assert.Ignore("Not supported.");
break;
case "EntitySpaces.OracleClientProvider":
string lq = emp.Query.Parse();
Assert.IsTrue(emp.Query.Load());
DateTime value = DateTime.Parse("2008-12-31 01:01:01");
object obj = emp.GetColumn("CastColumn");
Assert.AreEqual("System.DateTime", obj.GetType().ToString());
Assert.AreEqual(value, obj);
break;
default:
lq = emp.Query.Parse();
Assert.IsTrue(emp.Query.Load());
value = DateTime.Parse("2008-12-31");
obj = emp.GetColumn("CastColumn");
Assert.AreEqual("System.DateTime", obj.GetType().ToString());
Assert.AreEqual(value, obj);
break;
}
}
}
finally
{
Employee emp = new Employee();
emp.es.Connection.Name = "ForeignKeyTest";
emp.Query.Where(emp.Query.EmployeeID == empId);
if (emp.Query.Load())
{
emp.MarkAsDeleted();
emp.Save();
}
}
}
public void TestMultiDeleteEntity()
{
int empKey = -1;
int ordKey = -1;
CustomerGroup custGroup = new CustomerGroup();
custGroup.es.Connection.Name = "ForeignKeyTest";
EmployeeCollection empColl = new EmployeeCollection();
empColl.es.Connection.Name = "ForeignKeyTest";
Employee emp = new Employee();
emp.es.Connection.Name = "ForeignKeyTest";
Employee testEmp = new Employee();
testEmp.es.Connection.Name = "ForeignKeyTest";
Customer cust = new Customer();
cust.es.Connection.Name = "ForeignKeyTest";
Order ord = new Order();
ord.es.Connection.Name = "ForeignKeyTest";
try
{
using (esTransactionScope scope = new esTransactionScope())
{
// Setup
custGroup.GroupID = "YYYYY";
custGroup.GroupName = "Test";
custGroup.Save();
emp = empColl.AddNew();
emp.LastName = "LastName";
emp.FirstName = "FirstName";
cust = emp.CustomerCollectionByStaffAssigned.AddNew();
cust.CustomerID = "YYYYY";
cust.CustomerSub = "YYY";
cust.CustomerName = "Test";
cust.str().DateAdded = "2007-01-01 00:00:00";
cust.Active = true;
cust.Manager = 1;
ord = emp.OrderCollectionByEmployeeID.AddNew();
ord.CustID = "YYYYY";
ord.CustSub = "YYY";
ord.str().OrderDate = "2007-01-01";
empColl.Save();
empKey = emp.EmployeeID.Value;
ordKey = ord.OrderID.Value;
Assert.AreEqual(1, emp.CustomerCollectionByStaffAssigned.Count);
Assert.AreEqual(1, emp.OrderCollectionByEmployeeID.Count);
Assert.AreEqual(emp.EmployeeID.Value, cust.StaffAssigned.Value);
Assert.AreEqual(emp.EmployeeID.Value, ord.EmployeeID.Value);
// Test
testEmp.LoadByPrimaryKey(empKey);
testEmp.OrderCollectionByEmployeeID.MarkAllAsDeleted();
testEmp.CustomerCollectionByStaffAssigned.MarkAllAsDeleted();
testEmp.MarkAsDeleted();
testEmp.Save();
emp = new Employee();
emp.es.Connection.Name = "ForeignKeyTest";
Assert.IsFalse(emp.LoadByPrimaryKey(empKey));
ord = new Order();
ord.es.Connection.Name = "ForeignKeyTest";
Assert.IsFalse(ord.LoadByPrimaryKey(ordKey));
cust = new Customer();
cust.es.Connection.Name = "ForeignKeyTest";
Assert.IsFalse(cust.LoadByPrimaryKey("YYYYY", "YYY"));
}
}
finally
{
// Clean up
ord = new Order();
ord.es.Connection.Name = "ForeignKeyTest";
if (ord.LoadByPrimaryKey(ordKey))
{
ord.MarkAsDeleted();
ord.Save();
}
cust = new Customer();
cust.es.Connection.Name = "ForeignKeyTest";
if (cust.LoadByPrimaryKey("YYYYY", "YYY"))
{
cust.MarkAsDeleted();
cust.Save();
}
emp = new Employee();
emp.es.Connection.Name = "ForeignKeyTest";
if (emp.LoadByPrimaryKey(empKey))
{
emp.MarkAsDeleted();
emp.Save();
}
custGroup = new CustomerGroup();
custGroup.es.Connection.Name = "ForeignKeyTest";
if (custGroup.LoadByPrimaryKey("YYYYY"))
{
custGroup.MarkAsDeleted();
custGroup.Save();
}
}
}
public void TestSave()
{
int empKey = -1;
CustomerGroup custGroup = new CustomerGroup();
custGroup.es.Connection.Name = "ForeignKeyTest";
Employee emp = new Employee();
emp.es.Connection.Name = "ForeignKeyTest";
Customer cust = new Customer();
cust.es.Connection.Name = "ForeignKeyTest";
try
{
using (esTransactionScope scope = new esTransactionScope())
{
custGroup.GroupID = "XXXXX";
custGroup.GroupName = "Test";
custGroup.Save();
emp.LastName = "LastName";
emp.FirstName = "FirstName";
cust = emp.CustomerCollectionByStaffAssigned.AddNew();
cust.CustomerID = "XXXXX";
cust.CustomerSub = "XXX";
cust.CustomerName = "Test";
cust.str().DateAdded = "2007-01-01 00:00:00";
cust.Active = true;
cust.Manager = 1;
emp.Save();
empKey = emp.EmployeeID.Value;
Assert.AreEqual(1, emp.CustomerCollectionByStaffAssigned.Count);
Assert.AreEqual(emp.EmployeeID.Value, cust.StaffAssigned.Value);
}
}
finally
{
// Clean up
emp = new Employee();
emp.es.Connection.Name = "ForeignKeyTest";
if (emp.LoadByPrimaryKey(empKey))
{
CustomerCollection custColl = emp.CustomerCollectionByStaffAssigned;
custColl.MarkAllAsDeleted();
emp.MarkAsDeleted();
emp.Save();
}
custGroup = new CustomerGroup();
custGroup.es.Connection.Name = "ForeignKeyTest";
if (custGroup.LoadByPrimaryKey("XXXXX"))
{
custGroup.MarkAsDeleted();
custGroup.Save();
}
}
}
public void TestSaveSimpleCollection()
{
int empKey = -1;
EmployeeCollection empColl = new EmployeeCollection();
empColl.es.Connection.Name = "ForeignKeyTest";
Employee emp = new Employee();
emp.es.Connection.Name = "ForeignKeyTest";
try
{
using (esTransactionScope scope = new esTransactionScope())
{
empColl.LoadAll();
emp = empColl.AddNew();
emp.LastName = "LastName";
emp.FirstName = "FirstName";
Order ord = emp.OrderCollectionByEmployeeID.AddNew();
ord.CustID = "10001";
ord.CustSub = "001";
ord.str().OrderDate = "2007-01-01 00:00:00";
empColl.Save();
empKey = emp.EmployeeID.Value;
Assert.AreEqual(1, emp.OrderCollectionByEmployeeID.Count);
Assert.AreEqual(emp.EmployeeID.Value, ord.EmployeeID.Value);
}
}
finally
{
// Clean up
emp = new Employee();
emp.es.Connection.Name = "ForeignKeyTest";
if (emp.LoadByPrimaryKey(empKey))
{
OrderCollection ordColl = emp.OrderCollectionByEmployeeID;
ordColl.MarkAllAsDeleted();
emp.MarkAsDeleted();
emp.Save();
}
}
}
public void TestSaveSelfReference()
{
int empKey = -1;
int supvKey = -1;
Employee emp = new Employee();
emp.es.Connection.Name = "ForeignKeyTest";
Employee supv = new Employee();
supv.es.Connection.Name = "ForeignKeyTest";
try
{
using (esTransactionScope scope = new esTransactionScope())
{
emp.LastName = "LastName";
emp.FirstName = "FirstName";
supv = emp.EmployeeCollectionBySupervisor.AddNew();
supv.LastName = "SupvLast";
supv.FirstName = "SupvFirst";
emp.Save();
empKey = emp.EmployeeID.Value;
supvKey = supv.EmployeeID.Value;
Assert.AreEqual(1, emp.EmployeeCollectionBySupervisor.Count);
Assert.AreEqual(emp.EmployeeID.Value, supv.Supervisor.Value);
}
}
finally
{
// Clean up
emp = new Employee();
emp.es.Connection.Name = "ForeignKeyTest";
if (emp.LoadByPrimaryKey(empKey))
{
EmployeeCollection empColl = emp.EmployeeCollectionBySupervisor;
empColl.MarkAllAsDeleted();
emp.MarkAsDeleted();
emp.Save();
}
}
}
public void SaveAlternateConnection()
{
Employee emp = new Employee();
int empId = -1;
try
{
using (esTransactionScope scope = new esTransactionScope())
{
emp = new Employee();
emp.es.Connection.Name = "ForeignKeyTest";
emp.LastName = "McTest";
emp.FirstName = "Testy";
emp.Age = 30;
emp.Save();
empId = emp.EmployeeID.Value;
emp = new Employee();
emp.es.Connection.Name = "ForeignKeyTest";
Assert.IsTrue(emp.LoadByPrimaryKey(empId));
Assert.AreEqual(30, emp.Age.Value);
}
}
finally
{
// Clean up
emp = new Employee();
emp.es.Connection.Name = "ForeignKeyTest";
if (emp.LoadByPrimaryKey(empId))
{
emp.MarkAsDeleted();
emp.Save();
}
}
}
public void ErrorsWithContinueTrueNoFailures()
{
EmployeeCollection coll = new EmployeeCollection();
coll.es.Connection.Name = "ForeignKeyTest";
coll.LoadAll();
Assert.AreEqual(5, coll.Count, "Initial Load");
using (esTransactionScope scope = new esTransactionScope())
{
// INSERT will succeed
Employee emp5 = coll.AddNew();
emp5.FirstName = "First5";
emp5.LastName = "Last5";
coll.Save(true);
Assert.AreEqual(0, coll.Errors.Count(), "ErrorsCount");
Assert.IsNull(coll.es.DeletedEntities, "DeletedErrors");
// Confirm only 1 row got INSERTed
EmployeeCollection coll2 = new EmployeeCollection();
coll2.es.Connection.Name = "ForeignKeyTest";
coll2.LoadAll();
Assert.AreEqual(6, coll2.Count, "Load");
// Confirm auto-incremeting PKs are brought back
// and delete the successfully inserted row
int emp5Id = emp5.EmployeeID.Value;
Employee emp5Ins = new Employee();
emp5Ins.es.Connection.Name = "ForeignKeyTest";
Assert.IsTrue(emp5Ins.LoadByPrimaryKey(emp5Id), "LoadByPK2");
emp5Ins.MarkAsDeleted();
emp5Ins.Save();
}
}
public void ErrorsWithContinueTrueDel()
{
EmployeeCollection coll = new EmployeeCollection();
coll.es.Connection.Name = "ForeignKeyTest";
coll.LoadAll();
Assert.AreEqual(5, coll.Count, "Initial Load");
using (esTransactionScope scope = new esTransactionScope())
{
// SQLite does not enforce FK constraints
// So force a concurrency failure instead
if (coll.es.Connection.ProviderSignature.DataProviderName
== "EntitySpaces.SQLiteProvider")
{
Employee empConcurr = new Employee();
empConcurr.es.Connection.Name = "ForeignKeyTest";
empConcurr.LoadByPrimaryKey(2);
empConcurr.MarkAsDeleted();
empConcurr.Save();
}
// DELETE will fail - FK constraint
Employee emp4 = coll.FindByPrimaryKey(2);
emp4.MarkAsDeleted();
coll.Save(true);
Assert.AreEqual(1, coll.Errors.Count(), "ErrorsCount");
Assert.IsNotNull(coll.es.DeletedEntities, "DeletedErrors");
foreach (Employee e in coll.Errors)
{
Assert.IsTrue(e.es.RowError.Length > 0);
}
if (coll.es.Connection.ProviderSignature.DataProviderName
!= "EntitySpaces.SQLiteProvider")
{
// Confirm same table row count
EmployeeCollection coll2 = new EmployeeCollection();
coll2.es.Connection.Name = "ForeignKeyTest";
coll2.LoadAll();
Assert.AreEqual(5, coll2.Count, "Load");
// Confirm nothing got DELETEd
Employee empDel = new Employee();
empDel.es.Connection.Name = "ForeignKeyTest";
Assert.IsTrue(empDel.LoadByPrimaryKey(2), "Delete");
}
}
}
public void ErrorsWithContinueTrueInsUpdDel()
{
EmployeeCollection coll = new EmployeeCollection();
coll.es.Connection.Name = "ForeignKeyTest";
coll.LoadAll();
Assert.AreEqual(5, coll.Count, "Initial Load");
using (esTransactionScope scope = new esTransactionScope())
{
// SQLite does not enforce FK constraints
// So force a concurrency failure instead
if (coll.es.Connection.ProviderSignature.DataProviderName
== "EntitySpaces.SQLiteProvider")
{
Employee empConcurr = new Employee();
empConcurr.es.Connection.Name = "ForeignKeyTest";
empConcurr.LoadByPrimaryKey(2);
empConcurr.MarkAsDeleted();
empConcurr.Save();
}
// INSERT will succeed
Employee emp1 = coll.AddNew();
emp1.FirstName = "First1";
emp1.LastName = "Last1";
// INSERT will fail - LastName required
Employee emp2 = coll.AddNew();
// UPDATE will fail - LastName required
Employee emp3 = coll.FindByPrimaryKey(1);
emp3.LastName = null;
// DELETE will fail - FK constraint
Employee emp4 = coll.FindByPrimaryKey(2);
emp4.MarkAsDeleted();
// INSERT will succeed
Employee emp5 = coll.AddNew();
emp5.FirstName = "First5";
emp5.LastName = "Last5";
coll.Save(true);
Assert.AreEqual(3, coll.Errors.Count(), "ErrorsCount");
Assert.IsNotNull(coll.es.DeletedEntities, "DeletedErrors");
foreach (Employee e in coll.Errors)
{
Assert.IsTrue(e.es.RowError.Length > 0);
}
// Confirm only 2 rows got INSERTed
EmployeeCollection coll2 = new EmployeeCollection();
coll2.es.Connection.Name = "ForeignKeyTest";
coll2.LoadAll();
if (coll.es.Connection.ProviderSignature.DataProviderName
== "EntitySpaces.SQLiteProvider")
{
Assert.AreEqual(6, coll2.Count, "Load");
}
else
{
Assert.AreEqual(7, coll2.Count, "Load");
// Confirm nothing got DELETEd
Employee empDel = new Employee();
empDel.es.Connection.Name = "ForeignKeyTest";
Assert.IsTrue(empDel.LoadByPrimaryKey(2), "Delete");
}
// Confirm nothing got UPDATEd
Employee empUpd = new Employee();
empUpd.es.Connection.Name = "ForeignKeyTest";
Assert.IsTrue(empUpd.LoadByPrimaryKey(1));
Assert.AreEqual("Smith", empUpd.LastName, "Update");
// Confirm auto-incremeting PKs are brought back
// and delete the 2 successfully inserted rows
int emp1Id = emp1.EmployeeID.Value;
Employee emp1Ins = new Employee();
emp1Ins.es.Connection.Name = "ForeignKeyTest";
Assert.IsTrue(emp1Ins.LoadByPrimaryKey(emp1Id), "LoadByPK1");
emp1Ins.MarkAsDeleted();
emp1Ins.Save();
int emp5Id = emp5.EmployeeID.Value;
Employee emp5Ins = new Employee();
emp5Ins.es.Connection.Name = "ForeignKeyTest";
Assert.IsTrue(emp5Ins.LoadByPrimaryKey(emp5Id), "LoadByPK2");
emp5Ins.MarkAsDeleted();
emp5Ins.Save();
}
}
public void CollectionMixedInsUpdDel()
{
using (esTransactionScope scope = new esTransactionScope())
{
// Setup
Employee newEmp = new Employee();
newEmp.es.Connection.Name = "ForeignKeyTest";
newEmp.LastName = "new";
newEmp.FirstName = "new";
newEmp.Save();
EmployeeCollection coll = new EmployeeCollection();
coll.es.Connection.Name = "ForeignKeyTest";
coll.LoadAll();
Assert.AreEqual(6, coll.Count, "Initial Load");
// Insert
Employee emp1 = coll.AddNew();
emp1.FirstName = "emp1";
emp1.LastName = "emp1";
// Update
Employee emp3 = coll.FindByPrimaryKey(1);
emp3.LastName = "emp3";
// Delete
Employee emp4 = coll.FindByPrimaryKey(newEmp.EmployeeID.Value);
emp4.MarkAsDeleted();
coll.Save();
// Confirm INSERT
Employee empIns = new Employee();
empIns.es.Connection.Name = "ForeignKeyTest";
Assert.IsTrue(empIns.LoadByPrimaryKey(emp1.EmployeeID.Value), "Insert");
// Confirm UPDATE
Employee empUpd = new Employee();
empUpd.es.Connection.Name = "ForeignKeyTest";
Assert.IsTrue(empUpd.LoadByPrimaryKey(1), "Update");
Assert.AreEqual("emp3", empUpd.LastName, "UpdateValue");
// Confirm DELETE
Employee empDel = new Employee();
empDel.es.Connection.Name = "ForeignKeyTest";
Assert.IsFalse(empDel.LoadByPrimaryKey(newEmp.EmployeeID.Value), "Delete");
}
}
