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