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