/// <summary>
///
/// </summary>
/// <param name="clientid"></param>
/// <param name="componentlookupid"></param>
/// <param name="categoryid"></param>
/// <returns></returns>
public bool RemoveAllLookupItems(string clientid,
string componentlookupid
)
{
DBDatabase db;
db = base.Database;
DBConst dbcomponentlookupid = DBConst.String(componentlookupid);
DBConst dbclientid = DBConst.String(clientid);
DBComparison component = DBComparison.Equal(DBField.Field(TzAccount.ComponentLookUpItem.ComponentLookupItemID.Name), dbcomponentlookupid);
DBComparison client = DBComparison.Equal(DBField.Field(TzAccount.ComponentLookUpItem.ClientID.Name), dbclientid);
DBQuery del = DBQuery.DeleteFrom(TzAccount.ComponentLookUpItem.Table)
.WhereAll(component, client);
int i = db.ExecuteNonQuery(del);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool ClearComponent()
{
DBQuery deleteView = DBQuery.DeleteFrom(TalentozSchema.Table);
db.ExecuteNonQuery(deleteView);
deleteView = DBQuery.DeleteFrom(TalentozSchemaInfo.Table);
db.ExecuteNonQuery(deleteView);
deleteView = DBQuery.DeleteFrom(TalentozSchemaClientInfo.Table);
db.ExecuteNonQuery(deleteView);
deleteView = DBQuery.DeleteFrom(TalentozView.Table);
db.ExecuteNonQuery(deleteView);
deleteView = DBQuery.DeleteFrom(TalentozViewSchema.Table);
db.ExecuteNonQuery(deleteView);
deleteView = DBQuery.DeleteFrom(TalentozViewSchemaRelation.Table);
db.ExecuteNonQuery(deleteView);
deleteView = DBQuery.DeleteFrom(TalentozTemplate.Table);
db.ExecuteNonQuery(deleteView);
deleteView = DBQuery.DeleteFrom(TalentozImportLog.Table);
db.ExecuteNonQuery(deleteView);
return(true);
}
/// <summary>
///
/// </summary>
/// <param name="clientid"></param>
/// <param name="componentitemrelationid"></param>
/// <returns></returns>
public bool RemoveItemRelation(string clientid,
string componentitemrelationid)
{
DBDatabase db;
db = base.Database;
DBConst dbclientID = DBConst.String(clientid);
DBConst dbcomponentitemrelationid = DBConst.String(componentitemrelationid);
DBComparison Client = DBComparison.Equal(DBField.Field(TzAccount.ComponentModalRelation.ClientID.Name), dbclientID);
DBComparison compModalID = DBComparison.Equal(DBField.Field(TzAccount.ComponentModalRelation.ComponentModalRelationID.Name), dbcomponentitemrelationid);
DBQuery del = DBQuery.DeleteFrom(TzAccount.ComponentModalRelation.Table)
.WhereAll(Client, compModalID);
int i = db.ExecuteNonQuery(del);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool RemoveAppComponent(string clientID, string appid, string elementid)
{
DBDatabase db;
db = base.Database;
DBConst dbClientID = DBConst.String(clientID);
DBConst dbappid = DBConst.String(appid);
DBConst dbelement = DBConst.String(elementid);
DBComparison client = DBComparison.Equal(DBField.Field(TzAccount.AppElements.ClientID.Name), dbClientID);
DBComparison app = DBComparison.Equal(DBField.Field(TzAccount.AppElements.AppID.Name), dbappid);
DBComparison element = DBComparison.Equal(DBField.Field(TzAccount.AppElements.ElementID.Name), dbelement);
DBQuery del = DBQuery.DeleteFrom(TzAccount.AppElements.Table)
.WhereAll(client, app, element);
int i = db.ExecuteNonQuery(del);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
public void _08_DeleteExistsXmlTest()
{
DBQuery q = DBQuery.DeleteFrom("Customers")
.WhereField("customer_id", Compare.In, DBValueGroup.All(1, 2, 3, 4, 5, 6))
.OrWhereField("picture", Compare.Is, DBConst.Null());
q = SerializeAndDeserialzeQueryToMatch(q, "Exists with Or Delete");
}
public bool RemoveView(string ViewID)
{
DBComparison dbView = DBComparison.Compare(DBField.Field(TalentozView.ViewID.Name),
Compare.Equals, DBConst.String(ViewID));
DBQuery deleteView = DBQuery.DeleteFrom(TalentozView.Table).Where(dbView);
db.ExecuteNonQuery(deleteView);
return(true);
}
public void Test_06_DeleteWithoutPictureAndCast()
{
DBQuery del = DBQuery.DeleteFrom("Categories")
.WhereField("Picture", Compare.Is, DBConst.Null())
.OrWhereField("CategoryID", Compare.GreaterThan, (DBConst)8);
this.OutputSql(del, "Insert with Sql Select statement");
this.OutputXML(del, "Insert with Sql Select statement");
}
public void Test_06_DeleteWithoutPictureAndCast()
{
DBQuery del = DBQuery.DeleteFrom("Categories")
.WhereField("Picture", Compare.Is, DBConst.Null())
.OrWhereField("CategoryID", Compare.GreaterThan, (DBConst)8);
string sql = this.OutputSql(del, " Delete with Null and Cast statement", true);
string xml = this.OutputXML(del, " Delete with Null and Cast statement");
ValidateXml(xml, sql, " Delete with Null and Cast statement");
}
public void _09_DeleteParameterXmlTest()
{
DBParam p1 = DBParam.ParamWithValue("first", 1);
DBParam p2 = DBParam.ParamWithValue("second", 2);
DBParam p3 = DBParam.ParamWithValue("third", 3);
DBQuery q = DBQuery.DeleteFrom("Customers")
.WhereField("customer_id", Compare.In, DBValueGroup.All(p1, p2, p3))
.OrWhereField("picture", Compare.Is, DBConst.Null());
q = SerializeAndDeserialzeQueryToMatch(q, "Parameterized Delete");
}
protected internal bool ClearTemplate(int clientID)
{
DBComparison client = DBComparison.Equal(DBField.Field(TalentozSchemaClientInfo.ClientID.Name), DBConst.Int32(clientID));
DBQuery deleteView = DBQuery.DeleteFrom(TalentozTemplate.Table).WhereAll(client);
db.ExecuteNonQuery(deleteView);
deleteView = DBQuery.DeleteFrom(TalentozImportLog.Table).WhereAll(client);;
db.ExecuteNonQuery(deleteView);
return(true);
}
protected internal bool RemoveTemplateByName(string name, int cid)
{
DBComparison client = DBComparison.Equal(DBField.Field(TalentozTemplate.ClientID.Name), DBConst.Int32(cid));
DBComparison dbTemplate = DBComparison.Equal(DBField.Field(TalentozTemplate.Name.Name), DBConst.String(name));
DBQuery delete = DBQuery.DeleteFrom(TalentozTemplate.Table).WhereAll(dbTemplate, client);
if (db.ExecuteNonQuery(delete) > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool RemoveAllPrivileges(string groupID, string clientid)
{
DBComparison SecurityGroupID = DBComparison.Equal(DBField.Field(TzAccount.SecurityPrivilege.SecurityGroupID.Name), DBConst.String(groupID));
DBComparison Client = DBComparison.Equal(DBField.Field(TzAccount.SecurityPrivilege.ClientID.Name), DBConst.String(clientid));
DBQuery del = DBQuery.DeleteFrom(TzAccount.SecurityPrivilege.Table)
.WhereAll(SecurityGroupID, Client);
int i = db.ExecuteNonQuery(del);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
public static bool RemoveContext(string importID, string conn)
{
DataBase db = new DataBase();
db.InitDbs(conn);
DBQuery select;
DBComparison dbcompo = DBComparison.Equal(DBField.Field("importID"), DBConst.String(importID));
string[] a = { "importID", "ImportSchema", "ActionBy", "Step" };
select = DBQuery.DeleteFrom("tz_import_log").Where(dbcompo);
if (db.Database.ExecuteNonQuery(select) > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
///
/// </summary>
/// <param name="UserID"></param>
/// <returns></returns>
public bool Remove(string UserID)
{
DBDatabase db;
db = base.Database;
DBConst DBUserID = DBConst.String(UserID);
DBQuery del = DBQuery.DeleteFrom(TzAccount.User.Table)
.WhereFieldEquals(TzAccount.User.UserID.Name, DBUserID);
int i = db.ExecuteNonQuery(del);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
///
/// </summary>
/// <param name="tableid"></param>
/// <returns></returns>
public bool Remove(string tableid)
{
DBDatabase db;
db = base.Database;
DBConst dbtableid = DBConst.String(tableid);
DBQuery del = DBQuery.DeleteFrom(TzAccount.Tables.Table)
.WhereFieldEquals(TzAccount.Tables.TableID.Name, dbtableid);
int i = db.ExecuteNonQuery(del);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Remove(string clientID, string serverID)
{
DBDatabase db;
db = base.Database;
DBComparison client = DBComparison.Equal(DBField.Field(TzAccount.ClientServer.ClientID.Name), DBConst.String(clientID));
DBComparison server = DBComparison.Equal(DBField.Field(TzAccount.ClientServer.ServerID.Name), DBConst.String(serverID));
DBQuery del = DBQuery.DeleteFrom(TzAccount.ClientServer.Table)
.WhereAll(client, server);
int i = db.ExecuteNonQuery(del);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
///
/// </summary>
/// <param name="scriptID"></param>
/// <returns></returns>
public bool Remove(string scriptID)
{
DBDatabase db;
db = base.Database;
DBComparison dbscriptID = DBComparison.Equal(DBField.Field(TzAccount.DataScript.ScriptID.Name), DBConst.String(scriptID));
DBQuery del = DBQuery.DeleteFrom(TzAccount.DataScript.Table)
.WhereAll(dbscriptID);
int i = db.ExecuteNonQuery(del);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Remove(string clientID,
string formid)
{
DBDatabase db;
db = base.Database;
DBConst dbClientID = DBConst.String(clientID);
DBConst dbform = DBConst.String(formid);
DBComparison client = DBComparison.Equal(DBField.Field(TzAccount.Form.ClientID.Name), dbClientID);
DBComparison component = DBComparison.Equal(DBField.Field(TzAccount.Form.ComponentID.Name), dbform);
DBQuery del = DBQuery.DeleteFrom(TzAccount.Form.Table)
.WhereAll(client, component);
int i = db.ExecuteNonQuery(del);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool RemoveAll(string componentID)
{
DBDatabase db;
db = base.Database;
//DBConst dbfieldid = DBConst.String(fieldid);
DBConst dbcomponentID = DBConst.String(componentID);
//DBComparison field = DBComparison.Equal(DBField.Field(TzAccount.ComponentAttribute.FieldID.Name), dbfieldid);
DBComparison component = DBComparison.Equal(DBField.Field(TzAccount.ComponentAttribute.ComponentID.Name), dbcomponentID);
DBQuery del = DBQuery.DeleteFrom(TzAccount.ComponentAttribute.Table)
.WhereAll(component);
int i = db.ExecuteNonQuery(del);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
///
/// </summary>
/// <param name="formid"></param>
/// <param name="formfieldid"></param>
/// <param name="clientid"></param>
/// <returns></returns>
public bool Remove(string formid,
string formfieldid,
string clientid)
{
DBDatabase db;
db = base.Database;
DBComparison client = DBComparison.Equal(DBField.Field(TzAccount.FormFields.ClientID.Name), DBConst.String(clientid));
DBComparison form = DBComparison.Equal(DBField.Field(TzAccount.FormFields.FormID.Name), DBConst.String(formid));
DBComparison formfield = DBComparison.Equal(DBField.Field(TzAccount.FormFields.FieldID.Name), DBConst.String(formfieldid));
DBQuery del = DBQuery.DeleteFrom(TzAccount.FormFields.Table)
.WhereAll(client, form, formfield);
int i = db.ExecuteNonQuery(del);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
public void _10_CreateTableWithForeignKeys()
{
DBDatabase db = ConnectDb();
//Create the persons table
DBQuery createPersons = DBQuery.Create.Table("DSQL_Persons")
.Add("Person_ID", DbType.Int32, DBColumnFlags.AutoAssign | DBColumnFlags.PrimaryKey)
.Add("Person_Name", DbType.String, 50);
TestContext.WriteLine(createPersons.ToSQLString(db));
db.ExecuteNonQuery(createPersons);
//Create the orders table
DBQuery createOrders = DBQuery.Create.Table("DSQL_Orders")
.Add("Order_ID", DbType.Int32, DBColumnFlags.AutoAssign | DBColumnFlags.PrimaryKey)
.Add("Ordered_By", DbType.Int32)
.Add("Ordered_Date", DbType.DateTime)
.Add("Signed_By", DbType.Int32, DBColumnFlags.Nullable)
.Constraints(
DBConstraint.ForeignKey().Column("Ordered_By") //unnamed foreign key first
.References("DSQL_Persons").Column("Person_ID"),
DBConstraint.ForeignKey("Orders_Signed_By_2_Persons_PersonID").Column("Signed_By")
.References("DSQL_Persons").Column("Person_ID")
.OnDelete(DBFKAction.Cascade)
.OnUpdate(DBFKAction.Cascade)
);
//Execute the Create Table statements
TestContext.WriteLine(createOrders.ToSQLString(db));
db.ExecuteNonQuery(createOrders);
try
{
bool scripts = db.GetProperties().CheckSupports(DBSchemaTypes.CommandScripts);
DBParam pname = DBParam.Param("name", DbType.String);
DBScript insertperson = DBQuery.Script(
DBQuery.InsertInto("DSQL_Persons").Field("Person_Name").Value(pname),
DBQuery.Select(DBFunction.LastID())
);
//Insert one row into the persons table
pname.Value = "First Person";
TestContext.WriteLine(insertperson.ToSQLString(db));
int firstpid = Convert.ToInt32(this.ExecuteScalarScript(db, insertperson, scripts));
//And another row
pname.Value = "Second Person";
int secondpid = Convert.ToInt32(this.ExecuteScalarScript(db, insertperson, scripts));
//Create an order with orderedby = firstpid and signedby = secondpid
DBParam orderedby = DBParam.ParamWithValue(DbType.Int32, firstpid);
DBParam signedby = DBParam.ParamWithValue(DbType.Int32, secondpid);
DBScript insertorder = DBQuery.Script(
DBQuery.InsertInto("DSQL_Orders")
.Field("Ordered_By").Value(orderedby)
.Field("Ordered_Date").Value(DBFunction.GetDate())
.Field("Signed_By").Value(signedby),
DBQuery.Select(DBFunction.LastID())
);
TestContext.WriteLine(insertorder.ToSQLString(db));
int orderid = Convert.ToInt32(this.ExecuteScalarScript(db, insertorder, scripts));
//Now try to create an order that breaks referential integrity
orderedby.Value = -100;
try
{
orderid = Convert.ToInt32(db.ExecuteScalar(insertorder));
throw new InvalidOperationException("We should not be able to insert these rows. FAILED test");
}
catch (InvalidOperationException)
{
throw;
}
catch (Exception ex)
{
TestContext.WriteLine("Sucessfully caught an exception that breaks referential integrity");
}
//Finally check the cascading deletes
//check the Orders table count
//delete a person row
//ensure that the corresponding row for Signed By FK was deleted
DBQuery getcount = DBQuery.SelectCount().From("DSQL_ORDERS");
int ordercount = Convert.ToInt32(
db.ExecuteScalar(getcount));
DBQuery del = DBQuery.DeleteFrom("DSQL_Persons")
.WhereField("Person_ID", Compare.Equals, DBParam.ParamWithValue(secondpid));
int removed = db.ExecuteNonQuery(del);
Assert.AreEqual(1, removed);
TestContext.WriteLine("Removed a single row from the persons table");
int newordercount = Convert.ToInt32(db.ExecuteScalar(getcount));
//Make sure the orders row has been deleted
Assert.AreEqual(newordercount, ordercount - 1);
TestContext.WriteLine("Validated that the corresponding row in the orders table has been removed too");
}
finally
{
//Clean up tables in order
db.ExecuteNonQuery(DBQuery.Drop.Table("DSQL_Orders"));
db.ExecuteNonQuery(DBQuery.Drop.Table("DSQL_Persons"));
TestContext.WriteLine("Database has been cleaned up");
}
}
public void _07_SimpleDeleteXmlTest()
{
DBQuery q = DBQuery.DeleteFrom("Customers").WhereField("customer_id", Compare.Equals, DBConst.Int32(2));
q = SerializeAndDeserialzeQueryToMatch(q, "Simple Delete");
}