I ran a quick comparison of the different providers consisting of:
- Installation experience.
- Connectivity.
- Server Explorer experience.
- Updating from database experience (database first).
- Data type mappings (database first).
Here are the conclusions:
1. Installation experience.
Oracle's ODP.NET (beta 3):
Complex installation of separate x86 and x64 versions, installers not designed for Windows.
Fills system path (dangerously close to max length), does not install to standard folders (program files + appdata).
Devart's DotConnect:
Smooth installation, provider DLL is fully managed.
DataDirect's Progress:
Smooth installation, provider DLL is fully managed.
OPENLINK:
Seems to require server side installation, did not test further.
2. Connectivity.
Oracle's ODP.NET (beta 3):
Complicated to configure, requires Oracle installation on client machine and either additional TSN file in Oracle installation or a long and complex connection string that looks like LISP/Scheme.
Update: Couldn't find this in the documentation, however, connection string can also contain simple data source definition e.g. serverName:port/serviceName.
Devart's DotConnect:
Simple connection string + wizard.
DataDirect's Progress:
Simple connection string + wizard.
3. Server Explorer experience.
Oracle's ODP.NET (beta 3):
Most powerful of the three, enables easy editing, viewing of keys and indexes.
Devart's DotConnect:
Viewing of tables and fields.
DataDirect's Progress:
Enables easy editing, viewing of keys and indexes.
4. Updating from database experience (database first).
Oracle's ODP.NET (beta 3):
Straightforward.
Devart's DotConnect:
Shows tables from all schemas, no option to filter - makes finding tables exhausting.
DataDirect's Progress:
Straightforward.
5. Data type mappings (database first).
Oracle's ODP.NET (beta 3):
Default mapping for number(1,0), number(2,0) and number(3,0) are wrong*.
Can manually override for number(2,0) and number(3,0). Fixing for number(1,0) doesn't work (at least not on beta 3 - may have worked in beta 2).
Update: Now that the release version is out (112030) this has been fixed.
Some mappings are possible via section in app.config file.
Devart's DotConnect:
Shows tables from all schemas, no option to filter - makes finding tables exhausting.
Default mapping for binary float, binary double, timestamp with time zone, number(2,0), number(3,0) and number(4,0) are wrong*. Manual override should work - didn't check.
DataDirect's Progress:
Default mappings are OK*.
(*) Mappings I expected:
DB Data Type .NET Data Type
integer Decimal
int Decimal
smallint Decimal
long String
decimal Decimal
rowid String
float Decimal
double Decimal
binary float Single
binary double Double
char[40] String
charvar[40] String
natchar[40] String
natcharvar[40] String
natcharacter[40] String
natcharactervar[40] String
number Decimal
numeric Decimal
nvarchar2[40] String
real Decimal
date DateTime
timestamp DateTime
timestamplocal DateTime
timestampzone DateTimeOffset
xml String
raw15 Binary
raw16 Guid
raw17 Int64
number(1,0) Boolean
number(2,0) Byte or SByte
number(3,0) Byte or SByte (accepted Int16 as OK too)
number(4,0) Int16
number(5,0) Int16 (accepted Int32 as OK too)
number(6,0) Int32
number(7,0) Int32
number(8,0) Int32
number(9,0) Int32
number(10,0) Int32 (accepted Int64 as OK too)
number(11,0) Int64
number(15,0) Int64
number(16,0) Int64
number(17,0) Int64
number(18,0) Int64
number(19,0) Int64 (accepted Decimal as OK too)
number(20,0) Decimal (would accept Int64 as OK too)
number(21+,0) Decimal
If you want the database type to be able to store any number in the .NET type range then
IntX requires a number(N,0) where Ceil(log10(2^X)) = N for storage**.
Bool (Int1) ==> number(1,0)
Byte (Int8) ==> number(3,0)
Int16, UInt16 ==> number(5,0)
Int32, UInt32 ==> number(10,0)
Int64 ==> number(19,0)
UInt64 ==> number(20,0)
** Calculation assumes unsigned numbers, for signed numbers ceil(log(2^(X-1)).
References for data type assumptions:
TECH on the Net
Devart
Oracle
