HomeASP.NET Oracle Database Interaction Using ODP.NET ...
Oracle Database Interaction Using ODP.NET and ASP.NET: All Possible Ways To Get Connected
This article mainly concentrates on connecting to an Oracle database using ASP.NET in several possible ways, and finally focuses on ODP.NET and its advantages.
A downloadable file associated with this article is available here.
The sample downloadable solution (zip) was entirely developed using Visual Studio.NET 2003 Enterprise Architect on Windows Server 2003 Standard Edition, together with Oracle 10g (version 10.2). But, I am confident that it would work with other versions of Windows (which support .NET 1.1) as well.
What are OLEDB, ODBC, ADODB, .NET, ADO.NET, ODP.NET and hell?
Let us go back to history. Everyone knows how to connect to Oracle databases using old traditional ASP (using ADODB). ADODB is just a lightweight component on top of OLEDB. And OLEDB is said to be the beginning of Microsoft’s strategy for Universal Data Access (UDA). If we need to connect to desperate legacy systems (legacy databases such as Sybase and others), we should either work with the respective OLEDB provider of that database, or we can still use Microsoft ODBC (and face the penalty of slower performance). OLEDB can also work with ODBC internally. In summary, several of the complexities are hidden inside OLEDB, and we are provided with a very simple interface to work with databases called ADODB.
Is it not simply sufficient to work with ADODB and forget about ADO.NET? My answer to this question would be NOT AT ALL. Even though ADODB can meet all of our requirements, it is based on ActiveX technology and COM. The ActiveX components would not pass through a firewall (unless configured separately). Another issue of ADODB is that it is UNMANAGED. A final issue is that it does not have much interaction with XML at all. Of course, in the days when Microsoft started building ADODB technology, XML was not at all matured enough (and not even emerging).
Once XML emerged, matured and began to be accepted (or at least considered) by several companies, Microsoft also took part in developing XML and its family of technologies, together with other giant companies. Since XML is simply text (of course written in a structured manner), it is very light, easily transferable, platform independent and can even pass through networks (or even firewalls) very easily at high speed. No wonder Microsoft realized the power of XML and finally started to design a completely new platform based on XML technologies. And that new platform is nothing but the .NET platform. As ADO.NET is an integral part of the .NET framework itself, it is completely based on XML, which is fairly easy and quite manageable too.
ADO.NET internally contains few .NET data components (or .NET data providers) which can be used to connect to and access almost any database (again, an enhancement to the Universal Data Access strategy) in the world. Even though Microsoft designed its own .NET data providers, it has also opened its doors (specification) to the public and is encouraging other database companies to develop their own .NET data providers. One of them is Oracle’s ODP.NET, a .NET data provider that should be able to connect to and access Oracle databases with tight integrity.
Can’t we access Oracle without ODP.NET? YES, we still can. It is not compulsory for you to work with ODP.NET. We can still connect to and access Oracle using other, alternative methods like the OLEDB data provider for .NET, the ODBC data provider for .NET, and so on. In this article, I try to cover almost all of the methodologies for connecting to and accessing Oracle databases.
The .NET Framework Data Provider for OLE DB requires the installation of MDAC 2.6 or later. It uses native OLE DB through “COM interop” to enable data access. To use the .NET Framework Data Provider for OLE DB, you must use an OLE DB provider. All classes for “.NET Framework Data Provider for OLE DB” are located in the System.Data.OleDb namespace.
One of the most important issues to remember is that the .NET Framework Data Provider for OLE DB does not work with the OLE DB Provider for ODBC (MSDASQL). To access an ODBC data source using ADO.NET, use the .NET Framework Data Provider for ODBC.
Now we shall test an example for connecting to and accessing data from an Oracle database using the .NET Framework Data Provider for OLEDB.
Imports System.Data.OleDb . . Dim cn As New OleDbConnection("Provider=msdaora;Data Source=ORCL;User Id=scott;Password=tiger;") Try Dim da As New OleDbDataAdapter("select * from scott.emp", cn) Dim dt As New OleDbData da.Fill(dt) da.Dispose() Me.DataGrid1.DataSource = dt Me.DataGrid1.DataBind() dt.Dispose() Cath ex As Exception Me.lblError.Text = ex.Message Finally If cn.State = ConnectionState.Open Then cn.Close() End If End Try
Those who are familiar with System.data.SqlClient (.NET Framework Data Provider for Microsoft SQL Server) would find the above code quite understandable. Connection, Command, Reader and DataAdapter are the four important objects (for connectivity and data access) which would be available with almost every .NET provider. All of those four objects are preceded by the type of .NET provider (ex: oledbConnection, odbcConnection, sqlConnection, and so forth).
To work with the above code, I registered and configured a service name “ORCL” (using Net Configuration Assistant) at my client (Oracle Client), which has been specified as the data source with in the “connection string” above. The above example is demonstrated in “webform3” of the downloadable.
The .NET Framework Data Provider for OLE DB requires the installation of MDAC 2.6 or later, but MDAC 2.7 is recommended by Microsoft. It uses a native ODBC Driver Manager (DM) through “COM interop” to enable data access. To use the .NET Framework Data Provider for ODBC, you must use an ODBC driver.
All classes for “.NET Framework Data Provider for ODBC” are located in the System.Data.Odbc namespace. If you are using .NET Framework 1.0 (or Visual Studio.NET 2002), you need to download and install the provider (which is not provided by default) from http://msdn.microsoft.com/downloads.
Now we shall test an example for connecting to and accessing data from an Oracle database using .NET Framework Data Provider for ODBC.
Imports System.Data.Odbc . . Dim cnAs New OdbcConnection("Driver={Microsoft ODBC for Oracle};Server=ORCL;Uid=scott;Pwd=tiger;") Try Dim da As New OdbcDataAdapter("select * from scott.emp", cn) Dim dt As New OleDbData da.Fill(dt) da.Dispose() Me.DataGrid1.DataSource = dt Me.DataGrid1.DataBind() dt.Dispose() Cath ex As Exception Me.lblError.Text = ex.Message Finally If cn.State = ConnectionState.Open Then cn.Close() End If End Try
The explanation for the above code is very similar to that of the previous section (mainly, I replaced all “oledb” with “odbc” everywhere). But you need to take extreme care with the “connection string” I specified above. Another issue is that, you need not create any DSN (the old traditional way to access from ODBC) within the ODBC manager to run the above code! The above example is demonstrated in “webform4” of the downloadable.
The .NET Framework Data Provider for Oracle (or Microsoft’s Data Provider for Oracle) enables data access to Oracle data sources through Oracle client connectivity software. The data provider supports Oracle client software version 8.1.7 and later. The .NET Framework Data Provider for Oracle requires that Oracle client software (version 8.1.7 or later) be installed on the system before you can use it to connect to an Oracle data source.
All classes for the “.NET Framework Data Provider for Oracle” are located in the System.Data.OracleClient namespace and are contained in the System.Data.OracleClient.dll assembly. You will need to reference both the System.Data.dll and the System.Data.OracleClient.dll when compiling an application that uses the data provider. If you are using .NET Framework 1.0 (or Visual Studio.NET 2002), you need to download and install the provider (which is not provided by default) from http://msdn.microsoft.com/downloads.
Now we shall test an example of connecting and accessing data from an Oracle database using .NET Framework Data Provider for Oracle.
Imports System.Data.OracleClient . . Dim cn As New OracleConnection("Data Source=ORCL;User Id=scott;Password=tiger;") Try DimdaAsNewOracleDataAdapter("select * from scott.emp", cn) Dim dt As New OleDbData da.Fill(dt) da.Dispose() Me.DataGrid1.DataSource = dt Me.DataGrid1.DataBind() dt.Dispose() Cath ex As Exception Me.lblError.Text = ex.Message Finally If cn.State = ConnectionState.Open Then cn.Close() End If End Try
The explanation for the above code is very similar to that of the previous sections (mainly I replaced all “odbc” with “oracle” everywhere). But, you need to look carefully at the “imports” statement and “connection string” I specified above. The above example is demonstrated in “webform2” of the downloadable.
ODP.NET is freely available from the Oracle Technology Network (OTN) at http://www.oracle.com/technology/software/tech/windows/odpnet/index.html. The types are contained in two namespaces: Oracle.DataAccess.Client (data access classes and enumerations) and Oracle.DataAccess.Types (classes and structures for Oracle data types). Both namespaces are in the assembly Oracle.DataAccess.dll (generally available in Oracle client installed directory). ODP.NET requires Oracle client version 9.2 or later.
Now we shall test an example of connecting to and accessing data from an Oracle database using ODP.NET.
Imports Oracle.DataAccess.Client . . Dim cn As New OracleConnection("Data Source=ORCL;User Id=scott;Password=tiger;") Try Dim da As New OracleDataAdapter("select * from scott.emp", cn) Dim dt As New OleDbData da.Fill(dt) da.Dispose() Me.DataGrid1.DataSource = dt Me.DataGrid1.DataBind() dt.Dispose() Cath ex As Exception Me.lblError.Text = ex.Message Finally If cn.State = ConnectionState.Open Then cn.Close() End If End Try
The explanation for the above code is very similar to that of the previous sections. But, you need to look carefully at the “imports” statement; it is a bit different from the others because we are using ODP.NET. The above example is demonstrated in “webform1” of the downloadable.
Of all of the above methods of Oracle database access, the last two (.NET Framework data provider for Oracle and ODP.NET) are the preferred methods for working with Oracle databases. These two directly talk with Oracle databases without any mediation layer (like OLEDB or ODBC), thus improving performance. And another advantage is that both of them are managed providers (no “COM interop” in between).
ODP.NET supports all native Oracle data types in the Oracle.DataAccess.Types namespace. The ODP.NET data types—either classes or structures—more closely map to the native types than the .NET Framework data types. The ODP.NET has additional Oracle-specific types compared with the Microsoft provider, and those types expose additional methods and properties to interrogate, manipulate, and convert data.
The ODP.NET OracleDataAdapter allows you to safely map Oracle data types to .NET data types where there is a potential for data loss. ODP.NET features much tighter integration (for several new data types) with .NET than Microsoft data provider for Oracle. This tighter integration is very important from the point of view of the developer/programmer. And most of all, it is FREE.
Everybody knows that, to work with Oracle natively, one has to leave the Visual Studio.NET environment and go for SQL*Plus (or even iSQL*Plus for that matter). Oracle recently released an add-in for Visual Studio.NET called “Oracle Developer Tools for Visual studio.NET”, which became as popular as hotcakes for every Visual Studio.NET programmer who works with Oracle. It is freely downloadable at http://www.oracle.com/technology/tech/dotnet/tools/index.html). Of course, Oracle even became a Microsoft Visual Studio Development Partner (so estimate the future benefits).
When you install “Oracle Developer Tools for Visual studio.NET”, it automatically installs ODP.NET also and is directly integrated into Visual Studio.NET (similar to “server explorer” in Visual Studio.NET). You can develop stored procedures and more without leaving the Visual Studio.NET environment at all, along with the support of “intellisense”. Download it and give it a try.
Any comments, suggestions, bugs, errors, feedback etc. are highly appreciated at jag_chat@yahoo.com.
ASP.NET 
/ 23 
ASP.NET Export to Excel and Word using VB.NET and C#
