public List <METSTORERecord> METSTORE_CSV_RecsAsList(string sqlQuery)
{
List <METSTORERecord> DataSet = new List <METSTORERecord>();
// Open the DatabaseHandler Object
DBConnectAndRunSQL DB = new DBConnectAndRunSQL();
// get SQL query config
string OraConnectionDSource = WebConfigurationManager.AppSettings["GMIDB_Conn"];
string OraUserPass = "******" + WebConfigurationManager.AppSettings["GMIDB_user"] + ";Password="******"GMIDB_pass"] + ";";
string OraConnectionString = OraUserPass + OraConnectionDSource;
OracleDataReader reader = DB.GetResultsAsReader(sqlQuery, DB.OpenConnection(OraConnectionString));
/* if (GMI_TRANSreader.HasRows)
* Status.Print(false, true, "DEBUG: ", "Has Lines: " + GMI_TRANSreader.HasRows);
* else
* // how to handle when no rows are returned?
* Status.Print(false, true, "DEBUG: ", "Returns No Results: " + !GMI_TRANSreader.HasRows);
*
*/ //read the SQL results into the GMIREFRecord Object and add the object to a list.
while (reader.Read())
{
//TODO work out if null are being returned fix them
METSTORERecord record = new METSTORERecord();
record.METRIC_ID = reader.GetOracleDecimal(reader.GetOrdinal("METRIC_ID")).ToInt32();
record.DATE_TIME = reader.GetOracleTimeStamp(reader.GetOrdinal("DATE_TIME")).ToString();
record.DATE_TIME = record.DATE_TIME.Remove(record.DATE_TIME.Length - 10);
record.VALUE = reader.GetOracleDecimal(reader.GetOrdinal("VALUE")).ToInt32();
DataSet.Add(record);
}
return(DataSet);
}
public void PLSQLExec(string Hostname, string Feedname, string Operation, string Username)
{
// Open the DatabaseHandler Object
DBConnectAndRunSQL DB = new DBConnectAndRunSQL();
// get SQL query config
string OraConnectionDSource = WebConfigurationManager.AppSettings["GMIDB_Conn"];
string OraUserPass = "******" + WebConfigurationManager.AppSettings["GMIDB_user"] + ";Password="******"GMIDB_pass"] + ";";
string OraConnectionString = OraUserPass + OraConnectionDSource;
string OraStoredProc = WebConfigurationManager.AppSettings["PLSQL_GMI_TRANS"];
// Set up command object
OracleCommand OraCmd = new OracleCommand();
OraCmd.CommandText = OraStoredProc;
OraCmd.CommandType = CommandType.StoredProcedure;
OraCmd.Parameters.Add("p_hostname", OracleDbType.Varchar2).Value = Hostname;
OraCmd.Parameters.Add("p_initiating_process", OracleDbType.Varchar2).Value = Feedname;
OraCmd.Parameters.Add("p_blocking", OracleDbType.Varchar2).Value = Operation;
OraCmd.Parameters.Add("p_user", OracleDbType.Varchar2).Value = Username;
// Log
Status.Print(false, true, "AUDIT: ", Username + " " + OraCmd.CommandText + " " + Operation + " " + Hostname + " " + Feedname);
// Open the connection for the command and give it the connection object
OraCmd.Connection = DB.OpenConnection(OraConnectionString);
OraCmd.ExecuteNonQuery();
OraCmd.Dispose();
}
public void PLSQLExecRetransformation(string Hostname, string Username)
{
// Open the DatabaseHandler Object
DBConnectAndRunSQL DB = new DBConnectAndRunSQL();
// get SQL query config
string OraConnectionDSource = WebConfigurationManager.AppSettings["GMIDB_Conn"];
string OraUserPass = "******" + WebConfigurationManager.AppSettings["GMIDB_user"] + ";Password="******"GMIDB_pass"] + ";";
string OraConnectionString = OraUserPass + OraConnectionDSource;
string OraStoredProc = WebConfigurationManager.AppSettings["PLSQL_RETRANSFORM"];
// Set up command object
OracleCommand OraCmd = new OracleCommand();
OraCmd.CommandText = OraStoredProc;
OraCmd.CommandType = CommandType.StoredProcedure;
//Add parameter
OracleParameter p_hostname = new OracleParameter("p_hostname", OracleDbType.Varchar2);
p_hostname.Direction = ParameterDirection.Input;
p_hostname.Value = Hostname;
OraCmd.Parameters.Add(p_hostname);
CreateLogFiles Log = new CreateLogFiles();
Status.Print(false, true, "AUDIT: ", Username + " " + OraCmd.CommandText + " " + p_hostname.Value);
// Open the connection for the command and give it the connection object
OraCmd.Connection = DB.OpenConnection(OraConnectionString);
OraCmd.ExecuteNonQuery();
OraCmd.Connection.Close();
OraCmd.Dispose();
}
public List <string> getLOV()
{
// returns the possible list of values for the GMI_STATUS (COMMSSIONED, INSTALLED, DECOMMISSIONED etc)
string OraConnectionDSource = WebConfigurationManager.AppSettings["GMIDB_Conn"];
string OraUserPass = "******" + WebConfigurationManager.AppSettings["GMIDB_user"] + ";Password="******"GMIDB_pass"] + ";";
string OraConnectionString = OraUserPass + OraConnectionDSource;
string sql_lov = WebConfigurationManager.AppSettings["sqlGetGMI_ENV_LOV"];
DBConnectAndRunSQL handler = new DBConnectAndRunSQL();
// Create a list
List <string> lstLOV = new List <string>();
lstLOV = handler.GetResultsAsList(handler.GetResultsAsReader(sql_lov, handler.OpenConnection(OraConnectionString)));
return(lstLOV);
}
public void SQLInsert(string sqlInsert)
{
// Open the DatabaseHandler Object
DBConnectAndRunSQL DB = new DBConnectAndRunSQL();
// get SQL query config
string OraConnectionDSource = WebConfigurationManager.AppSettings["GMIDB_Conn"];
string OraUserPass = "******" + WebConfigurationManager.AppSettings["GMIDB_user"] + ";Password="******"GMIDB_pass"] + ";";
string OraConnectionString = OraUserPass + OraConnectionDSource;
// Set up command object
OracleCommand cmdInsert = new OracleCommand();
cmdInsert.CommandText = sqlInsert;
// Open the connection for the command and give it the connection object
cmdInsert.Connection = DB.OpenConnection(OraConnectionString);
cmdInsert.ExecuteNonQuery();
cmdInsert.Dispose();
}
public void PRC_ARCHIVE_DEVICE(string Hostname, string Feedname, string Username)
{
// Open the DatabaseHandler Object
DBConnectAndRunSQL DB = new DBConnectAndRunSQL();
// get SQL query config
string OraConnectionDSource = WebConfigurationManager.AppSettings["GMIDB_Conn"];
string OraUserPass = "******" + WebConfigurationManager.AppSettings["GMIDB_user"] + ";Password="******"GMIDB_pass"] + ";";
string OraConnectionString = OraUserPass + OraConnectionDSource;
string OraStoredProc = WebConfigurationManager.AppSettings["PLSQLARCHIVE_GMI_REF"];
// Set up command object
OracleCommand OraCmd = new OracleCommand();
OraCmd.CommandText = OraStoredProc;
OraCmd.CommandType = CommandType.StoredProcedure;
//Add parameter
OracleParameter process_id = new OracleParameter("process_id", OracleDbType.Decimal, ParameterDirection.Input);
OracleParameter p_sc_logical_name = new OracleParameter("p_sc_logical_name", OracleDbType.Varchar2, ParameterDirection.Input);
OracleParameter p_hostname = new OracleParameter("p_hostname", OracleDbType.Varchar2, ParameterDirection.Input);
OracleParameter p_commit = new OracleParameter("p_commit", OracleDbType.Varchar2, ParameterDirection.Input);
decimal FeedNumber = Convert.ToDecimal(WebConfigurationManager.AppSettings["Feedname"]);
process_id.Value = FeedNumber;
p_sc_logical_name.Value = "";
p_hostname.Value = Hostname;
p_commit.Value = 'Y';
OraCmd.Parameters.Add(process_id);
OraCmd.Parameters.Add(p_sc_logical_name);
OraCmd.Parameters.Add(p_hostname);
OraCmd.Parameters.Add(p_commit);
CreateLogFiles Log = new CreateLogFiles();
Status.Print(false, true, "AUDIT: ", Username + " " + OraCmd.CommandText + " " + process_id.Value + " " + p_sc_logical_name.Value + " " + p_hostname.Value + " " + p_commit.Value);
// Open the connection for the command and give it the connection object
OraCmd.Connection = DB.OpenConnection(OraConnectionString);
OraCmd.ExecuteNonQuery();
OraCmd.Connection.Close();
OraCmd.Dispose();
}
public List <GMI_TRANS_BLOCKRecord> RecsAsList(string sqlSELECTQuery)
{
List <GMI_TRANS_BLOCKRecord> DataSet = new List <GMI_TRANS_BLOCKRecord>();
// Open the DatabaseHandler Object
DBConnectAndRunSQL DB = new DBConnectAndRunSQL();
// get SQL query config
string OraConnectionDSource = WebConfigurationManager.AppSettings["GMIDB_Conn"];
string OraUserPass = "******" + WebConfigurationManager.AppSettings["GMIDB_user"] + ";Password="******"GMIDB_pass"] + ";";
string OraConnectionString = OraUserPass + OraConnectionDSource;
OracleDataReader reader = DB.GetResultsAsReader(sqlSELECTQuery, DB.OpenConnection(OraConnectionString));
if (reader.HasRows)
{
Status.Print(false, true, "DEBUG: ", "Reader has Lines: " + reader.HasRows);
}
else
{
// how to handle when no rows are returned?
Status.Print(false, true, "DEBUG: ", "Returns No Results: " + !reader.HasRows);
}
//read the SQL results into the GMIREFRecord Object and add the object to a list.
while (reader.Read())
{
//TODO work out if null are being returned fix them
GMI_TRANS_BLOCKRecord record = new GMI_TRANS_BLOCKRecord();
record.HOSTNAME = reader.GetOracleString(reader.GetOrdinal("HOSTNAME"));
record.GMI_INITIATING_PROCESS = reader.GetOracleString(reader.GetOrdinal("FEED"));
record.BLOCKING = reader.GetOracleString(reader.GetOrdinal("BLOCKING"));
record.USER_UPDATED = reader.GetOracleString(reader.GetOrdinal("USER_UPDATED"));
record.DATE_UPDATED = reader.GetOracleDate(reader.GetOrdinal("DATE_UPDATED"));
DataSet.Add(record);
}
return(DataSet);
}
public void GMI_REFArchive(string Hostname, string Feedname, string Username)
{
// Open the DatabaseHandler Object
Status.Print(false, true, "AUDIT: ", "GMI_REFArchive " + Hostname + " " + Feedname + " " + Username);
DBConnectAndRunSQL DB = new DBConnectAndRunSQL();
// get SQL query config
string OraConnectionDSource = WebConfigurationManager.AppSettings["GMIDB_Conn"];
string OraUserPass = "******" + WebConfigurationManager.AppSettings["GMIDB_user"] + ";Password="******"GMIDB_pass"] + ";";
string OraConnectionString = OraUserPass + OraConnectionDSource;
string sqlcmd = WebConfigurationManager.AppSettings["PLSQLARCHIVE_GMI_REF"];
sqlcmd = sqlcmd.Replace("/*HOST_MODIFIER*/", Hostname);
sqlcmd = sqlcmd.Replace("/*FEED_MODIFIER*/", Feedname);
Status.Print(false, true, "Username: "******"GMI_REFArchive " + sqlcmd);
// Set up command object
OracleCommand cmdDelete = new OracleCommand();
cmdDelete.CommandText = sqlcmd;
// Open the connection for the command and give it the connection object
cmdDelete.Connection = DB.OpenConnection(OraConnectionString);
cmdDelete.ExecuteNonQuery();
cmdDelete.Dispose();
}
public List <GMI_REFRecord> GMI_REF_RecsAsList(string sqlQuery)
{
List <GMI_REFRecord> DataSet = new List <GMI_REFRecord>();
// Open the DatabaseHandler Object
DBConnectAndRunSQL DB = new DBConnectAndRunSQL();
// get SQL query config
string OraConnectionDSource = WebConfigurationManager.AppSettings["GMIDB_Conn"];
string OraUserPass = "******" + WebConfigurationManager.AppSettings["GMIDB_user"] + ";Password="******"GMIDB_pass"] + ";";
string OraConnectionString = OraUserPass + OraConnectionDSource;
OracleDataReader GMI_REFreader = DB.GetResultsAsReader(sqlQuery, DB.OpenConnection(OraConnectionString));
if (GMI_REFreader.HasRows)
{
Status.Print(false, true, "DEBUG: ", "Has Lines: " + GMI_REFreader.HasRows);
}
else
{
// how to handle when no rows are returned?
Status.Print(false, true, "DEBUG: ", "Returns No Results: " + !GMI_REFreader.HasRows);
}
//read the SQL results into the GMIREFRecord Object and add the object to a list.
while (GMI_REFreader.Read())
{
//TODO work out if null are being returned fix them
GMI_REFRecord record = new GMI_REFRecord();
record.GMI_INITIATING_PROCESS = GMI_REFreader.GetOracleString(GMI_REFreader.GetOrdinal("GMI_INITIATING_PROCESS"));
record.HOSTNAME = GMI_REFreader.GetOracleString(GMI_REFreader.GetOrdinal("HOSTNAME"));
record.GMI_SOURCE = GMI_REFreader.GetOracleString(GMI_REFreader.GetOrdinal("GMI_SOURCE"));
record.LAST_UPDATED = GMI_REFreader.GetOracleDate(GMI_REFreader.GetOrdinal("LAST_UPDATED"));
record.GMI_LOCATION = GMI_REFreader.GetOracleString(GMI_REFreader.GetOrdinal("GMI_LOCATION"));
record.GMI_MGMT_REGION = GMI_REFreader.GetOracleString(GMI_REFreader.GetOrdinal("GMI_MGMT_REGION"));
record.GMI_ENVIRONMENT = GMI_REFreader.GetOracleString(GMI_REFreader.GetOrdinal("GMI_ENVIRONMENT"));
record.GMI_STATUS = GMI_REFreader.GetOracleString(GMI_REFreader.GetOrdinal("GMI_STATUS"));
record.INF = GMI_REFreader.GetOracleString(GMI_REFreader.GetOrdinal("INF"));
record.CAP = GMI_REFreader.GetOracleString(GMI_REFreader.GetOrdinal("CAP"));
record.LSG = GMI_REFreader.GetOracleString(GMI_REFreader.GetOrdinal("LSG"));
record.GMI_CLIENT_NAME = GMI_REFreader.GetOracleString(GMI_REFreader.GetOrdinal("GMI_CLIENT_NAME"));
record.DEVLOGMAP = GMI_REFreader.GetOracleString(GMI_REFreader.GetOrdinal("GMI_DEV_LOG_MAP"));
record.SC_LOGICAL_NAME = GMI_REFreader.GetOracleString(GMI_REFreader.GetOrdinal("SC_LOGICAL_NAME"));
if (record.DEVLOGMAP.ToString() == "null")
{
record.DEVLOGMAP = "-";
}
;
if (record.SC_LOGICAL_NAME.ToString() == "null")
{
record.SC_LOGICAL_NAME = "-";
}
;
if (record.GMI_CLIENT_NAME.ToString() == "null")
{
record.GMI_CLIENT_NAME = "-";
}
;
DataSet.Add(record);
}
return(DataSet);
}
