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.