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

    oracleConnection no need to open connection to query a stored proc

      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)

      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

      Dim da As New OracleDataAdapter(cursCmd)

      Dim ds As New DataSet
      Dim unCodeBiensNi As CodeBiensNi


      ' 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")}



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

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