public Business.Rules.MethodResult AddMissingColumns(MigrationStatus migrationStatus)
        {
            Business.Rules.MethodResult result = new Business.Rules.MethodResult();
            result.Message = "Errored on: ";
            Business.Rules.MethodResult missingColumnResult = this.CompareTable(migrationStatus);
            if(missingColumnResult.Success == false)
            {
                string columns = missingColumnResult.Message.Replace("Missing columns: ", string.Empty);
                string[] missingColumns = columns.Split(new string[] { ", " }, StringSplitOptions.RemoveEmptyEntries);
                foreach(string columnName in missingColumns)
                {
                    foreach(PropertyInfo property in migrationStatus.PersistentProperties)
                    {
                        if(property.Name == columnName)
                        {
                            string columnDefinition = this.GetDataColumnDefinition(migrationStatus.TableName, property);
                            string columnDataType = columnDefinition.Replace(columnName + " ", string.Empty);
                            columnDataType = columnDataType.Substring(0, columnDataType.Length - 2);
                            string command = this.GetAddColumnCommand(migrationStatus.TableName, columnName, columnDataType);
                            Business.Rules.MethodResult columnResult = this.RunCommand(command);
                            if(columnResult.Success == false)
                            {
                                result.Success = false;
                                result.Message += columnName + ", ";
                            }
                            break;
                        }
                    }
                }
            }

            migrationStatus.HasAllColumns = result.Success;
            return result;
        }
        public Business.Rules.MethodResult CompareTables(MigrationStatus migrationStatus)
        {
            Business.Rules.MethodResult overallResult = new Business.Rules.MethodResult();
            if (this.TablesAreOkToUse(migrationStatus) == true)
            {
                List<string> keys = this.GetCompareDataKeyList(migrationStatus);
                List<string> updateCommands = new List<string>();
                overallResult = this.CompareData(migrationStatus, keys, updateCommands);
                if (updateCommands.Count > 0)
                {
                    Business.Rules.MethodResult result = this.Synchronize(updateCommands);
                    overallResult.Message += result.Message;
                    if(result.Success == false)
                    {
                        overallResult.Success = false;
                    }

                    List<string> checkCommands = new List<string>();
                    Business.Rules.MethodResult checkResult = this.CompareData(migrationStatus, keys, checkCommands);
                    if (checkCommands.Count > 0)
                    {
                        overallResult.Success = false;
                        overallResult.Message += "Update failed on " + checkCommands.Count.ToString();
                    }
                }
            }
            else
            {
                overallResult.Success = false;
                overallResult.Message += "Table or column issue with " + migrationStatus.TableName;
            }
            return overallResult;
        }
        public Business.Rules.MethodResult CompareTable(MigrationStatus migrationStatus)
        {
            Business.Rules.MethodResult result = new Business.Rules.MethodResult();
            result.Message = "Missing columns: ";
            List<string> columnNames = this.RunTableQuery(migrationStatus.TableName);
            foreach(PropertyInfo property in migrationStatus.PersistentProperties)
            {
                bool found = false;
                foreach(string columnName in columnNames)
                {
                    if(columnName == property.Name)
                    {
                        found = true;
                        break;
                    }
                }

                if(found == false)
                {
                    result.Success = false;
                    result.Message += property.Name + ", ";
                }
            }
            return result;
        }
 private bool TablesAreOkToUse(MigrationStatus migrationStatus)
 {
     bool result = false;
     this.GetStatus(migrationStatus);
     if (migrationStatus.HasTable && migrationStatus.HasAllColumns && migrationStatus.HasDBTS &&
         migrationStatus.HasTimestampColumn && migrationStatus.HasTransferredColumn)
     {
         result = true;
     }
     return result;
 }
        private Business.Rules.MethodResult LoadData(MigrationStatus migrationStatus, string keyString)
        {
            Business.Rules.MethodResult methodResult = new Business.Rules.MethodResult();

            DataSet dataSet = new DataSet();
            dataSet.EnforceConstraints = false;
            DataTable dataTable = new DataTable();
            dataSet.Tables.Add(dataTable);

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = this.GetSelectStatement(migrationStatus, keyString);
            cmd.CommandType = CommandType.Text;
            using (SqlConnection cn = new SqlConnection(YellowstonePathology.Properties.Settings.Default.CurrentConnectionString))
            {
                cn.Open();
                cmd.Connection = cn;
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    dataTable.Load(dr, LoadOption.OverwriteChanges);
                }
            }

            DataTableReader dataTableReader = new DataTableReader(dataTable);
            while (dataTableReader.Read())
            {
                string commandText = this.GetInsertStatement(migrationStatus.TableName, migrationStatus.PersistentProperties, dataTableReader);
                Business.Rules.MethodResult result = this.RunCommand(commandText);
                if (result.Success == false)
                {
                    methodResult.Message += "Error in Loading Data " + commandText + Environment.NewLine;
                    methodResult.Success = false;
                    this.SaveError(migrationStatus.TableName, commandText);
                }
            }

            this.SetTransfered(migrationStatus.TableName, migrationStatus.KeyFieldName, keyString);
            return methodResult;
        }
        private Business.Rules.MethodResult CompareData(MigrationStatus migrationStatus, List<string>keys, List<string> updateCommands)
        {
            Business.Rules.MethodResult overallResult = new Business.Rules.MethodResult();
            bool needsTic = migrationStatus.PersistentProperties[0].PropertyType == typeof(string) ? true : false;
            string compareSelect = this.GetCompareSelect(migrationStatus);

            foreach (object key in keys)
            {
                string keyString = key.ToString();
                if(needsTic == true)
                {
                    keyString = "'" + keyString + "'";
                }

                DataTable sqlServerDataTable = this.GetSqlServerData(compareSelect, keyString);
                DataTable mySqlDataTable = this.GetMySqlData(compareSelect, keyString);

                if (mySqlDataTable.Rows.Count == 0)
                {
                    overallResult.Success = false;
                    overallResult.Message += "Missing " + migrationStatus.KeyFieldName + " = " + keyString;
                    this.SaveError(migrationStatus.TableName, "Update " + migrationStatus.TableName + " set Transferred = 0 where " + migrationStatus.KeyFieldName + " = " + keyString);
                    continue;
                }

                if (sqlServerDataTable.Rows[0].ItemArray.SequenceEqual(mySqlDataTable.Rows[0].ItemArray) == false)
                {
                    DataRowComparer dataRowComparer = new DataRowComparer(migrationStatus);
                    DataTableReader sqlServerDataTableReader = new DataTableReader(sqlServerDataTable);
                    sqlServerDataTableReader.Read();
                    DataTableReader mySqlDataTableReader = new DataTableReader(mySqlDataTable);
                    mySqlDataTableReader.Read();
                    Business.Rules.MethodResult result = dataRowComparer.Compare(sqlServerDataTableReader, mySqlDataTableReader);
                    if(result.Success == false)
                    {
                        updateCommands.Add(result.Message);
                    }
                }
            }
            return overallResult;
        }
        public void GetStatus(MigrationStatus migrationStatus)
        {
            migrationStatus.HasTimestampColumn = Business.Mongo.Gateway.HasSQLTimestamp(migrationStatus.TableName);
            migrationStatus.HasTransferredColumn = this.TableHasTransferColumn(migrationStatus.TableName);
            bool hasDBTS = Business.Mongo.Gateway.HasTransferDBTSAttribute(migrationStatus.TableName);
            bool hasTSA = Business.Mongo.Gateway.HasTransferTransferStraightAcrossAttribute(migrationStatus.TableName);
            migrationStatus.HasDBTS = hasDBTS && hasTSA;

            migrationStatus.HasTable = this.HasMySqlTable(migrationStatus.TableName);
            if (migrationStatus.HasTransferredColumn && migrationStatus.HasTable)
            {
                migrationStatus.UnLoadedDataCount = this.GetUnloadedDataCount(migrationStatus.TableName);
                migrationStatus.OutOfSyncCount = this.GetOutOfSyncCount(migrationStatus.TableName);
                migrationStatus.SqlServerTransferredCount = this.GetSqlServerRowCount(migrationStatus);
                migrationStatus.MySqlRowCount = this.GetMySqlRowCount(migrationStatus);
            }
            else
            {
                migrationStatus.UnLoadedDataCount = this.GetDataCount(migrationStatus.TableName);
                migrationStatus.OutOfSyncCount = migrationStatus.UnLoadedDataCount;
            }

            Business.Rules.MethodResult missingColumnResult = CompareTable(migrationStatus);
            migrationStatus.HasAllColumns = missingColumnResult.Success;
        }
        private string GetSelectStatement( MigrationStatus migrationStatus, string keys)
        {
            StringBuilder result = new StringBuilder();
            result.Append("Select ");

            foreach (PropertyInfo property in migrationStatus.PersistentProperties)
            {
                result.Append("[");
                result.Append(property.Name);
                result.Append("], ");
            }

            result = result.Remove(result.Length - 2, 2);
            result.Append(" from ");
            result.Append(migrationStatus.TableName);
            result.Append(" Where (Transferred is null or Transferred = 0) and ");
            result.Append(migrationStatus.KeyFieldName);
            result.Append(" in (");
            result.Append(keys);
            result.Append(") Order By ");
            result.Append(migrationStatus.KeyFieldName);
            return result.ToString();
        }
 private int GetSqlServerRowCount(MigrationStatus migrationStatus)
 {
     int result = 0;
     string key = migrationStatus.PersistentProperties[0].Name;
     SqlCommand cmd = new SqlCommand("Select count(*) from " + migrationStatus.TableName + " where Transferred = 1");
     using (SqlConnection cn = new SqlConnection(YellowstonePathology.Properties.Settings.Default.CurrentConnectionString))
     {
         cn.Open();
         cmd.Connection = cn;
         using (SqlDataReader dr = cmd.ExecuteReader())
         {
             while (dr.Read())
             {
                 result = (int)dr[0];
             }
         }
     }
     return result;
 }
        public Business.Rules.MethodResult BuildTable(MigrationStatus migrationSatus)
        {
            Business.Rules.MethodResult methodResult = new Business.Rules.MethodResult();
            methodResult.Success = false;

            if (migrationSatus.PersistentProperties.Count > 0)
            {
                string createTableCommand = this.GetCreateTableCommand(migrationSatus.TableName, migrationSatus.PersistentProperties);
                string createPrimaryKeyCommand = this.GetCreatePrimaryKeyCommand(migrationSatus.TableName, migrationSatus.KeyFieldName);
                string createTimeStampColumn = this.GetAddColumnCommand(migrationSatus.TableName, "Timestamp", "Timestamp");

                Business.Rules.MethodResult result = this.RunCommand(createTableCommand);
                if (result.Success == true)
                {
                    result = this.RunCommand(createPrimaryKeyCommand);
                }
                else
                {
                    methodResult.Success = false;
                    methodResult.Message += result.Message;
                }

                if (result.Success == true)
                {
                    result = this.RunCommand(createTimeStampColumn);
                }
                else
                {
                    methodResult.Success = false;
                    methodResult.Message += result.Message;
                }

                if (result.Success == false)
                {
                    methodResult.Success = false;
                    methodResult.Message += result.Message;
                }
            }

            return methodResult;
        }
        private int GetMySqlRowCount(MigrationStatus migrationStatus)
        {
            int result = 0;
            using (MySqlConnection cn = new MySqlConnection(ConnectionString))
            {
                cn.Open();
                MySqlCommand cmd = new MySqlCommand("Select count(*) from " + migrationStatus.TableName);
                cmd.Connection = cn;

                using (MySqlDataReader msdr = cmd.ExecuteReader())
                {
                    while (msdr.Read())
                    {
                        result = ((int)(long)msdr[0]);
                    }
                }
            }
            return result;
        }
 private List<string> GetDailyLoadDataKeys(MigrationStatus migrationStatus)
 {
     List<string> result = new List<string>();
     SqlCommand cmd = new SqlCommand("Select " + migrationStatus.KeyFieldName + " from " + migrationStatus.TableName + " where " +
         "(Transferred = 0 or Transferred is null) order by 1");
     using (SqlConnection cn = new SqlConnection(YellowstonePathology.Properties.Settings.Default.CurrentConnectionString))
     {
         cn.Open();
         cmd.Connection = cn;
         using (SqlDataReader dr = cmd.ExecuteReader())
         {
             while (dr.Read())
             {
                 result.Add(dr[0].ToString());
             }
         }
     }
     return result;
 }
        private string GetCompareSelect(MigrationStatus migrationStatus)
        {
            string result = "Select ";
            foreach (PropertyInfo property in migrationStatus.PersistentProperties)
            {
                result = result + "[" + property.Name + "], ";
            }

            result = result.Remove(result.Length - 2, 2);

            result = result + " from " + migrationStatus.TableName + " Where [" +  migrationStatus.KeyFieldName + "] = ";
            return result;
        }
 private List<string> GetBulkLoadDataKeys(MigrationStatus migrationStatus, string countToSelect)
 {
     List<string> result = new List<string>();
     SqlCommand cmd = new SqlCommand("Select top (" + countToSelect + ") " + migrationStatus.KeyFieldName + " from " + migrationStatus.TableName + " where " +
         "ReportNo not like '16%' and " +
         //"orderdate < '10/1/2016' and " +
         "(Transferred = 0 or Transferred is null) order by 1");
     using (SqlConnection cn = new SqlConnection(YellowstonePathology.Properties.Settings.Default.CurrentConnectionString))
     {
         cn.Open();
         cmd.Connection = cn;
         using (SqlDataReader dr = cmd.ExecuteReader())
         {
             while (dr.Read())
             {
                 result.Add(dr[0].ToString());
             }
         }
     }
     return result;
 }
        public Business.Rules.MethodResult DailyLoadData(MigrationStatus migrationStatus)
        {
            Business.Rules.MethodResult methodResult = new Business.Rules.MethodResult();
            if (this.TablesAreOkToUse(migrationStatus) == true)
            {
                if (migrationStatus.UnLoadedDataCount > 0)
                {
                    List<string> keys = this.GetDailyLoadDataKeys(migrationStatus);
                    string keyString = this.KeyStringFromList(migrationStatus, keys);
                    methodResult = this.LoadData(migrationStatus, keyString);
                    YellowstonePathology.Business.Mongo.Gateway.SetTransferDBTS(migrationStatus.TableName);

                    List<string> checkCommands = new List<string>();
                    Business.Rules.MethodResult checkResult = this.CompareData(migrationStatus, keys, checkCommands);
                    if (checkCommands.Count > 0)
                    {
                        methodResult.Success = false;
                        methodResult.Message += "Update failed on " + checkCommands.Count.ToString();
                    }
                }
            }
            return methodResult;
        }
 private List<string> GetSyncDataKeyList(MigrationStatus migrationStatus)
 {
     List<string> result = new List<string>();
     SqlCommand cmd = new SqlCommand("Select " + migrationStatus.KeyFieldName + " from " + migrationStatus.TableName + " where Transferred = 1 " +
         "and [TimeStamp] > (SELECT convert(int, ep.value) FROM sys.extended_properties AS ep " +
         "INNER JOIN sys.tables AS t ON ep.major_id = t.object_id " +
         "INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id " +
         "WHERE class = 1 and t.Name = '" + migrationStatus.TableName + "' and c.Name = 'TimeStamp' and ep.Name = 'TransferDBTS') order by 1");
     using (SqlConnection cn = new SqlConnection(YellowstonePathology.Properties.Settings.Default.CurrentConnectionString))
     {
         cn.Open();
         cmd.Connection = cn;
         using (SqlDataReader dr = cmd.ExecuteReader())
         {
             while (dr.Read())
             {
                 result.Add(dr[0].ToString());
             }
         }
     }
     return result;
 }
        public Business.Rules.MethodResult DailySync(MigrationStatus migrationStatus)
        {
            Business.Rules.MethodResult overallResult = new Business.Rules.MethodResult();
            if (this.TablesAreOkToUse(migrationStatus) == true)
            {
                overallResult = this.SynchronizeData(migrationStatus);

                Business.Rules.MethodResult loadresult = this.DailyLoadData(migrationStatus);
                overallResult.Message += loadresult.Message;
                if(loadresult.Success == false)
                {
                    overallResult.Success = false;
                }
            }
            else
            {
                overallResult.Success = false;
                overallResult.Message += "Table or column issue with " + migrationStatus.TableName;
            }
            return overallResult;
        }
        public Business.Rules.MethodResult BulkLoadData(MigrationStatus migrationStatus, int numberOfObjectsToMove, int whenToStop)
        {
            Business.Rules.MethodResult methodResult = new Business.Rules.MethodResult();
            if (this.TablesAreOkToUse(migrationStatus) == true)
            {
                if (migrationStatus.UnLoadedDataCount > 0)
                {

                    string countToMove = numberOfObjectsToMove.ToString();

                    int repetitions = this.GetTableRepeatCount(migrationStatus.TableName, countToMove);
                    if (repetitions > 0)
                    {
                        if (whenToStop <= repetitions)
                        {
                            repetitions = whenToStop;
                        }
                    }

                    for (int idx = 0; idx < repetitions; idx++)
                    {
                        List<string> keys = this.GetBulkLoadDataKeys(migrationStatus, countToMove);
                        if(keys.Count == 0)
                        {
                            break;
                        }
                        string keyString = this.KeyStringFromList(migrationStatus, keys);
                        methodResult = this.LoadData(migrationStatus, keyString);

                    }

                    YellowstonePathology.Business.Mongo.Gateway.SetTransferDBTS(migrationStatus.TableName);
                }
            }
            return methodResult;
        }
 public Business.Rules.MethodResult SynchronizeData(MigrationStatus migrationStatus)
 {
     Business.Rules.MethodResult overallResult = new Business.Rules.MethodResult();
     if (this.TablesAreOkToUse(migrationStatus) == true)
     {
         if (migrationStatus.OutOfSyncCount > 0)
         {
             List<string> updateCommands = new List<string>();
             List<string> keys = this.GetSyncDataKeyList(migrationStatus);
             Business.Rules.MethodResult result = this.CompareData(migrationStatus, keys, updateCommands);
             if (result.Success == false)
             {
                 overallResult.Success = false;
                 overallResult.Message += result.Message;
             }
             if (updateCommands.Count > 0)
             {
                 result = this.Synchronize(updateCommands);
                 overallResult.Message += result.Message;
                 if (result.Success == false)
                 {
                     overallResult.Success = false;
                 }
             }
             List<string> checkCommands = new List<string>();
             Business.Rules.MethodResult checkResult = this.CompareData(migrationStatus, keys, checkCommands);
             if (checkCommands.Count > 0)
             {
                 overallResult.Success = false;
                 overallResult.Message += "Update failed on " + checkCommands.Count.ToString();
             }
             YellowstonePathology.Business.Mongo.Gateway.SetTransferDBTS(migrationStatus.TableName);
         }
     }
     return overallResult;
 }
        private string KeyStringFromList(MigrationStatus migrationStatus, List<string> keys)
        {
            bool needsTic = migrationStatus.KeyFieldProperty.PropertyType == typeof(string) ? true : false;
            string result = string.Empty;

            foreach (string key in keys)
            {
                if (needsTic == true) result += "'" + key + "', ";
                else result += key + ", ";
            }
            result = result.Remove(result.Length - 2, 2);
            return result;
        }
Beispiel #21
0
 public DataRowComparer(MigrationStatus migrationStatus)
 {
     this.m_MigrationStatus = migrationStatus;
 }
 public bool BuildPersistentDataColumnProperty(MigrationStatus migrationStatus, string[] lines)
 {
     bool result = false;
     for (int idx = 0; idx < lines.Length; idx++)
     {
         if (lines[idx].Contains("[PersistentProperty") ||
             lines[idx].Contains("[PersistentDocumentIdProperty") ||
             lines[idx].Contains("[PersistentPrimaryKeyProperty"))
         {
             foreach (PropertyInfo property in migrationStatus.PersistentProperties)
             {
                 string matchstring = " " + property.Name;
                 int ndx = lines[idx + 1].LastIndexOf(" ");
                 string lineString = lines[idx + 1].Substring(ndx).TrimEnd();
                 if (lineString == matchstring)
                 {
                     int start = lines[idx].IndexOf("[Persistent");
                     string startingString = lines[idx].Substring(0, start);
                     StringBuilder dataDefinition = new StringBuilder();
                     dataDefinition.AppendLine(lines[idx]);
                     dataDefinition.Append(startingString);
                     this.GetDataColumnProperties(migrationStatus.TableName, property, dataDefinition);
                     lines[idx] = dataDefinition.ToString();
                     result = true;
                     break;
                 }
             }
         }
     }
     return result;
 }