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