Dotconnect Enterprise Library --> parameter of type "Cursor"

Dotconnect Enterprise Library --> parameter of type "Cursor"

Postby goethals.f » Mon 12 Oct 2009 13:22

Hello,

I have a question about the dotconnect/devart Enterprise Library concerning accessing/executing a stored procedure where one of the parmeter is an out-parameter of sys_refcursor --> see oracle procedure header below


Code: Select all
PROCEDURE SELECT_SECTION_BY_ID (p_Section_ID IN NUMBER, p_ResultSet OUT sys_refcursor)



In my first app I used the direct dotconnect objects (connection, parameter, OracleCursor, OracleReader) which works fine.

But how do you do the same with the Enterprise Library

I now have

Code: Select all
Dim tmpDatabase As Devart.Data.Oracle.EnterpriseLibrary.OracleDatabase = Nothing
Dim tmpConnection As Data.Oracle.OracleConnection = Nothing

If tmpDatabase Is Nothing Then
tmpDatabase = DatabaseFactory.CreateDatabase()
tmpConnection = tmpDatabase.CreateConnection()
End If


Dim _oracleCommand As Devart.Data.Oracle.OracleCommand = tmpDatabase.GetStoredProcCommand("SADERP_ADMIN.PCK_PG_SECTION.SELECT_SECTION_BY_ID")
tmpDatabase.AddInParameter(_oracleCommand, "p_Section_ID", System.Data.DbType.Int32, 100)

[b]tmpDatabase.AddOutParameter(_oracleCommand, "p_ResultSet", OracleDbType.Cursor, Nothing)[/b]


How do you have to specify the last parameter, so that it is of the specific cursor-type ?

and how te retrieve the cursor afterwards (via ExecuteNonQuery -> parameters -> GetDataReader)

thx
Fred
goethals.f
 
Posts: 60
Joined: Mon 18 May 2009 13:17

Postby goethals.f » Mon 12 Oct 2009 13:44

when I leave the out-parameter out, (so just 1 input-param) and use the method "ExecuteDataSet" then I got an dataset back wich has the results in it.

But,

*) is there a simple way by using in-out params, and just a simple dataReader to get the ref-cursor back
*) can you specify in an oracle proc 2 out-parameters as cursor --> and how do you do that via dotconnect (if ExecuteDataSet only returns 1 datatset)


thx
Fred
goethals.f
 
Posts: 60
Joined: Mon 18 May 2009 13:17

Postby Shalex » Tue 13 Oct 2009 13:59

Please don't add out parameters to the collection - out parameters will be initialized after stored procedure execution:
  • you can get the result set as data reader in the following way:
    Code: Select all
            Dim _oracleCommand As Devart.Data.Oracle.OracleCommand = tmpDatabase.GetStoredProcCommand("SADERP_ADMIN.PCK_PG_SECTION.SELECT_SECTION_BY_ID")
            tmpDatabase.AddInParameter(_oracleCommand, "p_Section_ID", System.Data.DbType.Int32, 100)

            tmpDatabase.ExecuteNonQuery(_oracleCommand)
            Dim reader As OracleDataReader = DirectCast(tmpDatabase.GetParameterValue(_oracleCommand, "p_ResultSet"), OracleCursor).GetDataReader
  • if your stored procedure contains 2 out cursor parameters, and you execute the ExecuteDataSet method, the result DataSet will contain two tables (Table and Table1) corresponding to your two result sets.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby goethals.f » Tue 13 Oct 2009 14:52

Shalex wrote:Please don't add out parameters to the collection - out parameters will be initialized after stored procedure execution:
  • you can get the result set as data reader in the following way:
    Code: Select all
            Dim _oracleCommand As Devart.Data.Oracle.OracleCommand = tmpDatabase.GetStoredProcCommand("SADERP_ADMIN.PCK_PG_SECTION.SELECT_SECTION_BY_ID")
            tmpDatabase.AddInParameter(_oracleCommand, "p_Section_ID", System.Data.DbType.Int32, 100)

            tmpDatabase.ExecuteNonQuery(_oracleCommand)
            Dim reader As OracleDataReader = DirectCast(tmpDatabase.GetParameterValue(_oracleCommand, "p_ResultSet"), OracleCursor).GetDataReader
  • if your stored procedure contains 2 out cursor parameters, and you execute the ExecuteDataSet method, the result DataSet will contain two tables (Table and Table1) corresponding to your two result sets.



OK

thx for the info. But now I have a small extra questions.
When I was doing some more test and had the DBMonitor running in the background.

I saw that by each execution of 'GetStoredProcCommand' there was a new connection made, openend, fetched the results and closed (close statement info was logged in the list). Even when I had my OracleDatabase and OracleConnection as global variables.
In the left tree I sas multiple connection-objects (which have each unique nr's and each have one executed command)

Is this the normal behaviour that after each execution the connection to the oracle DB is closed. Is there a setting to keep the connection really open and close it manually when I want. Or is the connection not really closed (but why it is then eacht time listed in the DBMonitor)

thx again

Fred
goethals.f
 
Posts: 60
Joined: Mon 18 May 2009 13:17

Postby Shalex » Wed 14 Oct 2009 12:51

We provide only interface of using our provider to Enterprise Library. Connection is used according to an internal implementation of Enterprise Library. But you can use the connection pooling feature of our provider (Pooling=true; in the connection string), which is turned on by default. In the case when Pooling=true; there will be only one connection from the database side (you can check the opened sessions with some tool) even though DBMonitor will display several connections.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle