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