public bool TestConnectionToServer(TargetServerInfo targetServer) { bool bSuccess = false; while (true) { try { using (SqlConnection connection = new SqlConnection(targetServer.ConnectionStringTargetDatabase)) { connection.Open(); connection.Close(); bSuccess = true; } } catch (SqlException ex) { if (ex.Number == 4060) { Thread.Sleep(1000); continue; } } break; } return(bSuccess); }
public static bool IsDBOnline(TargetServerInfo targetServer) { bool dbOnline = false; string query = "select state_desc from sys.databases where name = '" + targetServer.TargetDatabase + "'"; Retry.ExecuteRetryAction(() => { using (SqlConnection connection = new SqlConnection(targetServer.ConnectionStringRootDatabase)) { ScalarResults sr = SqlHelper.ExecuteScalar(connection, CommandType.Text, query.ToString()); if (sr != null && sr.ExecuteScalarReturnValue != null) { string val = sr.ExecuteScalarReturnValue.ToString(); if (val != null && val.Equals("ONLINE")) { dbOnline = true; return; } } else { dbOnline = false; return; } } }); return(dbOnline); }
private void Initialize(ref TargetServerInfo serverInfo) { _serverInfo = serverInfo; switch (serverInfo.ServerType) { case ServerTypes.SQLAzure: ServerTypeComboBox.SelectedIndex = 0; break; case ServerTypes.SQLAzureFed: ServerTypeComboBox.SelectedIndex = 1; break; default: ServerTypeComboBox.SelectedIndex = 2; break; } if (serverInfo.LoginSecure) { WindowsAuthenticationRadioButton.Checked = true; } else { rbSpecifyUserPassword.Checked = true; } ServerNamesComboBox.Text = serverInfo.ServerInstance == null ? "" : serverInfo.ServerInstance; UserNameTextBox.Text = serverInfo.Login == null ? "" : serverInfo.Login; PasswordTextBox.Text = serverInfo.Password == null ? "" : serverInfo.Password; tbDatabase.Text = _OldDatabase = serverInfo.RootDatabase == null ? "" : serverInfo.RootDatabase; }
public ServerConnect(ref TargetServerInfo serverInfo, bool target = false, bool lockServerChoice = false) { // // Required for Windows Form Designer support // InitializeComponent(); Initialize(ref serverInfo, target); }
public void Initialize(ref TargetServerInfo serverInfo, bool target) { ServerTypeComboBox.Items.Add(Properties.Resources.ServerType_SQLAzure); ServerTypeComboBox.Items.Add(Properties.Resources.ServerType_SQLAzureFed); ServerTypeComboBox.Items.Add(Properties.Resources.ServerType_SQLServer); _TargetServer = target; Initialize(ref serverInfo); }
public void CreateDatabase(TargetServerInfo targetServer, string collation, string edition, int dbSize, bool bDropDatabaseIfExists) { if (DoesDatabaseExist(targetServer)) { if (bDropDatabaseIfExists) { Retry.ExecuteRetryAction(() => { using (SqlConnection connection = new SqlConnection(targetServer.ConnectionStringRootDatabase)) { string tsql = "DROP DATABASE " + targetServer.TargetDatabase.Replace("\\[", "").Replace("\\])", ""); SqlHelper.ExecuteNonQuery(connection, CommandType.Text, tsql); } }); } else { return; } } try { StringBuilder sqlDBCreate = new StringBuilder("CREATE DATABASE "); if (targetServer.TargetDatabase.StartsWith("[", StringComparison.Ordinal) && targetServer.TargetDatabase.StartsWith("]", StringComparison.Ordinal)) { sqlDBCreate.Append(targetServer.TargetDatabase); } else { sqlDBCreate.Append("[" + targetServer.TargetDatabase + "]"); } if (collation.Length > 0) { sqlDBCreate.Append(" COLLATE " + collation); } if (targetServer.ServerType == ServerTypes.SQLAzure) { sqlDBCreate.Append(" (MAXSIZE = " + dbSize + " GB, EDITION = '" + edition + "')"); } Retry.ExecuteRetryAction(() => { using (SqlConnection connection = new SqlConnection(targetServer.ConnectionStringRootDatabase)) { SqlHelper.ExecuteNonQuery(connection, CommandType.Text, sqlDBCreate.ToString()); } }); } catch (Exception ex) { throw ex; } }
public ServerConnect(ref ServerConnection connection, ref TargetServerInfo serverInfo, bool target = false) { // // Required for Windows Form Designer support // InitializeComponent(); ServerConn = connection; Initialize(ref serverInfo, target); }
private void Initialize(ref TargetServerInfo serverInfo) { _serverInfo = serverInfo; if (File.Exists(HistoryFileName)) { try { _servers = CommonFunc.DecompressObjectFromFile <List <TargetServerInfo> >(HistoryFileName); } catch (Exception ex) { Debug.Write(ex.ToString()); _servers = new List <TargetServerInfo>(); } } else { _servers = new List <TargetServerInfo>(); } _bSkip = true; ServerNamesComboBox.DataSource = _servers; ServerNamesComboBox.DisplayMember = "ServerInstance"; _bSkip = false; if (_servers.Count > 0) { foreach (TargetServerInfo info in _servers) { if (info.ServerInstance.Equals(serverInfo.ServerInstance, StringComparison.OrdinalIgnoreCase) && info.ServerType == serverInfo.ServerType) { _ClickedProcessed = false; ServerNamesComboBox.SelectedItem = info; if (!_ClickedProcessed) { ServerNamesComboBox_SelectedIndexChanged(null, null); } return; } } } if (serverInfo.ServerInstance.Length == 0 && _servers.Count > 0) { _ClickedProcessed = false; ServerNamesComboBox.SelectedIndex = 0; if (!_ClickedProcessed) { ServerNamesComboBox_SelectedIndexChanged(null, null); } return; } UpdateForm(serverInfo); }
public static string BuildBCPUploadCommand(TargetServerInfo targetServer, string bcpArgs) { // bcpArgs example: ":128425:[dbo].[Categories] in "C:\Users\ghuey\AppData\Local\Temp\tmp1CD8.tmp" -n -E string[] schema_table = GetSchemaAndTableFromBCPArgs(bcpArgs); string schema = schema_table[0]; string table = schema_table[1]; StringBuilder args = new StringBuilder(200); if (bcpArgs.EndsWith("\r", StringComparison.Ordinal)) { bcpArgs = bcpArgs.Remove(bcpArgs.Length - 1); } string filteredCommands = bcpArgs.Substring(bcpArgs.IndexOf(" in ", StringComparison.Ordinal)); /* ** Note that BCP currently does not handle databases, schemas, and tables with "." in them very well. Thus, ** this next section of code. If there are no "." in any of these, then we can just "" the string and put the -q (quoted) flag ** on the BCP command and BCP will be happy. If there is just a period in the database name, then we can split ** out the database name (using the -d argument) and then "" everything out with the -q flag. If there is a "." ** in more than one of the three, then we will have to [ ] the string out and hope for the best (without the -q). ** ** The problem is that if you [ ] the string out, then you can't use the -q flag and if your table has computed column ** with an index, then BCP requires the -q flag (which you can't do with [ ]. Anyway, it is kind of a catch 22 problem. */ if (targetServer.TargetDatabase.IndexOf('.') < 1 && schema.IndexOf('.') < 1 && table.IndexOf('.') < 1) { args.Append("\"" + targetServer.TargetDatabase + "." + schema + "." + table + "\"" + filteredCommands + " -q -S " + targetServer.ServerInstance); } else if (targetServer.TargetDatabase.IndexOf('.') > 0 && schema.IndexOf('.') < 1 && table.IndexOf('.') < 1) { args.Append("\"" + schema + "." + table + "\"" + filteredCommands + " -d \"" + targetServer.TargetDatabase + "\" -q -S " + targetServer.ServerInstance); } else { args.Append("[" + targetServer.TargetDatabase + "].[" + schema + "].[" + table + "]" + filteredCommands + " -S " + targetServer.ServerInstance); } if (targetServer.LoginSecure) { args.Append(" -T"); } else { string login = targetServer.Login[0] == '"' ? targetServer.Login : "******"" + targetServer.Login + "\""; string password = targetServer.Password[0] == '"' ? targetServer.Password : "******"" + targetServer.Password + "\""; args.Append(" -U " + login + " -P " + password); } return(args.ToString()); }
public ServerConnect(ref SqlConnection connection, ref TargetServerInfo serverInfo, bool target = false, bool lockServerChoice = false) { SqlConn = connection; // // Required for Windows Form Designer support // InitializeComponent(); Initialize(ref serverInfo, target); if (lockServerChoice) { ServerTypeComboBox.Enabled = false; } }
public static FederationMemberDistribution GetRootTables(TargetServerInfo targetServer) { string sqlQuery = "SELECT s.name, t.name FROM sys.tables t" + " JOIN sys.schemas s ON t.schema_id = s.schema_id" + " JOIN sys.dm_db_partition_stats p ON t.object_id=p.object_id" + " WHERE p.index_id=1" + " ORDER BY s.name, t.name;"; FederationMemberDistribution md = new FederationMemberDistribution(); md.FedType = "root"; md.Low = ""; md.High = ""; md.DatabaseName = targetServer.RootDatabase; try { using (SqlConnection connection = new SqlConnection(targetServer.ConnectionStringRootDatabase)) { Retry.ExecuteRetryAction(() => { connection.Open(); using (SqlDataReader sdr = SqlHelper.ExecuteReader(connection, CommandType.Text, sqlQuery)) { if (sdr != null && !sdr.IsClosed) { while (sdr.Read()) { FederationTableInfo ti = new FederationTableInfo(); ti.Schema = sdr.GetString(0); ti.Table = sdr.GetString(1); ti.FederatedColumn = ""; md.Tables.Add(ti); } sdr.Close(); } } connection.Close(); }); } } catch (Exception ex) { System.Diagnostics.Debug.WriteLine(ex.Message); } return(md); }
public bool DoesDatabaseExist(TargetServerInfo targetServer) { bool dbExists = false; Retry.ExecuteRetryAction(() => { using (SqlConnection connection = new SqlConnection(targetServer.ConnectionStringRootDatabase)) { string query = "IF (EXISTS(select 1 from sys.databases where name = '" + targetServer.TargetDatabase + "'))" + Environment.NewLine + " SELECT 1" + Environment.NewLine + "ELSE" + Environment.NewLine + " SELECT 0"; ScalarResults sr = SqlHelper.ExecuteScalar(connection, CommandType.Text, query.ToString()); dbExists = Convert.ToBoolean((int)sr.ExecuteScalarReturnValue); } }); return(dbExists); }
public void BCPUploadData(TargetServerInfo targetServer, AsyncUpdateStatus updateStatus, AsyncQueueBCPJob queueBCPJob, string bcpArgs, ref StringCollection bcpUploadCommands) { AsyncNotificationEventArgs eventArgs = new AsyncNotificationEventArgs(0, 0, "", "", Color.DarkSlateBlue); NameValueCollection englishLanguage = (NameValueCollection)ConfigurationManager.GetSection("en-US"); NameValueCollection defaultLanguage = (NameValueCollection)ConfigurationManager.GetSection(Thread.CurrentThread.CurrentCulture.Name); if (defaultLanguage == null) { defaultLanguage = englishLanguage; if (defaultLanguage == null) { eventArgs.DisplayText = CommonFunc.FormatString(Properties.Resources.ErrorLanguageSectionNotFound, Application.ExecutablePath); eventArgs.DisplayColor = Color.Red; eventArgs.StatusMsg = Properties.Resources.ErrorApplicationConfiguration; updateStatus(eventArgs); return; } } try { int totalRowsCount = Convert.ToInt32(Regex.Match(bcpArgs, defaultLanguage["BCPNumber"]).Value, CultureInfo.InvariantCulture); // bcpArgs example: ":128425:[dbo].[Categories] in "C:\Users\ghuey\AppData\Local\Temp\tmp1CD8.tmp" -n -E // schema_table[0] == schema // schema_table[1] == table string[] schema_table = CommonFunc.GetSchemaAndTableFromBCPArgs(bcpArgs); string args = CommonFunc.BuildBCPUploadCommand(targetServer, bcpArgs); QueueBCPJobs(updateStatus, queueBCPJob, schema_table[0], schema_table[1], totalRowsCount, args, targetServer.ConnectionStringTargetDatabase); } catch (Exception ex) { eventArgs.DisplayText = ex.ToString(); eventArgs.DisplayColor = Color.Red; eventArgs.StatusMsg = Properties.Resources.ErrorParsingBCPArgs; updateStatus(eventArgs); } }
private void ConnectSqlConnection(TargetServerInfo serverInfo) { // Go ahead and connect SqlConn.ConnectionString = serverInfo.ConnectionStringRootDatabase; Retry.ExecuteRetryAction(() => { SqlConn.Open(); ScalarResults sr = SqlHelper.ExecuteScalar(SqlConn, CommandType.Text, "SELECT @@VERSION"); string version = (string)sr.ExecuteScalarReturnValue; if (version.IndexOf("Azure") > 0) { serverInfo.ServerType = ServerTypes.AzureSQLDB; } else { serverInfo.ServerType = ServerTypes.SQLServer; } serverInfo.Version = SqlConn.ServerVersion; }); }
private void UpdateForm(TargetServerInfo serverInfo) { if (serverInfo == null) { UserNameTextBox.Text = ""; PasswordTextBox.Text = ""; tbDatabase.Text = ""; WindowsAuthenticationRadioButton.Checked = false; gboxSaveServerInfo.Text = ""; } else { _bSkip = true; ServerNamesComboBox.SelectedIndex = -1; ServerNamesComboBox.Text = serverInfo.ServerInstance == null ? "" : serverInfo.ServerInstance; _bSkip = false; UserNameTextBox.Text = serverInfo.Login == null ? "" : serverInfo.Login; PasswordTextBox.Text = serverInfo.Password == null ? "" : serverInfo.Password; tbDatabase.Text = _OldDatabase = serverInfo.RootDatabase == null ? "" : serverInfo.RootDatabase; gboxSaveServerInfo.Text = ServerNamesComboBox.Text; if (serverInfo.Save) { rbSave.Checked = true; } else { rbForget.Checked = true; } } if (serverInfo.LoginSecure) { WindowsAuthenticationRadioButton.Checked = true; } else { rbSpecifyUserPassword.Checked = true; } }
private void ConnectServerConnection(TargetServerInfo serverInfo) { ServerConn.ServerInstance = serverInfo.ServerInstance; ServerConn.LoginSecure = serverInfo.LoginSecure; if (serverInfo.LoginSecure == false) { ServerConn.Login = serverInfo.Login; ServerConn.Password = serverInfo.Password; } ServerConn.DatabaseName = serverInfo.RootDatabase; ServerConn.SqlExecutionModes = SqlExecutionModes.ExecuteSql; ServerConn.Connect(); serverInfo.Version = ServerConn.ServerVersion.ToString(); if (ServerConn.DatabaseEngineType == DatabaseEngineType.SqlAzureDatabase) { serverInfo.ServerType = ServerTypes.AzureSQLDB; } else { serverInfo.ServerType = ServerTypes.SQLServer; } }
public void Initialize(ref TargetServerInfo serverInfo, bool target) { _TargetServer = target; Initialize(ref serverInfo); }
public DatabaseCreateStatus CreateDatabase(TargetServerInfo targetServer, string collation, string edition, string dbSize, string performanceLevel, bool bDropDatabaseIfExists) { if (DoesDatabaseExist(targetServer)) { if (bDropDatabaseIfExists) { Retry.ExecuteRetryAction(() => { using (SqlConnection connection = new SqlConnection(targetServer.ConnectionStringRootDatabase)) { string tsql = ""; if (targetServer.TargetDatabase[0] == '[') { tsql = "DROP DATABASE " + targetServer.TargetDatabase; } else { tsql = "DROP DATABASE [" + targetServer.TargetDatabase + "]"; } try { SqlHelper.ExecuteNonQuery(connection, CommandType.Text, tsql); } catch (SqlException ex) { if (ex.Number == -2) { while (DoesDatabaseExist(targetServer)) { Thread.Sleep(2000); } } else { throw ex; } } } }); } else { // Since the database already exists and they didn't want to drop it, return success return(DatabaseCreateStatus.Success); } } try { StringBuilder sqlDBCreate = new StringBuilder("CREATE DATABASE "); if (targetServer.TargetDatabase.StartsWith("[", StringComparison.Ordinal) && targetServer.TargetDatabase.StartsWith("]", StringComparison.Ordinal)) { sqlDBCreate.Append(targetServer.TargetDatabase); } else { sqlDBCreate.Append("[" + targetServer.TargetDatabase + "]"); } if (collation.Length > 0) { sqlDBCreate.Append(" COLLATE " + collation); } if (targetServer.ServerType == ServerTypes.AzureSQLDB) { if (dbSize.Length > 0) { sqlDBCreate.Append(" (MAXSIZE = " + dbSize + ", EDITION = '" + edition + "'"); } else { sqlDBCreate.Append(" (EDITION = '" + edition + "'"); } if (performanceLevel.Length > 0) { sqlDBCreate.Append(", service_objective='" + performanceLevel + "'"); } sqlDBCreate.Append(")"); } bool bWait = false; Retry.ExecuteRetryAction(() => { string connStr = targetServer.ConnectionStringRootDatabase; using (SqlConnection connection = new SqlConnection(connStr)) { try { SqlHelper.ExecuteNonQuery(connection, CommandType.Text, sqlDBCreate.ToString()); } catch (SqlException ex) { if (ex.Number == -2) { bWait = true; return; } throw ex; } } }); bool bSuccess = false; if (!bWait) { bSuccess = TestConnectionToServer(targetServer); if (bSuccess) { return(DatabaseCreateStatus.Success); } } return(DatabaseCreateStatus.Waiting); } catch (Exception ex) { throw ex; } }
private void ConnectCommandButton_Click(object sender, EventArgs e) { Cursor csr = null; try { csr = this.Cursor; // Save the old cursor this.Cursor = Cursors.WaitCursor; // Display the waiting cursor TargetServerInfo serverInfo = new TargetServerInfo(); _ErrorFlag = false; // Assume no error if (rbSpecifiedDB.Checked && tbDatabase.Text.Length == 0) { MessageBox.Show(Properties.Resources.MessageSpecifyDatabase); tbDatabase.Focus(); return; } serverInfo.ServerInstance = ServerNamesComboBox.Text; serverInfo.RootDatabase = rbSpecifiedDB.Checked ? tbDatabase.Text : "master"; serverInfo.LoginSecure = WindowsAuthenticationRadioButton.Checked; serverInfo.Save = rbSave.Checked; if (WindowsAuthenticationRadioButton.Checked == false) { // Use SQL Server authentication if (UserNameTextBox.Text.Length == 0) { MessageBox.Show(Properties.Resources.MessageUserName); UserNameTextBox.Focus(); return; } serverInfo.Password = PasswordTextBox.Text; if (Regex.IsMatch(ServerNamesComboBox.Text, Properties.Resources.SQLAzureCloudName, RegexOptions.IgnoreCase)) { string svrName = ServerNamesComboBox.Text.Substring(0, ServerNamesComboBox.Text.IndexOf('.')); string svrExt = "@" + svrName; string usrSvrName = ""; int idx = UserNameTextBox.Text.IndexOf('@'); if (idx > 0) { usrSvrName = UserNameTextBox.Text.Substring(idx); } if (!usrSvrName.Equals(svrExt)) { if (idx < 1) { // Ok, the user forgot to put "@server" at the end of the user name. See if they want // us to add it for them. DialogResult dr = MessageBox.Show(CommonFunc.FormatString(Properties.Resources.AzureUserName, svrExt), Properties.Resources.AzureUserNameWarning, MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1); if (dr == DialogResult.Yes) { UserNameTextBox.Text = UserNameTextBox.Text + svrExt; } } else { // Ok, to get there, the user added @server to the end of the username, but the @server does not match @server in Server name. // Check to see if the user wants us to fix for them. DialogResult dr = MessageBox.Show(CommonFunc.FormatString(Properties.Resources.ServerNamesDoNotMatch, usrSvrName, svrExt), Properties.Resources.AzureUserNameWarning, MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1); if (dr == DialogResult.Yes) { UserNameTextBox.Text = UserNameTextBox.Text.Substring(0, idx) + svrExt; } } } } serverInfo.Login = UserNameTextBox.Text; } // Go ahead and connect if (SqlConn != null) { ConnectSqlConnection(serverInfo); } else if (ServerConn != null) { ConnectServerConnection(serverInfo); } else { ServerConn = new ServerConnection(); ConnectServerConnection(serverInfo); } bool isNew = true; foreach (TargetServerInfo svrInfo in _servers) { if (svrInfo.ServerInstance.Equals(serverInfo.ServerInstance, StringComparison.OrdinalIgnoreCase) && svrInfo.ServerType == serverInfo.ServerType) { isNew = false; svrInfo.Version = serverInfo.Version; break; } } if (isNew) { _servers.Add(serverInfo); } List <TargetServerInfo> saveList = new List <TargetServerInfo>(); foreach (TargetServerInfo svrInfo in _servers) { if (svrInfo.Save) { saveList.Add(svrInfo); } } if (saveList.Count == 0) { if (File.Exists(HistoryFileName)) { File.Delete(HistoryFileName); } } else { CommonFunc.CompressObjectToFile(saveList, HistoryFileName); } _serverInfo.ServerInstance = serverInfo.ServerInstance; _serverInfo.ServerType = serverInfo.ServerType; _serverInfo.TargetDatabase = serverInfo.TargetDatabase; _serverInfo.Login = serverInfo.Login; _serverInfo.LoginSecure = serverInfo.LoginSecure; _serverInfo.Save = serverInfo.Save; _serverInfo.Password = serverInfo.Password; _serverInfo.RootDatabase = serverInfo.RootDatabase; _serverInfo.Version = serverInfo.Version; this.DialogResult = DialogResult.OK; } catch (ConnectionFailureException ex) { string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message; MessageBox.Show(message, Properties.Resources.Error2, MessageBoxButtons.OK, MessageBoxIcon.Error); _ErrorFlag = true; } catch (SmoException ex) { string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message; MessageBox.Show(message, Properties.Resources.Error2, MessageBoxButtons.OK, MessageBoxIcon.Error); _ErrorFlag = true; } catch (Exception ex) { string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message; MessageBox.Show(message, Properties.Resources.Error2, MessageBoxButtons.OK, MessageBoxIcon.Error); _ErrorFlag = true; } finally { this.Cursor = csr; // Restore the original cursor } }
public void ExecuteSQLonTarget(TargetServerInfo targetServer, AsyncUpdateStatus updateStatus, AsyncQueueBCPJob queueBCPJob, string sqlToExecute) { DateTime dtStart = DateTime.Now; AsyncNotificationEventArgs eventArgs = new AsyncNotificationEventArgs(NotificationEventFunctionCode.ExecuteSqlOnAzure, 0, "", CommonFunc.FormatString(Properties.Resources.ProcessStarted, dtStart.ToString(), dtStart.ToUniversalTime().ToString()) + Environment.NewLine, Color.DarkSlateBlue); StringCollection strColl = new StringCollection(); StringCollection bcpUploadCommands = new StringCollection(); bool inBCPCommand = false; int idx = 0; AsyncProcessingStatus.FinishedProcessingJobs = true; updateStatus(eventArgs); string connectionStr = targetServer.ConnectionStringTargetDatabase; CommentAreaHelper cah = new CommentAreaHelper(); try { cah.FindCommentAreas(sqlToExecute); } catch (Exception ex) { Debug.WriteLine(ex.ToString()); } long lCharCnt = 0; int sqlCmdLoopCtr = 0; AsyncProcessingStatus.NumberOfCommands = 0; AsyncProcessingStatus.NumberOfCommandsExecuted = 0; if (cah.Lines.Count() > 0) { foreach (string line in cah.Lines) { if (line.Trim().Equals(Properties.Resources.Go) && !cah.IsIndexInComments(lCharCnt)) { AsyncProcessingStatus.NumberOfCommands++; } lCharCnt += line.Length + cah.CrLf; } } using (SqlConnection con = new SqlConnection(connectionStr)) { StringBuilder sql = new StringBuilder(5000); StringCollection sqlDepends = new StringCollection(); string currentObject = ""; bool comment = false; try { Retry.ExecuteRetryAction(() => { con.Open(); }, () => { con.Close(); }); } catch (Exception ex) { eventArgs.DisplayColor = Color.Red; eventArgs.PercentComplete = 100; if (ex is SqlException) { eventArgs.DisplayText = CommonFunc.FormatString(Properties.Resources.ProcessAborting, ((SqlException)ex).Number.ToString(), ex.Message, sql.ToString(), DateTime.Now.ToString(CultureInfo.CurrentCulture)) + Environment.NewLine; } else { eventArgs.DisplayText = CommonFunc.FormatString(Properties.Resources.ProcessAborting, "", ex.Message, sql.ToString(), DateTime.Now.ToString(CultureInfo.CurrentCulture)) + Environment.NewLine; } updateStatus(eventArgs); return; } lCharCnt = 0; foreach (string sqlCmd in cah.Lines) { ++sqlCmdLoopCtr; if (CancelProcessing) { con.Close(); eventArgs.StatusMsg = Properties.Resources.Canceled; eventArgs.DisplayColor = Color.DarkCyan; eventArgs.DisplayText = Environment.NewLine + CommonFunc.FormatString(Properties.Resources.ProcessCanceledAt, DateTime.Now.ToString(CultureInfo.CurrentCulture)) + Environment.NewLine; eventArgs.PercentComplete = 100; updateStatus(eventArgs); return; } if (inBCPCommand) { if (sqlCmd.Length == 0) { ++lCharCnt; continue; // Get rid of blank line when in BCP Command } if (sqlCmd.Trim().Equals(Properties.Resources.Go) && !cah.IsIndexInComments(lCharCnt)) { sql.Remove(0, sql.Length); inBCPCommand = false; lCharCnt += sqlCmd.Length + cah.CrLf; continue; } } if (sqlCmd.StartsWith(CommonFunc.FormatString(Properties.Resources.RemoveComment))) { lCharCnt += sqlCmd.Length + cah.CrLf; continue; // Get rid of program generated comments } if (!comment) { idx = sqlCmd.IndexOf(CommonFunc.FormatString(Properties.Resources.RemoveCommentStart)); if (idx > -1) { comment = true; lCharCnt += sqlCmd.Length + cah.CrLf; continue; } } else { idx = sqlCmd.IndexOf(CommonFunc.FormatString(Properties.Resources.RemoveCommentEnd)); if (idx > -1) { comment = false; lCharCnt += sqlCmd.Length + cah.CrLf; continue; } lCharCnt += sqlCmd.Length + cah.CrLf; continue; } // Look for BCP string. I.E. "-- BCPArgs:2345:.dbo.Categories" in "C:\Users\ghuey\AppData\Local\Temp\tmp1CD8.tmp" -n -E if (sqlCmd.StartsWith("-- BCPArgs:", StringComparison.Ordinal)) { BCPUploadData(targetServer, updateStatus, queueBCPJob, sqlCmd.Substring(11), ref bcpUploadCommands); // if queueBCPJob is null, then BCP upload is not queued up for a parallel batch process (basically, it is finished by now). if (queueBCPJob == null) { ++AsyncProcessingStatus.NumberOfCommandsExecuted; eventArgs.DisplayText = ""; eventArgs.PercentComplete = (int)(((float)AsyncProcessingStatus.NumberOfCommandsExecuted / (float)AsyncProcessingStatus.NumberOfCommands) * 100.0); eventArgs.StatusMsg = CommonFunc.FormatString(Properties.Resources.BCPProcessingStatus, AsyncProcessingStatus.NumberOfCommandsExecuted.ToString(), AsyncProcessingStatus.NumberOfCommands.ToString()); updateStatus(eventArgs); } inBCPCommand = true; lCharCnt += sqlCmd.Length + cah.CrLf; continue; } if (sqlCmd.Trim().Equals(Properties.Resources.Go) && !cah.IsIndexInComments(lCharCnt) || sqlCmdLoopCtr == cah.Lines.Count()) { if (sql.Length == 0) { lCharCnt += sqlCmd.Length + cah.CrLf; continue; } try { Retry.ExecuteRetryAction(() => { NonQueryResults nqr = SqlHelper.ExecuteNonQuery(con, CommandType.Text, sql.ToString()); }, () => { ResetConnection(con); }); Match cmdStr = Regex.Match(sql.ToString(), "(CREATE|ALTER)[\\w\\W]+(INDEX|TABLE|VIEW|PROCEDURE|ROLE|SCHEMA|TRIGGER|TYPE).*", RegexOptions.IgnoreCase); if (cmdStr.Success) { int cr = cmdStr.Value.IndexOf("\r"); if (cr > 0) { currentObject = cmdStr.Value.Substring(0, cr > 70 ? 70 : cr); } else { currentObject = cmdStr.Value.Substring(0, cmdStr.Value.Length > 70 ? 70 : cmdStr.Value.Length); } } else { currentObject = sql.ToString().Substring(0, sql.ToString().Length > 70 ? 70 : sql.ToString().Length); } currentObject = currentObject.Replace("\r", "").Replace("\n", " "); eventArgs.DisplayColor = Color.DarkGreen; eventArgs.DisplayText = Properties.Resources.Success + " " + currentObject + Environment.NewLine; } catch (Exception ex) { eventArgs.DisplayColor = Color.Red; if (ex is SqlException) { if (((SqlException)ex).Number == 208) { --AsyncProcessingStatus.NumberOfCommandsExecuted; sqlDepends.Add(sql.ToString()); eventArgs.DisplayText = ""; } else { eventArgs.DisplayText = DateTime.Now.ToString(CultureInfo.CurrentCulture) + CommonFunc.FormatString(Properties.Resources.ErrorNumAndMsg, ((SqlException)ex).Number.ToString(), ex.Message) + Environment.NewLine + sql.ToString() + Environment.NewLine; } } else { eventArgs.DisplayText = DateTime.Now.ToString(CultureInfo.CurrentCulture) + CommonFunc.FormatString(Properties.Resources.ErrorNumAndMsg, "", ex.Message) + Environment.NewLine + sql.ToString() + Environment.NewLine; } } ++AsyncProcessingStatus.NumberOfCommandsExecuted; eventArgs.PercentComplete = (int)(((float)AsyncProcessingStatus.NumberOfCommandsExecuted / (float)AsyncProcessingStatus.NumberOfCommands) * 100.0); eventArgs.StatusMsg = CommonFunc.FormatString(Properties.Resources.BCPProcessingStatus, AsyncProcessingStatus.NumberOfCommandsExecuted.ToString(), AsyncProcessingStatus.NumberOfCommands.ToString()); updateStatus(eventArgs); sql.Remove(0, sql.Length); } else { sql.AppendLine(sqlCmd); } lCharCnt += sqlCmd.Length + cah.CrLf; } // Ok, check for error that happened because of dependency and retry foreach (string sqlDep in sqlDepends) { try { Retry.ExecuteRetryAction(() => { NonQueryResults nqr = SqlHelper.ExecuteNonQuery(con, CommandType.Text, sqlDep); }, () => { ResetConnection(con); }); int startIdx = sqlDep.IndexOf("CREATE ", 0, StringComparison.CurrentCultureIgnoreCase); if (startIdx < 0) { startIdx = sqlDep.IndexOf("ALTER ", 0, StringComparison.CurrentCultureIgnoreCase); } int len = sqlDep.Substring(startIdx).Length > 70 ? 70 : sqlDep.Substring(startIdx).Length; currentObject = sqlDep.Substring(startIdx, len) + " ..."; eventArgs.DisplayColor = Color.DarkGreen; eventArgs.DisplayText = Properties.Resources.Success + " " + currentObject.Replace("\r", "").Replace("\n", " ") + Environment.NewLine; } catch (Exception ex) { eventArgs.DisplayColor = Color.Red; if (ex is SqlException) { eventArgs.DisplayText = DateTime.Now.ToString(CultureInfo.CurrentCulture) + CommonFunc.FormatString(Properties.Resources.ErrorNumAndMsg, ((SqlException)ex).Number.ToString(), ex.Message) + Environment.NewLine + sql.ToString() + Environment.NewLine; } else { eventArgs.DisplayText = DateTime.Now.ToString(CultureInfo.CurrentCulture) + CommonFunc.FormatString(Properties.Resources.ErrorNumAndMsg, "", ex.Message) + Environment.NewLine + sql.ToString() + Environment.NewLine; } } ++AsyncProcessingStatus.NumberOfCommandsExecuted; eventArgs.PercentComplete = (int)(((float)AsyncProcessingStatus.NumberOfCommandsExecuted / (float)AsyncProcessingStatus.NumberOfCommands) * 100.0); eventArgs.StatusMsg = CommonFunc.FormatString(Properties.Resources.BCPProcessingStatus, AsyncProcessingStatus.NumberOfCommandsExecuted.ToString(), AsyncProcessingStatus.NumberOfCommands.ToString()); updateStatus(eventArgs); } con.Close(); // Output BCP upload command summary if (bcpUploadCommands.Count > 0) { eventArgs.DisplayColor = Color.Green; eventArgs.DisplayText = Properties.Resources.BCPUploadSummary + Environment.NewLine; eventArgs.PercentComplete = 99; updateStatus(eventArgs); foreach (string bcpUploadCommand in bcpUploadCommands) { eventArgs.DisplayText = bcpUploadCommand + Environment.NewLine; updateStatus(eventArgs); } } AsyncProcessingStatus.FinishedAddingJobs = true; while (true) { if (AsyncProcessingStatus.FinishedProcessingJobs) { break; } Thread.Sleep(500); } // Done DateTime dtEnd = DateTime.Now; TimeSpan tsDuration = dtEnd.Subtract(dtStart); string sHour = tsDuration.Hours == 1 ? Properties.Resources.TimeHour : Properties.Resources.TimeHours; string sMin = tsDuration.Minutes == 1 ? Properties.Resources.TimeMinute : Properties.Resources.TimeMinutes; string sSecs = tsDuration.Seconds == 1 ? Properties.Resources.TimeSecond : Properties.Resources.TimeSeconds; eventArgs.StatusMsg = Properties.Resources.Done; eventArgs.DisplayColor = Color.DarkCyan; eventArgs.DisplayText = CommonFunc.FormatString(Properties.Resources.ProcessingFinished, dtEnd.ToString(), dtEnd.ToUniversalTime().ToString(), tsDuration.Hours + sHour + tsDuration.Minutes.ToString() + sMin + tsDuration.Seconds.ToString() + sSecs); eventArgs.PercentComplete = 100; updateStatus(eventArgs); } }