.NET CORE 2.1 - Oracle DB exceeded maximum connect time (ORA-02399) even though I called Dispose()

Question

I have a method written in C# that connects to Oracle db, do the work and then call Dispose() in the finally part. However, I got this error:
ORA-02399:exceeded maximum connect time, you are being logged off When I refresh, I got another error:ORA-01012: not logged on The third refresh loads the data.

Project is: class library .NET Core 2.1
NugetPackage:Oracle.ManagedDataAccess.Core (2.18.3)
Here is my code

public object GetAllDataByDate(string user, DateTime from,DateTime to, int count=10)
    {

        if (con.State != ConnectionState.Open)
            con.Open();
        try
        {
           return ExtractData(user, from,to, count);

        }
        catch (Exception ex)
        {
            return new
            {
                Error = "Error occured during the extraction of data",
                ex.GetType().FullName,
                ex.Message,
                ex.InnerException
            };
        }
        finally
        {
            con.Dispose();
        }

    }

Do I need to call con.Close() also ? or Did I miss something in my code ? Thanks

no I didn't because Dispose normally calls close implicitly. — Mohamed Sahbi, Dec 04 '18 at 14:53

Neal · Answer 1 · 2018-12-04T14:44:06.743

2

Your try catch block includes a return statement, this will circumvent the finally block from running. Refactor to try and use: https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/using-statement

using(var con = new DisposableObject()){}

Pattern, and don't return inside of a try catch finally block. Anything that implements IDisposable can be used with the using block (99% of the time recommended but there's always a few exceptions that prove the rule.

A quick sample of the behaviour your try catch block behaviour:

using System;

public class Program
{
    public static void Main()
    {
        Console.WriteLine(Test());
    }
    public static string Test() 
    {
        var response = "Hi";
        try
        {
            response = "bye";
            return response;
        }catch(Exception ex){
            response = "fail";
        }finally{
            response = "finally";
        }
        return response;
    }
}

The outcome will be "bye" not "finally"

edited Dec 04 '18 at 14:44

answered Dec 04 '18 at 14:37

Neal

573
3
16

I know about using statement, but according to Microsoft docs, the code in Finally block will be executed, isn't that right ? – Mohamed Sahbi Dec 04 '18 at 15:07
Only if you are not returning in the try or catch block :-) – Neal Dec 04 '18 at 16:29
1

Your return is breaking execution at that point so the block does not continue execution. Try the sample with my try catch. Dotnetfiddle is a great place to test code behaviour in a minimalistic fashion. – Neal Dec 04 '18 at 16:38
I did try it, if you declare response outside that method and then print it, you will see that it change in the finally block. Return in try and catch doesn't mean that the finally block won't be executed. And I don't return anything after that (not like the example that you mentioned) – Mohamed Sahbi Dec 05 '18 at 11:48
You say that it executes the finally block if returning inside your try block. This does not seem correct. See the sample that I posted: https://dotnetfiddle.net/hq4u2G – Neal Dec 05 '18 at 12:05
But best of luck, seems like you know what you're doing :-) – Neal Dec 05 '18 at 12:06
here is what I meant https://dotnetfiddle.net/Z63Ehy .Anyway, thank you for your help – Mohamed Sahbi Dec 05 '18 at 19:32
My apologies, seems like the behaviour alters somewhat. Please see the following question: https://stackoverflow.com/questions/421797/what-really-happens-in-a-try-return-x-finally-x-null-statement In essence: the IL strips your return statement. Make sure you aren't keeping connections alive, or instantiating multiple cons. Good luck – Neal Dec 06 '18 at 08:24

Mohamed Sahbi · Accepted Answer · 2018-12-08T11:22:24.183

Finally I resolved it. It wasn't related to the code, but the connection string itself. Oracle by default enable pooling in .NET, which results the timeout problem. for more details, here is the documentation.
I just had to add Pooling=False; to my connection string.

Meanwhile, I changed con.Dispose(); to con.Close(); because it failed in the UnitTest when I called twice in the same TestMethod (Although it did work during the debugging)

I just found out this answer while writing this respone, so if you want a simple explanation about Connection pooling, I recommend reading that answer.

.NET CORE 2.1 - Oracle DB exceeded maximum connect time (ORA-02399) even though I called Dispose()

2 Answers2