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);
        }
Exemple #2
0
        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();
        }
Exemple #4
0
        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);
        }
Exemple #5
0
        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();
        }
Exemple #6
0
        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();
        }
Exemple #7
0
        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);
        }
Exemple #8
0
        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();
        }
Exemple #9
0
        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);
        }