Call Oracle from ADO.NET: C#, OCI: Invoke the get_departments Procedure: Listing 3

Get a cursor by defining a cursor parameter with ParameterDirection set to Output, and call the ExecuteReader method in the OracleCommand instance (objCmd) to receive the cursor and print the result set referenced by the OracleDataReader instance (objReader).

using System;
using System.Text;
using System.Data.OracleClient;
using System.Data;

namespace Invoke_PLSQL_Code
{
   class Program
   {
      internal static void PrintReader(OracleDataReader 
         objReader)
      {
         for (int i = 0; i < objReader.FieldCount; i++)
         {
            System.Console.Write("{0}\t", 
               objReader.GetName(i));
         }
         System.Console.Write("\n");

         while (objReader.Read())
         {
            for (int i = 0; i < objReader.FieldCount; i++)
            {
               System.Console.Write("{0}\t", 
                  objReader[i].ToString());
            }
            System.Console.Write("\n");
         }
      }

      static void Main(string[] args)
      {
         using (OracleConnection objConn = new 
            OracleConnection(
               "Data Source=orcl; 
               User ID=scott; Password=tiger")) 
         {
            OracleCommand objCmd = new 
               OracleCommand();
            objCmd.Connection = objConn;
            objCmd.CommandText = 
               "human_resources.get_departments";
            objCmd.CommandType = 
               CommandType.StoredProcedure;
            objCmd.Parameters.Add("cur_department", 
               OracleType.Cursor).Direction = 
               ParameterDirection.Output;

            try
            {
               objConn.Open();
               OracleDataReader objReader = 
                  objCmd.ExecuteReader();
               PrintReader(objReader);
            }
            catch (Exception ex)
            {
               System.Console.WriteLine("Exception: {0}", 
                  ex.ToString());
            }

            objConn.Close();
         }
      }
   }
}
comments powered by Disqus
Upcoming Events

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.