public void Test_07_CreateTable() { DBQuery create = DBQuery.Create.Table("dbo", "MoreCategories") .Add("mc_id", DbType.Int32, DBColumnFlags.AutoAssign) .Add("mc_name", DbType.String, 50) .Add("mc_desc", DbType.String, 255, DBColumnFlags.Nullable) .Add("mc_ot_id", DbType.Int32, DBColumnFlags.Nullable) .Add("mc_ot_name", DbType.AnsiString, 50, DBColumnFlags.Nullable) .Constraints( DBConstraint.PrimaryKey("pk_Id_and_name").Columns("mc_id", "mc_name") ); this.OutputAndValidate(create, " Create Table with primary key constraint"); }
public void Test_08_CreateTableWithForeignKey() { DBQuery create = DBQuery.Create.Table("dbo", "MoreCategories") .Add("mc_id", DbType.Int32, DBColumnFlags.AutoAssign | DBColumnFlags.PrimaryKey) .Add("mc_name", DbType.String, 50) .Add("mc_desc", DbType.String, 255, DBColumnFlags.Nullable) .Add("mc_ot_id", DbType.Int32, DBColumnFlags.Nullable) .Add("mc_ot_name", DbType.AnsiString, 50, DBColumnFlags.Nullable) .Constraints( DBConstraint.ForeignKey("fk_to_OtherTable").Columns("mc_ot_id", "mc_ot_name") .References("OtherTable").Columns("ot_id", "ot_name") ); this.OutputAndValidate(create, " Create Table with foreign key constraint"); }
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"); } }