Clever Geek Handbook

C # returns result from Oracle storage procedure and populates DataTable

I am a C # SQL Server developer for Oracle programming . The code below works for setting cmdText: "select * from myTable". I would like to put this into a Stored Procedure now, I need help on how to write this Oracle Stored Procedure and get the results into a DataTable in my C # code . Thank you. 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 earlier in the code.

Also any advice on refactoring my code is appreciated . This is the approach I used to support ODP.net as well as the Oracle ODBC connections that the site requires.

Update

I can get this to work using:

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

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

+2


source to share


2 answers


You really need to support ODBC. Otherwise just use only ODP.Net, it has optimized Oracle access. The most efficient way to get data from Oracle is to use reflector cursors, you must learn how 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;
+1


source


Set the CommandType property,

dbCmd.CommandType=StoredProcedure;


The fill method opens and closes a database connection implicitly, so there is no need to open and close a database connection.

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

+1


source






More articles:


All Articles