/// <summary> /// Drops the specified dbs /// </summary> /// <param name="dbNames"></param> protected virtual void DropDb(params string[] dbNames) { //wipe out the db name for the connection as need to connect to the service db! var curentDbName = Dsc.DbName; Dsc.DbName = string.Empty; try { foreach (var dbName in dbNames) { ConsoleEx.Write($"Dropping db: {dbName}... ", ConsoleColor.DarkYellow); //first cut the connection to the db if any using (var conn = new NpgsqlConnection(Dsc.GetConnectionString(serviceDatabase: true))) { conn.Open(); var cmd = new NpgsqlCommand(); cmd.Connection = conn; cmd.CommandText = $"SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '{dbName}';"; cmd.ExecuteNonQuery(); conn.CloseConnection(); } //it should be possible to drop the db now using (var conn = new NpgsqlConnection(Dsc.GetConnectionString(serviceDatabase: true))) { conn.Open(); var cmd = new NpgsqlCommand(); cmd.Connection = conn; cmd.CommandText = $"DROP DATABASE IF EXISTS {dbName}"; cmd.ExecuteNonQuery(); conn.CloseConnection(); } ConsoleEx.Write("Done!" + Environment.NewLine, ConsoleColor.DarkGreen); } Console.WriteLine(); } catch (Exception ex) { HandleException(ex); } finally { //restore previously set dbname Dsc.DbName = curentDbName; } }
/// <summary> /// Drops specified schemas /// </summary> /// <param name="schemaNames">k,v: schemaname,dbname</param> protected virtual void DropSchema(Dictionary <string, string> schemaNames) { //cache whatever is the current db name, as it will be restored later; in order to drop schema, need to connect to a particular db! var curentDbName = Dsc.DbName; try { foreach (var schema in schemaNames) { Dsc.DbName = schema.Value; ConsoleEx.Write($"Dropping schema: {schema.Key}... ", ConsoleColor.DarkYellow); //FIXME - need to check if db exists, oherwise will fail... using (var conn = new NpgsqlConnection(Dsc.GetConnectionString())) { conn.Open(); var cmd = new NpgsqlCommand(); cmd.Connection = conn; cmd.CommandText = $"DROP SCHEMA IF EXISTS {schema.Key} CASCADE;"; cmd.ExecuteNonQuery(); conn.CloseConnection(); } ConsoleEx.Write("Done!" + Environment.NewLine, ConsoleColor.DarkGreen); ConsoleEx.WriteLine("INFO:", ConsoleColor.DarkMagenta); ConsoleEx.WriteLine("With EF Core dropping schema is not enough! You will reset the migrations too. They should be located in the public schema.", ConsoleColor.DarkMagenta); } } catch (Exception ex) { HandleException(ex); } finally { //restore previously set dbname Dsc.DbName = curentDbName; } }