private void TestSmallInsert(DBRunner runner, ISQLExecuter executer, ISQL builder, DBDatabase db, DBTable table, IConnectionInfo connInfo)
{
Output("TestSmallInsert:");
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);
Output("Row inserted");
}
finally
{
conn.Close();
}
}
catch (Exception ex)
{
Output("TestSmallInsert failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
private void TestUnion(DBRunner runner, ISQLExecuter executer, ISQL builder, DBDatabase db, DBTable table1, DBTable table2, IConnectionInfo connInfo)
{
Output("TestUnion:");
Output("");
try
{
DBConnection conn = runner.OpenConnection(executer, db, connInfo);
try
{
Output("Insert more test rows");
Stmt_Insert stmtInsert = new Stmt_Insert(table1);
stmtInsert.AddColumns("uiNoegle", "txTekst", "iTal", "lStortTal", "dtDato", "bValg");
stmtInsert.AddValues(Guid.NewGuid(), "Giv mig en bog!", 123, (long)213142566, DateTime.Now, true);
Output(builder.ToSQL(stmtInsert));
runner.Insert(executer, conn, stmtInsert);
Output("Rows inserted");
Output("");
ShowContents(runner, executer, conn, table1);
Output("");
ShowContents(runner, executer, conn, table2);
Output("");
Stmt_Select stmtSelect1 = new Stmt_Select();
stmtSelect1.AddColumns(table1, "uiNoegle", "txTekst", "iTal", "lStortTal");
Stmt_Select stmtSelect2 = new Stmt_Select();
stmtSelect2.AddColumns(table2, "uiNoegle", "txTekst", "iTal", "lStortTal");
Stmt_Select stmtUnion = new Stmt_Select();
stmtUnion.AddColumns(table1, "uiNoegle", "txTekst", "iTal", "lStortTal");
stmtUnion.AddUnion(stmtSelect1);
stmtUnion.AddUnion(stmtSelect2);
stmtUnion.AddSort(table1, "iTal", Order.Ascending);
Output(builder.ToSQL(stmtUnion));
ShowContents(stmtUnion, runner, executer, conn);
}
finally
{
conn.Close();
}
}
catch (Exception ex)
{
Output("TestUpdate failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
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("");
}
}
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("");
}
}
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("");
}
}