Connecting to Oracle with C#

Hi everyone,

I am trying with little success to get a simple C# application to execute queries on a remote Oracle database using ODBC, OLEDB or ODP.NET. I have searched the web extensively and found hundreds of C# and VB code examples but none of them seem to work.

I have successfully installed Oracle9i (9.2.0.1.0) on Redhat8.0.
I have installed Oracle9i clients on my Windows XP Professional machine and can connect to the database using sqlplus from both Windows and Linux. I have installed MDAC2.7, ODBC .NET data provider (odbc_net.msi) and ODP.NET.

My main problem seems to be with the ODBC Data Sources configuration. I have configured the ODBC sources successfully for MySQL using MyODBC but really need to get Oracle working. It is my understanding that C# references the ODBC Sources on a machine using a Data Source Name (DSN), this DSN is configured in the ODBC Administration section of Control Panel -> Administrative Tools -> ODBC Data Sources. For Microsoft ODBC for Oracle this DNS comprises a Data Source Name, Description, User name and Server. What do I use for the Server field? I read in the help and on the web that this is an SQL*Net Easy configuration connection string or database alias. I have ran the Net Configuration Assistant and Net Manager applications installed with the oracle9i windows clients and configured the server IP and port and SID and tried setting the server field in the DNS to point to the service name configured with Net Manager. But no connections.

Am I correct in my assumption that

.NET code (C#) -> ODBC Data sources (DNSs) -> Oracle9i clients -> Oracle 9i DB

If so how does ODBC know where to look for Oracle service name configurations?
Is there a special place I should be installing Oracle clients on windows so .NET/ODBC knows where they are?
Is there an environmental variable I should be setting so .NET/ODBC Sources can find Oracle service names?

Currently there is no reference to my servers IP address or port anywhere but the Oracle9i service name / tnsnames.ora file. Should the server field in the ODBC DNS section contain this information?

I am this executing code having imported/using Microsoft.Data.Odbc;

string connString = "Driver={Microsoft ODBC for Oracle};DNS=oracle9i_cs;UID=scott;PWD=tiger";
OdbcConnection conn = new OdbcConnection(connString);
conn.Open();

where oracle9i_cs is the SystemDNS i configured in ODBC Data Sources with SystemDNS settings for a Miscorosft ODBC for Oracle connection:-

Data Source Name: oracle9i_cs
Desription: Oracle9i CSharp
User Name: scott
Server: oracle9i (service name configured in Windows Oracle Net Manager/Net Nonfig)

Is this correct or am i missing something silly?
Should I be pointing directly to my oracle database on Linux with the DNS?
The errors i am getting are of the form

Unhandled Exception: Microsoft.Data.Odbc.OdbcException: ERROR [IM006] [Microsoft]
[ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the ve
rsion of ODBC behavior that the application requested (see SQLSetEnvAttr).
ERROR [01S00] [Microsoft][ODBC driver for Oracle]Invalid connection string attribute
at Microsoft.Data.Odbc.OdbcConnection.Open()
at OracleTest.OracleTester.test()
at OracleTest.OracleTester.Main(String[] args)

I searched these error codes on google and found a few sites with seemingly unrelated problems. Any one have any ideas?

Getting desperate

Many thanks
Trent