using System;
using System.Data;
using Oracle.DataAccess.Client;
 
namespace ConsoleApplication28
{
      /// <summary>
      /// Summary description for Class1.
      /// </summary>
      class Class1
      {
            /// <summary>
            /// The main entry point for the application.
            /// </summary>
            [STAThread]
            static void Main(string[] args)
            {
                  // STEP 1
                  // NOTE: Substitute User ID, Password, Data Source
                  // as per your database setup
                  string connectStr =
                        "User Id=scott; Password=tiger; Data Source=orcl9i";
                                               
                  // Initialize connection
                  OracleConnection connection;
                  connection = new OracleConnection(connectStr);
                  connection.Open();
 
                  // STEP 2
                  // Set command to execute Test_Arraybind
                  // database stored procedure
                  OracleCommand cmd1 =
                        new OracleCommand("",connection);
                  cmd1.CommandText= "Test_Arraybind";
                  cmd1.CommandType = CommandType.StoredProcedure;
 
                  // STEP 3
                  // Initialize array with data
                  int[] myArrayDeptNo = new int[3]{1, 2, 3};
                  String[] myArrayDeptName =
                           {"Dev", "QA", "Facility"};
 
                  // Setting ArrayCount for command to 3 i.e. max.
                  // number of rows in the preceding arrays.
                  cmd1.ArrayBindCount = 3;
 
                  // STEP 4
                  // Instantiate Oracle parameter corresponding
                  // to DeptNo
                  OracleParameter deptNoParam =
                        new OracleParameter("deptno",OracleDbType.Int32);
                  deptNoParam.Direction = ParameterDirection.Input;
 
                  // Bind Array containing Deptartment numbers
                  // "deptNoParam" Oracle Parameter
                  deptNoParam.Value = myArrayDeptNo;
 
                  // Add Oracle Parameter to Command
                  cmd1.Parameters.Add(deptNoParam);
 
                  // Similarly bind Department Name parameter
                  OracleParameter deptNameParam =
                        new OracleParameter("deptname",
                        OracleDbType.Varchar2);
                  deptNameParam.Direction = ParameterDirection.Input;
                  deptNameParam.Value = myArrayDeptName;
                  cmd1.Parameters.Add(deptNameParam);
 
                  // STEP 5
                  // Execute the command calling stored procedure
                  try
                  {
                        cmd1.ExecuteNonQuery();
                        Console.WriteLine("{0} Rows Inserted",
                              cmd1.ArrayBindCount);}
                  catch (Exception e)
                  {
                        Console.WriteLine("Execution Failed:" + e.Message);}
 
                  // Step 6
                  // Clean-up DeptTab table data
                  OracleCommand cmd2 = new OracleCommand("",connection);
 
                  // Delete all the rows from the DeptTab table
                  cmd2.CommandText = "DELETE depttab WHERE deptno = :1";
 
                  // Bind with an array of 3 items
                  cmd2.ArrayBindCount = 3;
 
                  OracleParameter param1 = new OracleParameter();
                  param1.OracleDbType = OracleDbType.Int32;
                  param1.Value = myArrayDeptNo;
                  cmd2.Parameters.Add(param1);
 
                  // Execute the delete statement through command
                  try
                  {
                        cmd2.ExecuteNonQuery();
                        Console.WriteLine("Cleaned DeptTab table data");
                  }
                  catch (Exception e)
                  {
                        Console.WriteLine("Cleanup Failed:{0}" ,e.Message);}
                  finally
                  {
                        // Dispose the OracleCommand objesct
                        cmd1.Dispose();
                        cmd2.Dispose();
 
                        // Close and Dispose the OracleConnection object
                        connection.Close();
                        connection.Dispose();}
            }
      }
}