public void _07_DropTableIfExists()
{
DBDatabase db = ConnectDb();
//Check for existance and drop
DBQuery drop = DBQuery.Drop.Table(TableName).IfExists();
TestContext.WriteLine(drop.ToSQLString(db));
db.ExecuteNonQuery(drop);
TestContext.WriteLine("Dropped table {0} if it existed\r\n", TableName);
//Now create the table - if it still exists an error will be thrown
CreateCustomTable(db);
//Add one row
InsertARowIntoCustomTable(db, "First", "Second");
TestContext.WriteLine("Added 1 row to an existing table\r\n");
//Get the number of rows (should be 1)
DBQuery countSql = DBQuery.SelectCount().From(TableName);
int count = Convert.ToInt32(db.ExecuteScalar(countSql));
Assert.AreEqual(1, count);
TestContext.WriteLine("Got the row count of {0} for table {1}\r\n", count, TableName);
//Insert another row based on the table existing (which it should)
DBParam p1 = DBParam.ParamWithValue(DbType.AnsiString, "Existing");
DBParam p2 = DBParam.ParamWithValue(DbType.String, "Description");
DBQuery insert = DBQuery.InsertInto(TableName).Fields("ColB", "ColC").Values(p1, p2);
TestContext.WriteLine(insert.ToSQLString(db));
db.ExecuteNonQuery(insert);
//Re-Check the count and make sure one has been added
int newcount = Convert.ToInt32(db.ExecuteScalar(countSql));
Assert.AreEqual(count + 1, newcount);
TestContext.WriteLine("Added 1 row to an existing table\r\n");
//drop the table - checking for it first even though we know it exists
db.ExecuteNonQuery(drop);
TestContext.WriteLine("Checked existance and dropped " + TableName + " table\r\n");
try
{
db.ExecuteNonQuery(DBQuery.InsertInto(TableName).Fields("ColB", "ColC").Values(p1, p2));
throw new InvalidOperationException("The insert succeeded on a table that should have been dropped");
}
catch (InvalidOperationException)
{
throw;
}
catch (Exception)
{
TestContext.WriteLine("Successfully caught exception for row insert");
}
}
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");
}
public void _08_CreateTableIfNotExists()
{
DBDatabase db = ConnectDb();
//Make sure the table does not exist first
DBQuery dropit = DBQuery.Drop.Table(TableName).IfExists();
db.ExecuteNonQuery(dropit);
DBParam p1 = DBParam.ParamWithValue(DbType.AnsiString, "Existing");
DBParam p2 = DBParam.ParamWithValue(DbType.String, "Description");
DBQuery insert = DBQuery.InsertInto(TableName).Fields("ColB", "ColC").Values(p1, p2);
try
{
db.ExecuteNonQuery(insert);
throw new InvalidOperationException("The insert succeeded on a table that should have been dropped");
}
catch (InvalidOperationException)
{
throw;
}
catch (Exception)
{
TestContext.WriteLine("Successfully caught exception for row insert. We know the table does not exist");
}
//This should now not exist and be created
DBQuery ifnotexistsCreate = GetCustomTableQuery().IfNotExists();
TestContext.WriteLine(ifnotexistsCreate.ToSQLString(db));
db.ExecuteNonQuery(ifnotexistsCreate);
//Now we should be able to insert a row
db.ExecuteNonQuery(insert);
int count = Convert.ToInt32(db.ExecuteScalar(DBQuery.SelectCount().From(TableName)));
//Check that the rows are there
Assert.AreEqual(1, count);
//Check that it does not try and create again
db.ExecuteNonQuery(ifnotexistsCreate);
//Validate that it has not just succeeded and re-created an empty table
count = Convert.ToInt32(db.ExecuteScalar(DBQuery.SelectCount().From(TableName)));
//Check that the rows are there
Assert.AreEqual(1, count);
//Finally just drop the table
db.ExecuteNonQuery(dropit);
}
/// <summary>
/// Returns a statement that will insert a row into the custom table - must have been created before
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
private DBQuery InsertARowIntoCustomTable(DBDatabase db, object first, object second)
{
DBParam p1 = DBParam.ParamWithValue(DbType.AnsiString, first);
DBParam p2 = DBParam.ParamWithValue(DbType.String, second);
DBQuery ins = DBQuery.InsertInto(TableName)
.Fields(TblCol2, TblCol3)
.Values(p1, p2);
int count = db.ExecuteNonQuery(ins);
return(ins);
}
public void _11_UpdateSubSelectAndParameterXmlTest()
{
DBParam cid = DBParam.ParamWithValue("cid", 10);
DBQuery thirtyDaysOrders = DBQuery.Select()
.Sum(DBField.Field("order_value")).From("ORDER").As("O")
.WhereField("order_date", Compare.GreaterThan, DBConst.DateTime(DateTime.Today.AddDays(-30)))
.AndWhere("customer_id", Compare.Equals, cid);
DBQuery q = DBQuery.Update("CUSTOMERS").Set("customer_name", DBConst.String("new name"))
.AndSet("customer_address", DBConst.String("new address"))
.AndSet("order_value", thirtyDaysOrders)
.WhereField("customer_id", Compare.Equals, cid);
q = SerializeAndDeserialzeQueryToMatch(q, "Sub Select Update");
}
public void Test_04_InsertScript()
{
byte[] imgdata = this.GetLocalImage("bomb.gif");
DBParam cName = DBParam.ParamWithValue("name", DbType.String, (object)"newType_1");
DBParam cDesc = DBParam.ParamWithValue("desc", DbType.String, (object)"newDescrption_1");
DBParam cPic = DBParam.ParamWithValue("pic", DbType.Binary, (object)imgdata);
DBInsertQuery ins = DBQuery.InsertInto("Categories")
.Fields("CategoryName", "Description", "Picture")
.Values(cName, cDesc, cPic);
DBSelectQuery sel = DBQuery.Select(DBFunction.LastID());
DBScript script = DBQuery.Begin(ins)
.Then(sel)
.End();
this.OutputSql(script, "Insert and Return last ID script");
this.OutputXML(script, "Insert and Return last ID script");
}
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");
}
}
