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