Introduction
Today, one has several options to establish a connection to an Oracle database, for example ODBC, OLE DB, ODP.NET or even third party drivers. This small tool will verify (almost) all of them.
Background
When you develop an application which connects to an Oracle database, you have to decide on a technology to use for connection. Sometimes, your customer have got already an Oracle client installed and are not able or willing to change that. When you ask "which component did you install?" many times, you do not get a clear answer, but only "We have an Oracle client installed on our machines." The ConnectionTester.exe tests installed components.
Another use-case is when you have several components and/or Oracle versions installed on your PC and you like to test all variants of it.
ConnectionTester.exe tests the following providers:
- +|-ODP: Test (+) or skip (-) Oracle Data Provider for .NET
- +|-ADO: Test (+) or skip (-) Microsoft .NET Framework Data Provider for Oracle
- +|-OleDB: Test (+) or skip (-) OLE DB (Microsoft provider and Oracle provider)
- +|-ODBC: Test (+) or skip (-) ODBC drivers (Microsoft driver and Oracle driver)
- +|-DevArt: Test (+) or skip (-) Devart dotConnect
- +|-all: Test all possible Oracle providers/drivers, evaluated before other switches
- wait: Wait for key stroke after each connetion
- cs: Print ConnectionString for each connection (Consider security, password is shown as clear text)
Switches are not case-sensitive
Using the Application
At the first step, the command line parameters are evaluated. Then the program simply creates a DbConnection object, opens a connection and closes again. Based on input parameter, it checks all providers one by one.
Syntax is similar to SQL*Plus, all the following commands are valid:
c:\> ConnectionTester.exe scott/tiger@DB1
c:\> ConnectionTester.exe scott@DB1
Password: *****
c:\> ConnectionTester.exe
Database or <ENTER> for exit: DB1
User: scott
Password: *****
You have two versions of ConnectionTester, ConnectionTester_x86.exe and ConnectionTester_x64.exe. Obviously, ConnectionTester_x86.exe runs in 32-bit mode, whereas ConnectionTester_x64.exe runs in 64-bit mode.
ConnectionTester.exe is just a wrapper for both, so:
c:\> ConnectionTester.exe scott/tiger@DB1
is equivalent to:
c:\> ConnectionTester_x64.exe scott/tiger@DB1
c:\> ConnectionTester_x86.exe scott/tiger@DB1
64-bit version will be executed only on a 64-bit Windows.
Other options are used to control which providers will be tested.
+|-ODP: Test (+) or skip (-) Oracle Data Provider for .NET+|-ADO: Test (+) or skip (-) Microsoft .NET Framework Data Provider for Oracle+|-OleDB: Test (+) or skip (-) OLE DB (Microsoft provider and Oracle provider)+|-ODBC: Test (+) or skip (-) ODBC drivers (Microsoft driver and Oracle driver)+|-DevArt: Test (+) or skip (-) Devart dotConnect+|-all: Test all possible Oracle providers/drivers, evaluated before other switches
wait: Wait for key stroke after each connetion
This option is useful when you like to analyze with tools like Process Monitor from Sysinternalcs: Print ConnectionString for each connection (Consider security, password is shown as clear text)-help or -h: Prints a help message
Switches are not case-sensitive.
Some examples:
ConnectionTester.exe scott/tiger@DB1 +all -devart
Testing all providers apart from Devart dotConnect. This is the default.ConnectionTester.exe scott/tiger@DB1 -all +odbc
Testing only ODBC drivers.ConnectionTester.exe scott/tiger@DB1 -all +opd +oledb
Testing ODP.NET and OLE DB providers.ConnectionTester.exe scott/tiger@DB1 +all
Testing all providers.
Source Code
Connection Tester.csproj File
Note, the AssemblyName depends on the compile target! When you compile on AnyCPU, you get two .exe, ConnectionTester_x86.exe and ConnectionTester_x64.exe. Output file ConnectionTester_x.exe is considered as junk.
<PropertyGroup Condition=" '$(Configuration)|
$(Platform)' == 'Release|AnyCPU' ">
<AssemblyName>ConnectionTester_x</AssemblyName>
...
</PropertyGroup>
<PropertyGroup Condition="'$(Configuration)|
$(Platform)' == 'Release|x64'">
<AssemblyName>ConnectionTester_x64</AssemblyName>
<OutputPath>..\bin\</OutputPath>
...
</PropertyGroup>
<PropertyGroup Condition="'$(Configuration)|
$(Platform)' == 'Release|x86'">
<AssemblyName>ConnectionTester_x86</AssemblyName>
<OutputPath>..\bin\</OutputPath>
...
</PropertyGroup>
<Target Name="AfterBuild">
<MSBuild Condition="'$(Platform)' == 'AnyCPU'"
Projects="$(MSBuildProjectFile)"
Properties="Platform=x64;PlatFormTarget=x64"
RunEachTargetSeparately="true" />
<MSBuild Condition="'$(Platform)' == 'AnyCPU'"
Projects="$(MSBuildProjectFile)"
Properties="Platform=x86;PlatFormTarget=x86"
RunEachTargetSeparately="true" />
<Delete Files="$(OutputPath)Oracle.DataAccess.dll" />
</Target>
Testing a Provider
Each provider is a little different, I did not manage to use a single method to establish the connection. Following C# pseudo-code illustrates the core function of the program.
DbConnection con = default(DbConnection);
var str = new DbConnectionStringBuilder();
str.Add("UserId", user);
str.Add("Password", pw);
str.Add("Data Source", database);
try {
using ( con = new Oracle.DataAccess.Client.OracleConnection(str.ConnectionString) ) {
con.Open();
Console.WriteLine("Connection successful");
con.Close();
}
} catch ( Exception ex ) {
Console.WriteLine("Connection failed: " + ex.Message);
}
try {
using ( con = new Oracle.ManagedDataAccess.Client.OracleConnection(str.ConnectionString) ) {
con.Open();
Console.WriteLine("Connection successful");
con.Close();
}
} catch ( Exception ex ) {
Console.WriteLine("Connection failed: " + ex.Message);
}
str.Add("Provider", "OraOLEDB.Oracle");
try {
using ( con = new System.Data.OleDb.OleDbConnection(str.ConnectionString) ) {
con.Open();
Console.WriteLine("Connection successful");
con.Close();
}
} catch ( Exception ex ) {
Console.WriteLine("Connection failed: " + ex.Message);
}
str["Provider"] = "msdaora";
try {
using ( con = new System.Data.OleDb.OleDbConnection(str.ConnectionString) ) {
con.Open();
Console.WriteLine("Connection successful");
con.Close();
}
} catch ( Exception ex ) {
Console.WriteLine("Connection failed: " + ex.Message);
}
str.Remove("Provider");
try {
using ( con = new System.Data.OracleClient.OracleConnection(str.ConnectionString) ) {
con.Open();
Console.WriteLine("Connection successful");
con.Close();
}
} catch ( Exception ex ) {
Console.WriteLine("Connection failed: " + ex.Message);
}
str["Driver"] = "Oracle in OraClient11g_home1";
try {
using ( con = new System.Data.Odbc.OdbcConnection(str.ConnectionString) ) {
con.Open();
Console.WriteLine("Connection successful");
con.Close();
}
} catch ( Exception ex ) {
Console.WriteLine("Connection failed: " + ex.Message);
}
str["Driver"] = "Microsoft ODBC for Oracle";
try {
using ( con = new System.Data.Odbc.OdbcConnection(str.ConnectionString) ) {
con.Open();
Console.WriteLine("Connection successful");
con.Close();
}
} catch ( Exception ex ) {
Console.WriteLine("Connection failed: " + ex.Message);
}
All providers from Oracle are loaded with dynamic bind, so you can compile the assembly even if you do not have any Oracle client installed on your machine.
Usage of .config Files
Microsoft .NET Framework provides Publisher Policy for assemblies. When .config files are present (ConnectionTester_x64.exe.config, resp. ConnectionTester_x86.exe.config) then ConnectionTester.exe bypass publisher policies and will try to load specific version of Oracle.DataAccess.dll, resp. Oracle.ManagedDataAccess.dll. This is useful if you like to test every specific version of these DLLs.
However, typically this is not required. In this case, you can delete .config files. ConnectionTester.exe will test only one version of DLL according to publisher policies in GAC.
Example Output
c:\>ConnectionTester_x64.exe scott@DB1 -all +odp +odbc
Password: ******
Running Test on 64 bit
Found Oracle oci.dll Version 11.2
************************************************************
Oracle Data Provider for .NET (ODP.NET 1.x) not available in Oracle version 11.2
************************************************************
Try to connect via Oracle Data Provider for .NET (ODP.NET 2.0)
Try to load Oracle.DataAccess.dll (Version 2.102.*.*)
Assembly: Oracle.DataAccess, Version=2.112.4.0,
Culture=neutral, PublicKeyToken=89b483f429c47342, processorArchitecture=Amd64
DLL-Location: Loaded from GAC
Connection successful
Try to load Oracle.DataAccess.dll (Version 2.111.*.*)
Version 2.112.4.0 already tested
Try to load Oracle.DataAccess.dll (Version 2.112.*.*)
Version 2.112.4.0 already tested
Skip Oracle.DataAccess.dll (Version 2.121.*.*)
************************************************************
Try to connect via Oracle Data Provider for .NET (ODP.NET 4.0)
Try to load Oracle.DataAccess.dll (Version 4.112.*.*)
Assembly: Oracle.DataAccess, Version=4.112.4.0,
Culture=neutral, PublicKeyToken=89b483f429c47342, processorArchitecture=Amd64
DLL-Location: Loaded from GAC
Connection successful
Skip Oracle.DataAccess.dll (Version 4.121.*.*)
************************************************************
Try to connect via Oracle Data Provider for .NET (ODP.NET 4.0 Managed Driver)
Assembly: Oracle.ManagedDataAccess, Version=4.121.1.0,
Culture=neutral, PublicKeyToken=89b483f429c47342, processorArchitecture=MSIL
DLL-Location: Loaded from GAC
Connection successful
************************************************************
Try to connect via Microsoft .NET Framework Data Provider for Oracle
Note: This provider has been deprecated by Microsoft!
Assembly: System.Data.OracleClient, Version=4.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089, processorArchitecture=Amd64
DLL-Location: Loaded from GAC
Connection successful
************************************************************
Try to connect via ODBC Driver: Oracle in OraClient11g_home1
DLL-Location: C:\oracle\product\11.2\Client_x64\BIN\SQORA32.DLL
Version: 11.2.0.1.0
Connection successful
************************************************************
c:\>
Points of Interest
According to my knowledge, all available providers are covered. COM based ActiveX Data Objects (ADO) uses OLE DB, Oracle Providers for ASP.NET uses ODP.NET, etc.
History
- 1.0 Initial release
- 1.1 Improved ODBC handling
- 1.2 Optionally bypass Publisher Policies in GAC in order to test dedicated every single version of Oracle DLLs
