this.Connection.Close(); does not close the Oracle session! Pleasehelp!

Discussion in 'ASP .Net' started by S_K, Dec 6, 2007.

  1. S_K

    S_K Guest

    Hi all!

    I'm writing an ASP.NET web application that uses an Oracle database.

    I OPEN the Oracle connection by using the following code:

    if (this.ConnectionString != "")
    {
    this.Connection = new OracleConnection(this.ConnectionString);
    this.Connection.Open();
    return;
    }

    I then create the command object by using the following:

    OracleCommand DBCmd = new
    OracleCommand("PRL_STORE_PAYMENT_REQ_PKG.GET_PAYMENT_REQ_LOOKUP",
    objConnect.Connection);
    DBCmd.CommandType = System.Data.CommandType.StoredProcedure;

    I then I run the OracleCommand.ExecuteReader method to execute the
    stored procedure.

    I then CLOSE the connection by using the
    this.Connection.Close(); command.

    However, the sessions stay active!!!
    I have a very angry DBA on my tail about this one!
    Please help!

    Thanks
    Steve
     
    S_K, Dec 6, 2007
    #1
    1. Advertising

  2. Re: this.Connection.Close(); does not close the Oracle session! Please help!

    I'm pretty sure "OracleConnection" is not a standard Framework class. You'll
    probably have better luck asking the component vendor (or in their forums).
    My guess is that it has to do with connection pooling. Do the number of
    connections grow endlessly, or are they reused over and over? If it's the
    later, then you should actually have a very happy DBA.

    Scott



    "S_K" <> wrote in message
    news:...
    > Hi all!
    >
    > I'm writing an ASP.NET web application that uses an Oracle database.
    >
    > I OPEN the Oracle connection by using the following code:
    >
    > if (this.ConnectionString != "")
    > {
    > this.Connection = new OracleConnection(this.ConnectionString);
    > this.Connection.Open();
    > return;
    > }
    >
    > I then create the command object by using the following:
    >
    > OracleCommand DBCmd = new
    > OracleCommand("PRL_STORE_PAYMENT_REQ_PKG.GET_PAYMENT_REQ_LOOKUP",
    > objConnect.Connection);
    > DBCmd.CommandType = System.Data.CommandType.StoredProcedure;
    >
    > I then I run the OracleCommand.ExecuteReader method to execute the
    > stored procedure.
    >
    > I then CLOSE the connection by using the
    > this.Connection.Close(); command.
    >
    > However, the sessions stay active!!!
    > I have a very angry DBA on my tail about this one!
    > Please help!
    >
    > Thanks
    > Steve
     
    Scott Roberts, Dec 6, 2007
    #2
    1. Advertising

  3. S_K

    sloan Guest

    Re: this.Connection.Close(); does not close the Oracle session! Please help!

    Did you close the reader?

    if(null!=reader)
    {
    reader.Close();
    }

    ???

    A reader is a live firehouse....thus you use it, then you tidy up.
    You can't get reader, close the connection , then use the reader. That
    doesn't make sense.

    Use the reader as quickly as possible, then close it.




    "S_K" <> wrote in message
    news:...
    > Hi all!
    >
    > I'm writing an ASP.NET web application that uses an Oracle database.
    >
    > I OPEN the Oracle connection by using the following code:
    >
    > if (this.ConnectionString != "")
    > {
    > this.Connection = new OracleConnection(this.ConnectionString);
    > this.Connection.Open();
    > return;
    > }
    >
    > I then create the command object by using the following:
    >
    > OracleCommand DBCmd = new
    > OracleCommand("PRL_STORE_PAYMENT_REQ_PKG.GET_PAYMENT_REQ_LOOKUP",
    > objConnect.Connection);
    > DBCmd.CommandType = System.Data.CommandType.StoredProcedure;
    >
    > I then I run the OracleCommand.ExecuteReader method to execute the
    > stored procedure.
    >
    > I then CLOSE the connection by using the
    > this.Connection.Close(); command.
    >
    > However, the sessions stay active!!!
    > I have a very angry DBA on my tail about this one!
    > Please help!
    >
    > Thanks
    > Steve
     
    sloan, Dec 6, 2007
    #3
  4. S_K

    bruce barker Guest

    RE: this.Connection.Close(); does not close the Oracle session! Please

    you will need to turn connection pooling off (see your connect string
    properties). with pooling on, connection.close only returns the connection to
    the pool, it does not close it. you may make your dba happy by lowing the
    pool timeout.

    -- bruce (sqlwork.com)


    "S_K" wrote:

    > Hi all!
    >
    > I'm writing an ASP.NET web application that uses an Oracle database.
    >
    > I OPEN the Oracle connection by using the following code:
    >
    > if (this.ConnectionString != "")
    > {
    > this.Connection = new OracleConnection(this.ConnectionString);
    > this.Connection.Open();
    > return;
    > }
    >
    > I then create the command object by using the following:
    >
    > OracleCommand DBCmd = new
    > OracleCommand("PRL_STORE_PAYMENT_REQ_PKG.GET_PAYMENT_REQ_LOOKUP",
    > objConnect.Connection);
    > DBCmd.CommandType = System.Data.CommandType.StoredProcedure;
    >
    > I then I run the OracleCommand.ExecuteReader method to execute the
    > stored procedure.
    >
    > I then CLOSE the connection by using the
    > this.Connection.Close(); command.
    >
    > However, the sessions stay active!!!
    > I have a very angry DBA on my tail about this one!
    > Please help!
    >
    > Thanks
    > Steve
    >
     
    bruce barker, Dec 6, 2007
    #4
  5. S_K

    S_K Guest

    Re: this.Connection.Close(); does not close the Oracle session!Please

    On Dec 6, 3:13 pm, bruce barker
    <> wrote:
    > you will need to turn connection pooling off (see your connect string
    > properties). with pooling on, connection.close only returns the connection to
    > the pool, it does not close it. you may make your dba happy by lowing the
    > pool timeout.
    >
    > -- bruce (sqlwork.com)
    >
    >
    >
    > "S_K" wrote:
    > > Hi all!

    >
    > > I'm writing an ASP.NET web application that uses an Oracle database.

    >
    > > I OPEN the Oracle connection by using the following code:

    >
    > > if (this.ConnectionString != "")
    > > {
    > > this.Connection = new OracleConnection(this.ConnectionString);
    > > this.Connection.Open();
    > > return;
    > > }

    >
    > > I then create the command object by using the following:

    >
    > > OracleCommand DBCmd = new
    > > OracleCommand("PRL_STORE_PAYMENT_REQ_PKG.GET_PAYMENT_REQ_LOOKUP",
    > > objConnect.Connection);
    > > DBCmd.CommandType = System.Data.CommandType.StoredProcedure;

    >
    > > I then I run the OracleCommand.ExecuteReader method to execute the
    > > stored procedure.

    >
    > > I then CLOSE the connection by using the
    > > this.Connection.Close(); command.

    >
    > > However, the sessions stay active!!!
    > > I have a very angry DBA on my tail about this one!
    > > Please help!

    >
    > > Thanks
    > > Steve- Hide quoted text -

    >
    > - Show quoted text -


    I don't actually CLOSE the reader but I DO Dispose the reader:
    reader.Dispose();

    Doesn't that close the reader as well???

    Also, how do you turn off the connection pooling in the connection
    string? All I have is the basic connection string.

    Thanks for your quick replies.

    Steve
     
    S_K, Dec 6, 2007
    #5
  6. S_K

    bruce barker Guest

    Re: this.Connection.Close(); does not close the Oracle session! Pl

    no, dispose will return it to the pool if pooling is enabled (all dispose
    does is call close if needed).

    you will need the connection string settings docs for the driver you are
    using..

    -- bruce (sqlwork.com)


    "S_K" wrote:

    > On Dec 6, 3:13 pm, bruce barker
    > <> wrote:
    > > you will need to turn connection pooling off (see your connect string
    > > properties). with pooling on, connection.close only returns the connection to
    > > the pool, it does not close it. you may make your dba happy by lowing the
    > > pool timeout.
    > >
    > > -- bruce (sqlwork.com)
    > >
    > >
    > >
    > > "S_K" wrote:
    > > > Hi all!

    > >
    > > > I'm writing an ASP.NET web application that uses an Oracle database.

    > >
    > > > I OPEN the Oracle connection by using the following code:

    > >
    > > > if (this.ConnectionString != "")
    > > > {
    > > > this.Connection = new OracleConnection(this.ConnectionString);
    > > > this.Connection.Open();
    > > > return;
    > > > }

    > >
    > > > I then create the command object by using the following:

    > >
    > > > OracleCommand DBCmd = new
    > > > OracleCommand("PRL_STORE_PAYMENT_REQ_PKG.GET_PAYMENT_REQ_LOOKUP",
    > > > objConnect.Connection);
    > > > DBCmd.CommandType = System.Data.CommandType.StoredProcedure;

    > >
    > > > I then I run the OracleCommand.ExecuteReader method to execute the
    > > > stored procedure.

    > >
    > > > I then CLOSE the connection by using the
    > > > this.Connection.Close(); command.

    > >
    > > > However, the sessions stay active!!!
    > > > I have a very angry DBA on my tail about this one!
    > > > Please help!

    > >
    > > > Thanks
    > > > Steve- Hide quoted text -

    > >
    > > - Show quoted text -

    >
    > I don't actually CLOSE the reader but I DO Dispose the reader:
    > reader.Dispose();
    >
    > Doesn't that close the reader as well???
    >
    > Also, how do you turn off the connection pooling in the connection
    > string? All I have is the basic connection string.
    >
    > Thanks for your quick replies.
    >
    > Steve
    >
     
    bruce barker, Dec 6, 2007
    #6
  7. S_K

    Russell Guest

    Re: this.Connection.Close(); does not close the Oracle session!Please help!

    OracleConnection is in the System.Data.OracleClient namespace.

    Although Oracle also provides such a class, in their
    Oracle.DataAccess.Client namespace.

    If the connection pool advice doesn't work out, try also calling
    Dispose on the connection. Unless the issue has been resolved in a
    recent version, there's a flaw in the Framework class that
    necessitates this. And it can't hurt in any event.

    On Dec 6, 4:52 pm, "Scott Roberts" <-webworks-
    software.com> wrote:
    > I'm pretty sure "OracleConnection" is not a standard Framework class. You'll
    > probably have better luck asking the component vendor (or in their forums).
    > My guess is that it has to do with connection pooling. Do the number of
    > connections grow endlessly, or are they reused over and over? If it's the
    > later, then you should actually have a very happy DBA.
    >
    > Scott
    >
    > "S_K" <> wrote in message
    >
    > news:...
    >
    >
    >
    > > Hi all!

    >
    > > I'm writing an ASP.NET web application that uses an Oracle database.

    >
    > > I OPEN the Oracle connection by using the following code:

    >
    > > if (this.ConnectionString != "")
    > > {
    > > this.Connection = new OracleConnection(this.ConnectionString);
    > > this.Connection.Open();
    > > return;
    > > }

    >
    > > I then create the command object by using the following:

    >
    > > OracleCommand DBCmd = new
    > > OracleCommand("PRL_STORE_PAYMENT_REQ_PKG.GET_PAYMENT_REQ_LOOKUP",
    > > objConnect.Connection);
    > > DBCmd.CommandType = System.Data.CommandType.StoredProcedure;

    >
    > > I then I run the OracleCommand.ExecuteReader method to execute the
    > > stored procedure.

    >
    > > I then CLOSE the connection by using the
    > > this.Connection.Close(); command.

    >
    > > However, the sessions stay active!!!
    > > I have a very angry DBA on my tail about this one!
    > > Please help!

    >
    > > Thanks
    > > Steve- Hide quoted text -

    >
    > - Show quoted text -
     
    Russell, Dec 7, 2007
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. tranky
    Replies:
    0
    Views:
    315
    tranky
    Oct 15, 2007
  2. Sheetal
    Replies:
    3
    Views:
    805
  3. Feyruz
    Replies:
    4
    Views:
    2,337
    Sherm Pendley
    Oct 14, 2005
  4. hyejin
    Replies:
    8
    Views:
    378
    Martin Honnen
    Aug 29, 2006
  5. moso97ad
    Replies:
    4
    Views:
    134
    moso97ad
    Oct 19, 2009
Loading...

Share This Page