private DataTable GetDataFromSourceDBTable(Database sourceDb)
 {
     try
     {
         return(OracleDB_DAL.SelectDataFromTable(sourceDb, SourceTableSelectQuery, SourceTableSelectFilters));
     }
     catch (Exception ex)
     {
         _tableCopierLog.Error("Error while getting data from source table for Table Copier ID : " + TableCopierID, ex);
         return(null);
     }
 }
        public void FetchDataAndImport()
        {
            try
            {
                Database sourceDb = DatabaseFactory.CreateDatabase(SourceDbConnectionStringName);

                DataTable sourceDataTable = GetDataFromSourceDBTable(sourceDb);

                if (sourceDataTable == null || sourceDataTable.Rows.Count == 0)
                {
                    _tableCopierLog.Info(" No data fetched from source table for Table Copier ID : " + TableCopierID);
                    return;
                }
                else
                {
                    _tableCopierLog.Info(" Data fetched successfully from source table for Table Copier ID : " + TableCopierID);

                    InsertOrUpdateDataToDestiantionDBTable(ref sourceDataTable);

                    _tableCopierLog.Info(" Data updated successfully in destination table for Table Copier ID : " + TableCopierID);

                    DataRow[] insertSuccessRows = sourceDataTable.Select(_successColumnName + "=" + true);

                    switch (CopyType)
                    {
                    case TableCopyType.StatusBased:
                        //Updating the source table with a flag to indicate that these records are already migrated so that we dont create duplicate records in the next iteration
                        foreach (var row in insertSuccessRows)
                        {
                            OracleDB_DAL.UpdateValuesForRecord(sourceDb, null, SourceTableName, SourceTableUpdateFilters, row);
                        }
                        break;

                    case TableCopyType.SequenceBased:
                        break;

                    case TableCopyType.TimeStampBased:
                        var    te           = insertSuccessRows.OrderByDescending(z => int.Parse(z.Field <string>("FDATEVAL"))).ThenByDescending(z => int.Parse(z.Field <string>("FTIMEVAL")));
                        string lastSyncDate = te.First().Field <string>("FDATEVAL");
                        string lastSyncTime = te.First().Field <string>("FTIMEVAL");
                        MongoDB_DAL.UpdateLastSyncDateTime(TableCopierID, lastSyncDate, lastSyncTime);
                        break;

                    default:
                        break;
                    }
                }
            }
            catch (Exception ex)
            {
                _tableCopierLog.Error(" Error Occured While Copying data for Table Copier ID : " + TableCopierID, ex);
            }
        }
        private void InsertOrUpdateDataToDestiantionDBTable(ref DataTable sourceDataTable)
        {
            IDataReader dataReaderInfo = null;

            try
            {
                Database destinationDB = DatabaseFactory.CreateDatabase(DestinationDbConnectionStringName);

                sourceDataTable.Columns.Add(_successColumnName, typeof(bool));

                foreach (DataRow row in sourceDataTable.Rows)
                {
                    dynamic        columnValueInfo               = null;
                    List <dynamic> columnValueInfoList           = new List <dynamic>();
                    List <dynamic> primaryKeyColumnValueInfoList = new List <dynamic>();

                    foreach (DataColumn col in sourceDataTable.Columns)
                    {
                        if (col.ColumnName.ToUpper() != "ROWID" && col.ColumnName.ToUpper() != "FDATEVAL" && col.ColumnName.ToUpper() != "FTIMEVAL" && col.ColumnName.ToUpper() != _successColumnName.ToUpper())
                        {
                            columnValueInfo       = new ExpandoObject();
                            columnValueInfo.Key   = col.ColumnName;
                            columnValueInfo.Value = row[col.ColumnName];

                            columnValueInfoList.Add(columnValueInfo);

                            if (UpdateDestinationTableIfDataAlreadyExists == "Y" && DestinationTablePrimaryKeyFieldList.Contains(col.ColumnName))
                            {
                                primaryKeyColumnValueInfoList.Add(columnValueInfo);
                            }
                        }
                    }

                    if (UpdateDestinationTableIfDataAlreadyExists == "Y")
                    {
                        if (primaryKeyColumnValueInfoList.Count > 0)
                        {
                            List <string> selectCoulmnList = new List <string>();
                            if (DestinationTableUpdateConditions != null && DestinationTableUpdateConditions.Count > 0)
                            {
                                selectCoulmnList = DestinationTableUpdateConditions.Select(l => l.ColumnName).ToList();
                            }

                            DataTable existingInfoTable = OracleDB_DAL.CheckReordExistsInTable(destinationDB, null, DesntiationTableName, primaryKeyColumnValueInfoList, selectCoulmnList);
                            if (existingInfoTable != null && existingInfoTable.Rows.Count > 0)
                            {
                                DataRow existingInfoRow = existingInfoTable.Rows[0];
                                bool    updateRecord    = true;

                                if (DestinationTableUpdateConditions != null && DestinationTableUpdateConditions.Count > 0)
                                {
                                    foreach (ColumnValueInfo info in DestinationTableUpdateConditions)
                                    {
                                        if (info.Value.ToString().Trim() == existingInfoRow[info.ColumnName].ToString().Trim())
                                        {
                                            updateRecord = true;
                                        }
                                        else
                                        {
                                            updateRecord = false;
                                            break;
                                        }
                                    }
                                }

                                if (updateRecord)
                                {
                                    //OracleDB_DAL.DeleteRecordFromTable(destinationDB, transaction, DesntiationTableName, primaryKeyColumnValueInfoList);
                                    foreach (var pkColumnValueInfo in primaryKeyColumnValueInfoList)
                                    {
                                        foreach (var colValueInfo in columnValueInfoList)
                                        {
                                            if (columnValueInfo.Key == pkColumnValueInfo.Key)
                                            {
                                                columnValueInfoList.Remove(colValueInfo);
                                                break;
                                            }
                                        }
                                    }

                                    if (OracleDB_DAL.UpdateRecordInTable(destinationDB, null, DesntiationTableName, columnValueInfoList, primaryKeyColumnValueInfoList) >= 1)
                                    {
                                        row[_successColumnName] = true;
                                    }
                                    else
                                    {
                                        row[_successColumnName] = false;
                                    }
                                }
                            }
                            else
                            {
                                if (OracleDB_DAL.InsertRecordToTable(destinationDB, null, DesntiationTableName, columnValueInfoList) == 1)
                                {
                                    row[_successColumnName] = true;
                                }
                                else
                                {
                                    row[_successColumnName] = false;
                                }
                            }
                        }
                    }
                    else
                    {
                        if (OracleDB_DAL.InsertRecordToTable(destinationDB, null, DesntiationTableName, columnValueInfoList) == 1)
                        {
                            row[_successColumnName] = true;
                        }
                        else
                        {
                            row[_successColumnName] = false;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                if (dataReaderInfo != null && !dataReaderInfo.IsClosed)
                {
                    dataReaderInfo.Close();
                }

                _tableCopierLog.Error(" Error while inserting/updating record in destination db table for Table Copier ID : " + TableCopierID, ex);
            }
        }