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);
            }
        }
Exemplo n.º 4
0
        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();
        }
Exemplo n.º 6
0
        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);
            }
        }
Exemplo n.º 8
0
        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}");
            }
        }
Exemplo n.º 9
0
        //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
                }
            }
        }