public List <string> GetDatabases()
{
List <string> databases = new List <string>();
string sqlCommand = "SELECT name FROM master.sys.databases where name NOT IN ('master','model','msdb','tempdb')";
try
{
if (connector.Open())
{
if (filterTextBox.Text != default_text)
{
sqlCommand += " AND name LIKE '%" + filterTextBox.Text + "%'";
}
var reader = connector.ReadResults(connector.CreateCommand(sqlCommand));
while (reader.Read())
{
databases.Add(reader[0].ToString());
}
reader.Close();
if (connector.GetConnectionState() == ConnectionState.Open)
{
connector.Close();
}
}
return(databases);
}
catch (Exception e)
{
Console.WriteLine($"{e}: There was an error while retrieving the database list.");
return(databases);
}
}
private void backgroundWorker3_DoWork(object sender, DoWorkEventArgs e)
{
BackgroundWorker worker = sender as BackgroundWorker;
for (int i = 1; i < backupList.Items.Count; i += 2)
{
if (connector2.Open())
{
try
{
string sql = $"BACKUP DATABASE \"{backupList.Items[i]}\" TO DISK = \'{backupDirectoryTextBox.Text}\\{backupList.Items[i]}.BAK\' WITH INIT";
// string sql = $"BACKUP DATABASE \"{s}\" TO DISK = \'{backupDirectoryTextBox.Text}\\{s}.BAK\'";
var command = connector2.CreateCommand(sql);
command.CommandTimeout = 0;
var reader = connector2.ReadResults(command);
if (connector2.GetConnectionState() == ConnectionState.Open)
{
connector2.Close();
}
Logger.Info($"Successfully Backed Up Database: {backupList.Items[i]}");
}
catch (Exception ex)
{
connector2.Close();
Logger.Error(ex, $"An Error Occurred While Attempting to Backup Database: {backupList.Items[i]}");
// This is extremely annoying and unreliable when errors occur on multiple databases.
//ef = new ErrorForm(ex);
//ef.Show();
break;
}
}
int percentComplete = (int)(i / (float)(backupList.Items.Count) * 100);
worker.ReportProgress(percentComplete);
}
}
private void restoreDatabase(int i, string[] filesToRestore, SQLConnector conn, String workersName)
{
string databaseName = filesToRestore[i].Split('\\').Last().Split('.').First();
string restoreSql = $@"RESTORE DATABASE [{databaseName}] FROM DISK='{filesToRestore[i]}' WITH REPLACE";
Console.WriteLine($"{workersName} runs: Restoring {databaseName} using query: {restoreSql}");
Logger.Info($"{workersName} runs: Restoring {databaseName} using query: {restoreSql}");
try
{
conn.Open();
var command = conn.CreateCommand(restoreSql);
command.CommandTimeout = 0;
conn.ReadResults(command);
conn.Close();
}
catch (Exception ex)
{
if (conn != null && conn.GetConnectionState() == ConnectionState.Open)
{
conn.Close();
}
Logger.Error(ex, $"An error occurred while attempting to restore {databaseName}");
}
}
public static List <String> GetDatabaseList()
{
SQLConnector conn = new SQLConnector("");
conn.InitializeConnection();
conn.Open();
var temp = new List <String>();
var reader = conn.ReadResults(conn.CreateCommand("SELECT name FROM master.sys.databases where name NOT IN ('master','model','msdb','tempdb')"));
while (reader.Read())
{
temp.Add(reader[0].ToString());
}
conn.Close();
return(temp);
}
public void RetrieveTables_DatabaseTablesAreNotOrdered_TablesIsOrdered()
{
// Arrange
CreateTestDatabase();
var database = new Database("CustomerDatabase");
List <string> unsortedTables = new List <string>();
SQLConnector conn = new SQLConnector("");
string sql = $"USE [{database.Name}] SELECT Name FROM sys.tables WHERE is_ms_shipped = 0";
// Act
database.RetrieveTables();
conn.InitializeConnection();
conn.Open();
var reader = conn.ReadResults(conn.CreateCommand(sql));
while (reader.Read())
{
unsortedTables.Add(reader[0].ToString());
}
reader.Close();
conn.Close();
unsortedTables.Sort();
// Assert
Console.WriteLine($"Unsorted List Contains: {unsortedTables.Count} Items");
Console.WriteLine("Items:");
for (int i = 0; i < unsortedTables.Count; i++)
{
Console.WriteLine($"Item # {i}: {unsortedTables[i]}");
}
Console.WriteLine($"Sorted List Contains: {database.Tables.Count} Items");
for (int i = 0; i < database.Tables.Count; i++)
{
Console.WriteLine($"Item # {i}: {database.Tables[i]}");
}
Assert.IsTrue(database.Tables.SequenceEqual(unsortedTables));
// Cleanup
DeleteTestDatabase();
}
public void RetrieveTables()
{
Tables = new List <String>();
SQLConnector conn = new SQLConnector("");
conn.InitializeConnection();
conn.Open();
string sql = $"USE [{Name}] SELECT Name FROM sys.tables WHERE is_ms_shipped = 0 order by Name";
Console.WriteLine($"Getting Tables Using: {sql}");
var reader = conn.ReadResults(conn.CreateCommand(sql));
while (reader.Read())
{
Tables.Add(reader[0].ToString());
}
conn.Close();
}
private void DeleteTestDatabase()
{
SQLConnector conn;
conn = new SQLConnector("");
conn.InitializeConnection();
string sql;
try // Delete previously created database.
{
sql = File.ReadAllText(@"..\..\DBScripts\DropTestDatabase.sql");
conn.Open();
conn.ReadResults(conn.CreateCommand(sql));
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
private SQLConnector connectToSQL()
{
SQLConnector conn = null;
try
{
conn = new SQLConnector(Dashboard.SqlInfoData.Data_Source, Dashboard.SqlInfoData.Initial_Catalog, Dashboard.SqlInfoData.User_Id, Dashboard.SqlInfoData.Password);
conn.InitializeConnection();
}
catch (Exception ex)
{
if (conn != null && conn.GetConnectionState() == ConnectionState.Open)
{
conn.Close();
}
Logger.Error(ex, $"An error occurred while attempting to estbalish SQL Connection. " +
$"Data Source: {Dashboard.SqlInfoData.Data_Source}, " +
$"Initial Catalog: {Dashboard.SqlInfoData.Initial_Catalog}, " +
$"User ID: {Dashboard.SqlInfoData.User_Id}, " +
$"Password: {Dashboard.SqlInfoData.Password}");
}
return(conn);
}
private void CreateTestDatabase()
{
SQLConnector conn;
conn = new SQLConnector("");
conn.InitializeConnection();
string sql;
try // Attempt to create the desired database.
{
sql = File.ReadAllText(@"..\..\DBScripts\CreateTestDatabase.sql");
conn.Open();
conn.ReadResults(conn.CreateCommand(sql)).Close();
sql = File.ReadAllText(@"..\..\DBScripts\AddTable.sql");
conn.ReadResults(conn.CreateCommand(sql)).Close();
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
//checks over all databases that were restored and checks there state. If any are stuck in restoring state, then restore them again (single threaded at this point)
private void stuckRestoringCheck(string[] filesToRestore, SQLConnector conn)
{
conn.Open();
var dbResults = conn.ReadResults(conn.CreateCommand("SELECT NAME FROM SYS.DATABASES WHERE NAME NOT IN ('tempdb', 'master', 'model', 'msdb')"));
List <string> databases = new List <string>();
while (dbResults.Read())
{
databases.Add(dbResults[0].ToString());
}
conn.Close();
Boolean keepgoing = true;
//make sure all other background workers are done with their restoring before proceeding
while (keepgoing)
{
int workersWorking = 0;
if (backgroundWorker1.IsBusy)
{
workersWorking++;
}
if (backgroundWorker3.IsBusy)
{
workersWorking++;
}
if (backgroundWorker4.IsBusy)
{
workersWorking++;
}
if (backgroundWorker5.IsBusy)
{
workersWorking++;
}
if (workersWorking == 1)
{
keepgoing = false;
}
}
//Console.Clear(); //this line seems to cause problems for some reason. didn't investigate, just disabled since non-essential
for (int i = 0; i < databases.Count; i++)
{
string dbName = databases[i];
string sql_check_state = $"SELECT DATABASEPROPERTYEX('{dbName}', 'Status')";
string result;
if (conn.GetConnectionState() == ConnectionState.Open)
{
conn.Close();
}
try
{
conn.Open();
var reader = conn.ReadResults(conn.CreateCommand(sql_check_state));
reader.Read();
result = reader[0].ToString(); //read the result's first row grab its first column
Console.WriteLine(dbName + ": " + result);
conn.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex);
if (conn != null && conn.GetConnectionState() == ConnectionState.Open)
{
conn.Close();
}
result = "failed";
}
if (!result.Equals("ONLINE")) //if this database's state is not ONLINE then restore it again
{
Console.WriteLine($"{dbName} currently in state: {result}");
Console.WriteLine("restoring... starting check over again");
Logger.Info($"{dbName} currently in state: {result} - restoring... starting check over again");
restoreDatabase(i, filesToRestore, conn, "Final Check");
i = -1; //reset, start check over again to check this one again
}
}
}