Call Oracle from ADO.NET: C#, OCI: Work in a Disconnected Environment: Listing 6

Invoke the procedures defined in the human_resources package using the DataSet and DataAdapter objects model in order to access the Employee entities in a disconnected environment.

• 08/21/2008

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

namespace Invoke_PLSQL_Code
{
   class Program
   {
      static void Main(string[] args)
      {
         using (OracleConnection objConn = new 
            OracleConnection(
            "Data Source=orcl; 
            User ID=scott; Password=tiger")) 
         {
            OracleDataAdapter objAdapter = new 
               OracleDataAdapter();

            OracleCommand objSelectCmd = new 
               OracleCommand();
            objSelectCmd.Connection = objConn;
            objSelectCmd.CommandText = 
               "human_resources.select_employee";
            objSelectCmd.CommandType = 
               CommandType.StoredProcedure;
            objSelectCmd.Parameters.Add("cur_employee", 
               OracleType.Cursor).Direction = 
               ParameterDirection.Output;
            objAdapter.SelectCommand = objSelectCmd;

            OracleCommand objInsertCmd = new 
               OracleCommand();
            objInsertCmd.Connection = objConn;
            objInsertCmd.CommandText = 
               "human_resources.insert_employee";
            objInsertCmd.CommandType = 
               CommandType.StoredProcedure;
            objInsertCmd.Parameters.Add("p_empno", 
               OracleType.Number, 4, "empno");
            objInsertCmd.Parameters.Add("p_ename", 
               OracleType.VarChar, 10, "ename");
            objInsertCmd.Parameters.Add("p_job", 
               OracleType.VarChar, 9, "job");
            objInsertCmd.Parameters.Add("p_mgr", 
               OracleType.Number, 4, "mgr");
            objInsertCmd.Parameters.Add("p_hiredate", 
               OracleType.DateTime, 12, "hiredate");
            objInsertCmd.Parameters.Add("p_sal", 
               OracleType.Number, 7, "sal");
            objInsertCmd.Parameters.Add("p_comm", 
               OracleType.Number, 7, "comm");
            objInsertCmd.Parameters.Add("p_deptno", 
               OracleType.Number, 7, "deptno");
            objAdapter.InsertCommand = objInsertCmd;

            OracleCommand objUpdateCmd = new 
               OracleCommand();
            objUpdateCmd.Connection = objConn;
            objUpdateCmd.CommandText = 
               "human_resources.update_employee";
            objUpdateCmd.CommandType = 
               CommandType.StoredProcedure;
            objUpdateCmd.Parameters.Add("p_empno", 
               OracleType.Number, 4, "empno");
            objUpdateCmd.Parameters.Add("p_ename", 
               OracleType.VarChar, 10, "ename");
            objUpdateCmd.Parameters.Add("p_job", 
               OracleType.VarChar, 9, "job");
            objUpdateCmd.Parameters.Add("p_mgr", 
               OracleType.Number, 4, "mgr");
            objUpdateCmd.Parameters.Add("p_hiredate", 
               OracleType.DateTime, 10, "hiredate");
            objUpdateCmd.Parameters.Add("p_sal", 
               OracleType.Number, 7, "sal");
            objUpdateCmd.Parameters.Add("p_comm", 
               OracleType.Number, 7, "comm");
            objUpdateCmd.Parameters.Add("p_deptno", 
               OracleType.Number, 7, "deptno");
            objAdapter.UpdateCommand = objUpdateCmd;

            OracleCommand objDeleteCmd = new 
               OracleCommand();
            objDeleteCmd.Connection = objConn;
            objDeleteCmd.CommandText = 
               "human_resources.delete_employee";
            objDeleteCmd.CommandType = 
               CommandType.StoredProcedure;
            objDeleteCmd.Parameters.Add("p_empno", 
               OracleType.Number, 4, "empno");
            objAdapter.DeleteCommand = objDeleteCmd;

            try
            {
               DataTable dtEmp = new DataTable();
               objAdapter.Fill(dtEmp);

               System.Console.WriteLine(
                  "Employee count = {0}", 
                  dtEmp.Rows.Count);

               dtEmp.Rows.Add(7935, "John", "Manager", 
                  7782, DateTime.Now, 1300, 0, 10);
               objAdapter.Update(dtEmp);

               foreach (DataColumn objColumn in 
                  dtEmp.Columns)
               {
                  System.Console.Write("{0}\t", 
                     objColumn.Caption);
               }
               System.Console.WriteLine();

               foreach (DataRow objRow in dtEmp.Rows)
               {
                  for(int i=0; i<dtEmp.Columns.Count; i++)
                  {
                     System.Console.Write("{0}\t", 
                     objRow[i].ToString());
                  }
                  System.Console.WriteLine();
               }

               System.Console.WriteLine(
                  "Employee count = {0}", 
                  dtEmp.Rows.Count);
            }
            catch (Exception ex)
            {
               System.Console.WriteLine("Exception: {0}", 
                  ex.ToString());
            }

            objConn.Close();
         }
      }
   }
}