public DbExecuter EndTransaction() { if (_transaction == null) { return(this); } _transaction = null; _connection.CloseConnection(); return(this); }
/// <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; } }
private void startListen() { lock (_listenerLock) { _npgsqlConnection?.CloseConnection(); _npgsqlConnection = new DbExecuter(_schema.ConnectionString).BuildNpgSqlConnetion(); _npgsqlConnection.Notification += _OnEvent; try { _schema.CreateTriggerFunction(true); lock (_listenOnTablesLock) { foreach (var table in _listenOnTables) { table.TriggerCreate("Notify-TableEvent", "Notify-TableEvent", true); } } _npgsqlConnection.OpenConnection(); NpgsqlCommand prepareCommand = _npgsqlConnection.CreateCommand(); prepareCommand.CommandText = $"LISTEN \"Notify-TableEvent\";"; prepareCommand.ExecuteNonQuery(); prepareCommand.Dispose(); SetState(ListeningConnectionState.Connected); while (true) { _npgsqlConnection.OpenConnection(); var timeout = _npgsqlConnection.Wait(TimeSpan.FromSeconds(3)); //var timeout = Task.Run(() => //{ // try // { // return !_npgsqlConnection.Wait(TimeSpan.FromSeconds(5)); // } // catch (Exception) // { // return false; // } //}, _tokenSource.Token).GetAwaiter().GetResult(); if (!_tableNotificationSubject.HasObservers) { break; } if (_tokenSource.Token.IsCancellationRequested) { break; } if (timeout) { Logger.Debug(() => "Sending Keepalive Message..."); NpgsqlCommand keepAliveCommand = _npgsqlConnection.CreateCommand(); keepAliveCommand.CommandText = "SELECT 1"; keepAliveCommand.ExecuteNonQuery(); keepAliveCommand.Dispose(); } } NpgsqlCommand closeCommand = _npgsqlConnection.CreateCommand(); closeCommand.CommandText = $"UNLISTEN \"Notify-TableEvent\";"; closeCommand.ExecuteNonQuery(); closeCommand.Dispose(); SetState(ListeningConnectionState.Disconnected); } catch (Exception ex) { SetState(ListeningConnectionState.Error); Logger.Error(() => $"[{Id}] {ex.Message}"); } finally { _npgsqlConnection.CloseConnection(); _npgsqlConnection = null; } } }
private void Listen() { _listeningConnection?.CloseConnection(); _connectionStateChangeSubject.OnNext(ListeningConnectionState.Connecting); _listeningConnection = new DbExecuter(_connectionBuilder).BuildNpgSqlConnetion(); _listeningConnection.Notification += _OnEvent; lock (_listenerLock) { //Console.WriteLine($"{Thread.CurrentThread.ManagedThreadId}: Listen({notificationChannel}) - LOCK"); bool ErrorOccured = false; _tokenSource = new CancellationTokenSource(); try { _listeningConnection.OpenConnection(); NpgsqlCommand prepareCommand = _listeningConnection.CreateCommand(); prepareCommand.CommandText = $"LISTEN \"{_notifyName}\";"; prepareCommand.ExecuteNonQuery(); prepareCommand.Dispose(); _connectionStateChangeSubject.OnNext(ListeningConnectionState.Connected); while (true) { _listeningConnection.OpenConnection(); var timeout = Task.Run(() => { try { return(!_listeningConnection.Wait(TimeSpan.FromSeconds(10))); } catch (Exception) { return(false); } }, _tokenSource.Token).GetAwaiter().GetResult(); if (_tokenSource.Token.IsCancellationRequested) { break; } if (timeout) { //Console.WriteLine("Sending Keepalive Message..."); NpgsqlCommand keepAliveCommand = _listeningConnection.CreateCommand(); keepAliveCommand.CommandText = "SELECT 1"; keepAliveCommand.ExecuteNonQuery(); keepAliveCommand.Dispose(); } } //Console.WriteLine($"{Thread.CurrentThread.ManagedThreadId}: Listen({notificationChannel}) - AFTER WHILE"); } catch (Exception ex) { Console.WriteLine(ex.Message); Thread.Sleep(500); ErrorOccured = true; } if (ErrorOccured) { _connectionStateChangeSubject.OnNext(ListeningConnectionState.Error); } else { _connectionStateChangeSubject.OnNext(ListeningConnectionState.Disconnected); } } }