private void TestUpdate(DBRunner runner, ISQLExecuter executer, ISQL builder, DBDatabase db, DBTable table, IConnectionInfo connInfo)
{
Output("TestUpdate:");
Output("");
try
{
DBConnection conn = runner.OpenConnection(executer, db, connInfo);
try
{
Output("Insert more rows");
Stmt_Insert stmtInsert = new Stmt_Insert(table);
stmtInsert.AddAllColumns();
stmtInsert.AddValues(Guid.NewGuid(), "Dette er en tekst", 42, DateTime.Now, null, 6576547634);
stmtInsert.AddParameter("MEGET STOR TEKST");
stmtInsert.AddValue(true);
stmtInsert.AddParameter(new byte[432]);
Output(builder.ToSQL(stmtInsert));
runner.Insert(executer, conn, stmtInsert);
Output("Rows inserted");
Output("");
ShowContents(runner, executer, conn, table);
Output("");
Output("Update 1");
Stmt_Update stmtUpdate = new Stmt_Update(table);
stmtUpdate.AddColumns("txTekst", "iTal");
stmtUpdate.AddValues("En ny tekst", 534);
stmtUpdate.AddCriteria(new Crit_Match(table, "iTal", MatchType.Equal, 42));
Output(builder.ToSQL(stmtUpdate));
runner.Update(executer, conn, stmtUpdate);
Output("");
ShowContents(runner, executer, conn, table);
Output("");
Output("Update 2");
stmtUpdate = new Stmt_Update(table);
stmtUpdate.AddColumn("txStorTekst");
stmtUpdate.AddParameter("DETTE STÅR MED STORT!");
stmtUpdate.AddCriteria(new Crit_Match(table, "txStorTekst", MatchType.IsNull));
Output(builder.ToSQL(stmtUpdate));
runner.Update(executer, conn, stmtUpdate);
Output("");
ShowContents(runner, executer, conn, table);
Output("");
Stmt_Select stmtSelect = new Stmt_Select();
stmtSelect.AddTable(table);
stmtSelect.AddFunction(new Func_SubString(table, "txTekst", 3, 8));
Output(builder.ToSQL(stmtSelect));
ShowContents(stmtSelect, runner, executer, conn);
Output("");
Output("Delete");
Stmt_Delete stmtDelete = new Stmt_Delete(table);
stmtDelete.AddCriteria(new Crit_Match(table, "bValg", MatchType.Equal, false));
Output(builder.ToSQL(stmtDelete));
runner.Delete(executer, conn, stmtDelete);
Output("");
ShowContents(runner, executer, conn, table);
}
finally
{
conn.Close();
}
}
catch (Exception ex)
{
Output("TestUpdate failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
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);
}
}
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);
}
}
protected override DBSchemaProvider CreateSchemaProvider(DBDatabase forDatabase, DBDatabaseProperties properties)
{
return(new DBSqLiteSchemaProvider(forDatabase, properties));
}
public DBOleDbStatementBuilder(DBDatabase forDatabase, DBDatabaseProperties properties, System.IO.TextWriter tw, bool ownswriter)
: base(forDatabase, properties, tw, ownswriter)
{
}
/// <summary>
/// Takes the XML string and converts it back to a DBQuery.
/// It then checks the generated sql statement from the DBQuery and compares to the provided sql string
/// and makes sure they are the same (equal case sensitive).
/// </summary>
/// <param name="xml"></param>
/// <param name="sql"></param>
/// <param name="name"></param>
private void ValidateXml(string xml, string sql, string name)
{
DBDatabase db = CreateDB();
ValidateXml(db, xml, sql, name);
}
public DBOracleSchemaProvider(DBDatabase database, DBDatabaseProperties properties)
: base(database, properties)
{
}
public SecurityPrivilege(string conn)
{
InitDbs(conn);
db = base.Database;
}
/// <summary>
/// Creates a new instance of the DBMSAccessSchemaProvider
/// </summary>
/// <param name="database"></param>
/// <param name="properties"></param>
public DBMSAccessSchemaProvider(DBDatabase database, DBDatabaseProperties properties)
: base(database, properties)
{
}
public static void Main(String[] args)
{
try
{
DBRunner runner = new DBRunner();
ISQLExecuter executer = new DB2_SQLExecuter(new DB2_SQL());
DBDatabase database = new DBDatabase("EISST");
// Open connection to the database
Output("Open connection to database");
IConnectionInfo connInfo = new DB2_ConnectionInfo("vagn", "EISST", "EISST", "EISSTEISST");
DBConnection connection = runner.OpenConnection(executer, database, connInfo);
try
{
// Extract meta data for all the tables we need
Output("Extract table metadata");
DBTable pluginsTable = runner.GetTableMetaData(executer, connection, database, "EISST", "Plugins");
database.AddTable(pluginsTable);
DBTable grupperTable = runner.GetTableMetaData(executer, connection, database, "EISST", "Grupper");
database.AddTable(grupperTable);
DBTable gruppePostkasseTable = runner.GetTableMetaData(executer, connection, database, "EISST", "GruppePostkasse");
database.AddTable(gruppePostkasseTable);
DBTable side1Table = runner.GetTableMetaData(executer, connection, database, "EISST", "Mortality_Side1");
database.AddTable(side1Table);
DBTable side2Table = runner.GetTableMetaData(executer, connection, database, "EISST", "Mortality_Side2");
database.AddTable(side2Table);
Output("Find needed ids");
// Find the plug-in id for mortality documents
Guid pluginID = FindPluginID(pluginsTable, runner, executer, connection);
if (pluginID.Equals(Guid.Empty))
{
Output("Couldn't find plug-in ID");
return;
}
Output("Plug-in ID: " + pluginID.ToString("B").ToUpper());
// Find the group id where the documents are stored
Guid groupID = FindGroupID(grupperTable, runner, executer, connection);
if (groupID.Equals(Guid.Empty))
{
Output("Couldn't find group ID");
return;
}
Output("Group ID: " + groupID.ToString("B").ToUpper());
Output("Delete documents that are stored in the database");
FixMailBox(pluginID, groupID, gruppePostkasseTable, side1Table, side2Table, runner, executer, connection);
}
finally
{
Output("Close connection");
connection.Close();
}
}
catch (Exception ex)
{
Output("Exception failure: " + ex);
}
}
public DBOracleStatementBuilder(DBDatabase db, DBDatabaseProperties props,
System.IO.TextWriter writer, bool ownsWriter)
: base(db, props, writer, ownsWriter)
{
}
/// <summary>
///
/// </summary>
/// <param name="conn"></param>
public ImportTemplate(string conn)
{
InitDbs(conn);
db = base.Database;
}
private void TestSQLBuild(ISQL builder, DBDatabase db, DBTable table)
{
Output("TestSQLBuild:");
Output("");
try
{
Output("Criterias:");
ICriteria crit1 = new Crit_Match(table, "lStortTal", MatchType.Equal, 6576547634);
ICriteria crit2 = new Crit_Match(table, "txTekst", MatchType.Different, "Bent");
ICriteria crit3 = new Crit_Match(table, "sLilleTal", MatchType.IsNull);
Stmt_Select stmtSelect = new Stmt_Select();
stmtSelect.AddAllColumns(table);
stmtSelect.AddCriteria(crit1);
stmtSelect.AddCriteria(crit2);
stmtSelect.AddCriteria(crit3);
Output(builder.ToSQL(stmtSelect));
stmtSelect = new Stmt_Select();
stmtSelect.AddAllColumns(table);
stmtSelect.AddCriteria(new Crit_Or(crit1, crit2));
stmtSelect.AddCriteria(crit3);
Output(builder.ToSQL(stmtSelect));
stmtSelect = new Stmt_Select();
stmtSelect.AddAllColumns(table);
ICriteria tempCrit = new Crit_And(crit2, crit3);
stmtSelect.AddCriteria(new Crit_Or(crit1, tempCrit));
Output(builder.ToSQL(stmtSelect));
stmtSelect = new Stmt_Select();
stmtSelect.AddAllColumns(table);
stmtSelect.AddCriteria(new Crit_Or(new Crit_Or(crit1, crit2), crit3));
Output(builder.ToSQL(stmtSelect));
stmtSelect = new Stmt_Select();
stmtSelect.AddAllColumns(table);
stmtSelect.AddCriteria(crit1);
stmtSelect.AddCriteria(crit2);
stmtSelect.AddCriteria(new Crit_In(table, "iTal", true, 3, 5, 254, 31));
Output(builder.ToSQL(stmtSelect));
Stmt_Select stmtSelect1 = new Stmt_Select();
stmtSelect1.AddColumn(table, "iTal");
stmtSelect = new Stmt_Select();
stmtSelect.AddAllColumns(table);
stmtSelect.AddCriteria(new Crit_SubQuery(table, "iTal", stmtSelect1));
Output(builder.ToSQL(stmtSelect));
stmtSelect = new Stmt_Select();
stmtSelect.AddAllColumns(table);
stmtSelect.AddCriteria(new Crit_SubQuery(table, "iTal", true, stmtSelect1));
Output(builder.ToSQL(stmtSelect));
Output("");
Output("Aggregates:");
stmtSelect = new Stmt_Select();
stmtSelect.AddColumn(table, "iTal");
stmtSelect.Distinct = true;
Output(builder.ToSQL(stmtSelect));
stmtSelect = new Stmt_Select();
stmtSelect.AddTable(table);
stmtSelect.AddAggregate(new Aggre_Count());
Output(builder.ToSQL(stmtSelect));
stmtSelect = new Stmt_Select();
stmtSelect.AddTable(table);
stmtSelect.AddAggregate(new Aggre_Count(table, "iTal"));
Output(builder.ToSQL(stmtSelect));
stmtSelect = new Stmt_Select();
stmtSelect.AddTable(table);
stmtSelect.AddAggregate(new Aggre_Max(table, "iTal"));
Output(builder.ToSQL(stmtSelect));
stmtSelect = new Stmt_Select();
stmtSelect.AddTable(table);
stmtSelect.AddAggregate(new Aggre_Min(table, "iTal"));
Output(builder.ToSQL(stmtSelect));
Output("");
Output("Create tables:");
DBTable employees = db.AddTable("Employees");
employees.AddColumn("Employee_ID", ColumnType.String, 2, ColumnFlag.PrimaryKey | ColumnFlag.NotNull);
employees.AddColumn("Name", ColumnType.String, 50, ColumnFlag.NotNull);
DBTable orders = db.AddTable("Orders");
orders.AddColumn("Prod_ID", ColumnType.Int, ColumnFlag.PrimaryKey | ColumnFlag.NotNull);
orders.AddColumn("Product", ColumnType.String, 50, ColumnFlag.NotNull | ColumnFlag.IndexUnique);
orders.AddColumn("Employee_ID", ColumnType.String, 2, ColumnFlag.NotNull);
DBTable storage = db.AddTable("Storage");
storage.AddColumn("Storage_ID", ColumnType.Int, ColumnFlag.PrimaryKey | ColumnFlag.NotNull);
storage.AddColumn("Prod_ID", ColumnType.Int, ColumnFlag.NotNull);
storage.AddColumn("Count", ColumnType.Int, ColumnFlag.NotNull | ColumnFlag.IndexDesc);
Stmt_CreateTable stmtCreate = new Stmt_CreateTable(employees);
Output(builder.ToSQL(stmtCreate));
stmtCreate = new Stmt_CreateTable(orders);
Output(builder.ToSQL(stmtCreate));
stmtCreate = new Stmt_CreateTable(storage);
Output(builder.ToSQL(stmtCreate));
Output("");
Output("Joins:");
stmtSelect = new Stmt_Select();
stmtSelect.AddColumn(employees, "Name");
stmtSelect.AddColumn(orders, "Product");
stmtSelect.AddJoin(new Join_Inner(employees, "Employee_ID", orders, "Employee_ID"));
stmtSelect.AddColumn(storage, "Count");
stmtSelect.AddJoin(new Join_Inner(orders, "Prod_ID", storage, "Prod_ID"));
stmtSelect.AddCriteria(new Crit_Match(storage, "Count", MatchType.Bigger, 10));
stmtSelect.AddSort(employees, "Name", Order.Ascending);
stmtSelect.AddSort(orders, "Product", Order.Descending);
Output(builder.ToSQL(stmtSelect));
stmtSelect = new Stmt_Select();
stmtSelect.AddColumn(employees, "Name");
stmtSelect.AddColumn(orders, "Product");
stmtSelect.AddJoin(new Join_Left(employees, "Employee_ID", orders, "Employee_ID"));
Output(builder.ToSQL(stmtSelect));
stmtSelect = new Stmt_Select();
stmtSelect.AddColumn(employees, "Name");
stmtSelect.AddColumn(orders, "Product");
stmtSelect.AddJoin(new Join_Right(employees, "Employee_ID", orders, "Employee_ID"));
Output(builder.ToSQL(stmtSelect));
Output("");
Output("Misc");
DBTable employees1 = db.AddTable("Employees1");
employees1.AddColumn("Employee_ID", ColumnType.String, 2, ColumnFlag.PrimaryKey | ColumnFlag.NotNull);
employees1.AddColumn("Name", ColumnType.String, 50, ColumnFlag.NotNull);
stmtSelect = new Stmt_Select();
stmtSelect.AddAllColumns(employees);
Stmt_Insert stmtInsert = new Stmt_Insert(employees1);
stmtInsert.InsertFromSelect = stmtSelect;
Output(builder.ToSQL(stmtInsert));
}
catch (Exception ex)
{
Output("TestSQLBuild failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
private void TestFunctions(DBRunner runner, ISQLExecuter executer, ISQL builder, DBDatabase db, DBTable table, IConnectionInfo connInfo)
{
Output("TestFunctions:");
Output("");
try
{
DBConnection conn = runner.OpenConnection(executer, db, connInfo);
try
{
Output("Insert single row");
Stmt_Insert stmtInsert = new Stmt_Insert(table);
stmtInsert.AddColumns("uiNoegle", "iTal", "lStortTal", "dtDato", "bValg");
stmtInsert.AddValues(Guid.NewGuid(), 87, (long)2394287487, DateTime.Now, false);
Output(builder.ToSQL(stmtInsert));
runner.Insert(executer, conn, stmtInsert);
stmtInsert = new Stmt_Insert(table);
stmtInsert.AddColumns("uiNoegle", "txTekst", "iTal", "lStortTal", "dtDato", "bValg");
stmtInsert.AddValues(Guid.NewGuid(), "Blåbærgrød", 87, (long)2394287487, DateTime.Now, false);
Output(builder.ToSQL(stmtInsert));
runner.Insert(executer, conn, stmtInsert);
Output("Rows inserted");
Output("");
ShowContents(runner, executer, conn, table);
Output("");
Stmt_Select stmtSelect = new Stmt_Select();
stmtSelect.AddTable(table);
IFunction func = new Func_SubString(table, "txTekst", 3, 8);
stmtSelect.AddFunction(new Func_SubString(func, 0, 2));
Output(builder.ToSQL(stmtSelect));
ShowContents(stmtSelect, runner, executer, conn);
Output("");
stmtSelect = new Stmt_Select();
stmtSelect.AddTable(table);
stmtSelect.AddFunction(new Func_ToLower(table, "txTekst"));
Output(builder.ToSQL(stmtSelect));
ShowContents(stmtSelect, runner, executer, conn);
Output("");
stmtSelect = new Stmt_Select();
stmtSelect.AddTable(table);
stmtSelect.AddFunction(new Func_ToUpper(table, "txTekst"));
Output(builder.ToSQL(stmtSelect));
ShowContents(stmtSelect, runner, executer, conn);
}
finally
{
conn.Close();
}
}
catch (Exception ex)
{
Output("TestUpdate failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
public Table()
{
InitDbs("");
db = base.Database;
}
//
// .ctor
//
#region public DBMySqlSchemaProvider(DBDatabase database, DBDatabaseProperties properties)
public DBMySqlSchemaProvider(DBDatabase database, DBDatabaseProperties properties)
: base(database, properties)
{
}
/// <summary>
/// Outputs the specified query as a SQL statement onto the console. User the DaBDatabase from CreateDB()
/// </summary>
/// <param name="query"></param>
/// <param name="name"></param>
private string OutputSql(DBQuery query, string name, bool output)
{
DBDatabase db = CreateDB();
return(OutputSql(db, query, name, output));
}
//
// .ctor
//
#region public DBSQLClientSchemaProvider(DBDatabase database, DBDatabaseProperties properties)
/// <summary>
/// Creates a new instance of the schema provider for an SqlClient database
/// </summary>
/// <param name="database"></param>
/// <param name="properties"></param>
public DBSQLClientSchemaProvider(DBDatabase database, DBDatabaseProperties properties)
: base(database, properties)
{
}
public SecurityGroup(string conn)
{
InitDbs(conn);
db = base.Database;
}
//
// .ctor
//
internal DBSqLiteSchemaProvider(DBDatabase forDatabase, DBDatabaseProperties properties)
: base(forDatabase, properties)
{
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="forDatabase"></param>
/// <param name="properties"></param>
/// <param name="tw"></param>
/// <param name="ownswriter"></param>
internal protected DBSQLClientStatementBuilder(DBDatabase forDatabase, DBDatabaseProperties properties, System.IO.TextWriter tw, bool ownswriter)
: base(forDatabase, properties, tw, ownswriter)
{
}
private List <MetricValue> readMetricValuesIntoEntity(DBEntityBase entity, JobTarget jobTarget, string metricName, AppDRESTMetric appDRESTMetric, int timerangeDuration)
{
List <MetricValue> metricValues = new List <MetricValue>(appDRESTMetric.metricValues.Count);
foreach (AppDRESTMetricValue appDRESTMetricValue in appDRESTMetric.metricValues)
{
// Populate metrics into the list for output into CSV
MetricValue metricValue = new MetricValue();
metricValue.Controller = jobTarget.Controller;
metricValue.ApplicationID = jobTarget.ApplicationID;
metricValue.ApplicationName = jobTarget.Application;
if (entity != null)
{
if (entity is DBDatabase)
{
DBDatabase database = (DBDatabase)entity;
metricValue.EntityID = database.DatabaseID;
metricValue.EntityName = String.Format("{0}\\{1}", database.CollectorName, database.DatabaseName);
metricValue.EntityType = database.CollectorType;
}
else if (entity is DBCollector)
{
DBCollector databaseCollector = (DBCollector)entity;
metricValue.EntityID = databaseCollector.CollectorID;
metricValue.EntityName = databaseCollector.CollectorName;
metricValue.EntityType = databaseCollector.CollectorType;
}
}
metricValue.EventTimeStampUtc = UnixTimeHelper.ConvertFromUnixTimestamp(appDRESTMetricValue.startTimeInMillis);
metricValue.EventTimeStamp = metricValue.EventTimeStampUtc.ToLocalTime();
metricValue.EventTime = metricValue.EventTimeStamp;
metricValue.MetricName = metricName;
metricValue.MetricID = appDRESTMetric.metricId;
switch (appDRESTMetric.frequency)
{
case "SIXTY_MIN":
{
metricValue.MetricResolution = 60;
break;
}
case "TEN_MIN":
{
metricValue.MetricResolution = 10;
break;
}
case "ONE_MIN":
{
metricValue.MetricResolution = 1;
break;
}
default:
{
metricValue.MetricResolution = 1;
break;
}
}
metricValue.Count = appDRESTMetricValue.count;
metricValue.Min = appDRESTMetricValue.min;
metricValue.Max = appDRESTMetricValue.max;
metricValue.Occurrences = appDRESTMetricValue.occurrences;
metricValue.Sum = appDRESTMetricValue.sum;
metricValue.Value = appDRESTMetricValue.value;
metricValues.Add(metricValue);
}
return(metricValues);
}
protected override DBStatementBuilder CreateStatementBuilder(DBDatabase forDatabase, DBDatabaseProperties withProperties, System.IO.TextWriter writer, bool ownsWriter)
{
return(new DBStatementSQLiteBuilder(forDatabase, withProperties, writer, ownsWriter));
}
/// <summary>
///
/// </summary>
/// <param name="conn"></param>
public ComponentBuilder(string conn)
{
InitDbs(conn);
db = base.Database;
}
public void _04_JustDropTheTable()
{
DBDatabase db = ConnectDb();
this.DropCustomTable(db);
}
// no statement building
#else
//
// SQL String methods
//
#region public string ToSQLString(DBDatabase fordatabase)
/// <summary>
/// Generates the implementation specific sql statement for this query.
/// </summary>
/// <param name="fordatabase">The DBDatabase to use for generation of the SQL statement</param>
/// <returns>This SQL statement as a string</returns>
public string ToSQLString(DBDatabase fordatabase)
{
return(fordatabase.GetCommandText(this));
}
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 Field()
{
InitDbs("");
db = base.Database;
}
/* End SQL Server connections */
/* MySql Connections
* public const string DbConnection = "server=172.16.56.1;User Id=testaccount;Password=test;Persist Security Info=True;database=northwind;AllowUserVariables=true";
* public const string DbProvider = "MySql.Data.MySqlClient";
* /* End SQL Server connections */
/* SQLite connections
* public const string DbConnection = @"data source=C:\Development\DynaSQL\DynaSQLUnitTest.db;foreign keys=true;"; //we add the foreign keys=true here to enforce them
* public const string DbProvider = "System.Data.SQLite";
* /* End SQLite connections */
/* MS Access connections
* public const string DbConnection = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Sample Databases\Northwind2007.accdb;Persist Security Info=False;";
* public const string DbProvider = "System.Data.OleDb";
* /* End MSAccess connections */
#region private static DBDatabase CreateDb()
/// <summary>
/// Creates the database connection
/// </summary>
/// <returns></returns>
private static DBDatabase ConnectDb()
{
return(DBDatabase.Create(DbConnection, DbProvider));
}
private void TestTransactions(DBRunner runner, ISQLExecuter executer, DBDatabase db, DBTable table, IConnectionInfo connInfo)
{
Output("TestTransactions:");
Output("");
try
{
DBConnection conn = runner.OpenConnection(executer, db, connInfo);
try
{
Stmt_Insert stmtInsert;
Stmt_Select stmtSelect;
long result;
Output("Begin transaction");
DBTransaction trans = runner.CreateTransaction(executer);
trans.Begin(conn);
try
{
Output("Insert row");
stmtInsert = new Stmt_Insert(table);
stmtInsert.AddColumns("uiNoegle", "iTal", "lStortTal", "dtDato", "bValg");
stmtInsert.AddValues(Guid.NewGuid(), 87, (long)2394287487, DateTime.Now, false);
runner.Insert(executer, conn, stmtInsert);
stmtSelect = new Stmt_Select();
stmtSelect.AddTable(table);
stmtSelect.AddAggregate(new Aggre_Count());
result = runner.SelectWithSingleAggregate(executer, conn, stmtSelect);
Output("Count: " + result + " / 1");
Output("Rollback");
trans.RollbackAll();
}
catch (Exception)
{
trans.RollbackAll();
throw;
}
stmtSelect = new Stmt_Select();
stmtSelect.AddTable(table);
stmtSelect.AddAggregate(new Aggre_Count());
result = runner.SelectWithSingleAggregate(executer, conn, stmtSelect);
Output("Count: " + result + " / 0");
Output("");
Output("Begin new transaction");
trans = runner.CreateTransaction(executer);
trans.Begin(conn);
try
{
Output("Insert row");
stmtInsert = new Stmt_Insert(table);
stmtInsert.AddColumns("uiNoegle", "iTal", "lStortTal", "dtDato", "bValg");
stmtInsert.AddValues(Guid.NewGuid(), 87, (long)2394287487, DateTime.Now, false);
runner.Insert(executer, conn, stmtInsert);
stmtSelect = new Stmt_Select();
stmtSelect.AddTable(table);
stmtSelect.AddAggregate(new Aggre_Count());
result = runner.SelectWithSingleAggregate(executer, conn, stmtSelect);
Output("Count: " + result + " / 1");
Output("Commit");
trans.CommitAll();
}
catch (Exception)
{
trans.RollbackAll();
throw;
}
stmtSelect = new Stmt_Select();
stmtSelect.AddTable(table);
stmtSelect.AddAggregate(new Aggre_Count());
result = runner.SelectWithSingleAggregate(executer, conn, stmtSelect);
Output("Count: " + result + " / 1");
}
finally
{
conn.Close();
}
}
catch (Exception ex)
{
Output("TestTransactions failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
