I'm making some tests with Dapper, but there is a strange error.
Even with "using" statement, if I don't call the database for some time, e.g. 5 min, the next call gives me a "not logged on", the next call works.
I have created a scheduler that calls the DB every minute and had no error, if I did the same, every 5 minutes, it gives me the error.
I think that the connections is still open in the pool, even if I call an "open" , "close" connection and "using".
The connection's creation:
protected IDbConnection Connection
{
get
{
var oc = new OracleConnection(DatabaseConfig.ConnectionString);
oc.Open();
return oc;
}
}
the method:
public async Task<List<ItemLista>> GetItemListaAsync(int idLista)
{
using (var connection = Connection)
{
string nomeProcedure = "GC.PCK_ECOM_LISTA_PRESENTE.ILC_LER";
var parameters = new OracleDynamicParameters();
parameters.Add("E_TX_GC", dbType: OracleDbType.NVarchar2, size: 4000, direction: ParameterDirection.Output);
parameters.Add("E_TX_TS", dbType: OracleDbType.NVarchar2, size: 4000, direction: ParameterDirection.Output);
parameters.Add("P_ID_LISTA_LIC", dbType: OracleDbType.Decimal, value: idLista);
parameters.Add("R_REF_CUR_ILC", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
var result = await connection.QueryAsync<ItemLista>(nomeProcedure, parameters, commandType: CommandType.StoredProcedure);
//this connection.close changes nothing
connection.Close();
return result.AsList();
}
}
Asp.net-core 2.2
Dapper 1.50.5 (can't use 1.60.1 cause I'm using Dapper.fluentMap, and it's not compatible yet)
Oracle.ManagedDataAccess.Core 2.18.3
The problem was in the Oracle Database. The user that I had used was in a profile with exactly 5 min of idle max time.
So, the solution is to configure the Oracle pool "connection timeout" less than database's profile max idle time.
https://docs.oracle.com/cd/B19306_01/win.102/b14307/featConnecting.htm