Unable to connect Oracle database from C#

meeram39

I have an applications that pulls data from Sql Server and push to Oracle database which is in remote server. The remote server can be connected through VPN. Initially I tried to install Oracle client version 11g, and then different versions, but everything failed. My OS is windows 8.1, googled and find out that 11G will not support on Win8.1. So I tried in Windows 7 OS without installing client,using the example given in this link. However I am still getting an error message saying as "Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed". My OS is Windows 7 64 bit. I have downloaded and installed the Instant Client Package - Basic Lite: Smaller version of the Basic, with only English error messages and Unicode, ASCII, and Western European character set support from the Oracle site. I am not sure how to proceed with this. My question is: 1) The connection string provided in the article - I put the host name as Server IP. Is this correct? So where should I put the db name? 2) Should again require a tnsnames.ora file? If so, should I create a new one and put it in the same folder of exe? I am stuck at this point. Please provide any help on this. Thanks in advance

Dave Kreskowiak

You MUST have an Oracle Client installed for your app to work. Preferrably, one that is compatible with your Oracle server version. Next, you got the bad image format exception because your Oracle Client is 32-bit and your code is compiled to target AnyCpu. This means your code will run as 32-bit on a 32-bit O/S and 64-bit on a 64-bit O/S. Since you cannot mix 32 and 64-bit code in the same process, you got that error message. The fix is simple. Recompile your app to target x86 only. Go into your Project Properties, Build tab and look for the Platform Target option.

A guide to posting questions on CodeProject

Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak

meeram39

Thanks Dave, I have installed Oracle Client earlier and tried. I have installed 64 bit as well. Then, in one of the forums, it is found that I need to install 32 bit as well, so I installed both. What I will do now is, I will remove everything and start afresh, let me try once again. Meanwhile, could you please let me know where should I find the file tnsnames.ora?

Dave Kreskowiak

You don't need a TNSNAMES.ORA file. All it is is a text file with predefined connection strings in it. That's all. You can put the very same connection string in your OracleConnection object and it'll work. But, it's found under the path %ORACLE_HOME%/network/admin.

A guide to posting questions on CodeProject

Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak

meeram39

Ok. Thanks. Let me do the exercise once again and will update.

meeram39

I removed everything and installed fresh everything in C drive itself. So I got tnsnames and sqlnames ora files automatically. I tried ping from DOS prompt using ping and tnsping which was also successful. However, When I tried from dotnet, I am getting an error message, "The procedure entry point ons_init_wconfig_ctx could not be loaded in the dynamic link library oraons.dll". I have installed win64_11gR2_client in Windows 7, and the dotnet version is Visual Studio 2012 Enterprise Edition (Trial). My code is as below:

private void ConnectOracleDB()
{
string sqlstring = string.Empty;
Logger myLogger = new Logger();

        string myConnectionString = GenerateConnectionString();
        OracleConnection myConnection = new OracleConnection();
        myConnection.ConnectionString = myConnectionString;

        try
        {
            myLogger.AddtoLogFile("Trying to connect Oracle DB", "Page Load");
            myConnection.Open();                
        }
        catch (Exception ex)
        {
            myLogger.AddtoLogFile(ex.Message, "Connecting to Oracle DB");
        }

        //execute queries
        myConnection.Close();

    }

private string GenerateConnectionString()
{
return "Data Source=( DESCRIPTION = ( ADDRESS_LIST = ( ADDRESS = ( PROTOCOL = TCP )( HOST = {server ip} )( PORT = 1521 ) ) )( CONNECT_DATA = ( SERVER = DEDICATED )( SERVICE_NAME = {db name}) ) ); User Id= abcd; Password = 1234;";
}

Update: I fixed this now, as I copied the same to the bin directory on the Application root directory. But now another error comes as "Object doesn't exist" .. :(( :((

Dave Kreskowiak

All you have to do is Google the error message "The procedure entry point ons_init_wconfig_ctx could not be loaded in the dynamic link library oraons.dll"and start reading.

A guide to posting questions on CodeProject

Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak

meeram39

Thank you.