Using Microsoft .NET and C# with Oracle 9i

Using Microsoft .NET and C# with Oracle 9i


Oracle provides a software tool called ODP.NET that allows connectivity between the .NET languages and an Oracle database. This interface is a set of tools that allow the creation of .NET objects that connect directly to Oracle databases. Learn the basics of connecting to and performing simple queries to an Oracle database.

Prerequisites

Let’s face it -- neither Microsoft nor Oracle really wants to see their two flagship products work together. Microsoft would rather see a programmer use C# with SQL Server, Access, or just a plain XML data file before connecting to an Oracle data source. Oracle, on the other hand, has committed firmly to the J2EE development system for major development involving their databases.

However, Oracle does provide a software tool called ODP.NET that allows connectivity between the .NET languages and an Oracle database. This interface is a set of tools that allows the creation of .NET objects that connect directly to Oracle databases. This, at the very least, allows applications to connect to and make use of the power and capability of an Oracle database. It includes support for regular SQL queries and updates, stored procedures, and reading data from an Oracle record set into a .NET DataSet object, among other things. This article will cover the basics of connecting to and performing simple queries to an Oracle database using this set of objects.

First we’ll look at the set-up required to perform these tasks. If you are going to work with ODP.NET in ASP.NET applications, you will, obviously, need a web server with IIS and the .NET Framework installed and running. I will not be covering the steps needed to set up an IIS Web Application, as I will be focusing on actually working with the database.

If you are only working on a stand-alone application, you will just need the .NET Framework installed. In both cases, you will probably want some sort of development environment to allow easy editing of .NET code. Next, you will need to install the ODP.NET data provider on the web server if you are using ASP.NET or on your local machine if you are writing a stand-alone app. Also, each computer that will access the database as a client will need the Oracle client software installed. ODP.NET is a client-side library, and will need to be installed with your application if you are thinking of distributing your application widely. One of the nice things about ODP.NET is that it doesn’t require any extra configuration of the Oracle server. You can download the ODP.NET driver from the Oracle website at:

http://otn.oracle.com/software/tech/windows/odpnet/index.html

The first important thing to recognize about ODP.NET for Oracle 9i is that it contains two namespaces, first Oracle.DataAccess.Client. This contains the actual working classes for connecting to and acting on the Oracle database. The second namespace is Oracle.DataAccess.Types. This namespace has all the classes and methods required when working with specific Oracle data types. In this article, we won’t deal with the Types namespace at all. There are several classes to take note of in Oracle.DataAccess.Client and these are:

OracleConnection -- The basic class for connecting to a database

OracleCommand -- A class that represents a database command, either a text query or a stored procedure

OracleDataAdapter -- This class allows the programmer to pipe a returned Oracle record-set into a .NET DataSet

OracleParameter -- This class represents all the attributes and information in a stored procedure parameter

OracleDataReader -- This class represents a simple, read only data set, useful for quickly getting small, simple results, or data you will not want to change

Each of these classes will become very important in the future when we look a connecting to and working with an Oracle database.