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