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_CreateTableWithIndex()
{
DBDatabase db = ConnectDb();
try
{
DBQuery tbl = DBQuery.Create.Table("CustomTable")
.Add("col1", DbType.Int32, DBColumnFlags.PrimaryKey | DBColumnFlags.AutoAssign)
.Add("col2", DbType.String, 50)
.Add("col3", DbType.String, 255);
//create 3 indexes on this table for the other columns
DBQuery idx1 = DBQuery.Create.Index("CT_ColBoth").Unique().On("CustomTable")
.Columns("col1", Order.Ascending, "col2", Order.Ascending);
DBQuery idx2 = DBQuery.Create.Index(true, "CT_Col2").On("CustomTable")
.Add("col2");
DBQuery idx3 = DBQuery.Create.Index("CT_Col1").On("CustomTable")
.Add("col1", Order.Ascending);
if (db.GetProperties().CheckSupports(DBSchemaTypes.CommandScripts))
{
DBScript create = DBQuery.Script(tbl, idx1, idx2, idx3);
TestContext.WriteLine(create.ToSQLString(db));
db.ExecuteNonQuery(create);
}
else //scripts are not supported so we are going to execute individually
{
db.ExecuteNonQuery(tbl);
db.ExecuteNonQuery(idx1);
db.ExecuteNonQuery(idx2);
db.ExecuteNonQuery(idx3);
}
}
finally
{
DBQuery droptbl = DBQuery.Drop.Table("CustomTable");
DBQuery dropidx1 = DBQuery.Drop.Index("CT_ColBoth").On("CustomTable");
DBQuery dropidx2 = DBQuery.Drop.Index("CT_Col2").On("CustomTable");
DBQuery dropidx3 = DBQuery.Drop.Index("CT_Col1").On("CustomTable");
if (db.GetProperties().CheckSupports(DBSchemaTypes.CommandScripts))
{
DBScript dropall = DBScript.Script(dropidx3, dropidx2, dropidx1, droptbl);
TestContext.WriteLine("\r\n" + dropall.ToSQLString(db));
db.ExecuteNonQuery(dropall);
}
else
{
db.ExecuteNonQuery(dropidx1);
db.ExecuteNonQuery(dropidx2);
db.ExecuteNonQuery(dropidx3);
db.ExecuteNonQuery(droptbl);
}
}
}
/// <summary>
///
/// </summary>
/// <param name="clientid"></param>
/// <param name="groupid"></param>
/// <param name="userid"></param>
/// <param name="componentid"></param>
/// <param name="componentype"></param>
/// <param name="add"></param>
/// <param name="edit"></param>
/// <param name="view"></param>
/// <param name="remove"></param>
/// <returns></returns>
public string Save(string clientid,
string groupid,
string componentid,
int componentype,
bool add,
bool edit,
bool view,
bool remove
)
{
string a = Shared.generateID();
var insert = DBQuery.InsertInto(TzAccount.SecurityPrivilege.Table)
.Fields(TzAccount.SecurityPrivilege.PrivilegeID.Name,
TzAccount.SecurityPrivilege.ClientID.Name,
TzAccount.SecurityPrivilege.SecurityGroupID.Name,
//TzAccount.SecurityPrivilege.UserID.Name,
TzAccount.SecurityPrivilege.SecurityComponentID.Name,
TzAccount.SecurityPrivilege.ComponentType.Name,
TzAccount.SecurityPrivilege.Add.Name,
TzAccount.SecurityPrivilege.Edit.Name,
TzAccount.SecurityPrivilege.Remove.Name,
TzAccount.SecurityPrivilege.View.Name
).
Values(DBConst.String(a),
DBConst.String(clientid),
DBConst.String(groupid),
// DBConst.String(userid),
DBConst.String(componentid),
DBConst.Int32(componentype),
DBConst.Const(DbType.Boolean, add),
DBConst.Const(DbType.Boolean, edit),
DBConst.Const(DbType.Boolean, view),
DBConst.Const(DbType.Boolean, remove));
int val = 0;
using (DbTransaction trans = db.BeginTransaction())
{
val = db.ExecuteNonQuery(trans, insert);
trans.Commit();
}
if (val > 0)
{
return(a);
}
else
{
return("");
}
}
public string Save(string fieldName,
int fieldType,
int length,
bool isnull,
bool isPrimarykey,
string tableID)
{
string a = Shared.generateID();
DBConst dbfieldid = DBConst.String(a);
DBConst dbtableID = DBConst.String(tableID);
DBConst dbfieldName = DBConst.String(fieldName);
DBConst dbfieldtype = DBConst.Int32(fieldType);
DBConst dbisnull = DBConst.Const(DbType.Boolean, isnull);
DBConst dbisprimary = DBConst.Const(DbType.Boolean, isPrimarykey);
DBConst dblength = DBConst.Int32(length);
DBQuery insert = DBQuery.InsertInto(TzAccount.Field.Table).Fields(
TzAccount.Field.TableID.Name,
TzAccount.Field.FieldID.Name,
TzAccount.Field.FieldName.Name,
TzAccount.Field.FieldType.Name,
TzAccount.Field.Length.Name,
TzAccount.Field.IsNullable.Name,
TzAccount.Field.ISPrimaryKey.Name
).Values(
dbtableID,
dbfieldid,
dbfieldName,
dbfieldtype,
dblength,
dbisnull,
dbisprimary
);
int val = 0;
using (DbTransaction trans = db.BeginTransaction())
{
val = db.ExecuteNonQuery(trans, insert);
trans.Commit();
}
if (val > 0)
{
return(a);
}
else
{
return("");
}
}
protected internal bool Remove(string template, int cid)
{
DBComparison client = DBComparison.Equal(DBField.Field(TalentozTemplate.ClientID.Name), DBConst.Int32(cid));
DBComparison dbTemplate = DBComparison.Equal(DBField.Field(TalentozTemplate.TemplateID.Name), DBConst.String(template));
DBQuery delete = DBQuery.DeleteFrom(TalentozTemplate.Table).WhereAll(dbTemplate, client);
if (db.ExecuteNonQuery(delete) > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
///
/// </summary>
/// <param name="serverid"></param>
/// <param name="host"></param>
/// <param name="dbname"></param>
/// <param name="userName"></param>
/// <param name="password"></param>
/// <param name="port"></param>
/// <returns></returns>
public bool Update(string serverid, string host,
string dbname,
string userName,
string password,
int port, string serverName
)
{
DBDatabase db;
db = base.Database;
DBConst dbServerID = DBConst.String(serverid);
DBConst dbHostName = DBConst.String(host);
DBConst dbServerName = DBConst.String(serverName);
DBConst dbDBname = DBConst.String(dbname);
DBConst dbPassword = DBConst.String(password);
DBConst dbUserName = DBConst.String(userName);
DBConst dbPort = DBConst.Int32(port);
DBQuery update = DBQuery.Update(TzAccount.Server.Table).Set(
TzAccount.Server.Host.Name, dbHostName).Set(
TzAccount.Server.UserID.Name, dbUserName).Set(
TzAccount.Server.Password.Name, dbPassword).Set(
TzAccount.Server.Port.Name, dbPort).Set(
TzAccount.Server.ServerName.Name, dbServerName).WhereField(TzAccount.Server.ServerID.Name, Compare.Equals, dbServerID);
int i = db.ExecuteNonQuery(update);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
public void _02_JustCreateTheView()
{
DBDatabase db = ConnectDb();
DBQuery create = GetCustomViewQuery();
TestContext.WriteLine(create.ToSQLString(db));
db.ExecuteNonQuery(create);
}
/// <summary>
/// Drops the custom table
/// </summary>
/// <param name="db"></param>
private void DropCustomTable(DBDatabase db)
{
DBQuery drop = DBQuery.Drop.Table(TableName);
TestContext.WriteLine(drop.ToSQLString(db));
int droppedCount = db.ExecuteNonQuery(drop);
TestContext.WriteLine("Table {0} dropped", TableName);
}
public void _03_JustDropTheView()
{
DBDatabase db = ConnectDb();
DBQuery drop = DBQuery.Drop.View(ViewName);
TestContext.WriteLine(drop.ToSQLString(db));
db.ExecuteNonQuery(drop);
}
public void _11_DropTheFKTables()
{
DBDatabase db = ConnectDb();
try
{
db.ExecuteNonQuery(DBQuery.Drop.Table("DSQL_Persons"));
}
catch
{
}
try
{
db.ExecuteNonQuery(DBQuery.Drop.Table("DSQL_Orders"));
}
catch
{
}
}
/// <summary>
///
/// </summary>
/// <param name="host"></param>
/// <param name="dbname"></param>
/// <param name="userName"></param>
/// <param name="password"></param>
/// <param name="port"></param>
/// <returns></returns>
public string Save(string host,
string dbname,
string userName,
string password,
int port
, string serverName
)
{
DBDatabase db;
db = base.Database;
string a = Shared.generateID();
DBConst dbServerID = DBConst.String(a);
DBConst dbHostName = DBConst.String(host);
DBConst dbServerName = DBConst.String(serverName);
DBConst dbDBname = DBConst.String(dbname);
DBConst dbPassword = DBConst.String(password);
DBConst dbUserName = DBConst.String(userName);
DBConst dbPort = DBConst.Int32(port);
DBQuery insert = DBQuery.InsertInto(TzAccount.Server.Table).Fields(
TzAccount.Server.ServerID.Name,
TzAccount.Server.Host.Name,
TzAccount.Server.UserID.Name,
TzAccount.Server.Password.Name,
TzAccount.Server.Port.Name,
TzAccount.Server.DB.Name,
TzAccount.Server.ServerName.Name).Values(
dbServerID,
dbHostName,
dbUserName,
dbPassword,
dbPort,
dbDBname,
dbServerName
);
int val = 0;
using (DbTransaction trans = db.BeginTransaction())
{
val = db.ExecuteNonQuery(trans, insert);
trans.Commit();
}
if (val > 0)
{
return(a);
}
else
{
return("");
}
}
public string Save(string clientid, string groupName,
string description,
int context,
bool isBase,
int baseType
)
{
string a = Shared.generateID();
var insert = DBQuery.InsertInto(TzAccount.SecurityGroup.Table)
.Fields(TzAccount.SecurityGroup.SecurityGroupID.Name,
TzAccount.SecurityGroup.ClientID.Name,
TzAccount.SecurityGroup.GroupName.Name,
TzAccount.SecurityGroup.Context.Name,
TzAccount.SecurityGroup.IsBaseType.Name,
TzAccount.SecurityGroup.BaseType.Name,
TzAccount.SecurityGroup.Description.Name).
Values(DBConst.String(a),
DBConst.String(clientid),
DBConst.String(groupName),
DBConst.Int32(context),
DBConst.Const(DbType.Boolean, isBase),
DBConst.Int32(baseType),
DBConst.String(description));
int val = 0;
using (DbTransaction trans = db.BeginTransaction())
{
val = db.ExecuteNonQuery(trans, insert);
trans.Commit();
}
if (val > 0)
{
return(a);
}
else
{
return("");
}
}
/// <summary>
///
/// </summary>
/// <param name="tableName"></param>
/// <param name="tableCategory"></param>
/// <returns></returns>
public string Save(string serverID, string tableName,
string tableCategory, string clientID)
{
string a = Shared.generateID();
DBConst dbTableid = DBConst.String(a);
DBConst dbServerID = DBConst.String(serverID);
DBConst dbClientID = DBConst.String(clientID);
DBConst dbtableName = DBConst.String(tableName);
DBConst dbtableCategory = DBConst.String(tableCategory);
DBQuery insert = DBQuery.InsertInto(TzAccount.Tables.Table).Fields(
TzAccount.Tables.TableID.Name,
TzAccount.Tables.TableName.Name,
TzAccount.Tables.Category.Name,
TzAccount.Tables.ServerID.Name,
TzAccount.Tables.ClientID.Name
).Values(
dbTableid,
dbtableName,
dbtableCategory,
dbServerID,
dbClientID
);
int val = 0;
using (DbTransaction trans = db.BeginTransaction())
{
val = db.ExecuteNonQuery(trans, insert);
trans.Commit();
}
if (val > 0)
{
return(a);
}
else
{
return("");
}
}
/// <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);
}
//
// support methods that actually perform the operation on the database
//
#region private void CreateCustomTable(DBDatabase db)
/// <summary>
/// Creates a new custom table in the specified database
/// </summary>
/// <param name="db"></param>
private void CreateCustomTable(DBDatabase db)
{
DBCreateTableQuery create = GetCustomTableQuery();
//OleDb provider does not support default values
if (db.ProviderName == "System.Data.OleDb")
{
create.ClearDefaults();
}
TestContext.WriteLine(create.ToSQLString(db));
db.ExecuteNonQuery(create);
TestContext.WriteLine("Table {0} Created", TableName);
}
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>
///
/// </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);
}
}
protected internal bool UpdateView(string viewID, string viewName, string coreCompoent, string category)
{
// DBComparison client = DBComparison.Equal(DBField.Field(TalentozSchemaInfo.ClientID.Name), DBConst.Int32(clientID));
DBComparison comp = DBComparison.Equal(DBField.Field(TalentozView.ViewID.Name), DBConst.String(viewID));
// DBComparison att = DBComparison.Equal(DBField.Field(TalentozSchemaInfo.FieldID.Name), DBConst.String(attributeID));
DBQuery update = DBQuery.Update(TalentozView.Table)
.Set(TalentozView.Name.Name, DBConst.String(viewName))
.AndSet(TalentozView.CoreComponent.Name, DBConst.String(coreCompoent))
.AndSet(TalentozView.Catgory.Name, DBConst.String(category))
.AndSet(TalentozView.LastUPD.Name, DBConst.DateTime(DateTime.Now)).WhereAll(comp);
if (db.ExecuteNonQuery(update) > 0)
{
return(true);
}
else
{
return(false);
}
}
public void Clear()
{
DBQuery[] all = new DBQuery[] {
DBQuery.Drop.Table(base.Schema, Builder.Schema.TalentozSchema.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, Builder.Schema.TalentozSchemaInfo.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, Builder.Schema.TalentozSchemaClientInfo.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, Builder.Schema.TalentozView.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, Builder.Schema.TalentozViewSchema.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, Builder.Schema.TalentozTemplate.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, Builder.Schema.TalentozViewSchemaRelation.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, Builder.Schema.TalentozImportLog.Table).IfExists()
};
foreach (DBQuery q in all)
{
try
{
db.ExecuteNonQuery(q);
}
catch (System.Exception ex)
{
}
}
}
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);
}
}
/// <summary>
///
/// </summary>
/// <param name="pName"></param>
/// <param name="category"></param>
/// <param name="schemaType"></param>
/// <param name="title"></param>
/// <param name="primarykeys"></param>
/// <param name="state"></param>
/// <param name="tableName"></param>
/// <returns></returns>
protected internal string SaveComponent(string pName,
string category,
int schemaType,
string title,
string primarykeys,
int state,
string tableName,
string entityKey
)
{
string ComponentId = Shared.generateID();
Tech.Data.Query.DBQuery dBQuery = DBQuery.InsertInto(TalentozSchema.Table)
.Field(TalentozSchema.ComponentID.Name)
.Field(TalentozSchema.ComponentName.Name)
.Field(TalentozSchema.Category.Name)
.Field(TalentozSchema.ComponentType.Name)
.Field(TalentozSchema.Title.Name)
.Field(TalentozSchema.ComponentState.Name)
.Field(TalentozSchema.TableName.Name)
.Field(TalentozSchema.LastUPD.Name)
.Field(TalentozSchema.PrimaryKeys.Name)
.Field(TalentozSchema.EntityKey.Name)
.Values(DBConst.String(ComponentId),
DBConst.String(pName),
DBConst.String(category),
DBConst.Int32(schemaType),
DBConst.String(title),
DBConst.Int32(state),
DBConst.String(tableName),
DBConst.DateTime(DateTime.Today),
DBConst.String(primarykeys),
DBConst.String(entityKey));
if (db.ExecuteNonQuery(dBQuery) > 0)
{
return(ComponentId);
}
else
{
return("");
}
}
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");
}
}
public void Clear()
{
DBQuery[] all = new DBQuery[] {
DBQuery.Drop.Table(base.Schema, TzAccount.Client.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, TzAccount.User.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, TzAccount.Server.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, TzAccount.Tables.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, TzAccount.Field.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, TzAccount.ClientServer.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, TzAccount.DataScript.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, TzAccount.ScriptIntend.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, TzAccount.SecurityGroup.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, TzAccount.SecurityPrivilege.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, TzAccount.Component.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, TzAccount.ComponentAttribute.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, TzAccount.ComponentModal.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, TzAccount.ComponentModalItem.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, TzAccount.ComponentModalRelation.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, TzAccount.ComponentLookUpItem.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, TzAccount.ComponentLookUp.Table).IfExists(),
DBQuery.Drop.Table(base.Schema, TzAccount.ImportExportEvents.Table).IfExists(),
};
foreach (DBQuery q in all)
{
try
{
db.ExecuteNonQuery(q);
}
catch (System.Exception ex)
{
}
}
}
public void _06_CreateAndDropView()
{
DBDatabase db = ConnectDb();
CreateCustomTable(db);
//Wrap a try round this so we can definitely drop the table
try
{
InsertARowIntoCustomTable(db, "Beta", "Alpha");
InsertARowIntoCustomTable(db, "Alpha", "Beta");
//Create the view
DBQuery create = DBQuery.Create.View(ViewName)
.As(DBQuery.SelectAll()
.From(TableName)
.WhereField("ColB", Compare.Like, DBConst.String("A%")));
TestContext.WriteLine(create.ToSQLString(db));
db.ExecuteNonQuery(create);
TestContext.WriteLine("Created the view");
DBQuery selectView = DBQuery.SelectAll().From(ViewName);
int totalcount = 0;
db.ExecuteRead(selectView, reader =>
{
while (reader.Read())
{
string colb = reader["ColB"] as string;
totalcount += 1;
}
});
TestContext.WriteLine("Total Number of rows in view = {0}", totalcount);
DBDropQuery drop = DBQuery.Drop.View(ViewName);
db.ExecuteNonQuery(drop);
//Try another select to make sure it fails
try
{
db.ExecuteNonQuery(selectView);
throw new InvalidOperationException("The table was not dropped and the insert statement succeeded");
}
catch (InvalidOperationException)
{
throw;
}
catch (System.Exception ex)
{
TestContext.WriteLine("Expected failure after inserting on a dropped table:{0}", ex.Message);
}
}
catch (Exception)
{
throw;
}
finally
{
DropCustomTable(db);
}
}