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
Most   Popular
Upcoming Events

.NET Insight

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.