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