private void TestSmallSelect(DBRunner runner, ISQLExecuter executer, ISQL builder, DBDatabase db, DBTable table, IConnectionInfo connInfo)
{
Output("TestSmallSelect:");
Output("");
try
{
DBConnection conn = runner.OpenConnection(executer, db, connInfo);
try
{
Output("Get count");
Stmt_Select stmtSelect = new Stmt_Select();
stmtSelect.AddTable(table);
stmtSelect.AddAggregate(new Aggre_Count());
Output(builder.ToSQL(stmtSelect));
long result = runner.SelectWithSingleAggregate(executer, conn, stmtSelect);
Output("Count: " + result + " / 1");
}
finally
{
conn.Close();
}
}
catch (Exception ex)
{
Output("TestSmallSelect failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
private void TestConnection(DBRunner runner, ISQLExecuter executer, DBDatabase db, IConnectionInfo connInfo)
{
Output("TestConnection:");
Output("");
try
{
Output("Open connection");
DBConnection conn = runner.OpenConnection(executer, db, connInfo);
Output("Got connection");
Output("Close connection");
conn.Close();
Output("Connection closed");
}
catch (Exception ex)
{
Output("TestConnection failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
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 TestDatabase(DBRunner runner, ISQLExecuter executer, DBDatabase db)
{
Output("TestDatabase:");
Output("");
try
{
bool result = runner.DatabaseExists(executer, db);
Output("Database exists: " + result + " / False");
result = runner.CreateDatabase(executer, db);
Output("Create database: " + (result ? "Ok" : "Failed"));
if (result)
{
result = runner.DatabaseExists(executer, db);
Output("Database exists: " + result + " / True");
}
}
catch (Exception ex)
{
Output("TestDatabase failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
private void ShowContents(Stmt_Select statement, DBRunner runner, ISQLExecuter executer, DBConnection conn)
{
DBRowCollection coll = runner.Select(executer, conn, statement);
String line = "";
foreach (DBMetaColumn meta in coll.Meta)
{
line += meta.ColumnName + ", ";
}
Output(line.Substring(0, line.Length - 2));
foreach (DBRow r in coll)
{
line = "";
int count = r.Count;
for (int i = 0; i < count; i++)
{
line += r[i] == null ? "null, " : (r[i] + ", ");
}
Output(line.Substring(0, line.Length - 2));
}
}
private void TestTable(DBRunner runner, ISQLExecuter executer, ISQL builder, DBDatabase db, DBTable table, IConnectionInfo connInfo)
{
Output("TestTable:");
Output("");
try
{
DBConnection conn = runner.OpenConnection(executer, db, connInfo);
try
{
String name = table.TableName;
bool result = runner.TableExists(executer, conn, name);
Output("Table " + name + " exists: " + result + " / False");
Output("");
Output("Create table " + name);
Stmt_CreateTable stmtCreate = new Stmt_CreateTable(table);
Output(builder.ToSQL(stmtCreate));
runner.CreateTable(executer, conn, stmtCreate);
Output("Table created");
Output("");
result = runner.TableExists(executer, conn, name);
Output("Table " + name + " exists: " + result + " / True");
Output("");
if (result)
{
Output("Drop table " + name);
Stmt_DropTable stmtDrop = new Stmt_DropTable(table);
Output(builder.ToSQL(stmtDrop));
runner.DropTable(executer, conn, stmtDrop);
Output("Table dropped");
Output("");
result = runner.TableExists(executer, conn, name);
Output("Table " + name + " exists: " + result + " / False");
}
}
finally
{
conn.Close();
}
}
catch (Exception ex)
{
Output("TestTable failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
private static bool HasID(DBTable table, Guid documentID, DBRunner runner, ISQLExecuter executer, DBConnection connection)
{
SQL_SelectStatement sqlSelect = new SQL_SelectStatement();
sqlSelect.AddTable(table);
sqlSelect.AddAggregate(new Aggre_Count());
sqlSelect.AddCriteria(new Crit_MatchCriteria(table, "uiSkemaID", MatchType.Equal, documentID));
return(runner.SelectWithSingleAggregate(executer, connection, sqlSelect) > 0);
}
private void ShowContents(DBRunner runner, ISQLExecuter executer, DBConnection conn, DBTable table)
{
Output("Display contents of table " + table.TableName);
Stmt_Select stmtSelect = new Stmt_Select();
stmtSelect.AddAllColumns(table);
ShowContents(stmtSelect, runner, executer, conn);
}
public SP_User_SetPassword(string connectionString, ISQLExecuter executer = null)
{
if (string.IsNullOrEmpty(connectionString))
throw new ArgumentNullException("Could not create a new SP_User_SetPassword instance with empty connectionString");
if (executer == null)
_executer = new SQLExecuter(connectionString);
else
_executer = executer;
}
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 TestTable2(DBRunner runner, ISQLExecuter executer, DBDatabase db, String tableName, IConnectionInfo connInfo)
{
Output("TestTable 2:");
Output("");
try
{
Output("Getting metadata for table " + tableName);
using (DBConnection conn = runner.OpenConnection(executer, db, connInfo))
{
DBTable table = runner.GetTableMetaData(executer, conn, db, tableName);
if (table == null)
{
Output("No metadata found");
}
else
{
Output("Found metadata for table: " + table.TableName + " - Columns: " + table.NumberOfColumns);
}
}
Output("");
Output("Getting metadata");
DBDatabase newDB = new DBDatabase(db.DatabaseName);
runner.GetMetaData(executer, newDB, connInfo);
if (newDB.NumberOfTables == 0)
{
Output("No tables found");
}
else
{
foreach (DBTable table in newDB.Tables)
{
Output("Table: " + table.TableName + " - Columns: " + table.NumberOfColumns);
}
}
}
catch (Exception ex)
{
Output("TestTable 2 failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
private static Guid FindGroupID(DBTable gruppeTable, DBRunner runner, ISQLExecuter executer, DBConnection connection)
{
SQL_SelectStatement sqlSelect = new SQL_SelectStatement();
sqlSelect.AddColumn(gruppeTable, "uiGruppeID");
sqlSelect.AddCriteria(new Crit_MatchCriteria(gruppeTable, "txNavn", MatchType.Equal, "Dødsattester midlertidig opbevaring"));
DBRow row = runner.SelectAndReturnFirstRow(executer, connection, sqlSelect);
if (row == null)
{
return(Guid.Empty);
}
return((Guid)row["uiGruppeID"]);
}
private static Guid FindPluginID(DBTable pluginsTable, DBRunner runner, ISQLExecuter executer, DBConnection connection)
{
SQL_SelectStatement sqlSelect = new SQL_SelectStatement();
sqlSelect.AddColumn(pluginsTable, "uiPluginID");
sqlSelect.AddCriteria(new Crit_MatchCriteria(pluginsTable, "txNavn", MatchType.Equal, "dk.hob.ei.mortality.Plugin"));
DBRow row = runner.SelectAndReturnFirstRow(executer, connection, sqlSelect);
if (row == null)
{
return(Guid.Empty);
}
return((Guid)row["uiPluginID"]);
}
public SP_User_Insert(string connectionString, ISQLExecuter executer = null)
{
if (string.IsNullOrEmpty(connectionString))
{
throw new ArgumentNullException("Could not create a new SP_User_Insert instance with empty connectionString.");
}
if (executer == null)
{
_executer = new SQLExecuter(connectionString);
}
else
{
_executer = executer;
}
}
private static void AddIDs(SQL_SelectStatement sqlSelect, DBRunner runner, ISQLExecuter executer, DBConnection connection, Dictionary <Guid, Object> ids)
{
using (DBReader reader = runner.GetReader(executer, connection, sqlSelect))
{
DBRow row;
while ((row = reader.GetNextRow()) != null)
{
if (row["uiSkemaID"] != null)
{
Guid id = (Guid)row["uiSkemaID"];
ids[id] = null;
}
}
}
}
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("");
}
}
private static void FindDocuments(DBTable pluginsTable, DBTable gruppePostkasseTable, DBTable revisionssporTable, DBRunner runner, ISQLExecuter executer, DBConnection connection, String plugin, params String[] tables)
{
Output("");
Output("Find plugin ID for " + plugin);
SQL_SelectStatement sqlSelect = new SQL_SelectStatement();
sqlSelect.AddColumn(pluginsTable, "uiPluginID");
sqlSelect.AddCriteria(new Crit_MatchCriteria(pluginsTable, "txNavn", MatchType.Equal, plugin));
DBRow row = runner.SelectAndReturnFirstRow(executer, connection, sqlSelect);
if (row == null)
{
throw new Exception("Couldn't find plugin ID");
}
Guid pluginID = (Guid)row["uiPluginID"];
// Find all the document IDs
Dictionary <Guid, Object> documentIDs = new Dictionary <Guid, Object>();
Output("Find document IDs for " + gruppePostkasseTable.TableName);
sqlSelect = new SQL_SelectStatement();
sqlSelect.Distinct = true;
sqlSelect.AddColumn(gruppePostkasseTable, "uiSkemaID");
sqlSelect.AddCriteria(new Crit_MatchCriteria(gruppePostkasseTable, "uiPluginID", MatchType.Equal, pluginID));
AddIDs(sqlSelect, runner, executer, connection, documentIDs);
foreach (String tableName in tables)
{
Output("Find document IDs for " + tableName);
DBTable table = pluginsTable.Database.AddTable("EISST", tableName);
table.AddColumn("uiSkemaID", ColumnType.Guid, ColumnFlag.None);
sqlSelect = new SQL_SelectStatement();
sqlSelect.AddColumn(table, "uiSkemaID");
AddIDs(sqlSelect, runner, executer, connection, documentIDs);
}
Output("Check the revisionsspor to see which documents are missing");
sqlSelect = new SQL_SelectStatement();
sqlSelect.Distinct = true;
sqlSelect.AddColumn(revisionssporTable, "uiSkemaID");
sqlSelect.AddCriteria(new Crit_MatchCriteria(revisionssporTable, "uiPluginID", MatchType.Equal, pluginID));
sqlSelect.AddCriteria(new Crit_MatchCriteria(revisionssporTable, "iType", MatchType.Equal, (short)0));
sqlSelect.AddCriteria(new Crit_MatchCriteria(revisionssporTable, "uiSkemaID", MatchType.IsNotNull));
sqlSelect.AddSort(revisionssporTable, "uiSkemaID", Order.Ascending);
using (DBReader reader = runner.GetReader(executer, connection, sqlSelect))
{
while ((row = reader.GetNextRow()) != null)
{
Guid id = (Guid)row["uiSkemaID"];
if (!documentIDs.ContainsKey(id))
{
Output(id.ToString("B").ToUpper());
}
}
}
}
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 static void FixMailBox(Guid pluginID, Guid groupID, DBTable gruppePostkasseTable, DBTable side1Table, DBTable side2Table, DBRunner runner, ISQLExecuter executer, DBConnection connection)
{
SQL_SelectStatement sqlSelect = new SQL_SelectStatement();
sqlSelect.AddColumns(gruppePostkasseTable, "uiBrevID", "uiSkemaID", "txCPRNr", "txBesked");
sqlSelect.AddCriteria(new Crit_MatchCriteria(gruppePostkasseTable, "uiGruppeID", MatchType.Equal, groupID));
sqlSelect.AddCriteria(new Crit_MatchCriteria(gruppePostkasseTable, "uiPluginID", MatchType.Equal, pluginID));
using (DBReader reader = runner.GetReader(executer, connection, sqlSelect))
{
DBRow row;
int count = 0;
try
{
while ((row = reader.GetNextRow()) != null)
{
Guid documentID = (Guid)row["uiSkemaID"];
bool deleteIt = false;
int option = 0;
if (HasID(side1Table, documentID, runner, executer, connection))
{
if (HasID(side2Table, documentID, runner, executer, connection))
{
option = 1;
deleteIt = true;
}
else if (OnlyOnePage((String)row["txBesked"]))
{
option = 2;
deleteIt = true;
}
if (deleteIt)
{
// Delete the document
Output(option + "," + row["txCPRNr"] + "," + documentID.ToString("B").ToUpper());
SQL_DeleteStatement sqlDelete = new SQL_DeleteStatement(gruppePostkasseTable);
sqlDelete.AddCriteria(new Crit_MatchCriteria(gruppePostkasseTable, "uiBrevID", MatchType.Equal, row["uiBrevID"]));
runner.Delete(executer, connection, sqlDelete);
count++;
}
}
}
}
finally
{
Output("Deleted " + count + " documents");
}
}
}
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("");
}
}
