To read the article online, visit http://www.4GuysFromRolla.com/webtech/tips/t080902-1.shtml

User Tips: Using the .NET Framework Data Provider for Oracle


By Baris Inalpolat

Although MS SQL Server is gaining more popularity on Windows world, if your database layer resides on Unix, you have to interact with Oracle databases mainly. If you've had to access an Oracle database from an ASP.NET application you've likely been using, up until now, the generic OleDb drivers. This generic OleDb data provider is the data provider for any OleDb-compliant data source, such as Access, SQL, Excel, Oracle, etc.

The problem with a generic data provider is that it is not tweaked to maximize performance for a particular data store. In order to address this, Microsoft released a data provider specifically for Microsoft SQL Server 7.0 and up - the SqlClient data provider. Recently Microsoft released a .NET Framework Data Provider for Oracle. No longer do we have to use the generic OleDb driver! You can download the free Oracle driver at http://www.msdn.microsoft.com/downloads/default.asp?url=/downloads/sample.asp?url=/msdn-files/027/001/940/msdncompositedoc.xml .

This provider can be used instead of the OleDb provider, delivering a native interface for Oracle. Microsoft has reported that, in certain cases the performance of this specialized driver can exceed the generic OleDb driver by up to 200%! For information on making your .NET applications work faster and more efficiently with Oracle databases, be sure to read Greg Leake's article, Using .NET Framework Data Provider for Oracle to Improve .NET Application Performance.

The Oracle data provider supports all Oracle 9i data types and ref cursors of Oracle stored procedures which return result sets. One important note: Oracle 8i Release 3 (8.1.7) Client or later must be installed on the Web server for this provider to function properly.

If you have existing ASP.NET code that accesses an Oracle database, you'll be pleased to learn that migrating your code to use the new provider is quite simple. All you need to do is the follwoing three steps:

  1. Install the provider from Microsoft site,
  2. Add the reference (System.Data.OracleClient.dll, most likely under \Program Files\Microsoft.Net\OracleClient.Net) to your Visual Studio .NET project,
  3. Replace all OleDbConnection, OleDbDataAdapter, OleDbCommand, OleDbDataReader, etc. with OracleConnection, OracleDataAdapter, OracleCommand, and OracleDataReader in your code.

You will also want to add Imports System.Data.OracleClient (or using System.Data.OracleClient, if you are using C#) if you want to avoid writing the full namespace.

An example of converting from the OleDb provider to the Oracle provider can be seen below:

Public Function myDataTable(ByVal SQL As String, _
                            ByVal ConnStr As String) As DataTable
    Dim cn As OleDbConnection
    Dim dsTemp As DataSet
    Dim dsCmd As OleDbDataAdapter 

    cn = New OleDbConnection(ConnStr)
    cn.Open() 

    dsCmd = New OleDbDataAdapter(SQL, cn)
    dsTemp = New DataSet()
    dsCmd.Fill(dsTemp, "myQuery")
    cn.Close()
    Return dsTemp.Tables(0)
End Function 

The above code would be replaced with...

Public Function myDataTable(ByVal SQL As String, _
                            ByVal ConnStr As String) As DataTable
    Dim cn As OracleConnection
    Dim dsTemp As DataSet
    Dim dsCmd As OracleDataAdapter 

    cn = New OracleConnection(ConnStr)
    cn.Open() 

    dsCmd = New OracleDataAdapter(SQL, cn)
    dsTemp = New DataSet()
    dsCmd.Fill(dsTemp, "myQuery")
    cn.Close()
    Return dsTemp.Tables(0)
End Function 

Happy Programming!

  • By Baris Inalpolat
    bpolat@softhome.net
    Return to user tips...


  • Article Information
    Article Title: User Tips: Using the .NET Framework Data Provider for Oracle
    Article Author: Baris Inalpolat
    Article URL: http://www.4GuysFromRolla.com/webtech/tips/t080902-1.shtml


    Copyright 2014 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers