Connecting to Oracle 9 using the Microsoft .NET Framework

René Steiner, Akadia AG, Information Technology, CH-3604 Thun
Phone: +41 33 335 86 22 / Fax: +41 33 335 86 25 /
EMail: rene.steiner@akadia.com

... The Microsoft® .NET Framework is the infrastructure for the overall .NET Platform. The common language runtime and class libraries (including Microsoft Windows® Forms, ADO.NET, and ASP.NET) combine to provide services and solutions that can be easily integrated within and across a variety of systems.

The .NET Framework provides a fully managed, protected, and feature-rich application execution environment, simplified development and deployment, and seamless integration with a wide variety of languages.

Well, let's do it ... in this Tip we show how to connect to an Oracle 9.2.0 database from the .NET Framework using C#, MDAC and ODBC.

Microsoft .NET Framework Software Development Kit

The Microsoft® .NET Framework Software Development Kit (SDK) includes the .NET Framework, as well as everything you need to write, build, test, and deploy .NET Framework applications - documentation, samples, and command-line tools and compilers.

Download and Installation:

Goto:

http://msdn.microsoft.com/downloads/

Select:

-> Software Development Kits
-> Microsoft .NET Framework SDK

You'll get the whole framework and a C# command line compiler. Run the downloaded setup.exe if you haven't installed yet Microsoft .NET and follow the installation steps. You will be asked for Server Components which you don't need. If the installation asks for Microsoft Data Access Components MDAC you may continue or quit the installation. Anyway, you need to install MDAC 2.7 or higher prior ODBC data access is going to work.

Microsoft® Data Access Components (MDAC Version 2.7)

The Microsoft® Data Access Components (MDAC) are the key technologies that enable Universal Data Access, Microsoft's strategy for providing access to information across the enterprise. Data-driven client/server applications deployed over the Web or a local area network can use these components to integrate information from a variety of sources, both relational (SQL) and nonrelational. The SDK contains documentation, samples, and tools designed to help you create data access solutions.

MDAC SDK Components

The MDAC SDK consists of the following components:

ActiveX Data Objects (ADO) enables client applications to access and manipulate data from a database server through an OLE DB provider. Primary benefits are ease of use, high speed, low memory overhead, and a small disk footprint. ADO supports building client/server and Web-based applications.

OLE DB a set of COM-based interfaces that expose data from a variety of sources. It provides applications with uniform access to data stored in diverse data stores. The interfaces support the amount of database management system (DBMS) functionality appropriate to the data store, enabling the data store to share its data.

ODBC enables applications to access data from a variety of DBMSs. ODBC permits maximum interoperability so applications can access data in diverse DBMSs through a single interface. Furthermore, applications are independent of any DBMS from which they access data. Users can add a driver, which provides an interface between applications and a specific DBMS.

Download and Installation:

Goto:

http://www.microsoft.com/msdownload/platformsdk/sdkupdate/

Select:

-> MDAC SDK
-> Install This SDK

To open the MDAC SDK once it is installed:

From the Start menu, click Programs, point to Microsoft Platform SDK , and then click Platform SDK Documentation.

In the Contents pane, expand Data Services, and then navigate to MDAC SDK.

In the Contents pane, expand MDAC SDK, and then navigate to the appropriate SDK component (ADO, ODBC, etc.).

ODBC .NET Data Provider

The Open Database Connectivity (ODBC) .NET Data Provider is an add-on component to the .NET Framework. It provides access to native ODBC drivers the same way the OLE DB .NET Data Provider provides access to native OLE DB providers. The ODBC .NET Data Provider is intended to work with all compliant ODBC drivers but only the following drivers have been tested with the ODBC .NET Data Provider:

Microsoft SQL ODBC Driver

Microsoft ODBC Driver for Oracle

Microsoft Jet ODBC Driver

Download and Installation:

Goto:

http://msdn.microsoft.com/downloads/

Select:

-> .NET Framework
-> ODBC .NET Data Provider
-> Download
-> Double click on the downloaded file: odbc_net.msi

Use "Administrative Tools" and "Data Sources (ODBC)" to configure an ODBC data source on your Windows operating system. The menu points and the name of the configuration program may differ between different Windows versions. Click on the "Add" button and select the Oracle driver. You may use the default settings in most of the cases.

If you copy the source code below into a file called odbctest.cs you may compile it like this. Start the Command Shell (CMD) and type all in one line:

C:\Users\Zahn\Tmp\odbc> csc /reference:"C:\Program Files\
Microsoft.Net\Odbc.Net\Microsoft.Data.Odbc.dll" odbctest.cs

Microsoft (R) Visual C# .NET Compiler version 7.00.9466
for Microsoft (R) .NET Framework version 1.0.3705
Copyright (C) Microsoft Corporation 2001. All rights reserved.

The file Microsoft.Data.Odbc.dll is the so-called data provider we have previously installed. The shown path corresponds to the default path. If you have this file somewhere else stored adapt the path accordingly.

The C# compiler can be invoked at the command line by typing the name of its executable file (csc.exe) on the command line. You may need to adjust your path if you want csc.exe to be invoked from any subdirectory on your computer.

C:\Users\Zahn\Tmp\odbc> odbctest.exe

Connect to DSN=DotNetOdbc;UID=scott;PWD=tiger ...
.....