Connecting to an Oracle Database Using ASP.NET�A Step-by-Step Tutorial

Introduction

There are numerous articles on .NET and ASP.NET. However, recently I had to develop an ASP.NET application connecting to an Oracle database and, to make everything work together, I had to solve several problems. This article will show you the step-by-step process to develop an ASP.NET client connecting to an Oracle database server.

Objectives

This article attempts to answer the following questions:


  • How do you install and configure Internet Information Service (IIS), the .NET framework, and Oracle Client software?
  • How do you troubleshoot your IIS installation?
  • What Data Providers are available to connect to Oracle from ASP.NET?
  • How do you connect to an Oracle database using the different Data Providers?

Platform and Versions


  • Development/deployment machine: Windows 2000 or Windows XP
  • Internet Information Service (IIS) version 5 and above
  • .NET framework version: v1.1.4322
  • Oracle version: 9i

Step-by-Step Instructions to Connect to an Oracle Database from ASP.NET

Step 1: Install IIS and verify that it is installed and working properly

For ASP.NET to work, IIS must be installed before installing the .NET Framework. If you are not sure whether IIS is already installed, navigate to Start | Settings | Control Panel | Add/Remove Programs | Add/Remove Windows Components and see whether IIS is checked, as seen in the following picture.

If IIS is unchecked, check it and then click Next to install it. You will need the original Windows installation CD to finish the installation.

A quick way to test the installation of IIS is to type in and enter http://localhost on Internet Explorer. If IIS is installed and running properly, you will get a valid Web page without any errors.

Step 2: Download and install .Net framework from Microsoft

After completing the installation of the .NET framework, before proceeding further, verify that your IIS is properly configured for ASP.NET by following these steps:


  • Select Start | Run and type in Inetmgr and press Enter. The Internet Information Services configuration window will appear.
  • Right-click on Web Site (Default Web Site) node and select Properties.
  • Select the Home Directory tab and click on the Configuration button.
  • Select the Mappings (App Mappings) tab and make sure that the .aspx extension is properly mapped as it is shown below:

  • .aspx -> C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\
    aspnet_isapi.dll


If you did not find the entry for .aspx, ASP.NET is not installed properly (or IIS was installed after the .NET framework was installed). In either case, use the command prompt to navigate to the C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322 directory and run aspnet_regiis -i to install and configure ASP.NET properly. Once this is done, you can verify it by looking at the Application Configuration window again using the above steps.

Step 3: Install Oracle client software

Install Oracle client software from the Oracle installation CD provided by Oracle Corporation. When you install the Oracle Client software, make sure to do a full client installation with the Administrator option. This will install all the required files, including oci.dll.

Step 4: Create a new Oracle Service Name

Follow these steps:


  • Select Start | Programs | Oracle – OraHome 92 | Configuration and Migration Tools | Net Manager.
  • On the Oracle Net Manager window that appears, successively expand Oracle Net Configuration | Local | Service Naming nodes.
  • Select the Service Naming node and then press the green + icon on the left panel (see the picture below) to add a new Service Name. Follow the wizard and enter the information pertaining to your Oracle database.

Step 5: Add an ODBC data source

If you plan to use an ODBC Data Provider (see below for a detailed description on Data Providers), create an ODBC data source by following these steps:


  • Navigate to Start | Settings | Control Panel | Administrative Tools and double-click on Data Sources (ODBC).
  • Select the System DSN tab and click on the Add button to add a System Data Source (see the picture below).
  • Select the driver named Oracle in OraHome 92 when prompted.

This step can be omitted if you are planning to use the .NET Managed Provider for Oracle data provider that will be available in the System.Data.OracleClient namespace once installed (read below for more details on this).

Step 6: Setup permissions

Provide the ASPNET, IUSR_MACHINENAME and IWAM_MACHINENAME, Read & Execute, List Folder Contents, and Read permissions accounts to the ora92 (Oracle installation) directory and all sub-directories and files. (An ASP.NET account will be created and added to your users list when you install the .NET framework). You must restart your machine for the permission changes to take effect.

Step 7: Select and install a Data Provider

While developing an ASP.NET application using an Oracle database, initially it will be a bit confusing to decide which Data Provider to use. There are several Data Providers; the most common among them are listed below.


  1. Microsoft OLE DB Provider for Oracle
  2. Microsoft ODBC Driver for Oracle
  3. .NET Managed Provider for Oracle from Microsoft
  4. Oracle Data Provider for .NET (ODP.NET) from Oracle

The next page of this article will go into the details of each of these providers and give examples on how to connect to an Oracle database using these providers.

More by Author

Must Read