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;
}
