async public static Task <object> GetDataSingleAsync(string conStr, string query, OracleParameter[] parameters = null)
{
object scalar = null;
try
{
using (OracleConnection connection = new OracleConnection(conStr))
{
connection.Open();
using (OracleCommand command = new OracleCommand(query, connection))
{
if (parameters != null)
{
foreach (OracleParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
scalar = await command.ExecuteScalarAsync();
}
}
}
catch (Exception)
{
throw;
}
return(scalar);
}
public override async Task CheckHealthAsync(HealthCheckContext context, OracleCheckSettings settings)
{
using (OracleConnection dbConnection = new OracleConnection(settings.ConnectionString))
{
await dbConnection.OpenAsync(context.CancellationToken);
OracleCommand oracleCommand = dbConnection.CreateCommand();
oracleCommand.CommandText = SelectOneSqlCommandText;
await oracleCommand.ExecuteScalarAsync(context.CancellationToken);
context.Succeed();
}
}
public override async Task <string> GetLastKeyEntry()
{
var sql = "SELECT * from (SELECT \"Key\" FROM \"Hashes\" ORDER BY \"Key\" DESC) WHERE rownum = 1";
using (var cmd = new OracleCommand(sql, Conn /*, Tran*/))
{
cmd.Transaction = Tran;
cmd.CommandType = CommandType.Text;
var str = await cmd.ExecuteScalarAsync();
return(str == null ? null : (string)str);
}
}
public async Task <Post> GetMostLikedPostAsync(int currentUserId, int roleId)
{
using (var connection = GetConnection()) {
connection.Open();
var cmdTxt = @"select * from (select p.post_id, row_number() over(order by (select count(1)
from table(like_list)) desc) as rn
from s_post p) where rn = 1";
var cmd = new OracleCommand(cmdTxt, connection);
var result = (int)(decimal)await cmd.ExecuteScalarAsync();
connection.Close();
return(await GetPostAsync(currentUserId, roleId, result));
}
}
public async Task <string?> GetEmailByMsisdnAsync(string msisdn)
{
using var connection = new OracleConnection(settings.NfsDb);
await connection.OpenAsync();
using var command = new OracleCommand(@"
SELECT mail
FROM xxvip_intf_emp_history_mv
WHERE nfs_emp_id = (SELECT nfs_emp_id FROM xxvip_intf_phone_mv WHERE regexp_replace(phone, '[^0-9]', '') = :msisdn AND rownum = 1)
AND rownum = 1
" , connection);
command.Parameters.Add("msisdn", msisdn);
return((string?)await command.ExecuteScalarAsync());
}
public static async Task <DateTime?> GetMaxNextTimeAsync(OracleConnection connection, string runtimeId, string nextTimeColumnName)
{
string commandText = $"SELECT MAX({nextTimeColumnName}) FROM {DbTableName} WHERE STATUS = 0 AND RUNTIMEID != :id";
if (connection.State != ConnectionState.Open)
{
await connection.OpenAsync().ConfigureAwait(false);
}
#pragma warning disable CA2100 // Review SQL queries for security vulnerabilities
using var command = new OracleCommand(commandText, connection);
#pragma warning restore CA2100 // Review SQL queries for security vulnerabilities
command.Parameters.Add(new OracleParameter("id", OracleDbType.NVarchar2, runtimeId, ParameterDirection.Input));
object result = await command.ExecuteScalarAsync().ConfigureAwait(false);
return(result as DateTime?);
}
public async Task <int> GetCountOfMonthlyCommentsGivenAsync(int currentUserId)
{
using (var connection = GetConnection()) {
connection.Open();
var quarry = @"select count(*) from s_comment
where extract(year from created_at) = extract(year from sysdate)
and extract(month from created_at) = extract(month from sysdate)
and user_id = :currentUserId";
var cmd = new OracleCommand(quarry, connection);
cmd.Parameters.Add("currentUserId", currentUserId);
var result = (int)(decimal)await cmd.ExecuteScalarAsync();
connection.Close();
return(result);
}
}
public bool UserLogin(string un, string pw)
{
NewConnection();
OracleCommand cmd = new OracleCommand("SELECT USERNAME FROM ACCOUNT WHERE username=:un and password=:pw", con);
cmd.Parameters.Add(":un", un);
cmd.Parameters.Add(":pw", pw);
string result = Convert.ToString(cmd.ExecuteScalarAsync());
if (String.IsNullOrEmpty(result))
{
return(false);
}
else
{
return(true);
}
}
public async Task <string> OracleAsync(string tableName)
{
var teamMembers = new List <string>();
var connectionString = OracleConfiguration.OracleConnectionString;
using (var connection = new OracleConnection(connectionString))
{
connection.Open();
using (var command = new OracleCommand("SELECT DEGREE FROM user_tables WHERE ROWNUM <= 1", connection))
{
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
teamMembers.Add(reader.GetString(reader.GetOrdinal("DEGREE")));
}
}
}
var insertSql = string.Format(InsertHotelOracleSql, tableName);
var countSql = string.Format(CountHotelOracleSql, tableName);
var deleteSql = string.Format(DeleteHotelOracleSql, tableName);
using (var command = new OracleCommand(insertSql, connection))
{
var insertCount = await command.ExecuteNonQueryAsync();
}
using (var command = new OracleCommand(countSql, connection))
{
var hotelCount = await command.ExecuteScalarAsync();
}
using (var command = new OracleCommand(deleteSql, connection))
{
var deleteCount = await command.ExecuteNonQueryAsync();
}
}
return(string.Join(",", teamMembers));
}
public async Task <int> GetCountOfMonthlyLikesGivenAsync(int currentUserId)
{
using (var connection = GetConnection()) {
connection.Open();
var quarry = @"select count(*) from s_post
where extract(year from created_at) = extract(year from sysdate)
and extract(month from created_at) = extract(month from sysdate)
and exists (
select * from table(like_list)
where column_value = :currentUserId)";
var cmd = new OracleCommand(quarry, connection);
cmd.Parameters.Add("currentUserId", currentUserId);
var result = (int)(decimal)await cmd.ExecuteScalarAsync();
connection.Close();
return(result);
}
}
/// <summary>
/// This method will execute a stored procedure that returns a single value.
/// </summary>
/// <param name="connectionString">the connection string</param>
/// <param name="sql">the stored procedure name or SQL statement</param>
/// <param name="parameters">array of OracleParameter objects</param>
/// <param name="timeout"></param>
/// <returns>an object value</returns>
internal protected async Task <object> ExecuteScalarAsync(string connectionString, string sql, OracleParameter[] parameters = null, int timeout = QUERY_TIMEOUT)
{
object result = null;
using (OracleConnection connection = new OracleConnection(connectionString))
{
using (OracleCommand cmd = new OracleCommand(sql, connection))
{
cmd.CommandTimeout = timeout;
cmd.CommandType = sql.Contains(' ') ? CommandType.Text : CommandType.StoredProcedure;
if (parameters != null)
{
foreach (OracleParameter parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
}
CaptureSqlStatement(sql, parameters);
await connection.OpenAsync();
result = await cmd.ExecuteScalarAsync().ConfigureAwait(false);
if (result.IsNull())
{
foreach (OracleParameter parameter in parameters)
{
if (parameter.Direction == ParameterDirection.Output)
{
result = parameter.Value;
break;
}
}
}
}
}
return(result);
}
public async Task <String> GetHandleZTabela(string nmTabela)
{
string sql = @"SELECT HANDLE
FROM Z_TABELAS
WHERE upper(NOME) = :Nome";
String handleZTabela = String.Empty;
using (OracleConnection connection = new OracleConnection(ConnectionString))
{
await Task.Run(() => connection.OpenAsync());
OracleCommand command = new OracleCommand(sql, connection);
command.Parameters.Add("NOME", nmTabela.ToUpper());
var handle = await command.ExecuteScalarAsync();
handleZTabela = Convert.ToString(handle);
}
return(handleZTabela);
}