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; }
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; }