Connect to and navigate Oracle Database

[Arc]

Hey guys. wondering if anyone know how to connect to and navigate(i.e use SQL) an Oracle database with C#.

For now I am just learning C# and just doing some simple things. I am proficient at SQL, but have no clue how to send and received cursors/records in C#.

Any info is appreciated.

[Hack]

Hey Arc

Since you know SQL, I skipped ahead in this article until it got to the point of actually using it with C#. You can go back and view the other parts if you want, but they are all about the SQL language, and you really dont need that.

[Arc]

I found how to connect and all that, but how where do i get the .dll to use in the refrences? I have Oracle 10g installed on my comp but have no refrence... Hrmmm

[Arc]

NM I found it on the Oracle website.

Thanks!

[Arc]

BTW I am assuming ODP(Oracle data provider) is preffered over ADO when dealing with an oracle database correct? Or will ADO work just as well?

I intend to be dealing with PL/SQL procedures as well.

[jmcilhinney]

You haven't mentioned which version of VS.NET you're using. If it's 2002 then you need to get the data connector from Oracle. If it's a later version then Oracle support is built in with the System.Data.OracleClient namespace. It's basically the same thing but Microsoft just incorportaed it into the Framework proper. Either way, you're using ADO.NET to access the database. ADO.NET is not the same thing as ADO and should be used in all .NET apps unless there is a very good reason to do otherwise.

[Arc]

Hmm ok. I am pretty sure its the 2002 version, I've had it for quite a while. It's also the Student version.

Well, after much trial and error and i running into a problem.

I get this error when trying to connect.

"ORA-12541: TNS:no listener
Connection must be open for this operation"

Not sure what this means. I am able to connect to the database using sqlPLUS.

Here is my connection code.

I have imported the Oracle connection provider with
using Oracle.DataAccess.Client;

and I have added it in the resources page.

    private void Form1_Load(object sender, System.EventArgs e)
        {
            OracleConnection con = new OracleConnection();
                // Specify the connect string
                // NOTE: Modify User Id, Password, Data Source as per your database set up
                con.ConnectionString = "User Id=STUDENT;Password=LEARN;Data Source=localhost;";
            try
            {
                // Open the connection
                con.Open();
                Console.WriteLine("Connection to Oracle database established successfully !");
                Console.WriteLine(" ");
            }//end try
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }//end catch

            string cmdQuery = "SELECT * FROM STUDENT";
              
            // Create the OracleCommand object
            OracleCommand cmd = new OracleCommand(cmdQuery);
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;

            try
            {
                // Execute command, create OracleDataReader object
                OracleDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    // Output Employee Name and Number
                    Console.WriteLine("Employee Number: " + 
                        reader.GetDecimal(0) + 
                        " , " +
                        "Employee Name : " +

                        reader.GetString(1));
                }//end while
            }//end try
            catch (Exception ex) 
            {
                Console.WriteLine(ex.Message);
            }//end catch 
            finally
            {
                // Dispose OracleCommand object
                cmd.Dispose();

                // Close and Dispose OracleConnection object
                con.Close();
                con.Dispose();    
            }//end finaly


        } 

[jmcilhinney]

You haven't told us what line that occurs on. It sounds like you're getting to the line that calls ExecuteReader without the connection being opened, which would suggest that the call to Open is failing, which would have given you an error message. That implies that there is an issue with your connection string.

[Arc]

It's happening on the con.Open(); line.

The error message is from the catch statement below it.

What would be wrong with my connection string?

con.ConnectionString = "User Id=STUDENT;Password=LEARN;Data Source=localhost;";

The user and pass are correct since they work in sqlPlus.

[jmcilhinney]

The only thing I can see is that you aren't specifying Integrated Security. I don't know whether it's necessary or not but the examples on www.connectionstrings.com both specify 'yes' or 'no'. Other than that I couldn't say. I've never used Oracle myself so I can only speak in ADO.NET generalities. There's also a bit more information at the link above that may be useful.

[GaryMazzone]

The data source can not just say localhost with OracleDB. There can be more then one Instace on the localhost. You must supply a TNSNames file connection reference or build that reference in the connect string as follows:

     'Dim pathtoDB As String
        'pathtoDB = "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=OracleServer)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=Candy)))"
        'pathtoDB can be used instead of MyOracleDB
        'Data Source=MyOracleDB;User Id=username;Password=passwd;Integrated Security=no;"

The MyOracleDB is the name in the TNSNames file.