public void ParseFile(SMOScriptOptions options, bool _ParseFile)
{
//create a dummy asyncNotificationEventArg obj for cmdline scenario. TODO: ensure this works for console commandline
AsyncNotificationEventArgs e = new AsyncNotificationEventArgs(NotificationEventFunctionCode.ParseFolder, 0, "", "", Color.Black);
ParseFile(options, _ParseFile, e);
}
public void Initialize(ScriptDatabase sdb, SMOScriptOptions options, AsyncUpdateStatus updateStatus, AsyncNotificationEventArgs eventArgs, string outputDir)
{
_sdb = sdb;
_smoScriptOpts = options;
_updateStatus = updateStatus;
_eventArgs = eventArgs;
_outputDir = outputDir;
}
public static SMOScriptOptions CreateFromConfig()
{
var smoScriptOpts = new SMOScriptOptions();
LocalizedDefaultValueAttribute target = new LocalizedDefaultValueAttribute(CommonFunc.GetAppSettingsStringValue("TargetServerType"));
if (target != null && target.Value.ToString().Length > 0)
{
smoScriptOpts.TargetServer = (string)target.Value;
}
smoScriptOpts.ScriptDefaults = CommonFunc.GetAppSettingsBoolValue("ScriptDefaults");
smoScriptOpts.ScriptHeaders = CommonFunc.GetAppSettingsBoolValue("ScriptHeaders");
smoScriptOpts.IncludeIfNotExists = CommonFunc.GetAppSettingsBoolValue("IncludeIfNotExists");
LocalizedDefaultValueAttribute sdc = new LocalizedDefaultValueAttribute(ConfigurationManager.AppSettings["ScriptDropCreate"]);
smoScriptOpts.ScriptDropCreate = (string)sdc.Value;
smoScriptOpts.ScriptCheckConstraints = CommonFunc.GetAppSettingsBoolValue("ScriptCheckConstraints");
smoScriptOpts.ScriptCollation = CommonFunc.GetAppSettingsBoolValue("ScriptCollation");
smoScriptOpts.ScriptForeignKeys = CommonFunc.GetAppSettingsBoolValue("ScriptForeignKeys");
smoScriptOpts.ScriptPrimaryKeys = CommonFunc.GetAppSettingsBoolValue("ScriptPrimaryKeys");
smoScriptOpts.ScriptUniqueKeys = CommonFunc.GetAppSettingsBoolValue("ScriptUniqueKeys");
smoScriptOpts.ScriptIndexes = CommonFunc.GetAppSettingsBoolValue("ScriptIndexes");
LocalizedDefaultValueAttribute stad = new LocalizedDefaultValueAttribute(ConfigurationManager.AppSettings["ScriptTableAndOrData"]);
smoScriptOpts.ScriptTableAndOrData = (string)stad.Value;
if (smoScriptOpts.ScriptTableAndOrData.Length == 0)
{
if (CommonFunc.GetAppSettingsBoolValue("ScriptData"))
{
smoScriptOpts.ScriptTableAndOrData = Properties.Resources.ScriptOptionsTableSchemaData;
}
else
{
smoScriptOpts.ScriptTableAndOrData = Properties.Resources.ScriptOptionsTableSchema;
}
}
smoScriptOpts.ScriptTableTriggers = CommonFunc.GetAppSettingsBoolValue("ScriptTableTriggers");
smoScriptOpts.ActiveDirectorySP = CommonFunc.GetAppSettingsBoolValue("ActiveDirectorySP");
smoScriptOpts.BackupandRestoreTable = CommonFunc.GetAppSettingsBoolValue("BackupandRestoreTable");
smoScriptOpts.ChangeDataCapture = CommonFunc.GetAppSettingsBoolValue("ChangeDataCapture");
smoScriptOpts.ChangeDataCaptureTable = CommonFunc.GetAppSettingsBoolValue("ChangeDataCaptureTable");
LocalizedDefaultValueAttribute cc = new LocalizedDefaultValueAttribute(ConfigurationManager.AppSettings["CompatibilityChecks"]);
smoScriptOpts.CompatibilityChecks = (string)cc.Value;
smoScriptOpts.DatabaseEngineSP = CommonFunc.GetAppSettingsBoolValue("DatabaseEngineSP");
smoScriptOpts.DatabaseMailSP = CommonFunc.GetAppSettingsBoolValue("DatabaseMailSP");
smoScriptOpts.DatabaseMaintenancePlan = CommonFunc.GetAppSettingsBoolValue("DatabaseMaintenancePlan");
smoScriptOpts.DataControl = CommonFunc.GetAppSettingsBoolValue("DataControl");
smoScriptOpts.DistributedQueriesSP = CommonFunc.GetAppSettingsBoolValue("DistributedQueriesSP");
smoScriptOpts.FullTextSearchSP = CommonFunc.GetAppSettingsBoolValue("FullTextSearchSP");
smoScriptOpts.GeneralExtendedSPs = CommonFunc.GetAppSettingsBoolValue("GeneralExtendedSPs");
smoScriptOpts.GeneralTSQL = CommonFunc.GetAppSettingsBoolValue("GeneralTSQL");
smoScriptOpts.IntegrationServicesTable = CommonFunc.GetAppSettingsBoolValue("IntegrationServicesTable");
smoScriptOpts.LogShipping = CommonFunc.GetAppSettingsBoolValue("LogShipping");
smoScriptOpts.MetadataFunction = CommonFunc.GetAppSettingsBoolValue("MetadataFunction");
smoScriptOpts.OLEAutomationSP = CommonFunc.GetAppSettingsBoolValue("OLEAutomationSP");
smoScriptOpts.OLEDBTable = CommonFunc.GetAppSettingsBoolValue("OLEDBTable");
smoScriptOpts.ProfilerSP = CommonFunc.GetAppSettingsBoolValue("ProfilerSP");
smoScriptOpts.ReplicationSP = CommonFunc.GetAppSettingsBoolValue("ReplicationSP");
smoScriptOpts.ReplicationTable = CommonFunc.GetAppSettingsBoolValue("ReplicationTable");
smoScriptOpts.RowsetFunction = CommonFunc.GetAppSettingsBoolValue("RowsetFunction");
smoScriptOpts.SecurityFunction = CommonFunc.GetAppSettingsBoolValue("SecurityFunction");
smoScriptOpts.SecuritySP = CommonFunc.GetAppSettingsBoolValue("SecuritySP");
smoScriptOpts.SQLMailSP = CommonFunc.GetAppSettingsBoolValue("SQLMailSP");
smoScriptOpts.SQLServerAgentSP = CommonFunc.GetAppSettingsBoolValue("SQLServerAgentSP");
smoScriptOpts.SQLServerAgentTable = CommonFunc.GetAppSettingsBoolValue("SQLServerAgentTable");
smoScriptOpts.SystemCatalogView = CommonFunc.GetAppSettingsBoolValue("SystemCatalogView");
smoScriptOpts.SystemFunction = CommonFunc.GetAppSettingsBoolValue("SystemFunction");
smoScriptOpts.SystemStatisticalFunction = CommonFunc.GetAppSettingsBoolValue("SystemStatisticalFunction");
smoScriptOpts.Unclassified = CommonFunc.GetAppSettingsBoolValue("Unclassified");
return(smoScriptOpts);
}
public bool ParseFile(SMOScriptOptions options, bool _ParseFile, AsyncNotificationEventArgs e)
{
DateTime startTime = DateTime.Now;
ScriptDatabase sdb = new ScriptDatabase();
if (e.FunctionCode == NotificationEventFunctionCode.ParseFolder)
{
FileInfo fi = new FileInfo(_FileToProcess);
_SwTsql = new StreamWriter(_TargetFile, false);
sdb.Initialize(_Output.StatusUpdateHandler, options, false, _FileToProcess, _SwTsql);
}
else
{
sdb.Initialize(_Output.StatusUpdateHandler, options, false);
}
/****************************************************************/
string sqlText = CommonFunc.GetTextFromFile(_FileToProcess);
CommentAreaHelper cah = new CommentAreaHelper();
long totalCharacterOffset = 0;
bool bCommentedLine = false;
List <string> sqlCmds = new List <string>();
if (_ParseFile)
{
StringBuilder sb = new StringBuilder();
cah.FindCommentAreas(sqlText);
foreach (string line in cah.Lines)
{
if (line.Equals(Properties.Resources.Go, StringComparison.OrdinalIgnoreCase))
{
if (!cah.IsIndexInComments(totalCharacterOffset))
{
sqlCmds.Add(sb.ToString());
sb.Length = 0;
}
else
{
sb.Append(line + Environment.NewLine);
}
}
else
{
sb.Append(line + Environment.NewLine);
}
totalCharacterOffset += line.Length + cah.CrLf;
}
if (sb.Length > 0)
{
sqlCmds.Add(sb.ToString());
sb.Length = 0;
}
}
else
{
sqlCmds.Add(sqlText);
}
int numCmds = sqlCmds.Count();
int loopCtr = 0;
if (numCmds == 0)
{
e.DisplayText = CommonFunc.FormatString(Properties.Resources.MessageNoDataToProcess + Environment.NewLine, _FileToProcess);
if (e.FunctionCode == NotificationEventFunctionCode.ParseFile)
{
e.PercentComplete = 100;
}
else if (e.FunctionCode == NotificationEventFunctionCode.ParseFolder)
{
e.FilesProcessed++;
}
_Output.StatusUpdateHandler(e);
return(false);
}
e.DisplayColor = Color.DarkBlue;
e.DisplayText = "";
if (e.FunctionCode == NotificationEventFunctionCode.ParseFile)
{
e.StatusMsg = CommonFunc.FormatString(Properties.Resources.MessageProcessingStatus, loopCtr.ToString(), numCmds.ToString());
e.PercentComplete = 0;
}
else if (e.FunctionCode == NotificationEventFunctionCode.ParseFolder)
{
e.TotalDenominator = e.TotalDenominator + numCmds;
}
_Output.StatusUpdateHandler(e);
totalCharacterOffset = 0;
foreach (string cmd in sqlCmds)
{
++loopCtr;
if (AsyncProcessingStatus.CancelProcessing)
{
break;
}
if (cmd.Length == 0 || cmd.Equals(Environment.NewLine))
{
continue;
}
foreach (CommentArea ca in cah.CommentAreas)
{
if (ca.Start == totalCharacterOffset && ca.End == totalCharacterOffset + cmd.Length - cah.CrLf - 1) // note that the -1 is to put you at zero based counting
{
bCommentedLine = true;
break;
}
bCommentedLine = false;
}
totalCharacterOffset += cmd.Length + cah.CrLf;
if (_ParseFile && !bCommentedLine && !(cmd.StartsWith("/*~") || cmd.StartsWith("~*/")))
{
if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sPROCEDURE", RegexOptions.IgnoreCase))
{
sdb.ParseFileTSQLGo(cmd);
}
else if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sTABLE", RegexOptions.IgnoreCase))
{
sdb.ParseFileTable(cmd);
}
else if (Regex.IsMatch(cmd, "CREATE\\sXML\\sSCHEMA\\sCOLLECTION", RegexOptions.IgnoreCase))
{
sdb.ParseFileXMLSchemaCollections(cmd);
}
else if (Regex.IsMatch(cmd, "CREATE\\sTYPE", RegexOptions.IgnoreCase))
{
sdb.ParseFileUDT(cmd);
}
else if (Regex.IsMatch(cmd, "CREATE\\s[a-z\\s]*\\sINDEX", RegexOptions.IgnoreCase))
{
sdb.ParseFileIndex(cmd);
}
else if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sROLE", RegexOptions.IgnoreCase))
{
sdb.ParseFileRole(cmd);
}
else if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sSYNONYM", RegexOptions.IgnoreCase))
{
sdb.ParseFileSynonyms(cmd);
}
else if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sSCHEMA", RegexOptions.IgnoreCase))
{
sdb.ParseFileSchemas(cmd);
}
else if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sASSEMBLY", RegexOptions.IgnoreCase))
{
sdb.ParseFileAssemblies(cmd);
}
else if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sPARTITIONFUNCTIONS", RegexOptions.IgnoreCase))
{
sdb.ParseFilePartitionFunctions(cmd);
}
else if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sPARTITIONSCHEMES", RegexOptions.IgnoreCase))
{
sdb.ParseFilePartitionSchemes(cmd);
}
else
{
sdb.ParseFileTSQLGo(cmd);
}
if (e.FunctionCode == NotificationEventFunctionCode.ParseFolder)
{
e.NumeratorComplete = e.NumeratorComplete + 1;
}
}
else
{
sdb.OutputSQLString(cmd, Color.Black);
}
if (loopCtr % 20 == 0)
{
e.DisplayColor = Color.DarkBlue;
e.DisplayText = "";
if (e.FunctionCode == NotificationEventFunctionCode.ParseFile)
{
e.PercentComplete = (int)(((float)loopCtr / (float)numCmds) * 100.0);
e.StatusMsg = CommonFunc.FormatString(Properties.Resources.MessageProcessingStatus, loopCtr.ToString(), numCmds.ToString());
}
else if (e.FunctionCode == NotificationEventFunctionCode.ParseFolder)
{
e.PercentComplete = (int)(((float)e.NumeratorComplete / (float)e.TotalDenominator) * 100);
}
_Output.StatusUpdateHandler(e);
}
}
DateTime endTime = DateTime.Now;
if (e.FunctionCode == NotificationEventFunctionCode.ParseFile)
{
if (AsyncProcessingStatus.CancelProcessing)
{
e.DisplayText = CommonFunc.FormatString(Properties.Resources.MessageCanceledProcessing, DateTime.Now.ToString()) + Environment.NewLine;
e.StatusMsg = Properties.Resources.MessageCanceled;
}
else
{
e.DisplayColor = Color.DarkCyan;
if (_ParseFile)
{
e.DisplayText = Properties.Resources.AnalysisComplete + Environment.NewLine;
}
else
{
e.DisplayText = Properties.Resources.MessageFileReadAndReady;
}
e.StatusMsg = Properties.Resources.Done;
}
e.PercentComplete = 100;
e.DisplayText = CommonFunc.FormatString(Properties.Resources.MessageTotalFileProcessingTime + Environment.NewLine,
endTime.Subtract(startTime).ToString(),
Properties.Resources.RemoveComment,
_FileToProcess.ToString());
_Output.StatusUpdateHandler(e);
}
else if (e.FunctionCode == NotificationEventFunctionCode.ParseFolder)
{
_SwTsql.Flush();
_SwTsql.Close();
if (sdb.IssuesFound == true)
{
e.DisplayText = Properties.Resources.MessageFileChangedState +
CommonFunc.FormatString(Properties.Resources.MessageTotalFileProcessingTime,
endTime.Subtract(startTime).ToString(), _FileToProcess.ToString()) + Environment.NewLine;
e.DisplayColor = Color.Brown;
}
else
{
e.DisplayText = Properties.Resources.MessageFileNoChangeState +
CommonFunc.FormatString(Properties.Resources.MessageTotalFileProcessingTime,
endTime.Subtract(startTime).ToString(), _FileToProcess.ToString()) + Environment.NewLine;
e.DisplayColor = Color.DarkBlue;
}
_Output.StatusUpdateHandler(e);
e.FilesProcessed++;
}
return(sdb.IssuesFound);
}
public void ParseFile(string _FileToProcess, bool _ParseFile)
{
DateTime startTime = DateTime.Now;
AsyncNotificationEventArgs e = new AsyncNotificationEventArgs(NotificationEventFunctionCode.ParseFile, 0, "", "", Color.Black);
ScriptDatabase sdb = new ScriptDatabase();
sdb.Initialize(_Output.StatusUpdateHandler, SMOScriptOptions.CreateFromConfig(), false);
/****************************************************************/
string sqlText = CommonFunc.GetTextFromFile(_FileToProcess);
CommentAreaHelper cah = new CommentAreaHelper();
long totalCharacterOffset = 0;
bool bCommentedLine = false;
List <string> sqlCmds = new List <string>();
if (_ParseFile)
{
StringBuilder sb = new StringBuilder();
cah.FindCommentAreas(sqlText);
foreach (string line in cah.Lines)
{
if (line.Equals(Properties.Resources.Go, StringComparison.OrdinalIgnoreCase))
{
if (!cah.IsIndexInComments(totalCharacterOffset))
{
sqlCmds.Add(sb.ToString());
sb.Length = 0;
}
else
{
sb.Append(line + Environment.NewLine);
}
}
else
{
sb.Append(line + Environment.NewLine);
}
totalCharacterOffset += line.Length + cah.CrLf;
}
}
else
{
sqlCmds.Add(sqlText);
}
int numCmds = sqlCmds.Count();
if (numCmds == 0)
{
e.PercentComplete = 100;
e.DisplayText = "No data to process";
_Output.StatusUpdateHandler(e);
return;
}
int loopCtr = 0;
e.DisplayColor = Color.DarkBlue;
e.DisplayText = "";
e.StatusMsg = "Processing " + loopCtr.ToString() + " out of " + numCmds.ToString();
e.PercentComplete = 0;
_Output.StatusUpdateHandler(e);
totalCharacterOffset = 0;
foreach (string cmd in sqlCmds)
{
++loopCtr;
if (AsyncProcessingStatus.CancelProcessing)
{
break;
}
if (cmd.Length == 0 || cmd.Equals(Environment.NewLine))
{
continue;
}
foreach (CommentArea ca in cah.CommentAreas)
{
if (ca.Start == totalCharacterOffset && ca.End == totalCharacterOffset + cmd.Length - cah.CrLf - 1) // note that the -1 is to put you at zero based counting
{
bCommentedLine = true;
break;
}
bCommentedLine = false;
}
totalCharacterOffset += cmd.Length + cah.CrLf;
if (_ParseFile && !bCommentedLine && !(cmd.StartsWith("/*~") || cmd.StartsWith("~*/")))
{
if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sPROCEDURE", RegexOptions.IgnoreCase))
{
sdb.ParseFileTSQLGo(cmd);
}
else if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sTABLE", RegexOptions.IgnoreCase))
{
sdb.ParseFileTable(cmd);
}
else if (Regex.IsMatch(cmd, "CREATE\\sXML\\sSCHEMA\\sCOLLECTION", RegexOptions.IgnoreCase))
{
sdb.ParseFileXMLSchemaCollections(cmd);
}
else if (Regex.IsMatch(cmd, "CREATE\\sTYPE", RegexOptions.IgnoreCase))
{
sdb.ParseFileUDT(cmd);
}
else if (Regex.IsMatch(cmd, "CREATE\\s[a-z\\s]*\\sINDEX", RegexOptions.IgnoreCase))
{
sdb.ParseFileIndex(cmd);
}
else if (Regex.IsMatch(cmd, "CREATE ROLE", RegexOptions.IgnoreCase))
{
sdb.ParseFileRole(cmd);
}
else
{
sdb.ParseFileTSQLGo(cmd);
}
}
else
{
sdb.OutputSQLString(cmd, Color.Black);
}
if (loopCtr % 20 == 0)
{
e.PercentComplete = (int)(((float)loopCtr / (float)numCmds) * 100.0);
e.DisplayColor = Color.DarkBlue;
e.DisplayText = "";
e.StatusMsg = "Processing " + loopCtr.ToString() + " out of " + numCmds.ToString();
_Output.StatusUpdateHandler(e);
}
}
if (AsyncProcessingStatus.CancelProcessing)
{
e.DisplayText = "Canceled processing ..." + Environment.NewLine;
e.StatusMsg = "Canceled!";
}
else
{
e.DisplayColor = Color.DarkCyan;
if (_ParseFile)
{
e.DisplayText = Properties.Resources.AnalysisComplete + Environment.NewLine;
}
else
{
e.DisplayText = "File read and ready for processing.";
}
e.StatusMsg = "Done!";
}
e.PercentComplete = 100;
_Output.StatusUpdateHandler(e);
DateTime endTime = DateTime.Now;
e.DisplayText = string.Format(
"{1}Total processing time --> {0}",
endTime.Subtract(startTime).ToString(),
Properties.Resources.RemoveComment);
_Output.StatusUpdateHandler(e);
}