/// <summary>
/// Looks for a file named DatabaseDefaults.sql. If it exists it runs it. The purpose of this file is to create default data in the database. This will only be ran one time on initial creation of the database.
/// </summary>
private InstallResult BuildDatabaseDefaults()
{
OnBeforeDatabaseDefaults(EventArgs.Empty);
InstallResult result = new InstallResult();
Logger.Info("Creating initial database defaults from {0}.", INITIAL_DATABASE_DEFAULTS_FILENAME);
var script = UpdateScript.FromScriptName(INITIAL_DATABASE_DEFAULTS_FILENAME, ExecutingAssembly);
if (script.FileContents == null)
{
string message = string.Format("Initial database defaults file {0} could not be found. Database schema creation was successful but the defaults were not loaded into the database. This will cause a problem with login and other initial date the database is expecting. Try to to run it again in SQL manager if possible.", script.FullNamespaceName);
Logger.Error(message);
result.Success = false;
result.SetMessage(message);
return(result);
}
var dbCreateResuls = RunSingleScript(script);
if (dbCreateResuls.Status == SchemaChange.Status_Failed)
{
string message = string.Format("Building the initial database defaults for this database has failed. There was a problem while running the update file {0}. Here is the error: {1}. This will cause a problem with login and other initial date the database is expecting. Try to to run it again in SQL manager if possible.", script.Name, dbCreateResuls.ScriptErrors);
Logger.Error(message);
result.Success = false;
result.SetMessage(message);
}
Logger.Info("Initial database defaults created!");
OnAfterDatabaseDefaults(EventArgs.Empty);
return(result);
}
/// <summary>
/// Looks for the file named DatabaseBaseline.sql and runs it on a blank database.
/// </summary>
private InstallResult BuildInitialSchema()
{
OnBeforeInitialSchema(EventArgs.Empty);
InstallResult result = new InstallResult();
Logger.Info("Creating initial database schema from {0}.", INITIAL_DATABASE_SCHEMA_FILENAME);
var script = UpdateScript.FromScriptName(INITIAL_DATABASE_SCHEMA_FILENAME, ExecutingAssembly);
if (script.FileContents == null)
{
string message = string.Format("The initial schema creation for this database has failed. The {0} file could not be found at {1}.", INITIAL_DATABASE_SCHEMA_FILENAME, script.FullNamespaceName);
Logger.Error(message);
result.Success = false;
result.SetMessage(message);
return(result);
}
var dbCreateResuls = RunSingleScript(script);
if (dbCreateResuls.Status == SchemaChange.Status_Failed)
{
string message = string.Format("The initial schema creation for this database has failed. There was a problem while running the update file {0}. Here is the error: {1} ", script.Name, dbCreateResuls.ScriptErrors);
Logger.Error(message);
result.Success = false;
result.SetMessage(message);
}
Logger.Info("Initial database schema created!");
OnAfterInitialSchema(EventArgs.Empty);
return(result);
}
private Models.SchemaChange BuildSchemaFromFile(UpdateScript updateScript)
{
List <string> scriptLines = ExtractSqlFromFile(updateScript);
Models.SchemaChange script = new Models.SchemaChange();
script.DatabaseVersion = updateScript.Name.ToLower() == INITIAL_DATABASE_SCHEMA_FILENAME ? .5 : updateScript.Version;
script.LogThisChange = updateScript.Name.ToLower() == INITIAL_DATABASE_DEFAULTS_FILENAME ? false : true;
script.DateApplied = DateTime.Now;
script.ScriptName = updateScript.Name;
script.Status = Models.SchemaChange.Status_NotRan;
script.Notes = ExtractComments(scriptLines, NOTE_COMMENT_PREFIX);
script.ScriptContent = BuildScriptContent(scriptLines);
script.ScriptLines = scriptLines;
return(script);
}
/// <summary>
/// Load the scripts from the passed in assembly. The scripts have to be in the "Scripts" folder in the root of the assebmly to be detected.
/// </summary>
/// <param name="executingAssembly"></param>
/// <returns></returns>
internal static List <UpdateScript> FromAssembly(Assembly executingAssembly)
{
List <UpdateScript> scripts = new List <UpdateScript>();
string[] names = executingAssembly.GetManifestResourceNames();
foreach (var name in names)
{
if (name.Contains(ScriptNamespace))
{
int trimAmount = name.IndexOf(ScriptNamespace) + ScriptNamespace.Length + 1;
string scriptName = name.Substring(trimAmount, name.Length - trimAmount);
var updateScript = new UpdateScript(scriptName)
{
FullNamespaceName = name, FileContents = GetStream(executingAssembly, name)
};
scripts.Add(updateScript);
}
}
return(scripts.OrderBy(x => x.Version).ToList());
}
private List <string> ExtractSqlFromFile(UpdateScript script)
{
System.IO.StreamReader sqlFile = script.FileContents;
string sql = sqlFile.ReadToEnd();
sqlFile.Close();
string[] sqls = sql.Split(new string[] { "GO" }, StringSplitOptions.None);
List <string> sqlList = new List <string>();
foreach (var item in sqls)
{
if (!string.IsNullOrWhiteSpace(item))
{
if (!item.Contains("/*"))
{
sqlList.Add(item);
}
}
}
return(sqlList);
}
private SchemaChange RunSingleScript(string scriptName)
{
var updateScript = UpdateScript.FromScriptName(scriptName, ExecutingAssembly);
return(RunSingleScript(updateScript));
}
private SchemaChange RunSingleScript(UpdateScript script)
{
var schemaScript = BuildSchemaFromFile(script);
if (schemaScript == null)
{
schemaScript = new SchemaChange();
schemaScript.Status = Models.SchemaChange.Status_Failed;
schemaScript.ScriptErrors = string.Format("The script name {0} was not found. The script did not run.", script.Name);
return(schemaScript);
}
//Fire the Before Event if we are not creating inital schema or database defaults.
if (!script.Equals(INITIAL_DATABASE_DEFAULTS_FILENAME) && !script.Equals(INITIAL_DATABASE_SCHEMA_FILENAME))
{
ScriptRunEventArgs scriptEventArgs = new ScriptRunEventArgs()
{
ScriptName = script.Name,
ScriptVersion = script.Version
};
OnBeforeScriptRun(scriptEventArgs);
}
using (SqlConnection connection = new SqlConnection(ConnectionString.ConnectionString))
{
connection.Open();
foreach (var item in schemaScript.ScriptLines)
{
schemaScript.Comment = ExtractSingleComment(item, SCRIPT_COMMENT_PREFIX);
using (SqlTransaction transaction = connection.BeginTransaction())
{
try
{
SqlCommand sqlCommand = new SqlCommand(item, connection);
sqlCommand.Connection = connection;
sqlCommand.Transaction = transaction;
sqlCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
schemaScript.ScriptErrors = string.Format("Failed Running script {0} with the comment {1}. SQL Syntax is {2} Error: {3}", schemaScript.ScriptName, schemaScript.Comment, item, ex.Message);
//Get out becuase something broke!
schemaScript.Status = Models.SchemaChange.Status_Failed;
return(schemaScript);
}
}
}
schemaScript.Status = Models.SchemaChange.Status_Success;
if (schemaScript.LogThisChange)
{
AddSchemaChange(schemaScript);
}
//Fire the Before Event if we are not creating inital schema or database defaults.
if (!script.Equals(INITIAL_DATABASE_DEFAULTS_FILENAME) && !script.Equals(INITIAL_DATABASE_SCHEMA_FILENAME))
{
ScriptRunEventArgs scriptEventArgs = new ScriptRunEventArgs()
{
ScriptName = script.Name,
ScriptVersion = script.Version
};
OnAfterScriptRun(scriptEventArgs);
}
return(schemaScript);
}
}