public void Test_10_CreateView() { DBParam startswith = DBParam.Param("startswith", DbType.AnsiString, 50); DBQuery create = DBQuery.Create.View("dbo", "CategoriesByName") .As( DBQuery.Select() .Field("mc_id").As("id") .Field("mc_name").As("name") .Field("mc_desc").As("desc") .From("dbo", "MoreCategories") .Where("mc_name", Compare.Like, startswith) .OrderBy("mc_name") ); this.OutputAndValidate(create, " Create a view with a select statement"); }
public void Test_11_CreateProcedure() { DBParam custid = DBParam.Param("cust", DbType.Int32); DBParam orderid = DBParam.Param("id", DbType.Int32, ParameterDirection.Input); DBParam name = DBParam.Param("name", DbType.String, 50, ParameterDirection.Input); DBQuery create = DBQuery.Create.StoredProcedure("dbo", "ChangeOrderItemCustomer") .WithParams(orderid, name) .As( DBQuery.Declare(custid), DBQuery.Select().TopN(1) .Field(DBAssign.Set(custid, DBField.Field("customerid"))) .From("Orders") .WhereFieldEquals("order_id", orderid), DBQuery.Update("OrderItems") .Set("customerid", custid) .Set("customername", name) .WhereFieldEquals("orderid", orderid) ); this.OutputAndValidate(create, " Create Procedure ChangeOrderItemCustomer"); }
public void _12_CreateSProc() { DBDatabase db = ConnectDb(); this.CreateCustomTable(db); try { DBParam name = DBParam.Param("name", DbType.String, 50); DBParam desc = DBParam.Param("desc", DbType.String, 150); DBQuery ins = DBQuery.InsertInto(TableName).Fields(TblCol2, TblCol3).Values(name, desc); char one = 'A'; char two = 'a'; TestContext.WriteLine(ins.ToSQLString(db)); for (int i = 0; i < 26; i++) { int c = (int)one; c += i; string offset = ((char)c).ToString() + two.ToString(); name.Value = offset; desc.Value = "Description of " + offset; db.ExecuteNonQuery(ins); } int count = Convert.ToInt32(db.ExecuteScalar(DBQuery.SelectCount().From(TableName))); Assert.AreEqual(26, count); DBQuery q = DBQuery.Create.StoredProcedure("finditemsincol2") .WithParam("p1", DbType.String, 50, ParameterDirection.Input) .As( DBQuery.SelectAll().From(TableName) .WhereField(TblCol2, Compare.Like, DBParam.Param("p1")) ); TestContext.WriteLine("Execute Procedure: " + q.ToSQLString(db)); db.ExecuteNonQuery(q); TestContext.WriteLine("Created the new stored procedure"); DBQuery exec = DBQuery.Exec("finditemsincol2").WithParamValue("p1", DbType.String, 50, "A%"); count = 0; TestContext.WriteLine(exec.ToSQLString(db)); db.ExecuteRead(exec, reader => { while (reader.Read()) { count++; Assert.IsTrue(reader[TblCol2].ToString().StartsWith("A")); } TestContext.WriteLine("Executed the stored procedure and read '" + count.ToString() + "' rows"); }); Assert.AreEqual(1, count); } finally { try { DBQuery drop = DBQuery.Drop.StoredProcedure("finditemsincol2"); db.ExecuteNonQuery(drop); TestContext.WriteLine("Sucessfully dropped the stored procedure"); } catch { TestContext.WriteLine("DROP PROCEDURE failed"); } this.DropCustomTable(db); } }
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"); } }