How to connect Oracle Database directly from a Add-In in ArcMap

[Reza_MahmudunNaym]

Hello

I made a small add-in(.Net Sdk) for ArcMap 10.1. Now I need to read data from Oracle Server. I tried to connect oracle database using Oracle .Net client but it is throwing exception, then I tried with OleDB connection but still having same problem. But if I make a small console app to connect oracle then I can do it...

Can someone help, is not it possible to connect oracle from the add-in ?

Thanks
Naym

[RichWawrzonek]

Are you using ODP.NET?

The format is very similiar to ADO.NET, below is some psuedocode that I pasted together to get a dataset from Oracle. I have succesfully used similiar code from Add-Ins. You can use it as template to make your database call. Catch Oracle specific errors to find what the problem is.

using Oracle.DataAccess.Client;

internal void Main()
{
//CREATE QUERY
string sql = BuildSQL();

//CREATE A SQL COMMAND
OracleCommand cmd = new OracleCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
OracleConnection conn = new OracleConnection();

try
{
//OPEN THE CONNECTION
conn.ConnectionString = "Data Source=<network alias>;User Id={0};Password={1}";
conn.Open();

//ASSIGN THE CONNECTION TO THE COMMAND
cmd.Connection = conn;

//SET THE DATA ADAPTER FOR THE COMMAND AND FILL A DATASET
OracleDataAdapter da = new OracleDataAdapter(cmd);
Dataset _dataset = new DataSet();
da.Fill(_dataset);

//DO SOMETHING WITH THE DATASET

}
catch (OracleException ex) // Catch Oracle errors
{
    switch (ex.Number)
    {
        case 12545:
            MessageBox.Show(@"The database is unavailable");
            break;
        case 1017:
            MessageBox.Show(@"ORA-1017:" + Environment.NewLine +
                            @"Invalid username/password; logon denied");
            break;
        case 01013:
            MessageBox.Show(@"Query cancelled by user.");
            break;
        default:
            MessageBox.Show(@"Database error: " + ex.Message);
            break;
    }
}
catch (Exception ex) 
{
    MessageBox.Show(ex.ToString);
}
finally
{
    //CLEAN UP                
    conn.Close();
    conn.Dispose();
}

[Reza_MahmudunNaym]

Thanks for reply.

Yes, I am using odp.net. I used the same as you did but getting the following error.

Could not load file or assembly �??Oracle.DataAccess, Version=2.112.3.0,
Culture=neutral, PublicKeyToken=89b483f429c4732�?? or one of its dependencies.
An attempt was made to load a program with an incorrect format

[RichWawrzonek]

Without knowing any other details about your deployment it is tough to diagnose.  I believe the machine running the Add-in must have the same version of Oracle Client as the dev machine used to build the Add-In. I use the following code to verify the Oracle Client in my Add-ins:

internal bool VerifyOracleClient()
        {
            try
            {
                Assembly.Load("Oracle.DataAccess, Version=2.112.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342");
                return true;
            }
            catch (Exception)
            {
                MessageBox.Show(@"Your computer must have Oracle Client installed in order to use this application."
                    + Environment.NewLine + @"Click OK to exit.", @"My Add-In");
                return false;
            }
        }

[Reza_MahmudunNaym]

Hi Wawrzonek

I used your verification function using my version "Oracle.DataAccess, Version=2.112.3.0,Culture=neutral, PublicKeyToken=89b483f429c47342" and got execption.

Add-In for ArcMap 10.1 ( ArcDesktop 10.1)
OS: Windows 7 64bit
Oracle Client: 11g 64bit (ODAC1120320_x64)
VS2010

But when I verify same way in a console apps then it can load the assembly and works fine.
Could you tell me which version of Oracle Client is used for which version of ArcMap Add-In or what am I doing wrong here...

Thanks
Naym

[NeilClemmons · Accepted solution]

It could be a 64-bit vs 32-bit issue.  Because ArcObjects are 32-bit, any application that uses them must run as 32-bit.  The target platform should be set to x86 in your project properties.

[RichWawrzonek]

That's right. You can't use x64 version of Oracle Client with a x32 Add-in. That's why you need to verify the assembly. Modify the function as you need. The function above was used on a x32 machine so I only had to check for the version number.

BTW- I don't think you can install both clients (x32,x64) on the same machine. If changing clients I have found it best to completely remove the first before trying to install the second to avoid any issues.

[Reza_MahmudunNaym]

Thanks Wawrzonek and Neil

It was 32/64 bit issue. Now it is fixed. I installed 11g 32bit client and set the targeted platform 86x in Visual Studio Project properties and it works.

Previously I installed 11g 64 bit client and also 10g 32 bit client. But no one was supporting, even setting the platform 86x.
At last 11g 32 bit works.


Regards
Naym