public async Task <DataTable> QueryAsync(UserData userData, string sql, List <DatabaseParameter> parameters = null) { try { var connString = BuildConnectionString( Properties.AppSettings.ConnectionString, userData.Username, PasswordHelper.Decrypt(userData.EncryptedPassword)); using var conn = new OracleConnection(connString); try { await conn.OpenAsync(); var tx = (OracleTransaction)await conn.BeginTransactionAsync(); DataTable result = new DataTable("DATA"); sql = sql.Replace("&AO", Properties.AppSettings.ApplicationOwner); using var cmd = new OracleCommand(sql, conn); cmd.Transaction = tx; if (parameters != null && parameters.Count > 0) { foreach (var paremeter in parameters) { if (sql.IndexOf(":" + paremeter.Name) > -1) { cmd.Parameters.Add(GetParameter(paremeter.Name, paremeter.Value, paremeter.DataType, paremeter.Direction, paremeter.Size)); } } } var reader = await cmd.ExecuteReaderAsync(); result.Load(reader); return(result); } catch (Exception ex) { throw ex; } finally { if (conn.State == ConnectionState.Open) { await conn.CloseAsync(); } } } catch (Exception ex) { throw ExceptionHelper.Throw(ex, ClassName, "QueryAsync"); } }
public async Task <int> ExecuteAsync(UserData userData, string commandText, CommandType commandType = CommandType.Text, List <DatabaseParameter> parameters = null) { try { var connString = BuildConnectionString( Properties.AppSettings.ConnectionString, userData.Username, PasswordHelper.Decrypt(userData.EncryptedPassword)); using var conn = new OracleConnection(connString); try { await conn.OpenAsync(); var tx = (OracleTransaction)await conn.BeginTransactionAsync(); commandText = commandText.Replace("&AO", Properties.AppSettings.ApplicationOwner); using var cmd = new OracleCommand(commandText, conn) { Transaction = tx, CommandType = commandType }; if (parameters != null && parameters.Count > 0) { foreach (var paremeter in parameters) { if (commandText.IndexOf(":" + paremeter.Name) > -1) { cmd.Parameters.Add(GetParameter(paremeter.Name, paremeter.Value, paremeter.DataType, paremeter.Direction, paremeter.Size)); } } } return(await cmd.ExecuteNonQueryAsync()); } catch (Exception ex) { throw ex; } finally { if (conn.State == ConnectionState.Open) { await conn.CloseAsync(); } } } catch (Exception ex) { throw ExceptionHelper.Throw(ex, ClassName, "ExecuteAsync"); } }
public async Task DisconnectFromDatabaseAsync() { try { await connection.CloseAsync(); await connection.DisposeAsync(); Console.WriteLine("Rozłączono z bazą danych."); } catch (OracleException ex) { Console.WriteLine(ex.Message); } }
public async Task <bool> TestConnectionAsync() { OracleConnection oracleConnection = new OracleConnection(this._connectionString); bool result; try { await oracleConnection.OpenAsync(); await oracleConnection.CloseAsync(); result = true; } catch { result = false; } return(result); }
public override async Task DropAsync() { using (var connection = new OracleConnection(ConnectionString)) { OracleConnection.ClearPool(connection); } // TODO: better way to force delete database? await Task.Delay(250); using (var connection = new OracleConnection(DbaConnectionString)) { await connection.OpenAsync(); var varUsername = $"\"{_connectionString.UserId}\""; await connection.ExecuteAsync($"drop user {varUsername} CASCADE"); await connection.CloseAsync(); } }
public async Task PopulateAsync(DbSchema[] schemas) { using (var conn = new OracleConnection(Connection.ConnectionString)) { await conn.OpenAsync(); this.Logger.Information($"Opened connection to {conn.Database} on {conn.DataSource}"); try { foreach (var schema in schemas) { var cleanCmd = new OracleCommand($"TRUNCATE TABLE {schema.TableName}", conn); await cleanCmd.ExecuteNonQueryAsync(); this.Logger.Information($"table {schema.TableName} truncated"); using (var bulkCopy = new OracleBulkCopy(conn, OracleBulkCopyOptions.Default)) { bulkCopy.DestinationTableName = $"{schema.TableName}"; foreach (var col in schema.Columns) { bulkCopy.ColumnMappings.Add(col.Name, col.Name); } bulkCopy.WriteToServer(schema.Records); } this.Logger.Information($"{schema.Records.Rows.Count} records have been imported into {schema.TableName}"); } this.Logger.Information($"All transactions have been committed."); } catch (Exception ex) { this.Logger.Error(ex, $"An error occured while doing operations."); throw; } await conn.CloseAsync(); } }
public override async Task CreateAsync() { using (var connection = new OracleConnection(DbaConnectionString)) { await connection.OpenAsync(); var varUsername = $"\"{_connectionString.UserId}\""; var varPassword = $"\"{_connectionString.Password}\""; await connection.ExecuteAsync($"CREATE USER {varUsername} IDENTIFIED BY {varPassword}"); await connection.ExecuteAsync($@"GRANT ""CONNECT"" TO {varUsername}"); await connection.ExecuteAsync($@"GRANT ""RESOURCE"" TO {varUsername}"); await connection.ExecuteAsync($"GRANT CREATE SESSION TO {varUsername}"); await connection.ExecuteAsync($"GRANT UNLIMITED TABLESPACE TO {varUsername}"); await connection.CloseAsync(); } }
public async Task CloseAsync() { await _conn.CloseAsync(); }