The Basics on Using Oracle Data Provider for .NET

Recently I had to convert a classic ASP application that had an Oracle back-end to .NET 4.5. Since the types in System.Data.OracleClient were deprecated in .Net 4.0, Microsoft recommends that you use a third-party Oracle provider. I downloaded the latest Oracle Data Provider for .NET (ODP.NET).

My target system was 64 bit, so after unzipping the download, I ran the configuration batch file found at \ODP.NET_Managed121010\odp.net\managed\x64\configure.bat. Then I added a reference to the Oracle.ManagedDataAccess assembly.

I needed to retrieve data from the Oracle database three ways: a static select statement [13-26], a select with parameters [29-40] and a stored procedure with parameters [43-49]. For simplicity, I’m returning my records in a DataTable.

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;

namespace TestODP.NET
{
    public class GetMyOracleData
    {
        //Basic select 
        public DataTable GetAccounts() 
        { 
         StringBuilder mySQL = new StringBuilder();
         mySQL.Append("SELECT DISTINCT rtrim(acct_name) as accountName, ");
         mySQL.Append("rtrim(s_addr) as address, ");
         mySQL.Append("rtrim(s_city) as city, ");
         mySQL.Append("rtrim(s_state_cd) as state, ");
         mySQL.Append("sales_id as salesmanID, ");
         mySQL.Append("FROM adb.t_acct ");
         mySQL.Append("WHERE acct_id in ('1','2','3')");

         return MyOracleData.Accounts(mySQL.ToString());

        }

        //Select with parameters
        public DataTable GetCustomers()
        {
            StringBuilder mySQL = new StringBuilder();
            mySQL.Append("SELECT DISTINCT rtrim(cust_name) as custName, ");
            mySQL.Append("FROM adb.t_cust ");
            mySQL.Append("where acct_id = :ACCTID ");
            mySQL.Append("AND cust_id = :CUSTID ");
            mySQL.Append("order by cust_name");

            return MyOracleData.Customers(mySQL.ToString(), "0", "1");

        }

        //Stored procedure with parameters
        public DataTable GetCustomerDates()
        {
            StringBuilder mySQL = new StringBuilder();
            mySQL.Append("adb.getcustomerdates");

            return MyOracleData.CustomerDates(mySQL.ToString(), "0", "01/01/2000", "1");
        }
    }
}

Now for the actual calls to the Oracle database. Each class is quite self-explanatory. The most interesting is the last example that has several input parameters and returns a record set in an output parameter.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using Oracle.ManagedDataAccess;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;

namespace TestODP.NET
{
    public class MyOracleData
    {
        //Connection string of choice
        public static string ConnectionString()
        {
            return "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=myportnumber)))(CONNECT_DATA=(SID=mysid)));User Id=myuserid;Password=mypassword;";
        }

       //Run a generic SQL select statement that returns a recordset
       public static DataTable Accounts(string mySQLSTR)
        {
            OracleConnection connection = new OracleConnection(ConnectionString());
            DataTable returnTable = new DataTable();
            try
            {
                connection.Open();
                using (OracleCommand command = new OracleCommand(mySQLSTR, connection))
                {
                    returnTable.Load(command.ExecuteReader());
                }
            }
            catch (Exception ex)
            {
                returnTable = null;
            }
            finally
            {
                connection.Dispose();
            }
            return returnTable;
        }

       //Run a SQL select with parameters
       public static DataTable Customers(string mySQLSTR, string myACCTID, string myCUSTID)
       {
           string connectionString = ConnectionString();
           OracleConnection connection = new OracleConnection(connectionString);
           OracleDataReader readerResults = null;
           DataTable returnTable = new DataTable();
           try
           {
               connection.Open();
               using (OracleCommand command1 = new OracleCommand(mySQLSTR, connection))
               {
                   command1.BindByName = true;
                   command1.Parameters.Add(new OracleParameter("ACCTID", myACCTID));
                   command1.Parameters.Add(new OracleParameter("CUSTID", myCUSTID));
                   readerResults = command1.ExecuteReader();
                   command1.Parameters.Clear();
                   returnTable.Load(readerResults);
               }
           }
           catch (Exception ex)
           {
               returnTable = null;
           }
           finally
           {
               if (readerResults != null) { readerResults.Dispose(); }
               connection.Dispose();
           }
           return returnTable;
       }

       //Call a stored procedure that returns a recordset
       public static DataTable CustomerDates(string mySQLSTR, string myACCTID, string myTARGETDATE, string myCUSTID)
       {
           string connectionString = ConnectionString();
           OracleConnection connection = new OracleConnection(connectionString);
           OracleParameter paramOutput = null;
           DataTable returnTable = new DataTable();
           try
           {
               connection.Open();
               using (OracleCommand command1 = new OracleCommand(mySQLSTR, connection))
               {
                   command1.BindByName = true;
                   command1.CommandType = CommandType.StoredProcedure;
                   command1.Parameters.Add(new OracleParameter("VARACCTID", myACCTID));
                   command1.Parameters.Add(new OracleParameter("VARSTARTDATE", myTARGETDATE));
                   command1.Parameters.Add(new OracleParameter("VARCUSTID", myCUSTID));
                   paramOutput = command1.Parameters.Add(new OracleParameter("IO_CURSOR", OracleDbType.RefCursor, ParameterDirection.Output));
                   command1.ExecuteNonQuery();
                   returnTable.Load(((OracleRefCursor)paramOutput.Value).GetDataReader());
               }
           }
           catch (Exception ex)
           {
               returnTable = null;
           }
           finally
           {
               if (paramOutput != null) { paramOutput.Dispose(); }
               connection.Dispose();
           }
           return returnTable;
       }

    }
}

Finally, I added a small function to retrieve info on all the parameters for a given Oracle stored procedure – which really comes in handy when you don’t have access to the Oracle database.

        public static string StoredProcInfo(string SQLSTR)
        {
            //// This is a little utility that gets info about the parameters of an Oracle stored proc
            StringBuilder retString = new StringBuilder();
            OracleConnection connection = new OracleConnection(ConnectionString());
            try
            {
                connection.Open();
                using (OracleCommand spcommand = new OracleCommand(SQLSTR, connection))
                {
                    spcommand.CommandType = CommandType.StoredProcedure;
                    OracleCommandBuilder.DeriveParameters(spcommand);
                    retString.Append("Number of Parameter: " + spcommand.Parameters.Count.ToString() + "<br />");
                    for (int i = 0; i < spcommand.Parameters.Count; i++)
                    {
                        retString.Append(spcommand.Parameters[i].ParameterName.ToString());
                        retString.Append(" ");
                        retString.Append(spcommand.Parameters[i].Direction.ToString());
                        retString.Append(" ");
                        retString.Append(spcommand.Parameters[i].OracleDbType.ToString());
                        retString.Append("<br />");
                    }
                    spcommand.Parameters.Clear();
                }
            }
            catch (Exception ex) { retString.Append(ex.Message + "<br><br>" + ex.StackTrace); }
            finally { connection.Dispose(); }
            return retString.ToString();
        }
Tagged with:
Posted in Oracle
Advertisement