Microsoft promotes SQL Server as the database for your .NET applications, but with Oracle’s strong presence, you will undoubtedly need to utilize Oracle data in a .NET application. Oracle data was accessible in ADO.NET, using generic OLE-DB providers, but performance lagged behind that of SQL Server’s managed provider. Microsoft recognized the need for an Oracle-specific provider and released .NET Managed Provider For Oracle. Let's take a look at the ways you can take advantage of this connectivity to access important data.
Getting what you need
In the past, the term driver was often used to describe a tool allowing an application to access a database. This terminology has been replaced by the term managed provider in the .NET world. The .NET Managed Provider For Oracle is a .NET add-on, and it relies on the Oracle client software to interface with an Oracle database. So, in addition to having the .NET provider available, you’ll also have to install the Oracle client software.
You’ll need to add a reference to System.Data.OracleClient.dll to your project in Visual Studio .NET by right-clicking on the References list for the project or choosing Project | Add Reference from the menu bar. You’ll also probably want to import the System.Data.OracleClient namespace into your application.
Accessing the data
A very positive aspect of the .NET environment is the fact that database access code usually follows the same pattern regardless of backend database. First, you establish a connection, and then you configure the SQL statement or command. Finally, you execute the SQL and manipulate the results.
It stands to reason, then, that the code required for working with an Oracle database doesn't greatly differ from that used to access SQL Server. The object names are different. But the process is similar, as you can see from the VB.NET code in Listing A, which establishes a connection, creates a command object to return all the rows from a hypothetical PEOPLE table, and uses an OracleDataReader (analogous to the SqlDataReader and OleDbDataReader objects) to read the data.
If you've worked with SQL Server or another .NET provider, you will quickly notice the similarity of the class names used for Oracle. For instance, the SQL Server connection class is named SqlConnection, while the Oracle connection class is named OracleConnection.
Watch for errors
As with any .NET development, exceptions should be closely monitored to ensure proper code execution when accessing data. The OracleClient namespace includes an Oracle-specific exception class, OracleException, which may be used in a standard try…catch block. The code in Listing B takes advantage of this approach to catch any exceptions. Listing B presents an example of exception handling in action.
Although I utilized the OracleDataReader class within this article, the Oracle data provider includes versions of all of the standard ADO.NET classes like OracleDataSet and OracleDataAdapter. The .NET Managed Provider For Oracle from Microsoft provides the necessary tool to natively access Oracle data with performance boosts over OLE-DB access. And fortunately, the design pattern for the ADO.NET classes is such that accessing a database involves the same approach with similar objects, regardless of the type of database in use. This should make it easy to adapt any OLE-DB projects to use the Oracle provider.