CODEWITHSUNDEEP
c#oracle-databaseodp.net
Pablo
Pablo

Reputation: 29519

How to disable connection pool?

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?

Upvotes: 6

Views: 23030

Answers (1)

Christian Phillips
Christian Phillips

Reputation: 18759

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

Upvotes: 19

Related Questions