//Used to import the backups from the specified backup folder. public async Task ImportDatabases(List <string> databases, string connectionString) { string folderPath = $"{_backupFolderPath}{ DateTime.Now.ToString("dd-MM-yyyy")}\\"; try { using (SqlConnection conn = new SqlConnection(connectionString)) { //Try to open the server connection. conn.Open(); FormControls.OutputToApp("Importing databases..."); for (int i = 0; i < databases.Count; i++) { //Setup command for sql. FormControls.OutputToApp($"Dropping database: {databases[i]}"); string sqlCommand = $"DROP DATABASE IF EXISTS {databases[i]}"; SqlCommand cmd = new SqlCommand(sqlCommand, conn); cmd.ExecuteNonQuery(); await SQLPackage.Run("Import", databases[i], connectionString, folderPath); } } } catch (Exception e) { FormControls.OutputToApp($"Error: {e}"); } FormControls.OutputToApp("Finished Importing"); }
//Tests the given connection string to see whether a connection can be made with the SQL Server. public static async Task <bool> TestConnection(string connectionString, bool silent = false, bool popup = false) { //Gets the server name from the connection string, but only if the data source is at the start of the string. string serverName = connectionString.Split(';')[0].Split('=')[1].Trim(); try { using (SqlConnection conn = new SqlConnection(connectionString)) { await conn.OpenAsync(); if (!silent) { if (!popup) { FormControls.OutputToApp($"Connection to \"{serverName}\" successful!"); } else { MessageBox.Show($"Connection to \"{serverName}\" successful!"); } } FormControls.ToggleAllElements(true); return(true); } } catch (Exception e) { MessageBox.Show($"Connection to \"{serverName}\" failed. {e.Message}"); FormControls.ToggleAllElements(true); FormControls.ToggleStartButton(true); FormControls.ClearOutputToApp(); return(false); } }
//Used to export the database backup from LIVE to a local folder. public async Task <bool> ExportDatabases(List <string> databases, string connectionString) { string folderPath = $"{_backupFolderPath}{DateTime.Now.ToString("dd-MM-yyyy")}\\"; FormControls.ToggleAllElements(false); Directory.CreateDirectory(folderPath); for (int i = 0; i < databases.Count; i++) { if (!await SQLPackage.Run("Export", databases[i], connectionString, folderPath)) { FormControls.ClearOutputToApp(); return(false); } } FormControls.OutputToApp("------------------"); FormControls.OutputToApp("Finished Exporting"); return(true); }
//Used to run sqlpackage.exe which is needed to export/import databases. public static async Task <bool> Run(string action, string database, string connectionString, string backupFolderPath) { return(await Task.Run(() => { //Make sure that the action string is in the right format. action = action.First().ToString().ToUpper() + action.Substring(1).ToLower(); string[] templates = { "tf", "scs" }; if (!action.ToUpper().Equals("IMPORT") && !action.ToUpper().Equals("EXPORT")) { return false; } if (action.ToUpper().Equals("IMPORT")) { templates[0] = "sf"; templates[1] = "tcs"; } string command = $"SqlPackage.exe /a:{action} /{templates[0]}:\"{backupFolderPath}{database}.bacpac\" /{templates[1]}:\"{connectionString}; Initial Catalog={database}\""; Console.WriteLine($"{action}ing database: {database}"); ProcessStartInfo procStartInfo = new ProcessStartInfo("cmd", @"/c cd C:\Program Files\Microsoft SQL Server\150\DAC\bin & " + command); procStartInfo.RedirectStandardOutput = true; procStartInfo.RedirectStandardInput = true; procStartInfo.UseShellExecute = false; procStartInfo.CreateNoWindow = true; //makes the window visible when false. using (process = new Process()) { try { process.StartInfo = procStartInfo; process.Start(); FormControls.OutputToApp(process.Id.ToString()); while (!process.StandardOutput.EndOfStream) { string line = process.StandardOutput.ReadLine(); // do something with line Console.WriteLine(line); FormControls.OutputToApp(line); } // Add this: wait until process does its work process.WaitForExit(); // and only then read the result string result = process.StandardOutput.ReadToEnd(); Console.WriteLine(result); //process.Dispose(); if (process.ExitCode == 0) { return true; } else { return false; } } catch (Exception e) { FormControls.OutputToApp("Failed to export."); return false; } } })); }