Exemple #1
0
        public DataTable GetDataTable(String sql)
        {
            DataTable dataTable = null;

            OpenOracleConnection();
            try
            {
                string      name        = connection.ServiceName;
                CommonUntil commonUntil = new CommonUntil();

                OracleCommand command = new OracleCommand(sql, this.connection);
                command.Connection  = connection;
                command.CommandText = sql;
                command.CommandType = CommandType.Text;

                OracleDataReader oracleDataReader = command.ExecuteReader();

                dataTable = new DataTable();
                dataTable.Load(oracleDataReader);
            }
            catch (Exception ex)
            {
                Console.WriteLine("OraDBConductor Excetpion" + ex.Message);
                PostalService postalService = new PostalService();
                postalService.SendMail("*****@*****.**", "Intermediate Data Copier Alert", ex.Message);
            }
            finally
            {
                CloseOracleConnection();
            }


            return(dataTable);
        }
        static void Main(string[] args)
        {
            ProjectStringPool projectStringPool = new ProjectStringPool();
            OracleDBConductor oracleDBConductor = new OracleDBConductor();
            DataTable         dataTable;
            SqlEi_OmeObject   sqlEi_OmeObject;
            OraTc_OmeObject   oraTc_OmeObject;

            List <SqlEi_OmeObject> goodSQLServerEi_OmeObjects;
            List <SqlEi_OmeObject> insertedEi_OmeObjects;

            List <OraTc_OmeObject> updatedTc_OmeObjects;

            string oraResult;
            int    dataCount;
            string actionResult;

            //int deletedRows;

            try
            {
                string oraSQLString = projectStringPool.getSelectEi_OmeDataSQL();

                dataTable = oracleDBConductor.GetDataTable(oraSQLString);
                oraResult = "";

                goodSQLServerEi_OmeObjects = new List <SqlEi_OmeObject>();

                if (dataTable.Rows.Count > 0)
                {
                    foreach (DataRow row in dataTable.Rows)
                    {
                        sqlEi_OmeObject = new SqlEi_OmeObject();
                        oraTc_OmeObject = new OraTc_OmeObject();
                        oraResult       = "Y";

                        try
                        {
                            sqlEi_OmeObject.Ei_ome01 = row[dataTable.Columns["ome01"]].ToString();
                            sqlEi_OmeObject.Ei_ome02 = (row[dataTable.Columns["ome02"]]) == DBNull.Value ? "" :
                                                       Convert.ToDateTime(row[dataTable.Columns["ome02"]]).ToString("yyyy-MM-dd");
                            sqlEi_OmeObject.Ei_ome04 = row[dataTable.Columns["ome04"]].ToString();
                            sqlEi_OmeObject.Ei_ome05 = row[dataTable.Columns["ome042"]].ToString();
                            sqlEi_OmeObject.Ei_ome06 = row[dataTable.Columns["ome043"]].ToString();
                            sqlEi_OmeObject.Ei_ome07 = row[dataTable.Columns["ome044"]].ToString();
                            sqlEi_OmeObject.Ei_ome08 = row[dataTable.Columns["ome16"]].ToString();
                            sqlEi_OmeObject.Ei_ome09 = row[dataTable.Columns["ome21"]].ToString();
                            sqlEi_OmeObject.Ei_ome10 = (row[dataTable.Columns["ome211"]]) == DBNull.Value ? 0 :
                                                       Convert.ToDecimal(row[dataTable.Columns["ome211"]]);
                            sqlEi_OmeObject.Ei_ome11 = (row[dataTable.Columns["ome59"]]) == DBNull.Value ? 0 :
                                                       Convert.ToDecimal(row[dataTable.Columns["ome59"]]);
                            sqlEi_OmeObject.Ei_ome12 = (row[dataTable.Columns["ome59x"]]) == DBNull.Value ? 0 :
                                                       Convert.ToDecimal(row[dataTable.Columns["ome59x"]]);

                            sqlEi_OmeObject.Ei_ome13 = (row[dataTable.Columns["ome59t"]]) == DBNull.Value ? 0 :
                                                       Convert.ToDecimal(row[dataTable.Columns["ome59t"]]);
                        }
                        catch (Exception ex)
                        {
                            oraResult = "N";
                            Console.WriteLine("Foreach Exception:" + ex.Message);
                            PostalService postalService = new PostalService();
                            postalService.SendMail("*****@*****.**", "Intermediate Data Copier Alert", ex.Message);
                            break;
                        }
                        finally
                        {
                            if (oraResult == "Y")
                            {
                                SQLServerDataSecuricor dataSecuricor = new SQLServerDataSecuricor();
                                dataCount = 0;
                                dataCount = dataSecuricor.SelectEi_OmeRowCounts(sqlEi_OmeObject.Ei_ome01);
                                if (dataCount == 0)
                                {
                                    goodSQLServerEi_OmeObjects.Add(sqlEi_OmeObject);
                                }
                            }
                        } //End of try-catch-finally
                         //}//End of foreach
                    }     //End of if else
                    actionResult = "FAILED";

                    insertedEi_OmeObjects = new List <SqlEi_OmeObject>();
                    updatedTc_OmeObjects  = new List <OraTc_OmeObject>();

                    if (goodSQLServerEi_OmeObjects.Count > 0)
                    {
                        foreach (SqlEi_OmeObject ei_InsOme in goodSQLServerEi_OmeObjects)
                        {
                            SQLServerConductor sqlServerConductor = new SQLServerConductor();
                            actionResult = sqlServerConductor.InsertEi_OmeSQLServer(ei_InsOme);
                            if (actionResult == "SUCCESS")
                            {
                                insertedEi_OmeObjects.Add(ei_InsOme);
                            }
                        }

                        foreach (SqlEi_OmeObject ei_ome in goodSQLServerEi_OmeObjects)
                        {
                            oracleDBConductor.UpdateTc_OmeDB(ei_ome.Ei_ome01);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write("Exception : " + ex.Message);
                return;
            }
        }