When I cancel my Task using CancellationToken
method, It usually get's cancelled around min. 30 seconds. Time depends on how heavy query in Oracle is, it can take even to 1 minute.
Task is called on different thread, using Task.Run
(doing some UI loading animations while fetching data) .
If user cancels Task (closes window) while running and starts some new Oracle Task in app, things get's messed up - suddenly a lot of opened sessions appear in Oracle. I want avoid that by speeding up cancellation signal.
Here is my code:
//global static variable, defined when window opens
CancellationTokenSource cts= new CancellationTokenSource();
//CancellationToken get's cancelled when user leaves window
private void OnCloseWindow(object target, ExecutedRoutedEventArgs e)
{
cts.Cancel();
}
public async Task<List<EmployeeModel>> Get_Employees(CancellationToken cts)
{
var data = new List<EmployeeModel>();
try
{
using (OracleConnection con = new OracleConnection(conn_string))
{
con.OpenAsync(cts);
OracleCommand cmd = new OacleCommand("Myschema.Procedure_1", con)
{
CommandType = CommandType.StoredProcedure
};
cmd.Parameters.Add("name_in", OracleDbType.Varchar2).Value = "John";
cmd.Parameters.Add("surname_in", OracleDbType.Varchar2).Value = "Black";
using (cts.Register(() => cmd.Cancel()))
{
using (OracleDataReader rdr = cmd.ExecuteReaderAsync(cts))
{
while (await rdr.ReadAsync(cts))
{
data.Add(new EmployeeModel()
{
NAME = rdr.IsDBNull(0) ? null : rdr.GetString(0),
SURNAME = rdr.IsDBNull(1) ? null : rdr.GetString(1)
});
}
}
}
}
}
return data;
}
catch (OperationCanceledException)
{
return null;
}
catch (Exception)
{
return null;
}
}
I'm using OracleManagedDataAccess.dll, version 4.122.19.1. My workaround is to use setting on connection string for Max pool size=3
. That - at least during testing - doesn't produce more than 3 sessions at once in Oracle, but I'm not sure if this is the right way.
Is there anything else I can do to fasten cancellation to Oracle? Some kind of mechanism that I'm not aware of?