Exemple #1
0
        /// <summary>
        /// This function returns all the application ids from the source table
        /// </summary>
        /// <returns></returns>
        private string GetAllApplicationId()
        {
            try
            {
                string lSourceConnString = ReadConfigFile("SourceConnectionString");
                string lQuery            = string.Empty;
                string lApplicationId    = "'";

                lQuery = " SELECT APPLICATION_ID FROM SOURCEDB.T_EXT_APPLICATION ORDER BY APPLICATION_ID ";

                OracleProvider lProvider = new OracleProvider();
                DataTable      lRecords  = lProvider.FetchDataFromDB(lQuery, lSourceConnString);

                foreach (DataRow ldr in lRecords.Rows)
                {
                    lApplicationId = lApplicationId + ldr["APPLICATION_ID"].ToString() + "','";
                }

                if (lApplicationId.Length > 1)
                {
                    lApplicationId = lApplicationId.Remove(lApplicationId.Length - 2, 2);
                }
                else
                {
                    lApplicationId = string.Empty;
                }

                return(lApplicationId);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception: " + ex.Message);
                return(string.Empty);
            }
        }
Exemple #2
0
        /// <summary>
        /// This function fetches all the application id's from source based on a condition
        /// </summary>
        /// <returns>comma separated application_id</returns>
        private string FetchAllAppIDFromSource()
        {
            try
            {
                string lSourceConnString = ReadConfigFile("SourceConnectionString");
                string lQuery            = string.Empty;
                string lApplicationId    = "'";

                lQuery = " SELECT APPLICATION_ID ";
                lQuery = lQuery + "    FROM " + ReadConfigFile("SourceUser") + ".T_EXT_APPLICATION ";
                lQuery = lQuery + "   WHERE SUBMIT_DATE IS NOT NULL ";
                lQuery = lQuery + "         AND SUBMIT_DATE > ";
                lQuery = lQuery + "                (SELECT MAX (SUBMIT_DATE) FROM " + ReadConfigFile("DestUser") + ".T_APPLICATION) ";
                lQuery = lQuery + "ORDER BY SUBMIT_DATE ASC ";

                OracleProvider lProvider = new OracleProvider();
                DataTable      lRecords  = lProvider.FetchDataFromDB(lQuery, lSourceConnString);

                foreach (DataRow ldr in lRecords.Rows)
                {
                    lApplicationId = lApplicationId + ldr["APPLICATION_ID"].ToString() + "','";
                }

                if (lApplicationId.Length > 1)
                {
                    lApplicationId = lApplicationId.Remove(lApplicationId.Length - 2, 2);
                }
                else
                {
                    lApplicationId = string.Empty;
                }

                return(lApplicationId);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception: " + ex.Message);
                return(string.Empty);
            }
        }
Exemple #3
0
        /// <summary>
        /// This event handles the click event of the button
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            string         lApplicationId = string.Empty;
            OracleProvider lProvider      = new OracleProvider();
            OracleCommand  lCommand       = new OracleCommand();
            string         lInsertString  = string.Empty;

            try
            {
                //TODO: GRANT RIGHTS

                lblStatus.Text = "Fetching Application_Id's from Source";

                //Fetch all records from source
                lApplicationId = FetchAllAppIDFromSource();

                if (lApplicationId == string.Empty)
                {
                    DialogResult ldr = MessageBox.Show("No SUBMIT_DATE found in SOURCE Schema.\nCopy all the data from Source to destination ?", "Error", MessageBoxButtons.YesNo);

                    if (ldr == DialogResult.Yes)
                    {
                        lApplicationId = GetAllApplicationId();
                    }
                    else
                    {
                        // do nothing
                        return;
                    }
                }

                //Open connection and begin transaction
                lCommand = lProvider.OpenConnectionAndBeginTransaction();

                lblStatus.Text = "Copying data into T_APPLICATION Target Table.";

                //Get T_APPLICATION insert query
                lInsertString = GetQueryForTarget(TableName.T_APPLICATION, lApplicationId);

                if (lProvider.ExecuteNonQueryTrans(lCommand, lInsertString) > 0)
                {
                    lblStatus.Text = "Copying data into T_APPLICATION_ACTIVITIES Target Table.";

                    //Get T_APPLICATION_ACTIVITIES insert query
                    lInsertString = GetQueryForTarget(TableName.T_APPLICATION_ACTIVITIES, lApplicationId);

                    lProvider.ExecuteNonQueryTrans(lCommand, lInsertString);

                    lblStatus.Text = "Copying data into T_APPLICATION_FINANCE Target Table.";

                    //Get T_APPLICATION_FINANCE insert query
                    lInsertString = GetQueryForTarget(TableName.T_APPLICATION_FINANCE, lApplicationId);

                    lProvider.ExecuteNonQueryTrans(lCommand, lInsertString);

                    lblStatus.Text = "Copying data into T_BOARD_DIRECTORS Target Table.";

                    //Get T_BOARD_DIRECTORS insert query
                    lInsertString = GetQueryForTarget(TableName.T_BOARD_DIRECTORS, lApplicationId);

                    lProvider.ExecuteNonQueryTrans(lCommand, lInsertString);

                    //Commit the transaction
                    lProvider.CommitTransaction(lCommand);

                    lblStatus.Text = "Success!";
                    MessageBox.Show("Data Copied to Target Schema!", "Success");
                }
                else
                {
                    lProvider.RollBackTransaction(lCommand);
                }
            }
            catch (Exception ex)
            {
                lProvider.RollBackTransaction(lCommand);
                MessageBox.Show("EXCEPTION: " + ex.Message);
            }
        }