// Method to use for multithreading public static void PDWThread(ConcurrentQueue <string> inputQueue, ConcurrentQueue <string> outputQueue, ConcurrentDictionary <string, bool> inaccessibleDBs) { System.Data.SqlClient.SqlConnection conn = null; System.Data.SqlClient.SqlCommand cmd = null; string currentServer = ""; string currentDatabase = ""; // While we should continue to run the thread while (runThreads) { // While there are items in the thread's input queue while (inputQueue.Any()) { string line = null; if (inputQueue.Any()) { inputQueue.TryPeek(out line); } // Skip empty lines if (String.IsNullOrEmpty(line)) { inputQueue.TryDequeue(out line); continue; } var entries = line.Substring(0, line.Length - 1).Substring(1).Replace("\",\"", ",").Split(','); if (entries[0] != "1") { inputQueue.TryDequeue(out line); continue; } // If this is a diferent server or database, we need to reconnect if (currentServer != entries[1] || currentDatabase != entries[2] || conn == null) { string server = entries[1] + "," + serverPort; string sourceDb = entries[2]; // Skip if we found out we can't connect to this database if (inaccessibleDBs.ContainsKey(server + sourceDb)) { inputQueue.TryDequeue(out line); continue; } // End the old connection if (conn != null) { try { conn.Close(); conn = null; } catch (Exception ex) { returnCode = 1; if (!ignoreFailure) { runThreads = false; break; } } } // Connect to the database try { PDWscripter.Connect(system, server, sourceDb, authentication, userName, pwd, CommandTimeout, out conn, out cmd); currentServer = entries[1]; currentDatabase = entries[2]; } // If we aren't able to connect to the database because of an SQL reason (not authorized) catch (SqlException ex) { string errorLine = string.Format("\"{0}\",\"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\",\"{6}\",\"{7}\"", server, sourceDb, "", "", "", "", Thread.CurrentThread.Name, ex.ToString().Replace("\n", " ").Replace("\r", " ").Replace("\"", "'")); Logger.LogError(errorLine); currentDatabase = ""; currentServer = ""; inaccessibleDBs.TryAdd(server + sourceDb, true); returnCode = 1; if (!ignoreFailure) { runThreads = false; break; } else { inputQueue.TryDequeue(out line); continue; } } // Other error catch (Exception ex) { string errorLine = string.Format("\"{0}\",\"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\",\"{6}\",\"{7}\"", server, sourceDb, "", "", "", "", Thread.CurrentThread.Name, ex.ToString().Replace("\n", " ").Replace("\r", " ").Replace("\"", "'")); Logger.LogError(errorLine); returnCode = 1; if (!ignoreFailure) { runThreads = false; break; } else { inputQueue.TryDequeue(out line); continue; } } } // Gather the data to call PDW string wrkMode = entries[4]; string mode = entries[7].ToUpper(); Directory.CreateDirectory(entries[5]); string outFile = entries[5] + entries[6]; string featureToScript = entries[3] + "." + entries[8]; if (featureToScript.Split('.').Length == 1) { featureToScript = "dbo." + featureToScript; } else if (featureToScript.Split('.')[0] == "") { featureToScript = "dbo" + featureToScript; } var objectName = featureToScript; featureToScript = featureToScript.ToUpper(); Logger.Log("Processing " + outFile); bool success = true; try { success = CallPDW(conn, cmd, currentServer + "," + serverPort, currentDatabase, wrkMode, mode, featureToScript, outFile); } catch (Exception e) { Logger.Log("Error in " + entries[10]); success = false; } var scriptFileName = outFile.Replace(entries[5], "") + "_" + wrkMode; // Output the line to write to the CSV file string resultLine = string.Format("\"{0}\",\"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\",\"{6}\",\"{7}\",\"{8}\",\"{9}\",\"{10}\",\"{11}\"", entries[10], //MigrationObjectCode entries[1], //ServerName entries[2], //DatabaseName entries[3], //SchemaName entries[4], //WorkMode entries[5], //Output path scriptFileName, //Filename entries[7], //mode objectName, //ObjectName entries[9], //ObjectToScript success ? 1 : 0, //success dropTruncate //OptionDropTruncateIfExists ); outputQueue.Enqueue(resultLine); inputQueue.TryDequeue(out line); } Thread.Sleep(50); } try { conn.Close(); conn = null; } catch (Exception ex) { returnCode = 1; if (!ignoreFailure) { runThreads = false; } } }
static bool CallPDW(System.Data.SqlClient.SqlConnection conn, System.Data.SqlClient.SqlCommand cmd, string server, string sourceDb, string wrkMode, string mode, string featureToScript, string outFile) { returnCode = 0; PDWscripter c = null; PDWscripter cTarget = null; Boolean SourceFromFile = false; try { if (mode == "FULL" || mode == "DELTA" || mode == "COMPARE" || mode == "PERSISTSTRUCTURE") { if (mode == "FULL" && featureToScript != "ALL") { filterSpec = featureToScript; } c = new PDWscripter(system, server, sourceDb, authentication, userName, pwd, wrkMode, ExcludeObjectSuffixList, filterSpec, mode, CommandTimeout, conn, cmd); if (mode == "PERSISTSTRUCTURE") { // populate dbstruct class c.getDbstructure(outFile, wrkMode, true); } if (mode == "COMPARE") { c.getDbstructure(outFile, wrkMode, false); } } else { c = new PDWscripter(); } // generate full database script if (mode == "FULL" || mode == "DELTA") { c.getDbTables(false); c.IterateScriptAllTables(c, outFile); } if (mode == "COMPARE" || mode == "COMPAREFROMFILE") { SourceFromFile = false; if (wrkMode == "ALL" || wrkMode == "DDL") { if (mode == "COMPAREFROMFILE") { // retrieve database structure from JSON DDL file SourceFromFile = true; // intialize from Json file outFile = outFile.Replace(TargetDb, sourceDb); string outDBJsonStructureFile = outFile + "_STRUCT_DDL.json"; c.GetDDLstructureFromJSONfile(outDBJsonStructureFile); } else { c.getDbTables(false); } } if (mode == "COMPAREFROMFILE") { if (wrkMode == "ALL" || wrkMode == "DML") { // retrieve database structure from JSON DML file SourceFromFile = true; // intialize from Json file outFile = outFile.Replace(TargetDb, sourceDb); string outDBJsonStructureFile = outFile + "_STRUCT_DML.json"; c.GetDMLstructureFromJSONfile(outDBJsonStructureFile); } } FilterSettings Filters = new FilterSettings(); if (featureToScript != "ALL") { // retrieve filter settings from file Logger.Log("Retrieving filter settings file : " + FiltersFilePath + "- Feature : " + featureToScript + " - Database : ..."); GlobalFilterSettings gFilter = new GlobalFilterSettings(); Filters = gFilter.GetObjectsFromFile(FiltersFilePath, featureToScript, sourceDb); if (Filters == null) { throw new System.ArgumentException("Filter settings parameter can not be null - initialization from file : " + FiltersFilePath + "- Feature : " + featureToScript + " - Database : ..."); } Logger.Log("Filter settings OK"); } cTarget = new PDWscripter(system, serverTarget, TargetDb, authentication, userNameTarget, pwdTarget, wrkMode, "%", filterSpec, mode, CommandTimeout); Logger.Log("Target Connection Opened"); cTarget.getDbstructure(outFile, wrkMode, false); if (mode != "COMPAREFROMFILE") { cTarget.getDbTables(false); } cTarget.CompIterateScriptAllTables(c, cTarget, outFile, SourceFromFile, Filters); } } catch (Exception ex) { string errorLine = string.Format("\"{0}\",\"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\",\"{6}\",\"{7}\"", server, sourceDb, wrkMode, mode, featureToScript, outFile.Replace("\"", "\\\""), Thread.CurrentThread.Name, ex.ToString().Replace("\n", " ").Replace("\r", " ").Replace("\"", "'")); Logger.LogError(errorLine); Logger.Log(ex.ToString()); returnCode = 1; if (!ignoreFailure) { throw ex; } return(false); } if (conn == null) { if (c != null && c.conn != null) { try { c.conn.Close(); } catch (Exception ex) { returnCode = 1; if (!ignoreFailure) { throw ex; } return(false); } } } if (cTarget != null && cTarget.conn != null) { try { cTarget.conn.Close(); } catch (Exception ex) { returnCode = 1; if (!ignoreFailure) { throw ex; } return(false); } } return(true); }
static void Main(string[] args) { string server = ""; string sourceDb = ""; string userName = ""; string pwd = ""; string wrkMode = "ALL"; string filterSpec = "%"; string outFile = ""; string system = "PDW"; string authentication = "SQL"; string encriptSQLConneciton = "false"; string trustServerCertificate = "true"; string mode = ""; string ExcludeObjectSuffixList = " "; //"_old|_new|_test|_dba"; // used to exclude test or non-user objects; string serverTarget = ""; string strportTarget = ""; string TargetDb = ""; string userNameTarget = ""; string pwdTarget = ""; string featureToScript = ""; string FiltersFilePath = ""; string CommandTimeout = ""; Dictionary <String, String> parameters = new Dictionary <string, string>(); parameters = GetParametersFromArguments(args); foreach (string pKey in parameters.Keys) { switch (pKey) { case "-S": server = parameters[pKey]; break; case "-D": sourceDb = parameters[pKey]; break; case "-E": authentication = "WINDOWS"; break; case "-U": userName = parameters[pKey]; break; case "-P": pwd = parameters[pKey]; break; case "-W": wrkMode = parameters[pKey]; break; case "-M": mode = parameters[pKey].ToUpper(); break; case "-St": serverTarget = parameters[pKey]; break; case "-Dt": TargetDb = parameters[pKey]; break; case "-Ut": userNameTarget = parameters[pKey]; break; case "-Pt": pwdTarget = parameters[pKey]; break; case "-O": outFile = parameters[pKey]; break; case "-F": featureToScript = parameters[pKey].ToUpper(); break; case "-Fp": FiltersFilePath = parameters[pKey]; break; case "-X": ExcludeObjectSuffixList = parameters[pKey]; break; case "-t": CommandTimeout = parameters[pKey]; break; default: break; } } if (wrkMode != "ALL" & wrkMode != "DDL" & wrkMode != "DML") { Console.WriteLine("Uknown mode. USE: DML|DDL|ALL"); return; } if (mode == "COMPARE" & (String.IsNullOrEmpty(serverTarget) || String.IsNullOrEmpty(TargetDb))) { Console.WriteLine("Target Database elements must be completed ..."); return; } PDWscripter c = null; PDWscripter cTarget = null; Boolean SourceFromFile = false; try { if (mode == "FULL" || mode == "DELTA" || mode == "COMPARE" || mode == "PERSISTSTRUCTURE") { if (mode == "FULL" && featureToScript != "ALL") { filterSpec = featureToScript; } c = new PDWscripter(system, server, sourceDb, authentication, userName, pwd, wrkMode, ExcludeObjectSuffixList, filterSpec, mode, CommandTimeout); if (mode == "PERSISTSTRUCTURE") { // populate dbstruct class c.getDbstructure(outFile, wrkMode, true); } if (mode == "COMPARE") { c.getDbstructure(outFile, wrkMode, false); } } else { c = new PDWscripter(); } // generate full database script if (mode == "FULL" || mode == "DELTA") { c.getDbTables(false); c.IterateScriptAllTables(c, outFile); } if (mode == "COMPARE" || mode == "COMPAREFROMFILE") { SourceFromFile = false; if (wrkMode == "ALL" || wrkMode == "DDL") { if (mode == "COMPAREFROMFILE") { // retrieve database structure from JSON DDL file SourceFromFile = true; // intialize from Json file outFile = outFile.Replace(TargetDb, sourceDb); string outDBJsonStructureFile = outFile + "_STRUCT_DDL.json"; c.GetDDLstructureFromJSONfile(outDBJsonStructureFile); } else { c.getDbTables(false); } } if (mode == "COMPAREFROMFILE") { if (wrkMode == "ALL" || wrkMode == "DML") { // retrieve database structure from JSON DML file SourceFromFile = true; // intialize from Json file outFile = outFile.Replace(TargetDb, sourceDb); string outDBJsonStructureFile = outFile + "_STRUCT_DML.json"; c.GetDMLstructureFromJSONfile(outDBJsonStructureFile); } } FilterSettings Filters = new FilterSettings(); if (featureToScript != "ALL") { // retrieve filter settings from file Console.WriteLine("Retrieving filter settings file : " + FiltersFilePath + "- Feature : " + featureToScript + " - Database : ..."); GlobalFilterSettings gFilter = new GlobalFilterSettings(); Filters = gFilter.GetObjectsFromFile(FiltersFilePath, featureToScript, sourceDb); if (Filters == null) { throw new System.ArgumentException("Filter settings parameter can not be null - initialization from file : " + FiltersFilePath + "- Feature : " + featureToScript + " - Database : ..."); } Console.WriteLine("Filter settings OK"); } cTarget = new PDWscripter(system, serverTarget, TargetDb, authentication, userNameTarget, pwdTarget, wrkMode, "%", filterSpec, mode, CommandTimeout); Console.WriteLine("Target Connection Opened"); cTarget.getDbstructure(outFile, wrkMode, false); if (mode != "COMPAREFROMFILE") { cTarget.getDbTables(false); } cTarget.CompIterateScriptAllTables(c, cTarget, outFile, SourceFromFile, Filters); } } catch (Exception ex) { Console.WriteLine(ex); throw ex; } if (c.conn != null) { c.conn.Close(); } if (cTarget != null) { cTarget.conn.Close(); } Console.Write("Done !!! "); Environment.Exit(0); }