/// <summary>
/// Performs an install of a database
/// </summary>
public void Install(InstallSetup setup)
{
//The connection string must reference an existing database
if (!SqlServers.TestConnectionString(setup.ConnectionString))
{
throw new Exception("The connection string does not reference a valid database.");
}
try
{
UpgradeInstaller.UpgradeDatabase(setup);
}
catch (InvalidSQLException ex)
{
var sb = new StringBuilder();
sb.AppendLine();
sb.AppendLine("BEGIN ERROR SQL");
sb.AppendLine(ex.SQL);
sb.AppendLine("END ERROR SQL");
sb.AppendLine();
Log.Verbose(sb.ToString());
UpgradeInstaller.LogError(ex, sb.ToString());
throw;
}
catch (Exception ex)
{
throw;
}
}
private void comboBoxCreationServerName_DropDown(object sender, System.EventArgs e)
{
if (cboCreationServerName.Items.Count == 0)
{
cboCreationServerName.DataSource = SqlServers.GetServers();
}
}
private bool CreateDatabase()
{
var error = false;
var connectString = SqlServers.BuildConnectionString(optCreationIntegratedSecurity.Checked, string.Empty, cboCreationServerName.Text, txtCreationUserName.Text, txtCreationPassword.Text);
if (SqlServers.TestConnectionString(connectString) && SqlServers.HasCreatePermissions(connectString))
{
try
{
var setup = new InstallSetup()
{
MasterConnectionString = connectString,
NewDatabaseName = txtCreationDatabaseName.Text,
};
SqlServers.CreateDatabase(setup);
}
catch (Exception ex)
{
error = true;
System.Diagnostics.Debug.WriteLine(ex.ToString());
MessageBox.Show("Could not create database." + Environment.NewLine + ex.Message);
}
}
else
{
error = true;
MessageBox.Show("The account does not have permissions to create a database on this server.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(error);
}
private string[] GetDatabaseNames()
{
var connectString = SqlServers.BuildConnectionString(optConnectionIntegratedSecurity.Checked,
cboConnectionDatabaseName.Text,
cboConnectionServerName.Text,
txtConnectionUserName.Text,
txtConnectionPassword.Text);
return(SqlServers.GetDatabaseNames(connectString));
}
public HistoryForm(string connectionString)
: this()
{
List <HistoryItem> historyList = SqlServers.GetHistory(connectionString);
foreach (HistoryItem item in historyList.OrderByDescending(x => x.PublishDate))
{
lstItem.Items.Add(item.PublishDate.ToString("yyyy-MM-dd HH:mm:ss") + " / " + item.Version);
}
}
/// <summary>
/// Performs an install of a database
/// </summary>
public void Install(InstallSetup setup)
{
if (setup.InstallStatus == InstallStatusConstants.Create)
{
//Conection cannot reference an existing database
if (SqlServers.TestConnectionString(setup.ConnectionString))
{
throw new Exception("The connection string references an existing database.");
}
//The new database name must be specified
if (string.IsNullOrEmpty(setup.NewDatabaseName))
{
throw new Exception("A new database name was not specified.");
}
//The connection string and the new database name must be the same
var builder = new System.Data.SqlClient.SqlConnectionStringBuilder(setup.ConnectionString);
if (builder.InitialCatalog.ToLower() != setup.NewDatabaseName.ToLower())
{
throw new Exception("A new database name does not match the specified connection string.");
}
SqlServers.CreateDatabase(setup);
}
else if (setup.InstallStatus == InstallStatusConstants.Upgrade)
{
//The connection string must reference an existing database
if (!SqlServers.TestConnectionString(setup.ConnectionString))
{
throw new Exception("The connection string does not reference a valid database.");
}
}
try
{
UpgradeInstaller.UpgradeDatabase(setup);
}
catch (InvalidSQLException ex)
{
var sb = new StringBuilder();
sb.AppendLine();
sb.AppendLine("BEGIN ERROR SQL");
sb.AppendLine(ex.SQL);
sb.AppendLine("END ERROR SQL");
sb.AppendLine();
Console.WriteLine(sb.ToString());
UpgradeInstaller.LogError(ex, sb.ToString());
throw;
}
catch (Exception ex)
{
throw;
}
}
private void cmdViewHistory_Click(object sender, EventArgs e)
{
string connectionString = SqlServers.BuildConnectionString(optConnectionIntegratedSecurity.Checked, cboConnectionDatabaseName.Text, cboConnectionServerName.Text, txtConnectionUserName.Text, txtConnectionPassword.Text);
if (!SqlServers.TestConnectionString(connectionString))
{
MessageBox.Show("The information does not describe a valid connection string.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
HistoryForm F = new HistoryForm(connectionString);
F.Show();
}
private void cmdOK_Click(object sender, System.EventArgs e)
{
this.SaveSettings();
if (tabControlChooseDatabase.SelectedTab == this.tabPageConnection)
{
var connectString = SqlServers.BuildConnectionString(optConnectionIntegratedSecurity.Checked, cboConnectionDatabaseName.Text, cboConnectionServerName.Text, txtConnectionUserName.Text, txtConnectionPassword.Text);
var valid = SqlServers.TestConnectionString(connectString);
if (valid)
{
this.InstallSettingsUI1.SaveUI(_setup);
this.Action = ActionTypeConstants.Upgrade;
this.DialogResult = DialogResult.OK;
this.Close();
}
else
{
MessageBox.Show("The information does not describe a valid connection string.");
}
}
else if (tabControlChooseDatabase.SelectedTab == this.tabPageCreation)
{
bool error = false;
if (_cbCreateDatabase.Checked)
{
error = CreateDatabase();
}
if (!error)
{
var outputConnectString = SqlServers.BuildConnectionString(optCreationIntegratedSecurity.Checked, txtCreationDatabaseName.Text, cboCreationServerName.Text, txtCreationUserName.Text, txtCreationPassword.Text);
if (SqlServers.TestConnectionString(outputConnectString))
{
//_connectionString = outputConnectString;
//_databaseName = cboCreationServerName.Text + "." + txtCreationDatabaseName.Text;
//_createdDb = true;
}
this.Action = ActionTypeConstants.Create;
this.DialogResult = DialogResult.OK;
this.Close();
}
}
else if (tabControlChooseDatabase.SelectedTab == this.tabPageAzureCopy)
{
this.Action = ActionTypeConstants.AzureCopy;
this.DialogResult = DialogResult.OK;
this.Close();
}
}
private void buttonCreationRefresh_Click(object sender, System.EventArgs e)
{
this.Cursor = Cursors.WaitCursor;
try
{
if (cboConnectionServerName.Items.Count == 0)
{
cboCreationServerName.DataSource = SqlServers.GetServers();
}
}
catch (Exception ex)
{
throw;
}
finally
{
this.Cursor = Cursors.Default;
}
}
private void buttonConnectionTestConnection_Click(object sender, System.EventArgs e)
{
if (string.IsNullOrEmpty(cboConnectionDatabaseName.Text))
{
MessageBox.Show("The database name must be specified.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
var connectString = SqlServers.BuildConnectionString(optConnectionIntegratedSecurity.Checked, cboConnectionDatabaseName.Text, cboConnectionServerName.Text, txtConnectionUserName.Text, txtConnectionPassword.Text);
var valid = SqlServers.TestConnectionString(connectString);
if (valid)
{
MessageBox.Show("Connection Succeeded.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("The information does not describe a valid connection string.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void ServerName_DropDown(object sender, System.EventArgs e)
{
this.Cursor = Cursors.WaitCursor;
try
{
if (cboConnectionServerName.Items.Count == 0)
{
((ComboBox)sender).DataSource = SqlServers.GetServers();
}
}
catch (Exception ex)
{
throw;
}
finally
{
this.Cursor = Cursors.Default;
}
}
public bool Save(string connectionString)
{
try
{
using (var conn = new SqlConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
using (var command = new SqlCommand(GetVersionUpdateScript(), conn))
{
SqlServers.ExecuteCommand(command);
}
return(true);
}
}
catch (Exception ex)
{
return(false);
throw;
}
}
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, new InstallSetup());
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);
}
internal static void ExecuteSQL(SqlConnection connection, SqlTransaction transaction, string sql, InstallSetup setup, List <KeyValuePair <Guid, string> > failedScripts, List <Guid> successOrderScripts)
{
if (sql.StartsWith("--##METHODCALL"))
{
CallMethod(sql, connection, transaction, setup);
return;
}
//Test for empty statements
var originalSQL = sql.Trim();
sql = originalSQL;
if (string.IsNullOrEmpty(sql))
{
return;
}
//Test for noop statements (all comments/empty strings)
var lines = sql.BreakLines().TrimAll();
lines.RemoveAll(x => x.StartsWith("--"));
lines.RemoveAll(x => x == "");
if ([email protected]())
{
return;
}
lines = sql.BreakLines().TrimAll(); //Reset
#region Get Script Key
var isBody = false;
var key = Guid.NewGuid();
var l = lines.FirstOrDefault(x => x.StartsWith("--MODELID: "));
if (l != null)
{
lines.Remove(l);
l = l.Replace("--MODELID:", string.Empty).Trim();
sql = string.Join("\n", lines.ToArray()); //Remove the model key from the SQL before run
//if (!Guid.TryParse(l, out key)) key = Guid.NewGuid();
}
else
{
l = lines.FirstOrDefault(x => x.StartsWith("--MODELID,BODY: "));
if (l != null)
{
lines.Remove(l);
l = l.Replace("--MODELID,BODY:", string.Empty).Trim();
sql = string.Join("\n", lines.ToArray()); //Remove the model key from the SQL before run
if (!Guid.TryParse(l, out key))
{
key = Guid.NewGuid();
}
else
{
isBody = true;
}
}
}
#endregion
if (string.IsNullOrEmpty(sql))
{
return;
}
#region Try to remove objects before creation
var dropObjectName = string.Empty;
var dropSQL = GetSQLDropScript(sql);
//Add a bit of convenience for dropping DB objects before creation
if (!string.IsNullOrEmpty(dropSQL))
{
try
{
if (!setup.CheckOnly)
{
var dropCommand = new SqlCommand(dropSQL, connection);
dropCommand.Transaction = transaction;
dropCommand.CommandTimeout = 0;
SqlServers.ExecuteCommand(dropCommand);
}
}
catch (Exception ex)
{
//Ignore. The scripts should not need this. It has been added for convenience
}
}
#endregion
var command = new SqlCommand(sql, connection);
command.Transaction = transaction;
command.CommandTimeout = 0;
try
{
if (!setup.CheckOnly)
{
var debugText = "[" + DateTime.Now.ToString() + "]\r\n";
const int MAX_SQL = 500;
var sqlLength = Math.Min(sql.Length, MAX_SQL);
debugText += sql.Substring(0, sqlLength);
if (sqlLength == MAX_SQL)
{
debugText += "...";
}
debugText += "\r\n\r\n";
Log.Verbose(debugText);
_timer.Restart();
SqlServers.ExecuteCommand(command);
_timer.Stop();
Log.Debug <long, string>("Time:{Elapsed:000} Sql:{sql}", _timer.ElapsedMilliseconds, sql);
if (successOrderScripts != null && isBody)
{
successOrderScripts.Add(key);
}
}
}
catch (SqlException sqlexp)
{
if ((sqlexp.Number == 1779) && sql.StartsWith("--PRIMARY KEY FOR TABLE"))
{
//Ignore this error
return;
}
else if ((sqlexp.Number == 1781) && sql.StartsWith("--DEFAULTS FOR TABLE"))
{
//Ignore this error
return;
}
else if (failedScripts != null)
{
//Ignore this error, we will re-process it
failedScripts.Add(new KeyValuePair <Guid, string>(key, originalSQL));
return;
}
else
{
throw new InvalidSQLException(sqlexp.Message, sqlexp)
{
SQL = sql, FileName = setup.DebugScriptName
};
}
}
catch (Exception ex) { throw; }
finally
{
if (command != null)
{
command.Dispose();
}
}
}
public static void Save(string connectionString, string modelKey, IEnumerable <nHydrateDbObject> list, SqlTransaction transaction)
{
SqlConnection conn = null;
if (transaction == null)
{
conn = new SqlConnection(connectionString);
conn.Open();
}
else
{
conn = transaction.Connection;
}
try
{
//Create the table if need be
using (var command3 = new SqlCommand("if not exists(select * from sys.tables where name = '__nhydrateobjects')" + Environment.NewLine +
"CREATE TABLE [dbo].[__nhydrateobjects]" +
"([rowid] [bigint] IDENTITY(1,1) NOT NULL," +
"[id] [uniqueidentifier] NULL," +
"[name] [nvarchar](450) NOT NULL," +
"[type] [varchar](10) NOT NULL," +
"[schema] [nvarchar](450) NULL," +
"[CreatedDate] [datetime] NOT NULL," +
"[ModifiedDate] [datetime] NOT NULL," +
"[Hash] [varchar](32) NULL," +
"[Status] [varchar](500) NULL," +
"[ModelKey] [uniqueidentifier] NOT NULL)", conn))
{
command3.Transaction = transaction;
SqlServers.ExecuteCommand(command3);
}
//Add columns if missing
{
var sql = new StringBuilder();
sql.AppendLine("if exists(select * from sys.tables where name = '__nhydrateobjects') AND not exists (select * from sys.columns c inner join sys.tables t on c.object_id = t.object_id where c.name = 'status' and t.name = '__nhydrateobjects')");
sql.AppendLine("ALTER TABLE [dbo].[__nhydrateobjects] ADD [status] [Varchar] (500) NULL");
using (var command3 = new SqlCommand(sql.ToString(), conn))
{
command3.Transaction = transaction;
SqlServers.ExecuteCommand(command3);
}
}
{
var sql = new StringBuilder();
sql.AppendLine("delete from [__nhydrateobjects] where [id] IS NULL and ModelKey = '" + UpgradeInstaller.MODELKEY + "'");
using (var command3 = new SqlCommand(sql.ToString(), conn))
{
command3.Transaction = transaction;
SqlServers.ExecuteCommand(command3);
}
}
//Save items to the table
foreach (var item in list.Where(x => x.Changed))
{
using (var command = new SqlCommand("if exists(select * from [__nhydrateobjects] where [id] = @id) " +
"update [__nhydrateobjects] set [name] = @name, [type] = @type, [schema] = @schema, [CreatedDate] = @CreatedDate, [ModifiedDate] = @ModifiedDate, [Hash] = @Hash, [ModelKey] = @ModelKey, [Status] = @Status where [id] = @id " +
"else " +
"insert into [__nhydrateobjects] ([id], [name], [type], [schema], [CreatedDate], [ModifiedDate], [Hash], [ModelKey], [Status]) values (@id, @name, @type, @schema, @CreatedDate, @ModifiedDate, @Hash, @ModelKey, @Status)", conn))
{
command.Transaction = transaction;
command.Parameters.Add(new SqlParameter()
{
DbType = DbType.Guid, Value = (item.id == Guid.Empty ? System.DBNull.Value : (object)item.id), ParameterName = "@id", IsNullable = true
});
command.Parameters.Add(new SqlParameter()
{
DbType = DbType.Int64, Value = item.rowid, ParameterName = "@rowid", IsNullable = false
});
command.Parameters.Add(new SqlParameter()
{
DbType = DbType.String, Value = item.name, ParameterName = "@name", IsNullable = false
});
command.Parameters.Add(new SqlParameter()
{
DbType = DbType.String, Value = item.type, ParameterName = "@type", IsNullable = false
});
command.Parameters.Add(new SqlParameter()
{
DbType = DbType.String, Value = (item.schema == null ? System.DBNull.Value : (object)item.schema), ParameterName = "@schema", IsNullable = true
});
command.Parameters.Add(new SqlParameter()
{
DbType = DbType.DateTime, Value = item.CreatedDate, ParameterName = "@CreatedDate", IsNullable = false
});
command.Parameters.Add(new SqlParameter()
{
DbType = DbType.DateTime, Value = DateTime.Now, ParameterName = "@ModifiedDate", IsNullable = false
});
command.Parameters.Add(new SqlParameter()
{
DbType = DbType.String, Value = item.Hash, ParameterName = "@Hash", IsNullable = false
});
command.Parameters.Add(new SqlParameter()
{
DbType = DbType.Guid, Value = item.ModelKey, ParameterName = "@ModelKey", IsNullable = false
});
command.Parameters.Add(new SqlParameter()
{
DbType = DbType.String, Value = item.Status, ParameterName = "@Status", IsNullable = true
});
SqlServers.ExecuteCommand(command);
}
}
}
catch (Exception ex)
{
throw;
}
finally
{
if (transaction == null && conn != null)
{
conn.Close();
conn.Dispose();
}
}
}
