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.

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

On the first page of this article, we discussed the step-by-step process required to install and configure all the components necessary to establish a connection from an ASP.NET application to an Oracle database. In this part, we will discuss in detail the data providers available for ASP.NET to connect to an Oracle database.

Data Providers

Data Providers are also sometimes referred to as Drivers. To communicate with an Oracle database, you can use a variety of data providers. In addition to the major vendors such as Microsoft and Oracle, there are other third-party companies developing these data providers. The four Data Providers (drivers) we mentioned earlier are the most common. Each has its own advantages and disadvantages; these are discussed in the following paragraphs.

The OleDB and ODBC data providers from Microsoft are bridge providers, hence there is a performance penalty in using them. If execution speed is a concern, the native providers from Microsoft (System.Data.OracleClient) and Oracle (Oracle.DataAccess.Client) are better options. If you want to use the advanced features of Oracle, your choice would definitely be the data provider from Oracle (Oracle.DataAccess.Client).

To avoid the use of confusing jargon, in the following discussion we will be using the respective namespaces to distinguish between each of these providers. Please note that all the four different .NET providers have the same basic class taxonomy. Their underlying implementation is what makes them different. The connection string used in each provider is slightly different. The details and code samples applicable to each of them are given below:

System.Data.OleDb (Microsoft's .NET Framework Data Provider for OLE DB)

This namespace contains classes that make up the .NET Framework Data Provider for OLE DB-compatible data sources. These classes allow you to connect to an OLE DB data source, execute commands against the source, and read the results. This data provider is included by default in both 1.0 and 1.1 versions of the .NET Framework. Therefore, no additional installation is necessary.

Default Installation Directory: C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322

Assembly Name (DLL): System.Data.dll

A code sample showing, how to connect to Oracle using OleDb Data Provider is given below:

using System.Data.OleDb;
...
OleDbConnection  oracleConn = new OleDbConnection();
oracleConn.ConnectionString = "Provider=\"OraOLEDB.Oracle.1\";
                              User ID=scott;Password=tiger;
                              Data Source=yourSource;
                              Extended Properties="";
                              Persist Security Info=False";
oracleConn.Open();

System.Data.Odbc (Microsoft's .NET Framework Data Provider for ODBC—Free download from Microsoft)

This namespace contains classes that make up the .NET Framework Data Provider for ODBC. This is an add-in component to the Microsoft .NET Framework. It directly accesses native ODBC drivers in the same way that the OLE DB .NET Data Provider accesses native OLE DB providers. You need to download and install this, separate from the .Net framework installation.

Default installation directory: C:\Program Files\Microsoft.NET\Odbc.Net

Assembly name (DLL): Microsoft.Data.Odbc.dll

A code sample showing, how to connect to Oracle using Odbc Data Provider is given below:

using System.Data.Odbc;
...
OdbcConnection  oracleConn = new OdbcConnection();
oracleConn.ConnectionString = "DSN=odbcName;UID=scott;PWD=tiger;
                               DRIVER={Microsoft ODBC for Oracle};
                               SERVER=oracleServerNameOr IPAddress;";
oracleConn.Open();

System.Data.OracleClient (Microsoft's .NET Framework Data Provider for Oracle—Free download from Microsoft)

This namespace contains classes that make up the .NET Framework Data Provider for Oracle. This is an add-on component to the Microsoft .NET Framework and is supported from versions 1.1 and up. This provider accesses an Oracle database using the Oracle Call Interface (OCI) as provided by Oracle Client software. You need to download and install this, separate from the .NET framework installation.

Default installation directory: C:\Program Files\Microsoft.NET\OracleClient.Net

Assembly name (DLL): System.Data.OracleClient.dll

A code sample showing how to connect to Oracle using System.Data.OracleClient Data Provider is given below:

using System.Data.OracleClient;
...
OracleConnection oracleConn = new OracleConnection();
oracleConn.ConnectionString = "user id=scott;
                               data source=oracleSN;
                               password=tiger";
oracleConn.Open();

Oracle.DataAccess.Client (ODP.NET—The .NET data provider for the Oracle database by Oracle Corporation—Free download from Oracle)

Oracle Data Provider for .NET (ODP.NET) is the .NET data provider for the Oracle database by Oracle Corporation. ODP.NET uses Oracle native APIs to offer fast access to Oracle data from any .NET application. You need to download and install this, separate from the .NET framework installation.

Default Installation Directory: c:\orant9i\bin\Oracle.DataAccess.dll

Assembly Name (DLL): Oracle.DataAccess.dll

This assembly provides the following two namespaces:

  • The Oracle.DataAccess.Client namespace contains ODP.NET classes and enumerations.
  • The Oracle.DataAccess.Types namespace contains the Oracle Data Provider for .NET Types (ODP.NET Types).

A code sample showing how to connect to Oracle using Oracle.DataAccess.Client Data Provider is given below:

using Oracle.DataAccess.Client;
...
OracleConnection oracleConn = new OracleConnection();
oracleConn.ConnectionString = "User Id=scott;Password=tiger;
                               Data Source=oracleSN;";
oracleConn.Open();

Some Nuances

Except for the speed and implementation details, the classes and APIs within these namespaces are very similar. Even then, there are some nuances that you may want to be aware of:

Terminating an SQL statement

OleDB and ODBC command class objects (OleDbCommand and OdbcCommand) happily accept an SQL query string ending with ";". But when using the OracleCommand class from System.Data.OracleClient namespace, the SQL query statement cannot contain the terminating semicolon (";").

Data type casting issues

Even though the .NET documentation says otherwise, the following command will throw the exception "System.InvalidCastException: Specified cast is not valid" when used with the OracleCommand class belonging to the System.Data.OracleClient namespace.

double myVal = (double)oracleCommand.ExecuteScalar();

As a workaround, you may want to use the following code:

double myVal = Convert.ToDouble(oracleCommand.ExecuteScalar());

Summary

Newer languages and tools are making a programmer's life much easier. The components and widgets provided by ASP.NET will allow you to develop complex data driven applications without writing even a single line of SQL statement. However, there can be a few stumbling blocks when integrating technologies from multiple vendors such as Oracle and Microsoft. Connecting to an Oracle database from an ASP.NET application is not very difficult. Even then, due to the presence of different components involved in this process (IIS, .NET framework, ASP.NET, and Oracle), a systematic approach is necessary. Otherwise, this could possibly become an overwhelming task for a less experienced programmer. I hope this article helped you a little bit in your programming quest. Thanks for reading.



About the Author

Jayan Nair

Jayan Nair is a Senior Software Engineer with 15+ years of experience working with cutting edge software technologies. Currently he is developing the next generation software applications for the telecommnunications testing industry. Jayan's passions: Object Oriented software design and developing reusable software components. His motto: "if the software you write is not reusable, you are not writing software, but hardware instead". Jayan finished his Masters degree in Computer Science from Virginia Tech, Blacksburg, VA. His expertise includes, C, C++, Java, J2EE, Visual Basic, C#, ASP.NET and distributed applications. He is also a Sun Certified Programmer for the Java Platform (SCPJ). You can contact him at jnair1998@hotmail.com.

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Today's agile organizations pose operations teams with a tremendous challenge: to deploy new releases to production immediately after development and testing is completed. To ensure that applications are deployed successfully, an automatic and transparent process is required. We refer to this process as Zero Touch Deployment™. This white paper reviews two approaches to Zero Touch Deployment--a script-based solution and a release automation platform. The article discusses how each can solve the key …

  • The advent of compute accelerators, special purpose coprocessors that significantly improve the performance of traditional host-based central processing unit (CPU) computations, represents a major transition in the tools available to achieve supercomputing power. This white paper presents Dell solution offerings and shows how they push the envelope of available solutions that bring immediate added value to HPC users in terms of integration, flexibility, and efficiency of hybrid compute solutions.

Most Popular Programming Stories

More for Developers

RSS Feeds