public static void DeleteDB(DBEnvironments environment)
{
string dbName = GetDatabaseName(environment);
if (!IsExists(environment, dbName))
{
MessageOut("{0} not found", dbName);
return;
}
// Create Connection String from Configuration File
string sqlConStr = BuildConnectionString(environment, "master");
MessageOut("in {0}", sqlConStr);
// Uninstall ASP.NET Membership table
// No need because we can delete just the database
// SqlServices.Uninstall(dbName, SqlFeatures.All, sqlConStr);
// Single user to cut current connections.
string sqlQuery = string.Format("ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE; \r\n GO", dbName);
SqlScriptHelper.ExecuteScript(sqlQuery, sqlConStr);
// DROP DATABASE in context of master db
sqlQuery = string.Format("DROP DATABASE {0}; \r\n GO", dbName);
SqlScriptHelper.ExecuteScript(sqlQuery, sqlConStr);
}
public static void CreateDB(DBEnvironments environment)
{
string dbName = GetDatabaseName(environment);
if (IsExists(environment, dbName))
{
MessageOut("{0} already exists", dbName);
return;
}
// Create Connection String from Configuration File
string sqlConStr = BuildConnectionString(environment, "master");
MessageOut("in {0}", sqlConStr);
// CREATE DATABASE in context of master db
string sqlQuery = string.Format("CREATE DATABASE {0}; \r\n GO", dbName);
SqlScriptHelper.ExecuteScript(sqlQuery, sqlConStr);
// Create schema_version table
sqlConStr = BuildConnectionString(environment, dbName);
MessageOut("in {0}", sqlConStr);
SqlScriptHelper.ExecuteScriptFromEmbeddedResource("BananaCoding.Tools.Database.DBScripts.create_schema.sql", sqlConStr);
}
public static void GrantPermission(DBEnvironments environment, string userToGrant)
{
string dbName = GetDatabaseName(environment);
// Create Connection String from Configuration File
string sqlConStr = BuildConnectionString(environment, dbName);
MessageOut("in {0}", sqlConStr);
userToGrant = userToGrant.Replace("NT_", "NT ").Replace("NETWORK_", "NETWORK ");
// Grant DATABASE in context of master db
string sqlQuery = SqlScriptHelper.LoadScriptFromEmbeddedResource("BananaCoding.Tools.Database.DBScripts.grant_permission.sql");
sqlQuery = sqlQuery.Replace("[DBNAME]", dbName);
sqlQuery = sqlQuery.Replace("[DBUSER]", userToGrant);
SqlScriptHelper.ExecuteScript(sqlQuery, sqlConStr);
MessageOut("Grant Permission '{0}' on {1}", userToGrant, dbName);
}
private static void RunSQLFromFile(Stopwatch sw, string sqlscript, string table_name, string sqlConStr)
{
// Run Sql from file
if (File.Exists(sqlscript))
{
using (TransactionScope scope = new TransactionScope())
{
MessageOut(@"Run fixture ------ {0} --------------------------", table_name);
sw.Reset();
sw.Start();
SqlScriptHelper.ExecuteScriptFile(sqlscript, sqlConStr);
sw.Stop();
MessageOut(@"Run fixture ------ {0} ({1}ms) ------ successfully.", table_name, sw.ElapsedMilliseconds);
scope.Complete();
}
}
}
internal static void Migrate(Task task)
{
DBEnvironments environment = task.Environment;
int migrateTo = task.MigrateTo;
bool aspNET = task.AspNet;
string dbName = GetDatabaseName(environment);
if (!IsExists(environment, dbName))
{
MessageOut("{0} not found", dbName);
return;
}
// Create Connection String from Configuration File
string sqlConStr = BuildConnectionString(environment, dbName);
MessageOut("in {0}", sqlConStr);
// Update Schema version
int?latestVersion = GetLatestSchemaVersion(sqlConStr);
// Grant Permission
if (!string.IsNullOrEmpty(task.GrantPermission))
{
GrantPermission(environment, task.GrantPermission);
}
// Migrate 000 first
if (!latestVersion.HasValue && aspNET)
{
InstallMembership(environment);
}
// Get all migration files
var sw = new Stopwatch();
string[] files = Directory.GetFiles(@"migrate", "*.sql");
Array.Sort <string>(files);
foreach (var sqlscript in files)
{
// ignore vim backup file
if (sqlscript.EndsWith("~"))
{
continue;
}
string versionName = Path.GetFileNameWithoutExtension(sqlscript);
int versionNumber = GetVersionNumber(versionName);
if (latestVersion.HasValue && versionNumber <= latestVersion)
{
continue;
}
if (migrateTo != 0 && versionNumber > migrateTo)
{
break;
}
using (TransactionScope scope = new TransactionScope())
{
MessageOut(@"Run script ------ {0} --------------------------", versionName);
sw.Reset();
sw.Start();
// Run Sql from file
SqlScriptHelper.ExecuteScriptFile(sqlscript, sqlConStr);
// Update Schema version
UpdateVersionSchema(sqlConStr, versionName);
sw.Stop();
MessageOut(@"Run script ------ {0} ({1}ms) ------ successfully.", versionName, sw.ElapsedMilliseconds);
scope.Complete();
}
}
// Load Fixtures
if (task.Fixture)
{
LoadFixtures(sqlConStr);
}
}