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