title: Accessing Oracle 6i and 9i/10g Databases using C#
body: <p>I am making two build files using NAnt. The first aims to automatically compile Oracle 6i forms and reports and the second aims to compile Oracle 9i/10g forms and reports. Within the NAnt task is a C# script which prompts the developer for database credentials (username, password, database) in order to compile the forms and reports. I want to then run these credentials against the relevant database to ensure the credentials entered are correct and, if they are not, prompt the user to re-enter their credentials. My script currently looks as follows:</p>
  
  <pre><code>class GetInput
        {  
          public static void ScriptMain(Project project) 
          { 
            Console.Clear();
            Console.WriteLine("===================================================================");
            Console.WriteLine("Welcome to the Compile and Deploy Oracle Forms and Reports Facility");
            Console.WriteLine("===================================================================");
            Console.WriteLine();
  
            Console.WriteLine("Please enter the acronym of the project to work on from the following list:");
            Console.WriteLine();
            Console.WriteLine("--------");
            Console.WriteLine("- BCS");
            Console.WriteLine("- COPEN");
            Console.WriteLine("- FCDD");
            Console.WriteLine("--------");
            Console.WriteLine();
            Console.Write("Selection: ");
            project.Properties["project.type"] = Console.ReadLine();              
            Console.WriteLine();
  
            Console.Write("Please enter username: ");
            string username = Console.ReadLine();
            project.Properties["username"] = username;
            string password = ReturnPassword();
            project.Properties["password"] = password
            Console.WriteLine();
            Console.Write("Please enter database: ");
            string database = Console.ReadLine();
            project.Properties["database"] = database
            Console.WriteLine();
  
            //Call method to verify user credentials
  
            Console.WriteLine();
            Console.WriteLine("Compiling files...";
          }
  
          public static string ReturnPassword()
          {
              Console.Write("Please enter password: ");
              string password = "";
              ConsoleKeyInfo nextKey = Console.ReadKey(true);
  
              while (nextKey.Key != ConsoleKey.Enter)
              {
                  if (nextKey.Key == ConsoleKey.Backspace)
                  {
                      if (password.Length &gt; 0) 
                      { 
                          password = password.Substring(0, password.Length - 1); 
                          Console.Write(nextKey.KeyChar); 
                          Console.Write(" "); 
                          Console.Write(nextKey.KeyChar); 
                      } 
                  } 
                  else 
                  { 
                      password += nextKey.KeyChar; 
                      Console.Write("*"); 
                  } 
                  nextKey = Console.ReadKey(true); 
              }   
                  return password;
            }
        }  
  </code></pre>
  
  <p>Having done a bit of research, I find that you can connect to Oracle databases using the System.Data.OracleClient namespace <a href="http://msdn.microsoft.com/en-us/library/system.data.oracleclient.aspx" rel="nofollow noreferrer">clicky</a>. However, as mentioned in the link, Microsoft is discontinuing support for this so it is not a desireable solution. I have also fonud that Oracle provides its own classes for connecting to Oracle databases <a href="http://www.oracle.com/technology/sample_code/tech/windows/odpnet/howto/connect/index.html" rel="nofollow noreferrer">clicky</a>. However, this only seems to support connecting to Oracle 9 or newer databases (<a href="http://www.oracle.com/technology/software/tech/windows/odpnet/index.html" rel="nofollow noreferrer">clicky</a>) so it is not feasible solution as I also need to connect to Oracle 6i databases. </p>
  
  <p>I could achieve this by calling a bat script from within the C# script, but I would much prefer to have a single build file for simplicity. Ideally, I would like to run a series of commands such as is contained in the following .bat script:</p>
  
  <pre><code>rem -- Set Database SID --
  set ORACLE_SID=%DBSID%
  
  sqlplus -s %nameofuser%/%password%@%dbsid%
  set cmdsep on
  set cmdsep '"'; --"
  set term on
  set echo off
  set heading off
  select '========================================' || CHR(10) || 'Have checked and found both Password and ' || chr(10) || 'Database Identifier are valid, continuing ...' || CHR(10) || '========================================' from dual;
  </code></pre>
  
  <p>exit;</p>
  
  <p>This requires me to set the environment variable of ORACLE_SID and then run sqlplus in silent mode (-s) followed by a series of sql set commands (set x), the actual select statement and an exit command. Can I achieve this within a c# script without calling a bat script, or am I forced to call a bat script? Thanks in advance!</p>