Using Microsoft's OracleClient - Tips & Tricks

The OracleClient bundled with .Net 2.0 is a very good staring point for developing applications which don't require moderately advanced features of oracle (such as proxy login). However there are some corners that I have discovered which are really useful.

Using Oracle Instant Client

The this I liked best about OracleClient is it's ability to work with Oracle Instant Client. Now as many of you know Oracle instant client is just a collection of dll's with very lenient licensing terms. All you need to do is put these files in either application directory or any other directory in system path and viola no more bulky Oracle Client installations.

Getting rid of tnsnames.ora

For instant client this is very simple; just set DataSource in connection string to -

server.domain.com:1521/ORACLE_SERVICE_NAME

For Oracle 9, you have to use a longer form to get rid of tnsnames.ora

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=server.domain.com)(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=ORACLE_SERVICE_NAME)))

 Rolling out a TableAdapter

.Net framework does not have a base class for TableAdapter and the adapters generated by DataSet designer for Oracle are terrible. What's more, the stored procedures and functions are not supported and code generated for INSERT, DELETE and UPDATE by analyzing SELECT never seems to be satisfactory (to me at least). So lets roll up our sieves and code a TableAdapter base class.

Imports System.Data.OracleClient

Public MustInherit Class TableAdapter(Of Table As DataTable)
    Implements IDisposable

    Protected WithEvents Adapter As OracleDataAdapter

    Public Property Connection() As OracleConnection
        Get
            Return Me.Adapter.SelectCommand.Connection
        End Get
        Set(ByVal value As OracleConnection)
            With Me.Adapter
                If .SelectCommand.Connection IsNot value Then
                    .SelectCommand.Connection = value
                    If .InsertCommand IsNot Nothing Then .InsertCommand.Connection = value
                    If .UpdateCommand IsNot Nothing Then .UpdateCommand.Connection = value
                    If .DeleteCommand IsNot Nothing Then .DeleteCommand.Connection = value
                End If
            End With
        End Set
    End Property

    Public Sub New()
        MyBase.New()
        Me.Adapter = New OracleDataAdapter()
        Me.InitAdapter()
    End Sub

    Protected MustOverride Sub InitAdapter()

    Public Overloads Function Fill(ByVal dt As Table) As Integer
        If dt Is Nothing Then Throw New ArgumentNullException("dt")
        If Me.Adapter Is Nothing Then Throw New ObjectDisposedException(TypeName(Me))
        dt.Clear()
        Return Me.Adapter.Fill(dt)
    End Function
    Public Overloads Function Update(ByVal dt As Table) As Integer
        If dt Is Nothing Then Throw New ArgumentNullException("dt")
        If Me.Adapter Is Nothing Then Throw New ObjectDisposedException(TypeName(Me))
        Me.Adapter.Update(dt)
    End Function
    Protected Overridable Sub OnFillError(ByVal sender As Object, ByVal e As FillErrorEventArgs)

    End Sub
    Private Sub Adapter_FillError(ByVal sender As Object, ByVal e As FillErrorEventArgs) Handles Adapter.FillError
        Me.OnFillError(sender, e)
    End Sub
    Protected Overridable Sub OnRowUpdated(ByVal sender As Object, ByVal e As OracleRowUpdatedEventArgs)

    End Sub
    Private Sub Adapter_RowUpdated(ByVal sender As Object, ByVal e As OracleRowUpdatedEventArgs) Handles Adapter.RowUpdated
        Me.OnRowUpdated(sender, e)
    End Sub
    Protected Overridable Sub OnRowUpdating(ByVal sender As Object, ByVal e As OracleRowUpdatingEventArgs)

    End Sub
    Private Sub Adapter_RowUpdating(ByVal sender As Object, ByVal e As OracleRowUpdatingEventArgs) Handles Adapter.RowUpdating
        Me.OnRowUpdating(sender, e)
    End Sub
    Public Sub Dispose() Implements IDisposable.Dispose
        If Me.Adapter IsNot Nothing Then
            Me.Adapter.Dispose()
            Me.Adapter = Nothing
        End If
        GC.SuppressFinalize(Me)
    End Sub
End Class

I am sorry for not commenting the code but it does almost nothing and is very simple to understand. So here is the summery of code -

• The subclass for this class must override the InitAdapter() function (how? we shall see shortly :).
  
• The events supplied by OracleDataAdapter are captured and are provided as overridable functions. I added it as it helps to diagnose problem with DataAdaptor during runtime.
  
• This class does NOT implements IComponent so cannot be placed on form.
  
• This class does not opens up a connection but requires user to do so.
  
• This class always clears the dataset before filling it.

Creating a DataSet for table

I have found that creating a dataset by hand in Dataset designer is better than letting Visual Studio generate it automatically from SELECT query. It hardly takes any time and I can experiment with names and their types. I am always irritated when VS assigns Decimal data type to primary ID columns WTF Int32/64 is more like what I had in mind </rant>. Anyway here is a class for the ubiquitous Employee table - 

Imports System.Data.OracleClient

Public Class EmployeeTableAdapter
    Inherits TableAdapter(Of EmployeeDataSet.EmployeeDataTable)

    Protected Overrides Sub InitAdapter()
        ' Create table mappings to associate columns in Oracle database to dataset
        Dim tableMapping As Common.DataTableMapping = New Common.DataTableMapping("Table", "Employee")
        tableMapping.ColumnMappings.Add("EMPLOYEEID", "Id")
        tableMapping.ColumnMappings.Add("EMPLOYEENAME", "Name")
        tableMapping.ColumnMappings.Add("DEPTNAME", "Department")
        Me.Adapter.TableMappings.Add(tableMapping)
        ' Selecting data from the table
        Me.Adapter.SelectCommand = New OracleCommand( _
            "SELECT EmployeeId, EmployeeName, DeptName" & vbCrLf & _
            "FROM   TblEmployee" _
        )
        ' Creating a new employee
        Me.Adapter.InsertCommand = New OracleCommand( _
            "INSERT INTO TblEmployee(EmployeeId, EmployeeName, DeptName)" & vbCrLf & _
            "VALUES (SeqEmployeeId.NEXTVAL, :EmpName, :EmpDept)" & vbCrLf & _
            "RETURNING EmployeeId INTO :EmpId" _
        )
        With Me.Adapter.InsertCommand.Parameters
            .Add(":EmpName", OracleType.VarChar, 0, "Name")
            .Add(":EmpDept", OracleType.VarChar, 0, "Department")
            ' Parameters having OracleType.Number cannot be used with ParameterDirection.Output
            ' due to some but in OracleClient. However setting direction to InputOutput
            ' achieves the same thing.
            .Add(":EmpId", OracleType.Number, 0, "Id").Direction = ParameterDirection.InputOutput
        End With
        ' Updating an employee by without concurrency checking
        Me.Adapter.UpdateCommand = New OracleCommand( _
            "UPDATE TblEmployee SET " & vbCrLf & _
            "       EmployeeName = :EmpName," & vbCrLf & _
            "       DeptName     = :EmpDept," & vbCrLf & _
            "WHERE  EmployeeId   = :EmpId" _
        )
        ' Required as we are not returning anything from database
        Me.Adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None
        With Me.Adapter.UpdateCommand.Parameters
            .Add(":EmpName", OracleType.VarChar, 0, "Name")
            .Add(":EmpDept", OracleType.VarChar, 0, "Department")
            .Add(":EmpId", OracleType.Number, 0, "Id").SourceVersion = DataRowVersion.Original
        End With
        ' Deleting an employee using stored procedure
        Me.Adapter.DeleteCommand = New OracleCommand("ProcDeleteEmployee")
        Me.Adapter.DeleteCommand.CommandType = CommandType.StoredProcedure
        Me.Adapter.DeleteCommand.Parameters.Add("EmployeeId", OracleType.Number, 0, "Id").SourceVersion = DataRowVersion.Original
    End Sub
End Class

Points to ponder -

• The table in database contains columns with names EmployeeId, EmployeeName & DeptName (hmm bad design ;) which are mapped to corresponding columns in our dataset.
  
• The Id column of dataset has AutoIncrement = True, AutoIncrementSeed = -1 and AutoIncrementStep = -1 which ensures that the rows inserted into local dataset to have unique id's. This is done in VS Dataset designer so it is not apparent in code.
  
• When rows are inserted in database, their actual Id is updated by using the RETURNING clause (AFAIK it is exclusive to Oracle).
  
• Writing queries and assigning parameters is very repetitive job so once you do it there is not much difference for other tables.
  
• Delete command uses a stored procedure which accepts EmployeeId. Using it is similar to using a SQL query. but you have to remember exact parameter names.
  
• Using this table adapter is similar to the table adapter generated by visual studio. Only additional this is you have to supply a connection to it.
  
• When in doubt, create a table adapter in VS designer and observe its code.