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

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
SteveI'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" <steve_kershaw@.yahoo.comwrote in message
news:3619d1f2-ef79-41cb-a0af-5df58dd223a0@.e10g2000prf.googlegroups.com...

Quote:

Originally Posted by

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

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" <steve_kershaw@.yahoo.comwrote in message
news:3619d1f2-ef79-41cb-a0af-5df58dd223a0@.e10g2000prf.googlegroups.com...

Quote:

Originally Posted by

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

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:

Quote:

Originally Posted by

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
>

On Dec 6, 3:13 pm, bruce barker
<brucebar...@.discussions.microsoft.comwrote:

Quote:

Originally Posted by

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:

Quote:

Originally Posted by

Hi all!

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

I OPEN the Oracle connection by using the following code:

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

I then create the command object by using the following:

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

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
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:

Quote:

Originally Posted by

On Dec 6, 3:13 pm, bruce barker
<brucebar...@.discussions.microsoft.comwrote:

Quote:

Originally Posted by

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:

Quote:

Originally Posted by

Hi all!

Quote:

Originally Posted by

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

Quote:

Originally Posted by

I OPEN the Oracle connection by using the following code:

Quote:

Originally Posted by

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

Quote:

Originally Posted by

I then create the command object by using the following:

Quote:

Originally Posted by

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

Quote:

Originally Posted by

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

Quote:

Originally Posted by

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

Quote:

Originally Posted by

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

Quote:

Originally Posted by

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
>

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" <srobe...@.no.spam.here-webworks-
software.comwrote:

Quote:

Originally Posted by

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" <steve_kers...@.yahoo.comwrote in message
>
news:3619d1f2-ef79-41cb-a0af-5df58dd223a0@.e10g2000prf.googlegroups.com...
>
>
>

Quote:

Originally Posted by

Hi all!

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

I OPEN the Oracle connection by using the following code:

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

I then create the command object by using the following:

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

Thanks
Steve- Hide quoted text -

>
- Show quoted text -