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");
}
}
ExecuteProcAsync(
string procName,
OracleParameter[] cmdParams,
OracleConnection conn,
OracleTransaction transaction = null,
bool commit = true
)
{
// OracleTransaction trans = transaction ?? conn.BeginTransaction();
OracleTransaction trans = transaction ?? (await conn.BeginTransactionAsync()) as OracleTransaction;
using (OracleCommand cmd = new OracleCommand())
{
try
{
await PrepareCommandAsync(procName,
cmdParams,
cmd,
CommandType.StoredProcedure,
conn,
trans);
// int affectedCounts = cmd.ExecuteNonQuery();
int affectedCounts = await cmd.ExecuteNonQueryAsync();
if (transaction == null || commit)
{
trans.Commit();
}
cmd.Parameters.Clear();
return(0);
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
cmd.Dispose();
if (transaction == null)
{
trans.Dispose();
}
}
}
}
ExecuteSqlAsync(
string zsgSql,
OracleParameter[] cmdParams,
OracleConnection conn,
OracleTransaction transaction = null,
bool commit = true
)
{
OracleTransaction trans = transaction ?? (await conn.BeginTransactionAsync()) as OracleTransaction;
using (OracleCommand cmd = new OracleCommand())
{
try
{
await PrepareCommandAsync(zsgSql,
cmdParams,
cmd,
CommandType.Text,
conn,
trans);
int affectedCounts = await cmd.ExecuteNonQueryAsync();
if (transaction == null || commit)
{
trans.Commit();
}
cmd.Parameters.Clear();
return(affectedCounts);
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
cmd.Dispose();
if (transaction == null)
{
trans.Dispose();
}
}
}
}
