0 Replies Latest reply: Jan 17, 2013 1:56 PM by 612156 RSS

    oracleConnection no need to open connection to query a stored proc

    612156
      I discovered something I would it strange today. As I was looking at my colleague code, I found that when whe query a stored proc, there's no need to open the connection before the instruction dataAdapter fill.

      here's the example

      Public Function getCodeBiensNi() As List(Of CodeBiensNi)


      Dim CodeBiensNis As New List(Of CodeBiensNi)
      Try


      Dim cursCmd As OracleCommand = New OracleCommand("b240004k.get_bdm_code_biens_ni_2", connection)
      cursCmd.CommandType = CommandType.StoredProcedure

      Dim param0 As New OracleParameter

      param0.OracleDbType = OracleDbType.RefCursor
      param0.Direction = ParameterDirection.ReturnValue
      cursCmd.Parameters.Add(param0)

      Dim da As New OracleDataAdapter(cursCmd)

      Dim ds As New DataSet
      Dim unCodeBiensNi As CodeBiensNi

      da.Fill(ds)

      ' loop through the data set and display each table/row/column
      For Each dt As DataTable In ds.Tables

      For Each dr As DataRow In dt.Rows


      unCodeBiensNi = New CodeBiensNi With {.bcbn_code = dr.Item("bcbn_code"), _
      .bcbn_description = dr.Item("bcbn_description").ToString, _
      .bcbn_date_debut = dr.Item("bcbn_date_debut"), _
      .bcbn_date_fin = dr.Item("bcbn_date_fin")}




      CodeBiensNis.Add(unCodeBiensNi)
      Next
      Next
      ds.Dispose()
      da.Dispose()
      param0.Dispose()

      cursCmd.Dispose()


      Catch ex As Exception
      Throw New Exception("Erreur d'interrogation dans code biens ni. " + ex.Message)
      Finally

      End Try

      Return CodeBiensNis
      End Function

      connection is a private member that has a valid connection string but that's not open. ie. I didn't issue open.

      I even was on debug mode and all the time the state of connection is close.

      Although it work for store proc, it didn't work for simple SQL query. If issue something simple like "select 'toto' from dual", if I don't open the connection it doesn't work, it raise a exception.

      someone know why? I'm just too curious about that. Try to google it but i can't find the right keyword...