private bool TargetIsBlank(InstallSettings settings)
{
var connection = new SqlConnection(settings.GetCloudConnectionString());
var adapter = new SqlDataAdapter("select * from sys.tables", connection);
var ds = new DataSet();
adapter.Fill(ds);
return(ds.Tables[0].Rows.Count == 0);
}
/// <summary>
///
/// </summary>
public void SaveSettings(InstallSettings settings)
{
settings.CloudDatabase = txtDestDatabase.Text;
settings.CloudPassword = txtDestPassword.Text;
settings.CloudServer = txtDestServer.Text;
settings.CloudUserName = txtDestUserName.Text;
settings.PrimaryDatabase = txtSourceDatabase.Text;
settings.PrimaryPassword = txtSourcePassword.Text;
settings.PrimaryServer = txtSourceServer.Text;
settings.PrimaryUserName = txtSourceUserName.Text;
settings.PrimaryUseIntegratedSecurity = optSourceIntegratedSecurity.Checked;
}
/// <summary>
///
/// </summary>
public void SaveSettings(InstallSettings settings)
{
settings.CloudDatabase = txtDestDatabase.Text;
settings.CloudPassword = txtDestPassword.Text;
settings.CloudServer = txtDestServer.Text;
settings.CloudUserName = txtDestUserName.Text;
settings.PrimaryDatabase = txtSourceDatabase.Text;
settings.PrimaryPassword = txtSourcePassword.Text;
settings.PrimaryServer = txtSourceServer.Text;
settings.PrimaryUserName = txtSourceUserName.Text;
settings.PrimaryUseIntegratedSecurity = optSourceIntegratedSecurity.Checked;
}
/// <summary>
///
/// </summary>
public void LoadSettings(InstallSettings settings)
{
txtDestDatabase.Text = settings.CloudDatabase;
txtDestPassword.Text = settings.CloudPassword;
txtDestServer.Text = settings.CloudServer;
txtDestUserName.Text = settings.CloudUserName;
txtSourceDatabase.Text = settings.PrimaryDatabase;
txtSourcePassword.Text = settings.PrimaryPassword;
txtSourceServer.Text = settings.PrimaryServer;
txtSourceUserName.Text = settings.PrimaryUserName;
optSourceIntegratedSecurity.Checked = settings.PrimaryUseIntegratedSecurity;
optSourceUserPassword.Checked = !optSourceIntegratedSecurity.Checked;
this.UpdateLogin();
}
/// <summary>
///
/// </summary>
public void LoadSettings(InstallSettings settings)
{
txtDestDatabase.Text = settings.CloudDatabase;
txtDestPassword.Text = settings.CloudPassword;
txtDestServer.Text = settings.CloudServer;
txtDestUserName.Text = settings.CloudUserName;
txtSourceDatabase.Text = settings.PrimaryDatabase;
txtSourcePassword.Text = settings.PrimaryPassword;
txtSourceServer.Text = settings.PrimaryServer;
txtSourceUserName.Text = settings.PrimaryUserName;
optSourceIntegratedSecurity.Checked = settings.PrimaryUseIntegratedSecurity;
optSourceUserPassword.Checked = !optSourceIntegratedSecurity.Checked;
this.UpdateLogin();
}
public void Run(InstallSettings settings)
{
//STEPS TO COPY DATABASE TO AZURE
//1. Verify that target is a blank database
//2. Execute only tables schemas with no defaults, relations, etc
//3. Copy data with BCP one table at a time
//4. Run full installer on the target database
//1. Verify that target is a blank database
if (!this.TargetIsBlank(settings))
throw new Exception("The target database must be empty!");
//2. Execute only tables schemas and PK with no defaults, relations, etc
Assembly assem = Assembly.GetExecutingAssembly();
string[] resourceNames = assem.GetManifestResourceNames();
var resourceName = resourceNames.FirstOrDefault(x => x.EndsWith(".Create_Scripts.Generated.CreateSchema.sql"));
if (string.IsNullOrEmpty(resourceName)) throw new Exception("Could not find the 'CreateSchema.sql' resource!");
var scripts = SqlServers.ReadSQLFileSectionsFromResource(resourceName, null);
SqlConnection connection = null;
try
{
connection = new SqlConnection(settings.GetCloudConnectionString());
connection.Open();
////Create version table
//var sb = new StringBuilder();
//sb.AppendLine("if not exists(select * from sysobjects where name = '__nhydrateschema' and xtype = 'U')");
//sb.AppendLine("BEGIN");
//sb.AppendLine("CREATE TABLE [__nhydrateschema] (");
//sb.AppendLine("[dbVersion] [varchar] (50) NOT NULL,");
//sb.AppendLine("[LastUpdate] [datetime] NOT NULL,");
//sb.AppendLine("[ModelKey] [uniqueidentifier] NOT NULL,");
//sb.AppendLine("[History] [text] NOT NULL");
//sb.AppendLine(")");
//sb.AppendLine("--PRIMARY KEY FOR TABLE");
//sb.AppendLine("if not exists(select * from sysobjects where name = '__pk__nhydrateschema' and xtype = 'PK')");
//sb.AppendLine("ALTER TABLE [__nhydrateschema] WITH NOCHECK ADD CONSTRAINT [__pk__nhydrateschema] PRIMARY KEY CLUSTERED ([ModelKey])");
//sb.AppendLine("END");
//var command2 = new SqlCommand(sb.ToString(), connection);
//command2.ExecuteNonQuery();
foreach (string sql in scripts)
{
if (
sql.Contains("--CREATE TABLE") ||
sql.Contains("--CREATE AUDIT TABLE") ||
sql.StartsWith("--APPEND AUDIT") ||
sql.StartsWith("--PRIMARY KEY FOR TABLE"))
{
var command = new SqlCommand(sql, connection);
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw;
}
finally
{
if (connection != null)
connection.Close();
}
//3. Copy data with BCP one table at a time
this.CopyData(settings);
//4. Run full installer on the target database
var setup = new InstallSetup()
{
ConnectionString = settings.GetCloudConnectionString(),
InstallStatus = InstallStatusConstants.Upgrade,
};
UpgradeInstaller.UpgradeDatabase(setup);
}
private bool TargetIsBlank(InstallSettings settings)
{
var connection = new SqlConnection(settings.GetCloudConnectionString());
var adapter = new SqlDataAdapter("select * from sys.tables", connection);
var ds = new DataSet();
adapter.Fill(ds);
return (ds.Tables[0].Rows.Count == 0);
}
private void CopyData(InstallSettings settings)
{
//Get source tables
SqlConnection connection = null;
var sourceTables = new Dictionary<string, string>();
try
{
connection = new SqlConnection(settings.GetPrimaryConnectionString());
connection.Open();
var adpater = new SqlDataAdapter("select T.name as tablename, S.name as schemaname from sys.tables T inner join sys.schemas S on T.schema_id = S.schema_id where T.name <> 'dtproperties' AND T.name <> 'sysdiagrams' order by tablename", connection);
DataSet ds = new DataSet();
adpater.Fill(ds);
foreach (DataRow row in ds.Tables[0].Rows)
{
sourceTables.Add((string)row["tablename"], (string)row["schemaname"]);
}
}
catch (Exception ex)
{
throw;
}
finally
{
if (connection != null)
connection.Close();
}
var tempFolder = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString());
Directory.CreateDirectory(tempFolder);
//Create format file
foreach (string tableName in sourceTables.Keys)
{
//Get the format FROM THE SERVER!!!
string text = string.Empty;
text = settings.CloudDatabase + "." + sourceTables[tableName] + "." + tableName + " format nul -f \"" + Path.Combine(tempFolder, tableName + ".xml") + "\" -x -n -q -S " + settings.CloudServer + " -U " + settings.CloudUserName + " -P " + settings.CloudPassword;
System.Diagnostics.ProcessStartInfo procStartInfo = new System.Diagnostics.ProcessStartInfo("bcp", text);
procStartInfo.RedirectStandardOutput = true;
procStartInfo.UseShellExecute = false;
procStartInfo.CreateNoWindow = true;
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.StartInfo = procStartInfo;
proc.Start();
string result = proc.StandardOutput.ReadToEnd();
if (result.Contains("Error ="))
throw new Exception("There was an error while creating the format file for table '" + tableName + "'.", new Exception(result));
}
//Create data file
foreach (string tableName in sourceTables.Keys)
{
var columnList = this.GetColumnList(settings.GetCloudConnectionString(), tableName);
string text = "\"select " + columnList + " from " + settings.PrimaryDatabase + "." + sourceTables[tableName] + "." + tableName + "\" queryout \"" + Path.Combine(tempFolder, tableName + ".dat") + "\" -n -q ";
if (settings.PrimaryUseIntegratedSecurity)
text += "-T";
else
text += "-S " + settings.PrimaryServer + " -U " + settings.PrimaryUserName + " -P " + settings.PrimaryPassword;
System.Diagnostics.ProcessStartInfo procStartInfo = new System.Diagnostics.ProcessStartInfo("bcp", text);
procStartInfo.RedirectStandardOutput = true;
procStartInfo.UseShellExecute = false;
procStartInfo.CreateNoWindow = true;
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.StartInfo = procStartInfo;
proc.Start();
string result = proc.StandardOutput.ReadToEnd();
if (result.Contains("Error ="))
throw new Exception("There was an error while creating the data file for table '" + tableName + "'.", new Exception(result));
}
//Move data
foreach (string tableName in sourceTables.Keys)
{
string text = string.Empty;
text = settings.CloudDatabase + "." + sourceTables[tableName] + "." + tableName + " in \"" + Path.Combine(tempFolder, tableName + ".dat") + "\" -n -q -E -S " + settings.CloudServer + " -U " + settings.CloudUserName + " -P " + settings.CloudPassword; // +" -f " + Path.Combine(tempFolder, tableName + ".xml");
System.Diagnostics.ProcessStartInfo procStartInfo = new System.Diagnostics.ProcessStartInfo("bcp", text);
procStartInfo.RedirectStandardOutput = true;
procStartInfo.UseShellExecute = false;
procStartInfo.CreateNoWindow = true;
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.StartInfo = procStartInfo;
proc.Start();
string result = proc.StandardOutput.ReadToEnd();
if (result.Contains("Error ="))
throw new Exception("There was an error while moving data for table '" + tableName + "'.", new Exception(result));
}
//Remove temp folder
Directory.Delete(tempFolder, true);
}
public void Run(InstallSettings settings)
{
//STEPS TO COPY DATABASE TO AZURE
//1. Verify that target is a blank database
//2. Execute only tables schemas with no defaults, relations, etc
//3. Copy data with BCP one table at a time
//4. Run full installer on the target database
//1. Verify that target is a blank database
if (!this.TargetIsBlank(settings))
{
throw new Exception("The target database must be empty!");
}
//2. Execute only tables schemas and PK with no defaults, relations, etc
Assembly assem = Assembly.GetExecutingAssembly();
string[] resourceNames = assem.GetManifestResourceNames();
var resourceName = resourceNames.FirstOrDefault(x => x.EndsWith(".Create_Scripts.Generated.CreateSchema.sql"));
if (string.IsNullOrEmpty(resourceName))
{
throw new Exception("Could not find the 'CreateSchema.sql' resource!");
}
var scripts = SqlServers.ReadSQLFileSectionsFromResource(resourceName, null);
SqlConnection connection = null;
try
{
connection = new SqlConnection(settings.GetCloudConnectionString());
connection.Open();
////Create version table
//var sb = new StringBuilder();
//sb.AppendLine("if not exists(select * from sysobjects where name = '__nhydrateschema' and xtype = 'U')");
//sb.AppendLine("BEGIN");
//sb.AppendLine("CREATE TABLE [__nhydrateschema] (");
//sb.AppendLine("[dbVersion] [varchar] (50) NOT NULL,");
//sb.AppendLine("[LastUpdate] [datetime] NOT NULL,");
//sb.AppendLine("[ModelKey] [uniqueidentifier] NOT NULL,");
//sb.AppendLine("[History] [text] NOT NULL");
//sb.AppendLine(")");
//sb.AppendLine("--PRIMARY KEY FOR TABLE");
//sb.AppendLine("if not exists(select * from sysobjects where name = '__pk__nhydrateschema' and xtype = 'PK')");
//sb.AppendLine("ALTER TABLE [__nhydrateschema] WITH NOCHECK ADD CONSTRAINT [__pk__nhydrateschema] PRIMARY KEY CLUSTERED ([ModelKey])");
//sb.AppendLine("END");
//var command2 = new SqlCommand(sb.ToString(), connection);
//command2.ExecuteNonQuery();
foreach (string sql in scripts)
{
if (
sql.Contains("--CREATE TABLE") ||
sql.Contains("--CREATE AUDIT TABLE") ||
sql.StartsWith("--APPEND AUDIT") ||
sql.StartsWith("--PRIMARY KEY FOR TABLE"))
{
var command = new SqlCommand(sql, connection);
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
//3. Copy data with BCP one table at a time
this.CopyData(settings);
//4. Run full installer on the target database
var setup = new InstallSetup()
{
ConnectionString = settings.GetCloudConnectionString(),
InstallStatus = InstallStatusConstants.Upgrade,
};
UpgradeInstaller.UpgradeDatabase(setup);
}
private void CopyData(InstallSettings settings)
{
//Get source tables
SqlConnection connection = null;
var sourceTables = new Dictionary <string, string>();
try
{
connection = new SqlConnection(settings.GetPrimaryConnectionString());
connection.Open();
var adpater = new SqlDataAdapter("select T.name as tablename, S.name as schemaname from sys.tables T inner join sys.schemas S on T.schema_id = S.schema_id where T.name <> 'dtproperties' AND T.name <> 'sysdiagrams' order by tablename", connection);
DataSet ds = new DataSet();
adpater.Fill(ds);
foreach (DataRow row in ds.Tables[0].Rows)
{
sourceTables.Add((string)row["tablename"], (string)row["schemaname"]);
}
}
catch (Exception ex)
{
throw;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
var tempFolder = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString());
Directory.CreateDirectory(tempFolder);
//Create format file
foreach (string tableName in sourceTables.Keys)
{
//Get the format FROM THE SERVER!!!
string text = string.Empty;
text = settings.CloudDatabase + "." + sourceTables[tableName] + "." + tableName + " format nul -f \"" + Path.Combine(tempFolder, tableName + ".xml") + "\" -x -n -q -S " + settings.CloudServer + " -U " + settings.CloudUserName + " -P " + settings.CloudPassword;
System.Diagnostics.ProcessStartInfo procStartInfo = new System.Diagnostics.ProcessStartInfo("bcp", text);
procStartInfo.RedirectStandardOutput = true;
procStartInfo.UseShellExecute = false;
procStartInfo.CreateNoWindow = true;
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.StartInfo = procStartInfo;
proc.Start();
string result = proc.StandardOutput.ReadToEnd();
if (result.Contains("Error ="))
{
throw new Exception("There was an error while creating the format file for table '" + tableName + "'.", new Exception(result));
}
}
//Create data file
foreach (string tableName in sourceTables.Keys)
{
var columnList = this.GetColumnList(settings.GetCloudConnectionString(), tableName);
string text = "\"select " + columnList + " from " + settings.PrimaryDatabase + "." + sourceTables[tableName] + "." + tableName + "\" queryout \"" + Path.Combine(tempFolder, tableName + ".dat") + "\" -n -q ";
if (settings.PrimaryUseIntegratedSecurity)
{
text += "-T";
}
else
{
text += "-S " + settings.PrimaryServer + " -U " + settings.PrimaryUserName + " -P " + settings.PrimaryPassword;
}
System.Diagnostics.ProcessStartInfo procStartInfo = new System.Diagnostics.ProcessStartInfo("bcp", text);
procStartInfo.RedirectStandardOutput = true;
procStartInfo.UseShellExecute = false;
procStartInfo.CreateNoWindow = true;
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.StartInfo = procStartInfo;
proc.Start();
string result = proc.StandardOutput.ReadToEnd();
if (result.Contains("Error ="))
{
throw new Exception("There was an error while creating the data file for table '" + tableName + "'.", new Exception(result));
}
}
//Move data
foreach (string tableName in sourceTables.Keys)
{
string text = string.Empty;
text = settings.CloudDatabase + "." + sourceTables[tableName] + "." + tableName + " in \"" + Path.Combine(tempFolder, tableName + ".dat") + "\" -n -q -E -S " + settings.CloudServer + " -U " + settings.CloudUserName + " -P " + settings.CloudPassword; // +" -f " + Path.Combine(tempFolder, tableName + ".xml");
System.Diagnostics.ProcessStartInfo procStartInfo = new System.Diagnostics.ProcessStartInfo("bcp", text);
procStartInfo.RedirectStandardOutput = true;
procStartInfo.UseShellExecute = false;
procStartInfo.CreateNoWindow = true;
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.StartInfo = procStartInfo;
proc.Start();
string result = proc.StandardOutput.ReadToEnd();
if (result.Contains("Error ="))
{
throw new Exception("There was an error while moving data for table '" + tableName + "'.", new Exception(result));
}
}
//Remove temp folder
Directory.Delete(tempFolder, true);
}