public string CheckConnection() { DB_Toolbox env_dbkit = new DB_Toolbox(); using (OracleConnection connection = new OracleConnection()) { connection.ConnectionString = constr; try { connection.Open(); // DBstatus = ("Connection Successful!"); return ("EMIN-00001-Connection Successful!"); //stops the console from closing //until you hit the ENTER key //Console.ReadLine(); } catch (OracleException ex) { //DBstatus = ("Connection Error!"); return ("EMIN-00000-Connection Error! " + env_dbkit.DB_ERROR_FORMATTER("ORACLE", ex.ToString())); //Console.WriteLine(ex.ToString()); // stops the console from closing //until you hit the ENTER key // Console.ReadLine(); } } }
public string Update(string oldName, string newName) { DB_Toolbox environment_tools_db = new DB_Toolbox(); if (environment_tools_db.SQL_SELECT_DoesCMSUserExist(oldName) == "yes") { //User already exist please navigate to URL. //Update it return "Success-System Application " + oldName + " has been updated to " + newName; } else { return "Warning-No System Application with that name exist."; //do Nothing there is nothing to update. } }
public string Delete(string Name) { DB_Toolbox environment_tools_db = new DB_Toolbox(); if (environment_tools_db.SQL_SELECT_DoesCMSUserExist(Name) == "yes") { //User already exist please delete return "Success-System Application " + Name + " has been deleted."; } else { return "Warning-System Application " + Name + " does not exist."; //Do nothing, there is nothing to delete. } }
//These function will be used to create user in the Oracle Database. //TO BE RAN ONLY BY ACCOUT WITH DBA ROLE. //Check if the user exist ? public string Create(string Name) { DB_Toolbox environment_tools_db = new DB_Toolbox(); //Check to see if it exist, before creating if (environment_tools_db.SQL_SELECT_DoesCMSUserExist(Name) == "yes") { //User already exist please navigate to URL. return ("Warning-Operation did not Succeed. System Application already exist please use another name."); } else { return ("Success-User created successfully."); //Create User in the Database. } }
public string CheckIfCMSUserIsInstalled(string Name) { DB_Toolbox environment_Tools_db = new DB_Toolbox(); return environment_Tools_db.SQL_SELECT_DoesCMSUserExist(Name); }
protected void CreateDB() { DB_Toolbox environment_Tools_db = new DB_Toolbox(); /* InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.EP_DROP_ORACLE_TABLE("Application"))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.EP_DROP_ORACLE_TABLE("Log"))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.EP_DROP_ORACLE_TABLE("Roles"))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.EP_DROP_ORACLE_TABLE("Priveleges"))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.SQL_KILL_SESSIONS(SystemName))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.EP_DROP_ORACLE_TABLE("Users"))); InstallerHolder.Controls.Add(new LiteralControl("<hr />"));*/ InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.EP_DROP_ORACLE_USER(constr, SystemName))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.EP_DROP_ORACLE_TABLESPACE(constr, SystemName))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.EP_DROP_ORACLE_PROFILE(constr, SystemName))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.EP_DROP_ORACLE_ROLE(constr, SystemName + "_ROLE"))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.EP_ADD_ORACLE_PROFILE(constr, SystemName))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.SQL_Add_Tablespace(SystemName))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.EP_ADD_ORACLE_USER(constrSQLServer, SystemName, "alpha123"))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.EP_ADD_ORACLE_ROLE(constr, SystemName + "_ROLE"))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.EP_GRANT_ORACLE_PRIVILEGE(constr, "CREATE TABLE", SystemName))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.EP_GRANT_ORACLE_PRIVILEGE(constr, "CREATE PROCEDURE", SystemName))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.EP_GRANT_ORACLE_PRIVILEGE(constr, "CREATE SEQUENCE", SystemName))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.EP_GRANT_ORACLE_PRIVILEGE(constr, "SELECT ANY DICTIONARY", SystemName))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.SQL_Add_Table("Application"))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.SQL_Add_Table("Errors"))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.SQL_Add_Table("Log"))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.SQL_Add_Table("Roles"))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.SQL_Add_Table("Priveleges"))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.SQL_Add_Table("Users"))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.SQL_Add_Column("Users", "Name", "varchar2(50)", "", false))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.SQL_Add_Column("Errors", "Application_ID", "Number", "", false))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.SQL_Add_Column("Roles", "Name", "varchar2(50)", "", false))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.SQL_Add_Column("Priveleges", "Name", "varchar2(50)", "", false))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.SQL_Add_Column("Application", "Name", "varchar2(50)", "", false))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.SQL_Add_Column("Application", "Version", "varchar2(50)", "", false))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.SQL_Add_Column("Application", "CodeName", "varchar2(50)", "", false))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); InstallerHolder.Controls.Add(new LiteralControl(environment_Tools_db.SQL_Add_Sequence("ORACLE", constr1, "APPLICATION"))); InstallerHolder.Controls.Add(new LiteralControl("<hr />")); }
//EXAMPLE ON HOW TO PASS PARAMETERS TO DB. protected void CallProcedure_Min() { List<DB_Toolbox.DBParameters> paramList = new List<DB_Toolbox.DBParameters>(); paramList.Add(new DB_Toolbox.DBParameters { ParamName = "PARAM1", ParamDirection = ParameterDirection.Input, ParamValue = "123", ParamDBType = OracleDbType.Varchar2, ParamSize = 100 }); paramList.Add(new DB_Toolbox.DBParameters { ParamName = "PARAM2", ParamDirection = ParameterDirection.Output, ParamDBType = OracleDbType.Varchar2, ParamSize = 100 }); paramList.Add(new DB_Toolbox.DBParameters { ParamName = "PARAM3", ParamDirection = ParameterDirection.Output, ParamDBType = OracleDbType.Varchar2, ParamSize = 100 }); DB_Toolbox environment_Tools_db = new DB_Toolbox(); //SQL_PROCEDURE_PARAMS ( DB TYPE, CONNECTION STRING, PROCEDURE NAME, PARAMLIST dg.DataSource = environment_Tools_db.SQL_PROCEDURE_PARAMS("ORACLE", constr1, "TEST123", paramList); dg.DataBind(); InstallerHolder.Controls.Add(dg); }
//Calls Procedure and loads data to dataset. protected void CallProcedure() { DB_Toolbox environment_Tools_db = new DB_Toolbox(); DB_Toolbox.DBParameters[] dbParameters = new DB_Toolbox.DBParameters[3]; List<DB_Toolbox.DBParameters> infoList = new List<DB_Toolbox.DBParameters>(); dbParameters[0] = new DB_Toolbox.DBParameters(); dbParameters[0].ParamName = "PARAM1"; dbParameters[0].ParamDBType = OracleDbType.Varchar2; dbParameters[0].ParamDirection = ParameterDirection.Input; dbParameters[0].ParamValue = "123"; dbParameters[0].ParamSize = 100; dbParameters[0].ParamReturn = false; infoList.Add(dbParameters[0]); dbParameters[1] = new DB_Toolbox.DBParameters(); dbParameters[1].ParamName = "PARAM2"; dbParameters[1].ParamDBType = OracleDbType.Varchar2; dbParameters[1].ParamDirection = ParameterDirection.Output; dbParameters[1].ParamSize = 100; dbParameters[1].ParamReturn = true; infoList.Add(dbParameters[1]); dbParameters[2] = new DB_Toolbox.DBParameters(); dbParameters[2].ParamName = "PARAM3"; dbParameters[2].ParamDBType = OracleDbType.Varchar2; dbParameters[2].ParamDirection = ParameterDirection.Output; dbParameters[2].ParamSize = 100; dbParameters[2].ParamReturn = true; infoList.Add(dbParameters[2]); DataTable dt = new DataTable(); dt = environment_Tools_db.SQL_PROCEDURE("Oracle", constr1, "TEST123", infoList); dg.DataSource = dt; dg.DataBind(); InstallerHolder.Controls.Add(dg); InstallerHolder.Visible = true; submitCmsName.Enabled = false; }