How to disable connection pool?

Question

Connection string that my app is using to connect to DB is the following:

    private const string oradb = "Data Source=(DESCRIPTION=(ADDRESS_LIST="
                    + "(ADDRESS=(PROTOCOL=TCP)(HOST=host.name)(PORT=1521)))"
                    + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=service.name)));"
                    + "User Id=myusername;Password=mypass;";

In all DB access points of my app I am using the following pattern:

        OracleConnection conn = new OracleConnection(oradb);

        try
        {
            Console.WriteLine("Opening DB Connection...");
            conn.Open();

            string queryString = string.Format(@"SELECT ...");

            using (OracleCommand command = new OracleCommand(queryString, conn))
            {
                using (OracleDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                     ...
                    }
                }
            }
        }
        catch (Exception e)
        {
            Console.WriteLine("Exception occured during DB access: {0}", e.Message);
            dbr.Error = e.Message;
        }
        finally
        {
            Console.WriteLine("Closing DB connection");
            conn.Close();
            conn.Dispose();
        }

For sure I am properly handling exceptions and in try/catch/finally closing AND disposing connection object. However, often I am receiving oracle service message that I am holding oracle sessions. Moreover, if I just leave my app open and next day try to make operation, I am getting ora-12537 network session end of file exception first time, then second attempt is going through. After some reading it looks like I have to disable connection pool. If this is the right way to solve, how to disable pool? If not, then what other thing can be wrong?

Solution

You could add Pooling=False in the connection string, but this means a new connection is created each time.

+ "User Id=myusername;Password=mypass;Pooling=False;";

Take a look at this article, it might help with your issue. Also, take a look at this website page, specifically the Using Connection Pooling section

Comments

[James Barrass]

Not exactly what you asked for but....OracleConnection.ClearPool(conn);

[Pablo]

Where I have to do it? Can I specify in connection string not to use pool at all?

[James Barrass]

No idea, hence the comment, not an answer. I think you can Clear the pool after Close()

[Micke]

A side note: as OracleConnection (i.e. DbConnection) implements IDisposable you could use the using pattern, instead of calling Close and Dispose in your finally block.

[Pablo]

@Micke: It will swallow exceptions and I will have no information about them, right?

[Micke]

No, I don't see why it would swallow exceptions, if you put a using(...) before your try. Of course, I might be wrong, but I don't think so.

[Panagiotis Kanavos]

@Pablo no it won't. Also, connection pooling doesn't waste sessions, it reduces the number of sessions needed. What is the actual problem you are trying to solve? Connection pooling reduces sessions by reusing existing ones. Closing it will make things worse, not better

[Pablo]

The problem is that my Oracle administrator complaining that my app leaving open sessions. So once a day I am receiving automated e-mail with session snippets (when it was open, how long). So I want to make sure I don't hold any open sessions.

[Pablo]

There is no way to use pool and avoid leaving open sessions on server, right?

[Panagiotis Kanavos]

@Pablo what open sessions on server? There is nothing wrong with connection pooling, in fact it reduces sessions by reusing connections. Connection pooling is one of the most important scalability mechanisms. Are you sure you aren't trying to solve the wrong problem?

[Pablo]

I am receiving automatic e-mail messages from oracle, telling that I am holding a session(s). However, all my transactions are taking place during few seconds ending by closing/disposing connection.

[Christian Phillips]

Are you thrashing the DB with Opening / closing connections?

[Pablo]

@christiandev: I brought a snippet of "template" how I open/close connection in my original post.