public FrmDiscoverDetached(SqlInstance _oInstance)
{
InitializeComponent();
CenterToParent();
this._oInstance = _oInstance;
}
public FrmDiscoverDetached(SqlInstance _oInstance)
{
InitializeComponent();
CenterToParent();
this._oInstance = _oInstance;
}
public SqlDatabase(SqlInstance parent, string name, string status, sbyte singleUserAccess)
{
_parent = parent;
_name = name;
_status = status;
_singleUserAccess = singleUserAccess;
}
private void btnSave_Click(object sender, EventArgs e)
{
if (!Utils.IsStringValid(txtEditServers_SqlInstance.Text))
{
MessageBox.Show("Instance name has invalid characters", "Invalid data");
return;
}
if (!ValidateSettings())
{
return;
}
var oSqlServer = new SqlInstance(txtEditServers_SqlInstance.Text.Trim(), txtEditServers_User.Text,
txtEditServers_Password.Text, chkFrmEditServers_WinAuth.Checked, true);
if (SqlInstance.ListServers.Contains(oSqlServer))
{
SetStatus(oSqlServer + " exists yet.", Color.Red);
return;
}
lstEditServers_serverList.Items.Add(oSqlServer);
SqlInstance.ListServers.Add(oSqlServer);
ValidateSettings();
SetStatus(oSqlServer + " added.", Color.Green);
}
public void InitializeServer()
{
_s = new SqlInstance("localhost", "", "", true, true);
_token = new CancellationTokenSource().Token;
Utils.CreateLocalSettings();
Utils.WriteLog("Test Started.");
}
public FrmCreateDb(SqlInstance instance)
{
InitializeComponent();
CenterToParent();
_instance = instance;
ckbCreate.SetItemChecked(1, true); // AUTO_CREATE_STATISTICS
ckbCreate.SetItemChecked(2, true); // AUTO_UPDATE_STATISTICS
ckbCreate.SetItemChecked(5, true); // ENABLE_BROKER
ckbCreate.Enabled = false;
cmbRecovery.Enabled = false;
cmbRecovery.SelectedIndex = 0;
}
private async void btnTest_Click(object sender, EventArgs e)
{
var oServer = new SqlInstance(txtEditServers_SqlInstance.Text.Trim(),
txtEditServers_User.Text,
txtEditServers_Password.Text,
chkFrmEditServers_WinAuth.Checked,
true);
if (!ValidateSettings())
{
return;
}
SetButtons(false);
SetStatus(string.Format("Pinging {0} ...", txtEditServers_SqlInstance.Text.Trim()), Color.Black);
_cts = new CancellationTokenSource();
var token = _cts.Token;
var connection = Task.Run(() => oServer.TestConnection(token));
try
{
btnCancel.Show();
await connection;
if (_cts.IsCancellationRequested)
{
token.ThrowIfCancellationRequested();
}
if (oServer.IsOnline == true)
{
SetStatus("Connection OK to " + txtEditServers_SqlInstance.Text, Color.Green);
}
else
{
SetStatus(txtEditServers_SqlInstance.Text + " not connected", Color.Red);
}
}
catch (OperationCanceledException)
{
SetStatus("User canceled.", Color.Blue);
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
btnCancel.Hide();
}
SetButtons(true);
}
public FrmCreateDb(SqlInstance instance)
{
InitializeComponent();
CenterToParent();
_instance = instance;
ckbCreate.SetItemChecked(1, true); // AUTO_CREATE_STATISTICS
ckbCreate.SetItemChecked(2, true); // AUTO_UPDATE_STATISTICS
ckbCreate.SetItemChecked(5, true); // ENABLE_BROKER
ckbCreate.Enabled = false;
cmbRecovery.Enabled = false;
cmbRecovery.SelectedIndex = 0;
}
public static async Task <bool> VerifyBackup(SqlInstance instance, string filename, CancellationToken token)
{
var sb = new StringBuilder("RESTORE VERIFYONLY FROM DISK='@filename'");
sb.Replace("@filename", filename);
var verify = Task.Run(() => instance.ExecuteNonQueryAsync(sb.ToString(), token));
await verify;
if (token.IsCancellationRequested)
{
throw new OperationCanceledException();
}
return(verify.Result);
}
public void SqlServer_common()
{
SqlConnection conn = _s.GetConnection();
Assert.IsNotNull(conn);
Assert.IsNull(_s.Instancename);
Assert.AreEqual(_s.IsBusy, false);
Assert.IsNull(_s.IsOnline);
var connection = _s.TestConnectionSync();
Assert.AreEqual(connection, true);
_s.GetDbList(true);
_s.GetDbList(false);
var wrongName = RandomFactory.GetRandomListOfSpecialString(1, 64)[0];
var wrongdbcreated = _s.CreateDatabaseSync(wrongName);
Assert.AreEqual(wrongdbcreated, false);
wrongdbcreated = _s.CreateDatabaseSync(wrongName);
Assert.AreEqual(wrongdbcreated, false);
var goodNames = RandomFactory.GetRandomListOfString(8, 32);
foreach (var goodName in goodNames)
{
var dbcreated = _s.CreateDatabaseSync(goodName);
Assert.AreEqual(dbcreated, true);
var deleted = _s.Drop(goodName).Result;
Assert.AreEqual(deleted, true);
}
_s.GetPaths();
_s.GetPhysicalFiles();
Utils.SerializeBinary(new List<SqlInstance>(){_s});
_s = null;
_s = Utils.DeserializeBinary()[0];
Assert.AreNotEqual(_s, null);
}
private async void VerifyCollection(object sender, SqlInstance instance, string[] folder)
{
var checkedOk = 0;
UIOperationStarted(sender, instance, folder.Length);
foreach (var s in folder)
{
var status = String.Format("({0}/{1})", Array.IndexOf(folder, s) + 1, folder.Length);
var f = new FileInfo(s);
var token = GetCancellationToken(SqlInstance.ListServers.IndexOf(instance));
var verify = Task.Run(() => SqlDatabase.VerifyBackup(instance, f.FullName, token));
try
{
await verify;
SetStatus(sender, instance, status, "COMPLETED", f.FullName + " is valid");
checkedOk++;
}
catch (OperationCanceledException)
{
SetStatus(sender, _oDatabase, "END", "CANCEL");
break;
}
catch (SqlException ex)
{
SetStatus(sender, _oDatabase, "END", "ERROR", ex.Message);
}
catch (Exception ex)
{
SetStatus(sender, _oDatabase, "END", "EXCEPTION", ex.Message);
}
finally
{
_progressBars[SqlInstance.ListServers.IndexOf(instance)].Increment(1);
}
}
UIOperationClosed(sender, instance, true, String.Format("Verified successfully: {0}/{1}", checkedOk, folder.Length));
}
private async void btnTest_Click(object sender, EventArgs e)
{
var oServer = new SqlInstance(txtEditServers_SqlInstance.Text.Trim(),
txtEditServers_User.Text,
txtEditServers_Password.Text,
chkFrmEditServers_WinAuth.Checked,
true);
if (!ValidateSettings()) return;
SetButtons(false);
SetStatus(string.Format("Pinging {0} ...", txtEditServers_SqlInstance.Text.Trim()), Color.Black);
_cts = new CancellationTokenSource();
var token = _cts.Token;
var connection = Task.Run(() => oServer.TestConnection(token));
try
{
btnCancel.Show();
await connection;
if (_cts.IsCancellationRequested)
{
token.ThrowIfCancellationRequested();
}
if (oServer.IsOnline == true)
{
SetStatus("Connection OK to " + txtEditServers_SqlInstance.Text, Color.Green);
}
else
{
SetStatus(txtEditServers_SqlInstance.Text + " not connected", Color.Red);
}
}
catch (OperationCanceledException)
{
SetStatus("User canceled.", Color.Blue);
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
btnCancel.Hide();
}
SetButtons(true);
}
private void btnSave_Click(object sender, EventArgs e)
{
if (!Utils.IsStringValid(txtEditServers_SqlInstance.Text))
{
MessageBox.Show("Instance name has invalid characters", "Invalid data");
return;
}
if (!ValidateSettings()) return;
var oSqlServer = new SqlInstance(txtEditServers_SqlInstance.Text.Trim(),txtEditServers_User.Text,
txtEditServers_Password.Text, chkFrmEditServers_WinAuth.Checked, true);
if (SqlInstance.ListServers.Contains(oSqlServer))
{
SetStatus(oSqlServer + " exists yet.", Color.Red);
return;
}
lstEditServers_serverList.Items.Add(oSqlServer);
SqlInstance.ListServers.Add(oSqlServer);
ValidateSettings();
SetStatus(oSqlServer + " added.", Color.Green);
}
private void UIconnectingStarted(SqlInstance instance)
{
if (instance.IsBusy || instance.IsConnecting) return;
var index = SqlInstance.ListServers.IndexOf(instance);
ButtonDisable();
_progressBars[index].Value = 0;
_progressBars[index].Show();
_progressBars[index].Style = ProgressBarStyle.Marquee;
_progressBars[index].Update();
}
public SqlSystemDatabase(SqlInstance parent, string name, string status, sbyte singleUserAccess) : base(parent, name, status, singleUserAccess)
{
// to be implemented
}
private void UIconnectingEnded(SqlInstance instance)
{
if (instance.IsBusy) return;
var index = SqlInstance.ListServers.IndexOf(instance);
if (lstMain_Servers.SelectedItem !=null && lstMain_Servers.SelectedItem.Equals(instance)) EnableButtons(instance, null);
_progressBars[index].Hide();
_progressBars[index].Style = ProgressBarStyle.Blocks;
}
private void ProcessImages(SqlInstance s)
{
var index = SqlInstance.ListServers.IndexOf(s);
if (s.IsBusy)
{
_icons[index].Image = Resources.StatusAnnotations_Play_16xLG_color; _icons[index].Visible = true;
}
else
switch (s.IsOnline)
{
case true:
_icons[index].Image = Resources.StatusAnnotations_Complete_and_ok_16xLG_color;
_icons[index].Visible = true;
break;
case false:
_icons[index].Image = Resources.StatusAnnotations_Critical_16xLG_color;
_icons[index].Visible = true;
break;
case null:
_icons[index].Image = Resources.StatusAnnotations_Help_and_inconclusive_16xLG_color;
_icons[index].Visible = true;
break;
}
}
private async void RestoreFromFolder(object sender, SqlInstance instance, string folder, string[] backupArray)
{
var count = 0;
var restored = 0;
var backupsFailed = new List<string>();
UIOperationStarted(sender, instance, backupArray.Length, sender + " " + folder);
foreach (var s in backupArray)
{
count++;
var status = String.Format("({0}/{1})", count, backupArray.Length);
var f = new FileInfo(s);
var dbname = Path.GetFileNameWithoutExtension(s);
if (dbname == null) break;
if (SqlInstance.SystemNames.Any(dbname.Contains) || dbname.StartsWith("ReportServer"))
{
SetStatus(sender, instance, status, "SKIPPED", dbname + " is a system db: skipping");
continue;
}
var overwrite = false;
if (instance != null && instance.DatabasesCollection != null)
{
if (instance.DatabasesCollection.Any(x => x.Name == dbname))
{
var dr = MessageBox.Show("Database " + dbname + " exists yet, do you want to overwrite it?", "Confirmation", MessageBoxButtons.YesNoCancel);
if (dr == DialogResult.Cancel)
{
SetStatus(sender, instance, status, "INFO", USER_CANCELED);
break;
}
if (dr == DialogResult.Yes)
{
SetStatus(sender, instance, status, "INFO", "User chose to overwrite database " + dbname);
overwrite = true;
}
else
{
SetStatus(sender, instance, status, "INFO", "User chose not to overwrite database " + dbname);
Utils.WriteLog("User chose not to overwrite database " + dbname + " from " + f.FullName);
continue;
}
}
}
if (!Utils.IsStringValid(dbname))
{
SetStatus(sender, instance, status, "ERROR", string.Format("[{0}] is not a valid name, restore cannot continue", dbname));
continue;
}
if (!overwrite)
{
if (instance != null)
{
try
{
instance.CreateDatabaseSync(dbname);
//SetStatus(sender, instance, status, "COMPLETED", dbname + " created");
}
catch (SqlTimeoutException ex)
{
SetStatus(sender, instance, status, "TIMEOUT", ex.Message);
_progressBars[SqlInstance.ListServers.IndexOf(instance)].Increment(1);
continue;
}
catch (SqlException ex)
{
SetStatus(sender, instance, status, "ERROR", ex.Message);
_progressBars[SqlInstance.ListServers.IndexOf(instance)].Increment(1);
continue;
}
catch (Exception ex)
{
SetStatus(sender, instance, status, "EXCEPTION", ex.Message);
_progressBars[SqlInstance.ListServers.IndexOf(instance)].Increment(1);
continue;
}
}
}
if (instance == null || instance.DatabasesCollection == null) return;
var database = instance.DatabasesCollection.Find(x => x.Name == dbname);
database.GetDatabaseProperties();
var token = GetCancellationToken(SqlInstance.ListServers.IndexOf(instance));
var restore = Task.Run(() => database.RestoreAsync(f.FullName, token));
try
{
await restore;
if (token.IsCancellationRequested)
{
token.ThrowIfCancellationRequested();
}
SetStatus(sender, instance, status, "COMPLETED", database.Name + " restored");
restored++; //TODO: drop database if restored unsuccessful (and if !overwrite)
}
catch (OperationCanceledException)
{
SetStatus(sender, instance, status, "CANCEL", USER_CANCELED);
break;
}
catch (SqlException ex)
{
SetStatus(sender, instance, status, "ERROR", ex.Message);
}
catch (Exception ex)
{
SetStatus(sender, instance, status, "EXCEPTION", ex.Message);
}
finally
{
_progressBars[SqlInstance.ListServers.IndexOf(instance)].Increment(1);
}
if (restore.IsFaulted && !overwrite)
{
backupsFailed.Add(database.Name);
}
}
UIOperationClosed(sender, instance, true, String.Format("Restored successfully: {0}/{1}", restored, backupArray.Length));
// TODO: FIX connection state is connecting
//if (backupsFailed.Count > 0)
//{
// CleanUp(instance, backupsFailed);
//}
}
private async void lstMain_Servers_SelectedValueChanged(object sender, EventArgs e)
{
var server = (SqlInstance) lstMain_Servers.SelectedItem; // need a copy to handle another instance while this is connecting
_oInstance = server;
Debug.Assert(server != null);
var serverIndex = lstMain_Servers.SelectedIndex;
if (server.IsBusy)
{
prpServers.SelectedObject = server;
_databaseCollectionBindingList = new BindingList<SqlDatabase>(server.DatabasesCollection);
dgvDatabases.DataSource = _databaseCollectionBindingList;
}
if (!server.IsBusy && !server.IsConnecting)
{
prpServers.SelectedObject = null;
ButtonDisable();
dgvDatabases.DataSource = null;
try
{
UIconnectingStarted(server);
var token = GetCancellationToken(serverIndex);
await server.TestConnection(token);
if (token.IsCancellationRequested)
{
throw new OperationCanceledException();
}
if (server.IsOnline != false)
{
prpServers.SelectedObject = server;
server.GetDbList(tlsEnableSystemDbs.Checked);
server.GetPaths();
//server.GetProperties();
// reset size values
server.TotalDataSize = 0;
server.TotalLogSize = 0;
foreach (var database in server.DatabasesCollection)
{
if (database.Status == "ONLINE") database.GetDatabaseProperties();
server.TotalDataSize += database.RowsSize/1024;
server.TotalLogSize += database.LogSize/1024;
}
_databaseCollectionBindingList = new BindingList<SqlDatabase>(server.DatabasesCollection);
dgvDatabases.DataSource = _databaseCollectionBindingList;
server.TotalDataSize = Math.Round(server.TotalDataSize, 2);
server.TotalLogSize = Math.Round(server.TotalLogSize, 2);
}
}
catch (OperationCanceledException ex)
{
SetStatus("CONNECTION", server, "END", "CANCEL", ex.Message);
}
catch (Exception ex)
{
SetStatus("CONNECTION", server, "END", "ERROR", ex.Message);
}
UIconnectingEnded(server);
}
if (lstMain_Servers.Items.Count <= 0) return;
try
{
ButtonDisable();
EnableButtons(server, null);
}
catch
{
// debug
}
}
private void EnableButtons(SqlInstance instance, SqlDatabase database)
{
tlsEditConfig.Enabled = true;
tlsEnableSystemDbs.Enabled = true;
// instance unreachable, connecting or busy
if (instance.IsOnline == null || instance.IsConnecting || instance.IsBusy)
{
return;
}
if (instance.IsOnline != true) return;
foreach (ToolStripMenuItem i in mnuServer.DropDown.Items)
{
i.Enabled = true;
}
foreach (var c in tbxMain.Items.OfType<ToolStripButton>().Where(c => c.Name != "tlsClearLog" && c.Name != "tlsTools_CancelAll"))
{
c.Enabled = true;
}
tlsBackupAll.Enabled = true;
foreach (var i in tbxDatabase.Items.OfType<ToolStripButton>().Where(i => (string) i.Tag == "server"))
{
i.Enabled = true;
}
txtDatabase_Rename.Enabled = true;
foreach (ToolStripMenuItem i in mnuDatabase.DropDown.Items.OfType<ToolStripMenuItem>().Where(i => (string)i.Tag == "server"))
{
i.Enabled = true;
}
// Database busy or null
if (database == null || database.IsBusy) return;
if(database is SqlSystemDatabase)
{
foreach (var i in tbxDatabase.Items.OfType<ToolStripButton>().Where(i => (string) i.Tag == "sysdb"))
{
i.Enabled = true;
}
tlsDatabase_BackupWith.Enabled = true;
mnuDatabase_Check.Enabled = true;
mnuDatabase_Backup.Enabled = true;
return;
}
switch (database.Status)
{
// Database offline (Control.Tag == "offline")
case "OFFLINE":
foreach (var i in tbxDatabase.Items.OfType<ToolStripButton>().Where(i => (string) i.Tag == "offline"))
{
i.Enabled = true;
}
foreach (ToolStripMenuItem i in mnuDatabase.DropDown.Items)
{
if ((string) i.Tag == "offline")
{
i.Enabled = true;
}
}
//mnuDatabase_BackupWith.Enabled = true; //
break;
// Database online
case "ONLINE":
foreach (var i in tbxDatabase.Items.OfType<ToolStripButton>())
{
i.Enabled = true;
}
tlsDatabase_BackupWith.Enabled = true;
tlsDatabase_Online.Enabled = false;
foreach (ToolStripMenuItem i in mnuDatabase.DropDown.Items)
{
i.Enabled = true;
}
// Online single user
if (database.Access == "multi")
{
tlsDatabase_Multi.Enabled = false;
}
// Online Multi User
else
{
tlsDatabase_Single.Enabled = false;
}
break;
default:
tlsDatabase_Drop.Enabled = true;
break;
}
}
private static bool CanContinue(SqlInstance instance)
{
if (instance == null)
{
MessageBox.Show(ERROR_NO_SERVER);
return false;
}
if (instance.IsOnline == false)
{
MessageBox.Show(string.Format("instance {0} is not online.", instance));
return false;
}
if (instance.IsBusy)
{
MessageBox.Show(instance + WARNING_IS_BUSY);
return false;
}
return true;
}
private void UIOperationClosed(object sender, SqlInstance instance, bool batchOperation = false, string message = "")
{
Thread.Sleep(1); // avoids that the last operation is logged before this
if (batchOperation) SetStatus(sender, instance, "END", "INFO", message); // only logs instance operations
var serverIndex = SqlInstance.ListServers.IndexOf(instance);
if (instance != null) instance.IsBusy = false;
EnableButtons(instance, null);
_progressBars[serverIndex].Hide();
_progressBars[serverIndex].Style = ProgressBarStyle.Blocks;
UISelectObject(instance);
if (SqlInstance.ListServers.Count > 0)
{
dgvDatabases.ClearSelection();
dgvDatabases.Refresh();
}
}
private void UIOperationStarted(object sender, SqlInstance instance, int totalOperations, string message = "")
{
SetStatus(sender, instance, "BEGIN", "INFO", message);
var index = SqlInstance.ListServers.IndexOf(instance);
ButtonDisable();
instance.IsBusy = true;
_progressBars[index].Value = 0;
_progressBars[index].Show();
if (totalOperations == 0)
{
_progressBars[index].Style = ProgressBarStyle.Marquee;
_progressBars[index].Update();
}
else
{
_progressBars[index].Style = ProgressBarStyle.Blocks;
_progressBars[index].Maximum = totalOperations;
}
}
public SqlSystemDatabase(SqlInstance parent, string name, string status, sbyte singleUserAccess)
: base(parent, name, status, singleUserAccess)
{
// to be implemented
}
public SqlDatabase(SqlInstance parent, string name, string status, sbyte singleUserAccess)
{
_parent = parent;
_name = name;
_status = status;
_singleUserAccess = singleUserAccess;
}
public static async Task<bool> VerifyBackup(SqlInstance instance, string filename, CancellationToken token)
{
var sb = new StringBuilder("RESTORE VERIFYONLY FROM DISK='@filename'");
sb.Replace("@filename", filename);
var verify = Task.Run(() => instance.ExecuteNonQueryAsync(sb.ToString(), token));
await verify;
if (token.IsCancellationRequested)
{
throw new OperationCanceledException();
}
return verify.Result;
}
private void ValidateRestoreFolder(object sender, SqlInstance instance, string folder)
{
string[] backupArray;
try
{
backupArray = Directory.GetFiles(folder, "*.bak");
}
catch(UnauthorizedAccessException)
{
MessageBox.Show("You do not have permission to access " + folder);
return;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
if (backupArray.Length == 0)
{
MessageBox.Show(folder + " does not contain backups");
return;
}
var size = backupArray.Select(f => new FileInfo(f)).Select(file => file.Length).Sum();
var message = new StringBuilder();
message.Append("Number of backups: " + backupArray.Length + "\n");
foreach (var s in backupArray)
{
message.AppendLine(string.Format("{0} - File: {1}\n Database: {2}",
Array.IndexOf(backupArray, s) + 1, s, Path.GetFileNameWithoutExtension(s)));
}
message.Append("\nTotal size: " + Math.Round((size/(Math.Pow(1024, 3))), 2) + " GB\n\n");
var confirmation = MessageBox.Show(string.Format(message + "Do you confirm restoring them all?",
backupArray.Length, size), "Confirmation", MessageBoxButtons.YesNo);
if (!confirmation.Equals(DialogResult.Yes))
{
SetStatus(sender, instance, "END", "CANCEL");
return;
}
RestoreFromFolder(sender, instance, folder, backupArray);
}