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