public void executOnServer(string connectionsStr, string sqlScript) { try{ using (SqlConnection sqlConnect = new SqlConnection(connectionsStr)){ if (sqlConnect.State == ConnectionState.Closed) { sqlConnect.Open(); Console.WriteLine("Connection Open"); } Console.WriteLine("Running: \n" + sqlScript); SyncPCTablesLibrary.writeToLog("Running: \n" + sqlScript); SqlCommand command = new SqlCommand(sqlScript, sqlConnect); command.CommandTimeout = 0; command.ExecuteNonQuery(); Console.WriteLine("Script complete"); command.Dispose(); if (sqlConnect.State == ConnectionState.Open) { sqlConnect.Close(); } } } catch (Exception e) { Console.WriteLine("Error running script: " + sqlScript); Console.WriteLine(e.ToString() + "\n" + e.Message); Console.WriteLine(e.StackTrace); } }
public SyncPCTablesProcess(string config) { new SyncPCTablesLibrary(config); string nuConfig = config.Contains("\\\\")? config:config.Replace("\\", "\\\\"); if (File.Exists(nuConfig)) { if (SyncPCTablesLibrary.pcSyncType == SyncPCTablesLibrary.USE_PC_CARD_TYPE) { string tableListScript = File.ReadAllText(SyncPCTablesLibrary.pcTableFetchScript); tableListScript = tableListScript.Replace("PC_TABLE_NAME", SyncPCTablesLibrary.pcTableType); DataTable tempTab = SyncPCTablesLibrary.getDataFromSQL(tableListScript, SyncPCTablesLibrary.sourceConnectionProps.getConnectionString()); foreach (DataRow row in tempTab.Rows) { foreach (DataColumn column in tempTab.Columns) { destinationTableList.Add(row[column].ToString()); } } } else if (SyncPCTablesLibrary.pcSyncType == SyncPCTablesLibrary.USE_PC_CARD_LIST) { destinationTableList = SyncPCTablesLibrary.pcTableSyncList; } Console.WriteLine("Starting synchronization of the following tables:"); SyncPCTablesLibrary.writeToLog("Starting synchronization of the following tables:"); int k = 0; foreach (string tableName in destinationTableList) { ++k; Console.WriteLine(k.ToString() + "." + tableName); SyncPCTablesLibrary.writeToLog(k.ToString() + ". " + tableName); } int threads = destinationTableList.Count; syncThreads = new Thread[threads]; int i = 0; foreach (string tableName in destinationTableList) { if (!syncedTableList.Contains(tableName)) { syncThreads[i] = new Thread(() => synchTables(tableName)); syncedTableList.Add(tableName); ++i; } } runSync(); } else { Console.WriteLine("The specified configuration file: " + nuConfig + " does not exist. Please review configuration file parameter( -c )."); } }
public void synchTables(string tableName) { Console.WriteLine("Synchronizing table: " + tableName); try{ new TableSynchronizer(SyncPCTablesLibrary.sourceServer, SyncPCTablesLibrary.sourceDatabase, tableName, SyncPCTablesLibrary.destinationServer, SyncPCTablesLibrary.destinationDatabase, tableName); }catch (Exception e) { SyncPCTablesLibrary.writeToLog(e.ToString()); Console.WriteLine(e.ToString()); } }
public SyncPCTablesProcess() { if (SyncPCTablesLibrary.pcSyncType == SyncPCTablesLibrary.USE_PC_CARD_TYPE) { string tableListScript = File.ReadAllText(SyncPCTablesLibrary.pcTableFetchScript); tableListScript = tableListScript.Replace("PC_TABLE_NAME", SyncPCTablesLibrary.pcTableType); DataTable tempTab = SyncPCTablesLibrary.getDataFromSQL(tableListScript, SyncPCTablesLibrary.sourceConnectionProps.getConnectionString()); foreach (DataRow row in tempTab.Rows) { foreach (DataColumn column in tempTab.Columns) { destinationTableList.Add(row[column].ToString()); } } } else if (SyncPCTablesLibrary.pcSyncType == SyncPCTablesLibrary.USE_PC_CARD_LIST) { destinationTableList = SyncPCTablesLibrary.pcTableSyncList; } Console.WriteLine("Starting synchronization of the following tables:"); SyncPCTablesLibrary.writeToLog("Starting synchronization of the following tables:"); int k = 0; foreach (string tableName in destinationTableList) { ++k; Console.WriteLine(k.ToString() + "." + tableName); SyncPCTablesLibrary.writeToLog(k.ToString() + "." + tableName); } int threads = SyncPCTablesLibrary.concurrentThreads; syncThreads = new Thread[threads]; int i = 0; foreach (string tableName in destinationTableList) { if (!syncedTableList.Contains(tableName)) { ++i; syncThreads[i] = new Thread(() => synchTables(tableName)); syncedTableList.Add(tableName); Console.WriteLine("initialing thread " + i.ToString() + " for " + tableName); } } runSync(); }
public static void runSync() { int activeThreadCount = syncThreads.Count(); HashSet <Thread> startedThreadSet = new HashSet <Thread>(); HashSet <Thread> completedThreadSet = new HashSet <Thread>(); while (completedThreadSet.Count < destinationTableList.Count) { double waitTime = double.Parse(SyncPCTablesLibrary.WAIT_INTERVAL.ToString()) / 1000.0; activeThreadCount = 0; foreach (Thread pcThread in syncThreads) { if (pcThread.IsAlive) { ++activeThreadCount; } else { if (startedThreadSet.Contains(pcThread)) { completedThreadSet.Add(pcThread); } else { pcThread.Start(); startedThreadSet.Add(pcThread); } } } if (activeThreadCount >= SyncPCTablesLibrary.concurrentThreads) { Console.WriteLine("Current completed thread count: " + completedThreadSet.Count.ToString()); Console.WriteLine("Current running count: " + syncThreads.Count().ToString()); SyncPCTablesLibrary.writeToLog("Current completed thread count: " + completedThreadSet.Count.ToString()); SyncPCTablesLibrary.writeToLog("Current running thread count: " + syncThreads.Count().ToString()); Console.WriteLine("Waiting for " + waitTime.ToString() + " seconds"); SyncPCTablesLibrary.writeToLog("Waiting for " + waitTime.ToString() + " seconds"); Thread.Sleep(SyncPCTablesLibrary.WAIT_INTERVAL); } // Console.WriteLine("Current running count: " + syncThreads.Count.ToString()); } SyncPCTablesLibrary.closeLogFile(); }
public TableSynchronizer(string sourceServer, string sourceDB, string sourceTable, string destinationServer, string destinationDB, string destinationTable) { this.setSourceServer(sourceServer); this.setDestinationServer(destinationServer); this.setSourceDatabase(sourceDB); this.setDestinationDatabase(destinationDB); this.setSourceTable(sourceTable); this.setDestinationTable(destinationTable); this.setSQLFile(sourceTable); string commandStr = getCommandString(); initConnections(); if (!SyncPCTablesLibrary.forceTableMerge) { runTableComparison(commandStr); string result = File.ReadAllText(getOutputFile()); SyncPCTablesLibrary.writeToLog("Reading file: " + getOutputFile()); SyncPCTablesLibrary.writeToLog("Results of comparison for table" + sourceTable + ": " + result); if (result.Contains("comparison tables/views to have either a primary key, identity, rowguid or unique key column")) { Console.WriteLine("Running Table Merge for Source: " + this.getSourceDatabase() + ".." + this.getSourceTable() + " to Destination: " + this.getDestinationTable()); string sourceTab = this.getSourceDatabase() + ".dbo." + this.getSourceTable(); string destTab = this.getDestinationDatabase() + ".dbo." + this.getDestinationTable(); Console.WriteLine("Running Merge script for table: " + this.getDestinationTable()); SyncPCTablesLibrary.writeToLog("Running Merge script for table: " + this.getDestinationTable()); //runTableMerge(sourceTab, destTab); runTableMerge(this.getDestinationTable(), this.getDestinationTable()); } else { runSyncSQL(getSQLFile()); } } else { runTableMerge(this.getDestinationTable(), this.getDestinationTable()); } }
public void runTableMerge(string sourceTable, string destinationTable) { string placeHolder = "CURRENT_TABLE_NAME"; string colScript = SyncPCTablesLibrary.fetchColumnsScript.Replace(placeHolder, destinationTable).Replace(SyncPCTablesLibrary.destinationDatabase + "..", ""); ArrayList columnList = new ArrayList(); ArrayList columnListNoBraces = new ArrayList(); DataTable tempTab = SyncPCTablesLibrary.getDataFromSQL(colScript, SyncPCTablesLibrary.destinationConnectionProps.getConnectionString()); string rowData = ""; foreach (DataRow row in tempTab.Rows) { foreach (DataColumn column in tempTab.Columns) { rowData = "[" + row[column].ToString() + "]"; columnList.Add(rowData); columnListNoBraces.Add(row[column].ToString()); } } // runBulkInsert(sourceTab, destTab); StringBuilder tableUpdateClauseBuilder = new StringBuilder(); StringBuilder tableInsertClauseBuilder = new StringBuilder(); StringBuilder columnListBuilder = new StringBuilder(); StringBuilder searchConditionsBuilder = new StringBuilder(); string srcTable = "SOURCE"; string destTable = "TARGET"; foreach (string col in columnList) { tableUpdateClauseBuilder.Append(string.Format("{2}.{0} = {1}.{0},", col, srcTable, destTable)); } ArrayList searchFieldList = SyncPCTablesLibrary.rowSpecificFields.Count > 0? SyncPCTablesLibrary.rowSpecificFields:columnListNoBraces; foreach (string col in searchFieldList) { searchConditionsBuilder.Append(string.Format("{2}.[{0}] = {1}.[{0}] AND ", col, srcTable, destTable)); } foreach (string col in columnList) { tableInsertClauseBuilder.Append(string.Format("{0},", col)); columnListBuilder.Append(string.Format("{0},", col)); } tableUpdateClauseBuilder = SyncPCTablesLibrary.removeNLastChars(tableUpdateClauseBuilder, 1); tableInsertClauseBuilder = SyncPCTablesLibrary.removeNLastChars(tableInsertClauseBuilder, 1); columnListBuilder = SyncPCTablesLibrary.removeNLastChars(columnListBuilder, 1); searchConditionsBuilder = SyncPCTablesLibrary.removeNLastChars(searchConditionsBuilder, 5); string mergeScript = SyncPCTablesLibrary.mergeScript.Replace("DESTINATION_SERVER", SyncPCTablesLibrary.destinationServer) .Replace("DESTINATION_DATABASE", SyncPCTablesLibrary.destinationDatabase) .Replace("DESTINATION_TABLE", destinationTable.Replace(SyncPCTablesLibrary.destinationDatabase + "..", "")) .Replace("SOURCE_SERVER", SyncPCTablesLibrary.sourceServer) .Replace("SOURCE_DATABASE", SyncPCTablesLibrary.sourceDatabase) .Replace("SOURCE_TABLE", sourceTable.Replace(SyncPCTablesLibrary.sourceDatabase + "..", "")) .Replace("SEARCH_CONDITIONS", searchConditionsBuilder.ToString()) .Replace("TABLE_UPDATE_LIST", tableUpdateClauseBuilder.ToString()) .Replace("TABLE_COLUMN_LIST", columnListBuilder.ToString()) .Replace("TABLE_INSERT_LIST", tableInsertClauseBuilder.ToString()); executOnServer(SyncPCTablesLibrary.destinationConnectionProps.getConnectionString(), mergeScript); }
public void runSyncSQL(string queryFile) { string sql_query = ""; queryFile = queryFile != null?getSQLFile() : ""; try { if (File.Exists(queryFile)) { using (SqlConnection destinationConnection = new SqlConnection(destinationConnectionString)){ string sql_query_all = File.ReadAllText(queryFile); if (SyncPCTablesLibrary.forceTableMerge || sql_query_all.ToLower().Contains("not included in this script")) { // truncateDestinationTable(this.getDestinationTable()); string sourceTab = this.getSourceDatabase() + ".dbo." + this.getSourceTable(); string destTab = this.getDestinationDatabase() + ".dbo." + this.getDestinationTable(); runTableMerge(sourceTab, destTab); } else { string [] lineComp; destinationConnection.Open(); string[] lines = sql_query_all.Split('\n'); string identity_str = ""; int tempCounter = 0; //bool useBulkMethod =false; SyncPCTablesLibrary.writeToLog("Running script: " + sql_query_all); // useBulkMethod =true; tempCounter = 0; while (tempCounter < lines.Length) { if (lines[tempCounter].Contains("IDENTITY_INSERT")) { identity_str = lines[tempCounter]; break; } ++tempCounter; } Console.WriteLine("using insert method for " + this.getDestinationTable()); StringBuilder sqlBuilder = new StringBuilder(); string[] individualQueries = sql_query_all.Split(new string[] { "INSERT INTO" }, StringSplitOptions.None); if (lines.Length > 50 && individualQueries.Length == 0) { Console.WriteLine("Running SQL query: " + sql_query_all + "\n GO"); SqlCommand cmd = new SqlCommand(sql_query_all, destinationConnection); cmd.CommandTimeout = 0; cmd.ExecuteNonQuery(); } else { int div = 20; int counter = 0; for (int j = 0; j < individualQueries.Length; j++) { ++counter; if (j >= 1) { sqlBuilder.Append("\nINSERT INTO ").Append(individualQueries[j]); } if (counter == div || j == (individualQueries.Length - 1)) { if (individualQueries[0].Contains("IDENTITY_INSERT")) { lineComp = individualQueries[0].Split('\n'); sqlBuilder.Insert(0, "\n" + identity_str + "\n"); } sqlBuilder.Append(";"); sql_query = sqlBuilder.ToString(); sql_query = sql_query.Replace(",N'", ",'"); Console.WriteLine("Running SQL query: " + sql_query); SqlCommand cmd = new SqlCommand(sql_query, destinationConnection); cmd.CommandTimeout = 0; cmd.ExecuteNonQuery(); counter = 0; sqlBuilder.Remove(0, sqlBuilder.Length); } } } } Console.WriteLine(getSourceTable() + " on " + getSourceServer() + " has been successfully synchronized with " + getDestinationTable() + " and " + getDestinationServer()); destinationConnection.Close(); } } } catch (Exception e) { Console.WriteLine("Error running table comparison: " + e.Message); Console.WriteLine(e.StackTrace); } }