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);
     }
 }
Example #2
0
        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 ).");
            }
        }
Example #3
0
 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());
     }
 }
Example #4
0
        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();
        }
Example #5
0
        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);
            }
        }