Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

multiple sessions being created on the database by odp.net?

mlov83Jun 27 2010 — edited Jun 28 2010
guys hope someone can explain a little I have been looking at this for days and i just cant understand why??? A little background i have a flex application that calls a webservice the webservice is using odp.net to connect to oracle database. However it makes more then one connection. I know what you guys are thinking this idiot is not closing the connection. But thats not the case i close and dispose all connection and cmds. and i still get multiple connections. here is the kicker the application makes 12 connections, thats it. every call after that uses those 12 connections. which is ok with me. but what i cant figurered out for the live of me why 12 and not just 1 connection? does odp and asp.net have like an overflow of sort meaning if when its being called rapidly and the one conection is busy it will add aditional connections to handle the other calls? could that be the reason... im bafled here and i just cant understand why 12 and not1 connection? has anyone ever tried to figuered this out?
here is some sample code of my webservice.
 <WebMethod()> _
       Public Function GetVitalsDetails(ByVal w_source As String, _
                                        ByVal w_level As String, _
                                        ByVal w_id As String) As List(Of GetVitlsD)

        Dim W_FWVITALS_SOURCE As String
        Dim W_FWVITALS_SIGN_ID As String
        Dim W_FWVITALS_LEVEL As String
        Dim W_FWVITALS_COUNT As String
        Dim W_FWVITALS_MESSAGE As String
        Dim W_FWVITALS_DATE As String
        Dim W_FWVITALS_SEQUENCE As String




        ' this gets sends a value into the function stored on the database server. and recieves a response in the same trip
        Dim cn = New OracleConnection()

        cn.ConnectionString = ConnStr + "Statement Cache Size=1"


       


        'Try
        'create command object
        Dim cmd As New OracleCommand
        With cmd
            'specify that you are working with
            'stored procedure
            .CommandType = CommandType.StoredProcedure
            'provide the name of stored procedure
            ' you must provide the parameters to the procedure in the same order as the proceduere
            .CommandText = "FINANCE_VITALS.GetVitalsDetails"
            cmd.Parameters.Add("trans_cursor", OracleDbType.RefCursor, Data.ParameterDirection.Output)
            cmd.Parameters.Add("source_D", OracleDbType.Varchar2, ParameterDirection.Input).Value = w_source
            'cmd.Parameters.Add("date_D", OracleDbType.Varchar2, ParameterDirection.Input).Value = w_date
            cmd.Parameters.Add("level_D", OracleDbType.Varchar2, ParameterDirection.Input).Value = w_level
            cmd.Parameters.Add("id_D", OracleDbType.Varchar2, ParameterDirection.Input).Value = w_id
            'cmd.Parameters.Add("sequence_D", OracleDbType.Varchar2, ParameterDirection.Input).Value = w_seq


            If cn.State = ConnectionState.Open Then
                '        NOTHING
            Else
                .Connection = cn
                .Connection.Open()

            End If





            .ExecuteNonQuery()
            Dim results As New List(Of GetVitlsD)()
            'create the dataadapter object
            Dim adp As New OracleDataAdapter(cmd)
            'create the offline datatable
            Dim dt As New Data.DataTable
            'Dim ds As New System.Data.DataSet
            'fill the data table with rows
            adp.Fill(dt)
            adp.Dispose()
            For Each dr As System.Data.DataRow In dt.Rows
                W_FWVITALS_SOURCE = dr("FWVITALS_SOURCE").ToString
                W_FWVITALS_DATE = dr("FWVITALS_DATE").ToString
                W_FWVITALS_MESSAGE = dr("FWVITALS_MESSAGE").ToString
                W_FWVITALS_LEVEL = dr("FWVITALS_LEVEL").ToString
                W_FWVITALS_COUNT = dr("FWVITALS_COUNT").ToString
                W_FWVITALS_SIGN_ID = dr("FWVITALS_SIGN_ID").ToString
                W_FWVITALS_SEQUENCE = dr("FWVITALS_SEQUENCE").ToString

                Dim VIT As New GetVitlsD()
                VIT.fwvitals_source = W_FWVITALS_SOURCE
                VIT.fwvitals_date = W_FWVITALS_DATE
                VIT.fwvitals_message = W_FWVITALS_MESSAGE
                VIT.fwvitals_level = W_FWVITALS_LEVEL
                VIT.fwvitals_count = W_FWVITALS_COUNT
                VIT.fwvitals_sign_id = W_FWVITALS_SIGN_ID
                VIT.fwvitals_sequence = W_FWVITALS_SEQUENCE

                results.Add(VIT)

            Next

            adp.Dispose()
            cmd.Connection.Close()
            cn.Close()
            cmd.Dispose()
            dt.Dispose()
            Return results



        End With
        cn.Close()
        'Catch ex As Exception
        '    '    Dim pr = New GetVitlsD
        '    '    Dim results = New List(Of GetVitlsD)()
        '    '    ' IF ERROR OCCURS THEN RETURN ERROR.
        '    '    pr.fwvitals_count = "ERROR"
        '    '    results.Add(pr)
        '    '    Return results
        '    If cn.State = ConnectionState.Open Then
        '        cn.Close()
        '    End If
        'End Try

    End Function
please dont comment on the exception handling for right now is commented out... i know...
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 26 2010
Added on Jun 27 2010
2 comments
1,985 views