6

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?

Pablo
  • 28,133
  • 34
  • 125
  • 215
  • Not exactly what you asked for but....`OracleConnection.ClearPool(conn);` – James Sep 29 '15 at 10:08
  • Where I have to do it? Can I specify in connection string not to use pool at all? – Pablo Sep 29 '15 at 10:09
  • No idea, hence the comment, not an answer. I think you can Clear the pool after `Close()` – James Sep 29 '15 at 10:15
  • 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. – Micke Sep 29 '15 at 10:22
  • @Micke: It will swallow exceptions and I will have no information about them, right? – Pablo Sep 29 '15 at 10:23
  • 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. – Micke Sep 29 '15 at 10:25
  • @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 – Panagiotis Kanavos Sep 29 '15 at 10:26
  • 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 Sep 29 '15 at 10:31

1 Answers1

17

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

Christian Phillips
  • 18,399
  • 8
  • 53
  • 82
  • There is no way to use pool and avoid leaving open sessions on server, right? – Pablo Sep 29 '15 at 10:22
  • 1
    @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? – Panagiotis Kanavos Sep 29 '15 at 10:23
  • 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. – Pablo Sep 29 '15 at 10:26
  • 1
    Are you thrashing the DB with Opening / closing connections? – Christian Phillips Sep 29 '15 at 10:46
  • @christiandev: I brought a snippet of "template" how I open/close connection in my original post. – Pablo Sep 29 '15 at 11:06