Ejemplo n.º 1
0
        /// <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);
            }
        }
Ejemplo n.º 2
0
        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);
        }
Ejemplo n.º 3
0
        /// <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);
            }
        }
Ejemplo n.º 4
0
        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);
            }
        }
Ejemplo n.º 5
0
        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);
        }
Ejemplo n.º 7
0
        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");
        }
Ejemplo n.º 8
0
        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");
        }
Ejemplo n.º 9
0
        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");
        }
Ejemplo n.º 10
0
        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);
        }
Ejemplo n.º 11
0
        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);
            }
        }
Ejemplo n.º 12
0
        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);
            }
        }
Ejemplo n.º 13
0
        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);
            }
        }
Ejemplo n.º 14
0
        /// <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);
            }
        }
Ejemplo n.º 15
0
        /// <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);
            }
        }
Ejemplo n.º 16
0
        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);
            }
        }
Ejemplo n.º 17
0
        /// <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);
            }
        }
Ejemplo n.º 18
0
        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);
            }
        }
Ejemplo n.º 19
0
        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);
            }
        }
Ejemplo n.º 20
0
        /// <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);
            }
        }
Ejemplo n.º 21
0
        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");
            }
        }
Ejemplo n.º 22
0
        public void _07_SimpleDeleteXmlTest()
        {
            DBQuery q = DBQuery.DeleteFrom("Customers").WhereField("customer_id", Compare.Equals, DBConst.Int32(2));

            q = SerializeAndDeserialzeQueryToMatch(q, "Simple Delete");
        }