c# return resultset from Oracle store procedure and fill a DataTable

Question

I'm a c# SQL Server developer new to Oracle programming. The code below works for setting cmdText to: "select * from myTable". I'd like to now put this in a stored procedure, I need help with how to write this Oracle stored procedure and get the results into a DataTable in my c# code. Thanks. Code:

private DbProviderFactory DbFactory
    {
        get
        {
            dbProviderFactory = dbProviderFactory  ?? DbProviderFactories.GetFactory(providerInvariantName);
            return dbProviderFactory;
        }
    }

public DataTable ExecDataTable(string cmdText, params IDataParameter[] cmdParams)
    {
        DataTable resultDT = new DataTable();

        using (DbConnection dbConn = DbFactory.CreateConnection())
        {
            dbConn.ConnectionString = connectionString;
            using (DbCommand dbCmd = DbFactory.CreateCommand())
            {
                dbCmd.CommandText = cmdText;
                dbCmd.Connection = dbConn;

                try
                {
                    dbConn.Open();

                    if (cmdParams != null)
                    {
                        dbCmd.Parameters.AddRange(cmdParams);
                    }

                    using (DbDataAdapter dbDA = DbFactory.CreateDataAdapter())
                    {
                        dbDA.SelectCommand = dbCmd;
                        dbDA.Fill(resultDT);
                    }
                }
                finally
                {
                    dbConn.Close();
                }
            }
        }
        return resultDT;
    }

Note: connectionString, providerInvariantName are set previously in code.

Also any advice on refactoring my code is appreciated this is the approach I've taken to support ODP.net and also ODBC connections to Oracle which is required on site.

Update

I can get this to working using:

new Oracle.DataAccess.Client.OracleParameter("result", Oracle.DataAccess.Client.OracleDbType.RefCursor, ParameterDirection.Output);

However I can't seem to get a generic solution to work using DbParameter or IDataParameter how do I do this to support ODBC and ODP.net ?

Answer 1

Set CommandType property,

dbCmd.CommandType=StoredProcedure;

Fill method opens and close database connection implicitly so no need to open & close database connection.

Use cmd.Parameter.AddWithValue() method to push both, parameter and value.

Answer 2

Do you really have to support ODBC. Otherwise just use only ODP.Net, it has optimised access to Oracle. The most efficient way to get data from Oracle is using ref cursors, you should learn to use it.

create or replace
PROCEDURE  SP_GET_TBL (o_rc OUT sys_refcursor) AS
   open o_rc for
        select Col1, Col2, Col3 from Tbl;  
END SP_GET_TBL;