Oracle Data Provider for .NET (ODP.NET) features optimized ADO.NET data access to the Oracle database. ODP.NET allows developers to take advantage of advanced Oracle database functionality, including Real Application Clusters, XML DB, and self-updating client caches.

ODP.NET makes using Oracle from .NET more flexible, faster, and more stable. ODP.NET includes many features not found in other .NET drivers, including flexible LOB data types, self-tuning, run-time connection load balancing, fast connection failover, and Advanced Queuing.

Sample Code:
(Usage in c #)

    private OracleDB oDB;
 
    public List<term> GetTags(Decimal postID)
    {
      List</term><term> tagList = new List</term><term>();
 
      DataSet ds;
      DataTable dt;
      try
      {
      OracleParameter[] paraArray = new OracleParameter[] {
        new OracleParameter("P_TAXONOMY", OracleDbType.Varchar2,35, ParameterDirection.Input) {Value = Taxonomy.post_tag.ToString() },
        new OracleParameter("P_POST_ID", OracleDbType.Decimal,10, ParameterDirection.Input) {Value = postID},
        new OracleParameter("OUT_ERROR_MSG", OracleDbType.Varchar2,1000, null, ParameterDirection.Output),
        new OracleParameter("OUT_CUR", OracleDbType.RefCursor, ParameterDirection.Output)
      };
        oDB.Open();
        ds = oDB.FillDataset("LIVE_PKG.GetTaxonomy", CommandType.StoredProcedure, paraArray);
        dt = ds.Tables[0];
        if (ds.Tables[0].Rows.Count > 0)
        {
          foreach (DataRow row in ds.Tables[0].Rows)
          {
            TERM p = new TERM();
            p.TERM_ID = Decimal.Parse(row["TERM_ID"].ToString());
            p.NAME = row["NAME"].ToString();
            p.SLUG = row["SLUG"].ToString();
            p.TERM_TAXONOMY_ID = Decimal.Parse(row["TERM_TAXONOMY_ID"].ToString());
            p.TAXONOMY = row["TAXONOMY"].ToString();
            p.DESCRIPTION = row["DESCRIPTION"].ToString();
            p.PARENT = (row["PARENT"].ToString() != "") ? Decimal.Parse(row["PARENT"].ToString()) : 0;
            p.POST_COUNT = (row["PostCount"].ToString() != "") ? Decimal.Parse(row["PostCount"].ToString()) : 0;
 
            //get meta
 
            tagList.Add(p);
          }
        }
      }
      catch (OracleException ex) { oDB.Close();throw ex; }
      catch (Exception ex)
      {
        oDB.Close();
        throw ex;
      }
      finally
      {
        oDB.Close();
      }
      return tagList;
    }
 
 
    public void RemoveAll(string taxonomy, decimal postID)
    {
      try
      {
      OracleParameter[] paraArray = new OracleParameter[] {
        new OracleParameter("P_TAXONOMY", OracleDbType.Varchar2,35, ParameterDirection.Input) {Value =taxonomy},
        new OracleParameter("P_POST_ID", OracleDbType.Decimal,10, ParameterDirection.Input) {Value = postID },
        new OracleParameter("OUT_ERROR_MSG", OracleDbType.Varchar2,1000, null, ParameterDirection.Output)
      };
        oDB.Open();
        oDB.BeginTransaction();
        oDB.ExecuteNonQuery("LIVE_PKG.RemoveAll", CommandType.StoredProcedure, paraArray);
        string err = oDB.GetParameterValueBy("OUT_ERROR_MSG").ToString();
        oDB.CommitTransaction();
      }
      catch (OracleException ex)
      {
        oDB.Close();
        throw ex;
      }
      catch (Exception ex)
      {
        oDB.Close();
        throw ex;
      }
      finally
      {
        oDB.Close();
      }
    }
</term>

(OracleDB.vb)

''------------------------------------------------------------------------------
'' <readme>
''    This class required Oracle.ManagedDataAccess, or OPD.NET lib
''      and CustomHelpersErrorEventHandler (which not included, comment out for no use)
''    by Kevin
'' </readme>
''------------------------------------------------------------------------------
 
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Threading.Tasks
Imports Oracle.ManagedDataAccess.Client
Imports Oracle.ManagedDataAccess.Types
Imports System.Data
Imports System.IO
 
Public Class OracleDB
  Implements IDisposable
  Public Shared Event CustomError As CustomHelpersErrorEventHandler
 
  Private connString As String
  Private timeoutCmd As Integer = 60
  Protected conn As New OracleConnection()
  Protected cmd As New OracleCommand()
 
 
#Region "Constructors"
  Public Sub New()
  End Sub
  Public Sub New(_connString As String)
    connString = _connString
  End Sub
  Public Sub New(_connString As String, _timeoutCmd As Integer)
    connString = _connString
    CommandTimeout = _timeoutCmd
  End Sub
#End Region
 
 
#Region "Properties"
  Public ReadOnly Property ConnectionState() As ConnectionState
    Get
      Return conn.State
    End Get
  End Property
  Public Property ConnectionString() As String
    Get
      Return connString
    End Get
    Set(value As String)
      connString = value
    End Set
  End Property
 
  Public Property CommandTimeout As Integer
    Get
      Return cmd.CommandTimeout
    End Get
    Set(value As Integer)
      timeoutCmd = value
      cmd.CommandTimeout = timeoutCmd
    End Set
  End Property
 
  Public ReadOnly Property IsConnected() As Boolean
    Get
      If conn.State <> ConnectionState.Closed Then
        Return True
      Else
        Return False
      End If
    End Get
  End Property
 
#End Region
 
 
#Region "Pub Methods"
  Public Sub BeginTransaction()
    Try
      ' Start a local transaction
      cmd.Transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted)
    Catch ex As OracleException      
      cmd.Transaction.Rollback()
      Throw ex
      OnCustomError(Me, New CustomHelpersErrorEventArgs(ErrorTypes.CustomExceptionTypes.Transaction_Begin, ex))
    End Try
  End Sub
 
  Public Sub CommitTransaction()
    Try
      cmd.Transaction.Commit()
    Catch ex As OracleException      
      cmd.Transaction.Rollback()
      Throw ex
      OnCustomError(Me, New CustomHelpersErrorEventArgs(ErrorTypes.CustomExceptionTypes.Transaction_Commit, ex))
    End Try
  End Sub
 
  Public Sub RollbackTransaction()
    Try
      cmd.Transaction.Rollback()
    Catch ex As OracleException
      Throw ex
      OnCustomError(Me, New CustomHelpersErrorEventArgs(ErrorTypes.CustomExceptionTypes.Transaction_Rollback, ex))
    End Try
  End Sub
 
  Public Sub Open()
    Try
      'close connection first befor open
      conn.Close()
      conn.ConnectionString = connString
      conn.Open()
      cmd.Connection = conn
    Catch ex As OracleException
      Throw ex
      OnCustomError(Me, New CustomHelpersErrorEventArgs(ErrorTypes.CustomExceptionTypes.DatabaseConnection_Open, ex))
    Catch ex As Exception
      Throw ex
      OnCustomError(Me, New CustomHelpersErrorEventArgs(ErrorTypes.CustomExceptionTypes.ExecuteStoredProcedure, ex))
 
    End Try
  End Sub
 
  Public Sub Close()
    Try      
      'cmd.Dispose()
      'conn.Close()
      'conn.Dispose()
      'If Not (cmd Is Nothing) Then cmd.Dispose()
      If Not (conn Is Nothing) Then conn.Close()
      'If Not (conn Is Nothing) Then conn.Dispose()
    Catch ex As OracleException
      Throw ex
      OnCustomError(Me, New CustomHelpersErrorEventArgs(ErrorTypes.CustomExceptionTypes.DatabaseConnection_Close, ex))
    End Try
  End Sub
 
  Public Function ExecuteSQL(ByVal sql As String) As DataTable
    Dim dt As DataTable = New DataTable()
    Try
      cmd.CommandText = sql.ToString()
      cmd.CommandType = CommandType.Text
      Dim reader As OracleDataReader = cmd.ExecuteReader()
      dt.Load(reader)
      reader.Dispose()
    Catch ex As OracleException
      Throw ex
      OnCustomError(Me, New CustomHelpersErrorEventArgs(ErrorTypes.CustomExceptionTypes.ExecuteStoredProcedure, ex, "ExecuteSQL()...sql=" & sql.ToString(), True))
    End Try
    Return dt
  End Function
 
  Public Sub ExecuteNonQuery(ByVal sql As String)    
    Try
      cmd.CommandText = sql.ToString()
      cmd.CommandType = CommandType.Text
      cmd.ExecuteNonQuery()
    Catch ex As OracleException
      Throw ex
      OnCustomError(Me, New CustomHelpersErrorEventArgs(ErrorTypes.CustomExceptionTypes.ExecuteStoredProcedure, ex, "ExecuteNonQuery()...sql=" & sql.ToString(), True))
    End Try    
  End Sub
 
  Public Sub ExecuteNonQuery()
    Try
      cmd.ExecuteNonQuery()
    Catch ex As OracleException
      Throw ex
      OnCustomError(Me, New CustomHelpersErrorEventArgs(ErrorTypes.CustomExceptionTypes.ExecuteStoredProcedure, ex))
    Catch ex As Exception
      Throw ex
      OnCustomError(Me, New CustomHelpersErrorEventArgs(ErrorTypes.CustomExceptionTypes.ExecuteStoredProcedure, ex))
    End Try
  End Sub
 
  ''' <summary>
  ''' ExecuteNonQuery and use GetParameterValueBy() to get output values
  ''' </summary>
  ''' <param name="cmdPara"/>
  ''' <param name="cmdType"/>
  ''' <param name="parameters"/>
  ''' <remarks></remarks>
  Public Sub ExecuteNonQuery(ByVal cmdPara As String, ByVal cmdType As CommandType, Optional ByVal parameters As OracleParameter() = Nothing)
    Try
      cmd.Parameters.Clear()
      cmd.BindByName = True
      cmd.CommandText = cmdPara
      cmd.CommandType = cmdType
      If parameters IsNot Nothing Then
        AssignParameters(parameters)
      End If
 
      cmd.ExecuteNonQuery()
    Catch ex As OracleException
      Throw ex
      cmd.Parameters.Clear()
      OnCustomError(Me, New CustomHelpersErrorEventArgs(ErrorTypes.CustomExceptionTypes.ExecuteStoredProcedure, ex))
    Catch ex As Exception
      Throw ex
      cmd.Parameters.Clear()
      OnCustomError(Me, New CustomHelpersErrorEventArgs(ErrorTypes.CustomExceptionTypes.ExecuteStoredProcedure, ex))
    End Try
  End Sub
  ''' <summary>
  ''' pass parameter name
  ''' </summary>
  ''' <param name="name"/>
  ''' <returns></returns>
  ''' <remarks></remarks>
  Public Function GetParameterValueBy(name As String) As Object
 
    Return cmd.Parameters(name).Value
  End Function
 
 
  ''' <summary>
  ''' Adds or refreshes rows in the System.Data.DataSet to match those in the data source using the System.Data.DataSet name, and creates a System.Data.DataTable named "Table."
  ''' </summary>   
  ''' <param name="cmdType"/>A value indicating how theSqlCommand.CommandText property is to be interpreted.
  ''' <param name="parameters"/>The parameters of the Transact-SQL statement or stored procedure.
  ''' <returns>A System.Data.Dataset object.</returns>
  Public Function FillDataset(ByVal cmdPara As String, ByVal cmdType As CommandType, Optional ByVal parameters As OracleParameter() = Nothing) As DataSet
 
    'Dim connection As SqlConnection = Nothing
    'Dim command As SqlCommand = Nothing      
    'Dim transaction As OracleTransaction = Nothing
 
    Dim sqlda As OracleDataAdapter = Nothing
    Dim res As New DataSet
    Try
      cmd.BindByName = True
      cmd.CommandText = cmdPara
      cmd.CommandType = cmdType
      If parameters IsNot Nothing Then
        AssignParameters(parameters)
      End If
      sqlda = New OracleDataAdapter(cmd)
      Dim cb = New OracleCommandBuilder(sqlda)
      sqlda.Fill(res)
      cmd.Parameters.Clear()
 
    Catch ex As OracleException
      cmd.Parameters.Clear()
      Throw New Exception("Procedure (" & cmdPara & ") " & ex.Source & " " & ex.Message, ex.InnerException)
      OnCustomError(Me, New CustomHelpersErrorEventArgs(ErrorTypes.CustomExceptionTypes.ExecuteStoredProcedure, ex, "FillDataset()...", True))
    Catch ex As Exception
      cmd.Parameters.Clear()
      Throw New Exception("Procedure (" & cmdPara & ") " & ex.Source & " " & ex.Message, ex.InnerException)
      OnCustomError(Me, New CustomHelpersErrorEventArgs(ErrorTypes.CustomExceptionTypes.ExecuteStoredProcedure, ex, "FillDataset()...", True))
 
    Finally
      parameters = Nothing
      cmd.Parameters.Clear()
      If Not (sqlda Is Nothing) Then sqlda.Dispose()
    End Try
    Return res
  End Function
 
 
  Public Sub AddParameters(ByVal cmdParameters As OracleParameter())
    If (cmdParameters Is Nothing) Then Exit Sub
 
    Try
      'cmd.Parameters.AddRange(cmdParameters)
      For Each p As OracleParameter In cmdParameters
        cmd.Parameters.Add(p)
      Next
    Catch ex As Exception
      Throw New Exception("AssignParameters(): " & ex.Message, ex.InnerException)
      OnCustomError(Me, New CustomHelpersErrorEventArgs(ErrorTypes.CustomExceptionTypes.ExecuteStoredProcedure, ex, "FillDataset()...", True))
    End Try
 
  End Sub
 
 
  ''' <summary>
  ''' return formated date for oracle
  ''' </summary>
  ''' <param name="Dt"/>
  ''' <param name="partern"/>
  ''' <returns></returns>
  ''' <remarks></remarks>
  Public Function FormatDBDate(ByVal Dt As DateTime, Optional partern As String = "") As String
    Dim newTime As String = "NULL"
    If partern <> "" Then
      newTime = Format(Dt, partern)
    Else
      newTime = Format(Dt, "yyyy-MM-dd HH:mm:ss")
    End If
    Return newTime
  End Function
 
  ''' <summary>
  ''' Modified from vb code, return db string for inline query
  ''' </summary>
  ''' <param name="str"/>
  ''' <param name="maxlen"/>
  ''' <returns></returns>
  ''' <remarks></remarks>
  Public Function PrepareDBString(str As Object, Optional maxlen As Integer = 2000) As String
    If String.IsNullOrEmpty(str) Then
      Return "NULL"
    ElseIf Len(CStr(str)) < = 0 Then
      Return "''"
    End If
 
    Dim nChar As Long
    Dim sOrig As String
    Dim sNew As String
 
    'replace single quote (') with 2 single quotes ('')
    sOrig = str
    sNew = ""
    Do
      nChar = InStr(sOrig, "'")
      If nChar > 0 Then
        sNew = sNew & Left$(sOrig, nChar - 1) & "''"
        sOrig = Mid(sOrig, nChar + 1)
      Else
        sNew = sNew & sOrig
        Exit Do
      End If
    Loop
 
    ' must truncate to the width of the column
    If Len(sNew) > maxlen Then
      sNew = Left$(sNew, maxlen)
    End If
 
    'last char cannot be a single quote
    Do While Right$(sNew, 1) = "'"
      sNew = Left$(sNew, Len(sNew) - 1)
    Loop
 
    'surround the string with single quotes
    Return "'" & Trim(sNew) & "'"
  End Function
 
  Public Function PrepareDBDate(Dt As Object, Optional fmt As DBDateFormats = 0) As String
    If Dt Is Nothing Then
      Return "NULL"
    ElseIf Dt < = CDate("#1/1/1800#") Then
      Return "NULL"
    Else
      Select Case fmt
        Case DBDateFormats.DB_DATE : Return " TO_DATE('" & Format(Dt, "yyyy-MM-dd") & "','RRRR-MM-DD') "
        Case DBDateFormats.DB_TIME : Return " TO_DATE('" & Format(Dt, "HH:mm:ss") & "','HH24:MI:SS') "
        Case Else : Return " TO_DATE('" & Format(Dt, "yyyy-MM-dd HH:mm:ss") & "','RRRR-MM-DD HH24:MI:SS') "
      End Select
    End If
  End Function
 
  ''' <summary>
  ''' return number for inline query
  ''' 
  ''' <param name="num"/>
  ''' <param name="UseNull"/>
  ''' <returns></returns>
  ''' <remarks></remarks>
  Public Function PrepareDBNumber(num As Object, Optional UseNull As Boolean = True) As String
    If String.IsNullOrEmpty(num) Then
      Return IIf(UseNull, "NULL", CStr(0))
    ElseIf Len(CStr(num)) = 0 Then
      Return IIf(UseNull, "NULL", CStr(0))
    Else
      Return CStr(CDbl(num))
    End If
  End Function
 
 
  ' Protected implementation of Dispose pattern. 
  Protected Overridable Overloads Sub Dispose(disposing As Boolean)
    If disposing Then
      Console.WriteLine(vbNewLine + "[{0}].Base.Dispose(true)", Me.GetType().FullName)
      cmd.Parameters.Clear()
      cmd.Dispose()
      conn.Close()
      conn.Dispose()
      If cmd IsNot Nothing Then
        cmd.Dispose()
        cmd = Nothing
      End If
      If conn IsNot Nothing Then
        conn.Close()
        conn.Dispose()
        conn = Nothing
      End If
    Else
      Console.WriteLine(vbNewLine + "[{0}].Base.Dispose(false)", Me.GetType().FullName)
    End If
  End Sub
 
  Public Overloads Sub Dispose() Implements IDisposable.Dispose
    Console.WriteLine(vbNewLine + "[{0}].Base.Dispose()", Me.GetType().FullName)
 
    ' Dispose of unmanaged resources.
    Dispose(True)
    ' Suppress finalization.
    GC.SuppressFinalize(Me)
  End Sub
 
  Protected Overrides Sub Finalize()
    ' Simply call Dispose(False).
    Console.WriteLine(vbNewLine + "[{0}].Base.Finalize()", Me.GetType().FullName)
    Dispose(False)
  End Sub
 
  ''' <summary>
  ''' Raises the CustomError event.  This method is mainly provided so that objects that derive from this object can raise the CustomError event.
  ''' </summary>
  ''' <param name="sender"/>
  ''' <param name="e"/>CustomErrorEventArgs object
  Protected Sub OnCustomError(sender As Object, e As CustomHelpersErrorEventArgs)
    RaiseEvent CustomError(sender, e)
  End Sub
#End Region
 
#Region "Private methods"
  Private Sub AssignParameters(ByVal cmdParameters As OracleParameter())
    If (cmdParameters Is Nothing) Then Exit Sub
 
    Try
      cmd.Parameters.AddRange(cmdParameters)
      'For Each p As OracleParameter In cmdParameters
      'cmd.Parameters.Add(p)
      'Next
    Catch ex As Exception
      Throw New Exception("AssignParameters(): " & ex.Message, ex.InnerException)
      OnCustomError(Me, New CustomHelpersErrorEventArgs(ErrorTypes.CustomExceptionTypes.ExecuteStoredProcedure, ex, "FillDataset()...", True))
    End Try
 
  End Sub
 
  Private Sub AssignParameters_NO(ByVal parameterValues() As Object)
    If Not (cmd.Parameters.Count - 1 = parameterValues.Length) Then Throw New ApplicationException("Stored procedure's parameters and parameter values does not match.")
    Dim i As Integer
    For Each param As OracleParameter In cmd.Parameters
      If Not (param.Direction = ParameterDirection.Output) AndAlso Not (param.Direction = ParameterDirection.ReturnValue) Then
        param.Value = parameterValues(i)
        i += 1
      End If
    Next
  End Sub
#End Region
End Class
 
Public Enum DBDateFormats
  DB_DATE = 0
  DB_TIME = 1
  DB_DATETIME = 2
End Enum

Via: http://www.oracle.com/technetwork/topics/dotnet/index-085163.html

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.