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