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");
}
}
