Inside Oracle 47

Graham Keitch explores the tools available for building .NET applications around Oracle Database.

HardCopy Issue: 47 | Found In: Database, Development | Published: 01/02/2010 | Last Revision: 06/07/2010

Ten years after the release of Microsoft .NET, and with Visual Studio 2010 eagerly anticipated, the .NET development environment for Oracle Database continues to move forward and mature. Indeed Oracle itself provides a number of free tools for .NET developers that are packaged as Oracle Developer Tools for Visual Studio (ODT), a tightly integrated Visual Studio add-in that allows developers to remain within the Visual Studio environment for the entire development lifecycle.

Third Party Tools

Microsoft is removing its own Oracle data provider from the ADO.NET roadmap as most developers are already using the Oracle tools described above or those from third parties such as DataDirect.

The DataDirect ODBC Driver for Oracle is a well established and highly efficient tool. Its wire protocol ODBC driver doesn’t require Oracle Net client libraries to connect to the database. This simplifies deployment and eliminates the headache of installing, configuring and maintaining database software on each client machine. The driver manages application failover and work load balancing without requiring changes to application code. A 100 percent driver implementation of failover eliminates the need for costly server configurations.

The driver also includes a high-performance bulk load capability for loading large amounts of data very quickly. Tuning options control the amount of data returned across the network on single roundtrips from the Oracle server, without making application code changes.

Another publisher helping to connect .NET developers to Oracle is Quest Software who will be offering an Oracle Database Schema Provider (DSP) for Visual Studio Team System 2010 which will give developers the ability to work with Oracle databases without limitations. At the time of writing, the product is in beta phase but is expected to be released soon.

These include Oracle Database Extensions for .NET which makes it easy to develop, deploy and run stored procedures and functions written in a .NET managed language such as C# or Visual Basic. It also includes Oracle Providers for ASP.NET which allows application state to be stored in a persistent Oracle data source. ASP.NET developers can build their Web applications through ASP.NET services and controls that are part of the .NET Framework. By configuring the Oracle Providers for ASP.NET as default providers in a configuration file, ASP.NET applications can store various types of application states in an Oracle database, including session state, Web event and others. Oracle Data Provider for .NET (ODP.NET) facilitates Oracle data access from a .NET environment without sacrificing Oracle’s powerful data management capabilities such as Real Application Clusters, XML DB and advanced security features. The latest 11g release of ODP.NET includes a number of performance and functional enhancements that dramatically speed up and enhance data access. These include a new self-tuning capability which results in faster query execution. The improvements result from dynamically optimising the statement cache size during runtime. ODP.NET monitors statement usage and load conditions so that it can constantly tune cache size. The provider enables access to other key Oracle benefits such as availability notification, Advanced Queuing and User Defined-Types (UDTs). The latest ODP.NET 11g release publishes application performance counters for connection pooling which can be viewed using Windows Performance Monitor. It can also register for High Availability (HA) events which provide notifications about which database, service, host, or instance has gone down or come up. Developers can register a call back to notify the application when an event occurs and subsequently execute an event handler. Oracle Streams Advanced Queuing (AQ) tools make it easy for .NET developers who are developing applications that leverage Oracle’s database-integrated message queuing to create and modifying queue and queue tables. ODP.NET 11g also introduces an AQ programmatic interface that allows developers to access all of AQ’s operational features, such as ‘enqueue’ and ‘dequeue’ messages, listen for queue messages and message notification.

.NET development environment and Oracle Database

Connecting the .NET development environment to Oracle Database.

Oracle UDTs are used to represent complex data structures in the Oracle database. These data structures are representations of entities with multiple properties and methods in a single data structure. ODP.NET 11g supports both UDTs and built-in types through generic .NET provider type classes. The classes are generic in the sense that they can be used to represent any Oracle object type. Oracle Developer Tools for Visual Studio includes new tools to create and manage UDTs in the database. There is a custom class wizard, which takes an existing Oracle database UDT and auto-generates a .NET custom class in C#, Visual Basic or C++. There is also a new bulk copy feature that enables applications to load large amounts of data from one data source into an Oracle database table, with greater efficiency.