Example #1
0
        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("");
            }
        }
Example #2
0
        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("");
            }
        }
Example #3
0
        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("");
            }
        }
Example #4
0
        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("");
            }
        }
Example #5
0
        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("");
            }
        }
Example #6
0
        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("");
            }
        }