Introduction
This whitepaper takes you through different ways of database connectivity to Oracle database from .NET. This also talks about the advantages and disadvantages of all of these technologies.
Types of Connectivity
There are three ways by which one can connect to Oracle from .NET environment. They are as follows:
- ODBC.NET
- OLEDB.NET
- ODP.NET
The below figure shows how .NET talks to the Oracle database.
The above figure shows how three types of providers to connect to Oracle.
Differences between the Providers
Among the three data access methods, ODP.NET is most native to the .NET environment, bypassing the need for OLE DB or ODBC. It features high performance access to the Oracle Database, while providing accessing to advanced functionality which is not provided by OLEDB.NET or ODBC.NET. Similar to OLEDB.NET, ODBC.NET and ODP.NET can be used from any .NET language.
An important point is that the ODP.NET does not have one extra layer of data access which OLEDB and ODBC.NET have, hence there will be boost in performance. Since these data bridges are provided for generic data sources they cant be used for advanced database functionalities.
Major Features
ODP.NET makes using the Oracle database from .NET more flexible, fast, and stable than other .NET providers. It is part of the Oracle9i Release 2 Client, but can be used with any Oracle8, Oracle8i, or Oracle9i database server. Highlighted here are some of ODP.NET's key features.
XML Features
With XML becoming a popular language for data integration and web services, many .NET programmers are using it in their applications. ODP.NET provides two ways for programmers to exploit XML: XML DB and System.XML services.
XML DB is Oracle's high-performance, native XML storage and retrieval technology available with Oracle9i Release 2 database server. It provides a unique ability to store and manage both structured and unstructured data under a standard W3C XML data model. XML DB provides complete transparency and interchangeability between the XML and SQL metaphors. ODP.NET fully exposes all of XML DB's functionality to .NET clients. .NET clients make PL/SQL calls from ODP.NET to invoke XML DB's functionality. Click here for more information on XML DB.
Microsoft System.XML services are a set of interfaces for manipulating XML data sets from .NET data providers. ODP.NET interoperates with the System.XML APIs, feeding it data via the ODP.NET DataAdapter interfaces. One of the main differences between using XML DB and System.XML is that the former provides XML services where the data resides, on the database server. The latter manipulates XML on the client side. As such, ODP.NET provides great flexibility for programmers to choose the XML technology that best fits their project requirements.
Performance
One of ODP.NET's key differentiators over OLE DB .NET and ODBC .NET is better performance. As explained before, ODP.NET is native to the .NET environment, so it does not use a data access bridge, as is the case with the other two data access methods. A data access bridge is an additional layer of indirection for data to travel through. This not only means degraded performance, but also can introduce instability into the data access layer. An extra layer creates more potential for instability as it is another layer where bugs may appear. Furthermore, ODP.NET has many optimizations for retrieving and manipulating Oracle native types, such as LOBs and REF Cursors, whereas OLE DB .NET and ODBC .NET have very few.
.NET data access performance can be additionally improved by using the many tuning features available with ODP.NET. The settings of these tuning features will be determined by what fits your application usage profile best. Tuning features include:
- Connection pooling - sets up a pool of connections for an application, allowing connection lifetime, minimum and maximum pool size to be set
- Prefetching rows - specifies the number of rows that are to be fetched per server round trip
- Data chunk size (LONGs, LONG RAWs) - specifies the size of non-scalar data to be fetched
Native Oracle Types
In .NET, Microsoft has introduced a set of unified data types among the different .NET programming languages. With ODP.NET, Oracle users have access to .NET data types as well as Oracle native types. Oracle types can be fully manipulated within a .NET application and interoperate with .NET data types. Oracle native types provide advanced functionality to store and retrieve specialized data structures from the database, such as XML or Microsoft Word documents. Even with common types such as OraDecimal, the equivalent to the .NET decimal type, Oracle types provide better functionality. In the case of OraDecimal, it provides a higher level of precision at 38 than .NET decimal with a 28 precision.
ODP.NET supports the gamut of advanced Oracle types, including REF Cursors, LOBs (CLOBs, BLOBs, NCLOBs), BFILEs, Longs, RAWs, LONG RAWs, and N-data types. One of the limitations of using OLE DB .NET or ODBC .NET is that users cannot fully manipulate Oracle types. For example, in ODP.NET, multiple REF Cursor objects obtained as PL/SQL output parameters can be accessed in an arbitrary way. With the other two data access methods, REF Cursors must be accessed in a linear method.
Other Major Features
ODP.NET exposes many other Oracle database features, including PL/SQL, transactional, and Unicode support. ODP.NET users can fully execute PL/SQL stored procedures and functions in the database. PL/SQL can be packaged or non-packaged. Programmers are provided significant flexibility in using PL/SQL, including the ability to return multiple result sets from a stored procedure.
ODP.NET can participate in transactional applications with the Oracle database as the resource manager. ODP.NET employs Microsoft Enterprise Services as the transaction coordinator in a .NET environment. The Oracle Services for Microsoft Transaction Server (OraMTS) acts as a proxy among ODP.NET, Enterprise Services, and the Oracle database in order to coordinate these transactions. OraMTS provides a robust architecture for ODP.NET programmers to have their transactional applications maintain high availability and perform in a scalable manner.
ODP.NET has full Unicode support so that .NET users can globalize their applications easily in multiple written languages. This makes deploying applications in a multilingual environment easier and faster.
CONCLUSION
ODP.NET gives programmers better performance, flexibility, and feature selection for their .NET applications. With it, developers can use .NET, but not have to sacrifice the powerful data management capabilities that Oracle provides.