Accessing Oracle Database from C#

Question

I have a C# program that uses System.Data.OracleClient to access an oracle database.

The code uses OracleCommand, OracleDataReader objects. And it uses the TNS names to refer to specific oracle servers (as defined in the tnsnames.ora file).

It runs fine on my computer. And then I copied the binary to another computer, and upon running it encounters the error:

TNS:could not resolve the connect identifier specified.

The other computer has the same version of oracle client installed, and the identical copy of tnsnames.ora dropped in the oracle network/admin folder. And the other computer also has SQLDeveloper installed, and I am able to connect to the oracle servers by using those TNS names from inside its SQLDeveloper.

Why then is the c# program complaining about not able to resolve TNS identifier?

The connection string I use (as hardcoded into my c# program) is ;

"Data Source=TNS Name; User ID=user; Password=pass;"

Thanks

Answer 1

Is it failing to locate or load the tnsnames.ora file? Try running ProcessMonitor (http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx) and then running your c# program. You can see if it fails to locate the .ora file.

Answer 2

Open a command window, type tnsping yourdbname and hit enter, you should get back a bunch of info, but what you want to look for is

Used parameter files:

C:\Oracle\product\10.1.0\Client_1\network\admin\sqlnet.ora

Or something similar, this is the "Default" Oracle client on the system, make sure that this is the same path that you have the tnsnames.ora in (in my case we use sqlnet.ora)

Sometimes multiple Oracle product installs make multiple potential ORA_HOMEs, you can at least confirm the "active" client, so that way you are certain the files are in the right spot.

Also, can you connect to the db using SQLPlus?

Answer 3

You can also force all things Oracle to look in a fixed location for network files by setting the environment variable TNS_ADMIN to the location of your tnsnames.ora and sqlnet.ora files (by default ORACLE_HOME/network/admin).

Then you don't have to worry about guessing which network/admin folder is being used by the particular Oracle process you're running at the moment.