Accessing Oracle 6i and 9i/10g Databases using C#
- by Mike M
Hi all,
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:
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 > 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;
}
}
Having done a bit of research, I find that you can connect to Oracle databases using the System.Data.OracleClient namespace clicky. 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 clicky. However, this only seems to support connecting to Oracle 9 or newer databases (clicky) so it is not feasible solution as I also need to connect to Oracle 6i databases.
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:
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;
exit;
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!
