This discussion is archived
0 Replies Latest reply: Jan 17, 2013 11:56 AM by 612156 RSS

oracleConnection no need to open connection to query a stored proc

612156 Newbie
Currently Being Moderated
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...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points