Comparing Data Providers for Oracle
This article spotlights the differences between dotConnect for Oracle, Oracle Data Provider for .NET (ODP.NET) 11g, and .NET Framework Data Provider for Oracle.
From the interface point of view, the providers look pretty much alike. The question is, what features are supported and how they are supported. This article does not cover performance issues. For information on performance tests please refer to online article at www.devart.com/dotconnect/oracle/performance.html.
This article consists of the following sections:
Features Table
The following table enumerates major useful features that assist development of Oracle applications. Each feature in the table is briefly described later in the article.
| Feature | dotConnect for Oracle | MS Data Provider for Oracle | Oracle Data Provider for .NET | ODP.NET, Managed Driver |
|---|---|---|---|---|
|
Connectivity
|
||||
| Direct mode to access database without Oracle Client |
|
|
|
|
| Wide client and server versions support |
|
|
|
|
| OS authentication |
|
|
|
|
| Proxy Authentication |
|
|
|
|
| DBA privileges to open a session with |
|
|
|
|
|
Oracle data types
|
||||
| All Oracle data types support |
|
|
|
|
| Support for OBJECT, VARRAY, TABLE, REF types |
|
|
|
|
| Support for Oracle XML type |
|
|
|
|
| Support for REF CURSORs |
|
|
|
|
| AnyData type support |
|
|
|
|
|
Oracle technologies
|
||||
| Advanced Queuing (AQ) support |
|
|
|
|
| OracleGlobalization class to manipulate the globalization settings of the session, thread, and local computer. |
|
|
|
|
| Array binding feature |
|
|
|
|
| Transparent Application Failover support |
|
|
|
|
| Working with Oracle homes |
|
|
|
|
| OCI Pooling |
|
|
|
|
| Distributed transactions using TransactionScope in OCI mode support |
|
|
|
|
|
Toolkit components
|
||||
| OracleAlerter component to transfer messages between connections or client applications. |
|
|
|
|
| OraclePipe component to transfer data between connections or client applications. |
|
|
|
|
| OracleTrace component to trace command execution on Oracle server. |
|
|
|
|
| OracleScript component to execute scripts |
|
|
|
|
| OracleLoader component for fast data loading |
|
|
|
|
| OraclePackage component for easy working with PL/SQL packages |
|
|
|
|
|
.NET Framework features
|
||||
| 100% managed code |
|
|
|
|
| NET Core support |
|
|
|
|
| NET Compact Framework support |
|
|
|
|
| Mono Framework support |
|
|
|
|
| Sync Framework support |
|
|
|
|
| Workflow Foundation support |
|
|
|
|
| All-in-one component to retrieve and manipulate data from Oracle server data source |
|
|
|
|
| Advanced connection pooling |
|
|
|
|
| Asynchronous execution support |
|
|
|
|
| Paginal access to query result |
|
|
|
|
| Ability to retrieve metadata information |
|
|
|
|
| Ability of monitoring query and stored procedures execution |
|
|
|
|
| Data source enumeration |
|
|
|
|
| Batch processing support |
|
|
|
|
| Database change notification support |
|
|
|
|
| Provider-specific types support |
|
|
|
|
|
IDE, design time
|
||||
| Advanced integration with Visual Studio 2008 - 2019 |
|
|
|
|
| Cross-form components cooperation in WinForms applications |
|
|
|
|
| Help integrated into Visual Studio |
|
|
|
|
| Advanced design-time editors |
|
|
|
|
| Design-time wizards |
|
|
|
|
|
ORM solution support
|
||||
| LinqConnect (LINQ to Oracle) |
|
|
|
|
| Entity Framework v1 - v6 |
|
|
|
|
| Entity Framework Core |
|
|
|
|
| Support in Entity Developer - powerful ORM Designer with Visual Studio integration |
*
|
|
|
|
|
Other features
|
||||
| Support for Data Access Application Block in Enterprise Library |
|
|
|
|
| ASP.NET data providers: SimpleMembership, Membership, Role, Session State, Profile, Personalization, Site Map, Web Event |
|
|
|
|
| Distribution via NuGet packages |
|
|
|
|
| Easy to deploy |
|
|
|
|
| Free commercial usage |
*
|
|
|
|
| Free support for registered users during 1 year |
|
|
|
|
Detailed Information on Features
Of course, all data providers allow you to connect to the server and perform fundamental operations. So, common classes like OracleConnection, OracleCommand, OracleDataReader or OracleDataAdapter were not enumerated. The table shows that major advanced features are supported by the three data providers. dotConnect for Oracle provides extra components to manipulate data, scripts and packages. Additionaly, dotConnect for Oracle has wider compatibility list and can work without Oracle Client Software installed.
Connectivity
- Direct mode to access database without Oracle client
dotConnect for Oracle can use Oracle Call Interface (OCI) to connect to Oracle database server. This is usual way to develop Oracle applications with a third-generation language. However, it requires Oracle client software installed on workstation that is the cause of additional expenses for installation and administration. These problems can be eliminated by using Direct mode of connecting to server. In Direct mode dotConnect for Oracle talks to Oracle server in person, without calling any third party libraries. It allows your application to work with Oracle directly through TCP/IP protocol without involving Oracle client software. ODP.NET, Managed Driver also can connect Oracle without involving Oracle client software.
- Wide client and server versions support
dotConnect for Oracle supports Oracle servers 12c, 11g, 10g, 9i, 8i, 8.0 and 7.3, including Personal and Express editions. The following Oracle clients are supported: 12c, 11g, 10g, 9i, 8i and 8.0. Applications built with ODP.NET or Microsoft provider can work with fewer range of servers and clients. Only dotConnect for Oracle and ODP.NET support x64 versions of Oracle Client Software.
- OS authentication
All data providers support the OS authentication feature.
- Proxy authentication
In dotConnect for Oracle and ODP.NET you can take advantage of a useful Oracle feature - proxied connections. This helps to optimize application performance.
- DBA privileges to open a session with
Allows database administrators to connect to Oracle database with either SYSDBA or SYSOPER privileges. This is done through an attribute of the ConnectionString property. Microsoft Data Provider for Oracle does not allow opening session with DBA privileges.
Oracle data types
- All Oracle data types support
Both dotConnect for Oracle and ODP.NET data providers support all existing Oracle types. For types that do not map directly to .NET Framework types there are special classes and structures. Microsoft Data Provider for Oracle has limited types support.
- Support for OBJECT, VARRAY, TABLE, REF types
Both dotConnect for Oracle and ODP.NET data providers allow manipulating OBJECT, VARRAY, TABLE and REF types. Microsoft Data Provider for Oracle does not support these types.
- Support for Oracle XML type
Both dotConnect for Oracle and ODP.NET provide convenient toolset to work with XML data in Oracle server. Microsoft Data Provider for Oracle does not handle XML data.
- Support for REF CURSORs
Both dotConnect for Oracle and ODP.NET allow sophisticated REF CURSOR management. Microsoft Data Provider for Oracle has limited support for REF CURSORs' advanced features.
- AnyData type support
dotConnect for Oracle OracleAnyData class can be used as a table column datatype and lets you store heterogeneous values in a single column. The values can be of SQL built-in types as well as user-defined types.
Oracle technologies
- Advanced Queuing (AQ) support
dotConnect for Oracle provides convenient client side wrappers for packages and objects used in Oracle Advanced Queuing. In ODP.NET and Microsoft Data Provider for Oracle you have to work with pure DBMS packages.
- OracleGlobalization class
This class allows you to obtain and set the Oracle globalization settings of the session, thread, and local computer. The class is present in dotConnect for Oracle and ODP.NET.
- Array binding
This feature allows you to execute several INSERT SQL statements with different parameters at single query. Server is accessed only once, which increases speed of update a lot. Microsoft Data Provider for Oracle does not implement array binding.
- Transparent Application Failover support
Both dotConnect for Oracle and ODP.NET data providers have capability to automatically reconnect to the database if the connection fails. Microsoft Data Provider for Oracle does not have this capability.
- Working with Oracle homes
Both dotConnect for Oracle and ODP.NET data providers can switch between Oracle homes if more than one home is installed on the system. Microsoft Data Provider for Oracle does not have this capability.
- OCI Pooling
In dotConnect for Oracle you can use OCI Pooling instead of usual connection pooling. This helps to optimize performance in OCI mode.
- Distributed transactions using TransactionScope in OCI mode support
TransactionScope is a wrapper class which allows you to include your code into the transaction. It gives an opportunity to use local and distributed transactions. Distributed transactions are supported by all the compared providers.
Toolkit components
- OracleAlerter component
This dotConnect for Oracle component enables you to transfer messages between connections or client applications. There is no counterpart in ODP.NET and Microsoft Data Provider for Oracle.
- OraclePipe component
This dotConnect for Oracle component enables you to transfer data between connections or client applications. There is no counterpart in ODP.NET and Microsoft Data Provider for Oracle.
- OracleTrace component
This dotConnect for Oracle component helps tracing command execution on Oracle server. There is no counterpart in ODP.NET and Microsoft Data Provider for Oracle.
- OracleScript component
This dotConnect for Oracle component serves to execute series of SQL statements separated by special symbols, like SQL*Plus scripts. There is no counterpart in ODP.NET and Microsoft Data Provider for Oracle.
- OracleLoader component
This dotConnect for Oracle component serves to load external data into the Oracle database. It uses DirectPath OCI API to give highest possible performance. There is no counterpart in Microsoft Data Provider for Oracle. ODP.NET has a similar OracleBulkCopy component.
- OraclePackage component
This dotConnect for Oracle component provides access to procedures and variables of packages stored in the Oracle database. There is no counterpart in ODP.NET or Microsoft Data Provider for Oracle.
.NET Framework Features
The table above makes clear that ODP.NET and Microsoft Data Provider for Oracle have partial support for major .NET features. None of the unsupported by .NET features is vital to development process, but some would aid or speed it up greatly.
- 100% managed code
dotConnect for Oracle and Microsoft Data Provider for Oracle are written on pure managed code. They do not depend on any unmanaged library. This allows functioning in a restrictive environment (like on web servers), gives automatic optimization for x64 architecture. Oracle Data Provider for .NET includes ODP.NET, Managed Driver, which is also 100% managed code.
- .NET Core support
dotConnect for Oracle allows you to create .NET Core and .NET Standard targeting solutions. It includes .NET Standard compatible assemblies that support .NET Core 1.x, 2.x, and 3.0 and Full .NET Framework 4.6 and higher.
- .NET Compact Framework support
Both dotConnect for Oracle and ODP.NET allow creating applications for .NET Compact Framework. Microsoft Data Provider for Oracle and ODP.NET, Managed Driver does not support it.
- Mono Framework support
dotConnect for Oracle allows you to create applications for Mono framework (only Direct connection mode is available on Mono). Oracle Data Provider for .NET does not support it.
- Sync Framework support
dotConnect for Oracle provides the synchronization provider class and the necessary classes for provisioning and deprovisioning tables and synchronization scopes.
- Workflow Foundation support
dotConnect for Oracle supports Workflow Instance Store and Workflow Tracking.
- All-in-one component to retrieve and manipulate data
The OracleDataTable component in dotConnect for Oracle represents a single object that provides all of the functionality needed to retrieve and manipulate data from an Oracle server data source. It supports both connected and disconnected data access models. There is no counterpart in ODP.NET or Microsoft Data Provider for Oracle.
- Advanced connection pooling
All data providers utilize sophisticated mechanisms to optimize interaction with Oracle server. This allows you to balance network load and increase performance significantly.
- Asynchronous execution support
dotConnect for Oracle supports asynchronous execution of methods that take most of time in database applications, that is, opening a connection, transferring large blocks of data, and other operations alike.
- Paginal access to query result
dotConnect for Oracle allows you to request from server a subset of rows that a statement would return in normal way. This helps to save memory and improve performance by stripping off unnecessary data.
- Ability to retrieve metadata information
All data providers support OracleConnection.GetSchema() method, which is standard .NET way to obtain information on schema objects.
- Ability of monitoring query and stored procedures execution
dotConnect for Oracle provides OracleMonitor component, which allows you to monitor dynamic SQL execution in applications that use dotConnect for Oracle. There is no analogue in ODP.NET and Microsoft Data Provider for Oracle.
- Data source enumeration
This feature enables the application to generically obtain a collection of the Oracle data sources that the application can connect to. The capability is supported by dotConnect for Oracle and ODP.NET.
- Batch processing support
This feature allows updating specified number of rows in a single round-trip to the database when working with OracleDataAdapter component. The capability is supported by all data providers.
- Database change notification support
This feature enables applications to receive notifications when there is a change in a query result set, schema objects, or the state of the database. Using Database Change Notification, an application can easily maintain the validity of the client-side cache. The capability is supported by dotConnect for Oracle and ODP.NET.
- Provider-specific types support
Provider-specific types in its native formats can greatly enhance capabilities of client side applications. All data providers can fetch these types. dotConnect for Oracle also can modify the data in provider-specific format.
IDE, design time
This part of the table demonstrates that dotConnect for Oracle is the only product that supports true RAD development and that ODP.NET has only partial design-time support.
- Advanced integration with Visual Studio 2008 - 2019
All data providers support all design-time features known as Data Designer Extensibility. These features allow you to preview data, customize DataSets and many other operations.
- Cross-form components cooperation in WinForms applications
dotConnect for Oracle includes the InterForm technology, which allows components in WinForms applications to collaborate even if they are located on different forms of UserControls. This capability is supported in design time, which enables you to setup data access in a declarative way.
- Help integrated into Visual Studio
All data providers integrate the documentation into Visual Studio Help Collection.
- Advanced design-time editors
dotConnect for Oracle and Microsoft Data Provider for Oracle have components with greatly enhanced design-time tools, which allow you to setup components in a true RAD manner. There is no counterpart in ODP.NET.
- Design-time wizards
dotConnect for Oracle and Microsoft Data Provider for Oracle have wizards that leverage client-side application development. ODP.NET does not have such wizards.
ORM solution support
dotConnect for Oracle includes support for LinqConnect (formerly LINQ to Oracle) and Entity Framework ORM solutions. It comes with its own visual model designer - Entity Developer, which allows not just designing models and generating code for them, but executing LINQ and Entity SQL queries against the model and working with database data.
- LinqConnect (formerly LINQ to Oracle)
The technology allows you to employ object-oriented database application development with less efforts and in less time. It is compatible with Microsoft LINQ to SQL but provides wider set of features.
- Entity Framework v1 - v6
The latest ADO.NET evolution focuses on raising the level of abstraction from the logical (relational) level to the conceptual (entity) level. For this purpose Microsoft introduces the Entity Framework, designed to simplify data - object conversion and embed data access queries into program code. Devart dotConnect for Oracle supports all release versions of Entity Framework. ODP.NET also supports Entity Framework.
- Entity Framework Core
After Entity Framework v6, Microsoft concentrated efforts to make a cross-platform and more lightweight version of Entity Framework ORM. This resulted in complete Entity Framework rewrite and creation of Entity Framework Core. dotConnect for Oracle supports Entity Framework Core 1.1, 2.2, and 3.1.
- Support in Entity Developer - powerful ORM Designer with Visual Studio integration
dotConnect for Oracle Professional and Developer editions include Entity Developer - visual designer for ORM models with support for model first, database first and mixed approaches, seamless Visual Studio integration and lots of other features. Entity Developer supports Microsoft Data Provider for Oracle and ODP.NET and ODP.NET, Managed Driver too, but for these providers it must be purchased separately.
Other features
This section of the table contains other aspects of developing applications with Oracle: interaction with other technologies and licensing questions.
- Support for Data Access Application Block in Enterprise Library
dotConnect for Oracle makes it easy to employ Microsoft Patterns and Practices in your application.
- ASP.NET data providers: SimpleMembership provider, Membership provider, Role provider,
Session State provider, Profile provider
dotConnect for Oracle can be used as data source in ASP.NET Provider Model, which allows you to develop DBMS-independent web applications. It also supports the ASP.NET SimpleMembership provider that appeared in ASP.NET MVC 4. Since the version 12c, ODP.NET also includes ASP.NET data providers, but it does not support the SimpleMembership provider.
- Distribution via NuGet packages
dotConnect for Oracle has three sets of assemblies: for full .NET Framework, .NET Standard 1.3 compatible assemblies, and .NET Standard 2.0 compatible assemblies. .NET Standard compatible assemblies are available as NuGet packages. ODP.NET, Managed Driver also is available as NuGet packages from NuGet.org.
- Easy to deploy
Applications built with dotConnect for Oracle in Direct mode do not require Oracle Client Software installed on the computer, so it is very easy to deploy the applications. Microsoft Data Provider for Oracle is a part of .NET Framework but requires Oracle Client Software. ODP.NET requires specific Oracle Client Software version so it may be necessary to upgrade the client to newer version.
- Free commercial usage
Microsoft Data Provider for Oracle and ODP.NET are freely distributed and can be downloaded from web sites of appropriate vendors. dotConnect for Oracle is a commercial product and requires one-time registration fee, but there is a free Express edition of dotConnect for Oracle, which can be used without restrictions.
- Free support for registered users during 1 year
dotConnect for Oracle provides free reliable online support (using web forum or e-mail) for registered users.