Lingering Idle Oracle Connections? Persistent connections?

[jobs]

I've got an asp.net 2.0 site with class methods that pop in and out of
Oracle as follows.

Function GetNewOrderId() As Integer
If p_cnn.State = ConnectionState.Closed Then
p_cnn.Open()
End If
Dim cmd As New OracleCommand
With cmd
.CommandType = CommandType.Text
.CommandText = "select sq_order.NextVal from dual"
.CommandTimeout = 0
.Connection = p_cnn
End With
cmd.ExecuteScalar()
Dim result As Integer
result = cmd.ExecuteScalar()
cmd.Dispose()
p_cnn.Close()

Return result
End Function

I've reviewed every such method and all seem to be closing
connections.

We don't have any process that takes more than a few seconds. However,
when I look at session on the database I see several open for 15+
minutes.

I've asked the DBA to kill idle sessions after 10 minutes and I've
adjusted the IIS application pool to kill idle session after 10
minutes as well.

A few questions.

1. Any chance asp.net inline datasources could be causing this? Do
those close automatically after use?

2. How can I tell what an inactive connection was doing? So as to tie
it back to my code?
11 592D402C 54 13077 2255851 5929A3B8 85 PP1010 0 2147483644
INACTIVE DEDICATED 85 MYDB NETWORK?SERVICE 536:2732 MYCOMPANY
\MYSERVER01 MYSERVER01 w3wp.exe USER 00 0 5CD531E8 635300234 w3wp.exe
0 0 4444409 -1 0 0 0 11/15/2007 6:14:58 PM 185 NO NONE NONE NO
DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0

Here's one sample connection:



3. My connection string looks like this:


<add name="pp1010Connection" connectionString="Data
Source=MYDATABASE;Persist Security Info=True;User
ID=myuser;Password=mypassword;Unicode=True"
providerName="System.Data.OracleClient"/>

Should Persist Security Info be false?


Thanks in advance for any help or information!

 

[Guest]

use performance monitor (on the iis box) to see connection pool sizes (.net
clr data).

also your sample code does not release a connection on errors, this could
cause a leak if you get an error (say a deadlock)

use try/catch/finally blocks and close the connection in the finally.

-- bruce (sqlwork.com)

 

[jobs]

use try/catch/finally blocks and close the connection in the finally.

Thanks.

Question, If I use try/catch and want the exception error to happen so
that my global.asax application_error still fires and my error page
still happens where i have logging writing errors to a database, how
can I do that? do i need to raise an error again?

Also, any way to close all connections for that session when an error
occurs in the Application_Error sub of the global.asax? Keeping in
mind the instance for p_cnn might be unknown in the global.asax? Would
that be an okay approach?