Click here to Skip to main content
13,353,322 members (48,651 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


4 bookmarked
Posted 4 Apr 2010

General function for all DML operations using and oracle stored procedures

, 5 Apr 2010
Rate this:
Please Sign up or sign in to vote.
'All business logic access to the database can make use of this 'function if one is using a stored procedure.'--------------------------------------------------------------Public class classDbAccess#Region "DML OPERATIONS USING ORACLE STORED PROCEDURE" 'This take the necessary...
'All business logic access to the database can make use of this 'function if one is using a stored procedure.

Public class classDbAccess
    'This take the necessary parameters and inserts into an oracle stored procedure
    'Parameters specified in the param array and dataTypes must correspond to the the stored procedure specified

    ''parameters()   :Array of storedProcedure's Parameters, 
    ''dataTypes()    :array of corresponding dataTypes to parameters arranged in exact order, 
    ''values()       : array of corresponding parameter values to assign to parameters arranged accordingly
    ''storedProcedure: stored procedure's name,
    ''commitdata     :[optional]...commit Transaction(boolean)?...default=TRUE=YES, 
    ''otran          : oracle transaction
    ''errorMsg       :Special Error message, transaction to use

    Public Function doDmlOperation(ByVal parameters() As String, ByVal dataTypes() As OracleType, ByVal values() As Object, _
       ByVal storedProcedure As String, Optional ByVal logIdentifier As String = "", Optional ByVal commitData As Boolean = True, Optional ByVal errorMsg As String = "", _
       Optional ByRef oraTran As System.Data.OracleClient.OracleTransaction = Nothing, Optional ByRef uniqueKeyError As Boolean = False, Optional ByVal displayDupErrMsg As Boolean = True) As Boolean
        Dim cnt As Integer = 0
        Dim conn As New OracleConnection(decryptedConnString)    'dsoftDecrypt(connString))
        Catch ex As Exception
            MessageBox.Show("Could not connect to database" & Chr(13) & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            logError("Could not connect to database: " & ex.Message)
        End Try

        oraTran = conn.BeginTransaction
        Dim cmd As New OracleCommand(storedProcedure, conn, oraTran)
        cmd.CommandType = CommandType.StoredProcedure

            'Loop until all parameters have been assigned  
            Dim item As Object
            While cnt < parameters.Length
                With cmd.Parameters
                    .Add(parameters(cnt), dataTypes(cnt)).Value = values(cnt)             'this does the job...(assigns parameters)
                End With
                item = values(cnt)   'Checking what value is being passed in
                cnt += 1
            End While
        Catch er As Exception
            MessageBox.Show(errorMsg & Chr(13) & "Error while assigning variables to parameters" & Chr(13) & er.Message)
            logError("insertORupdate: " & er.Message)
            Return False
        End Try

            cmd.ExecuteNonQuery()                                      'EXECUTING TASK...
             If commitData = True Then oraTran.Commit() : conn.Close() ''closing transaction and connection after transaction has been committed
            updateLog("insertORupdate: " & storedProcedure & ": " & logIdentifier)       'updating log after successful execution of task
            Return True
        Catch ex As Exception
            uniqueKeyError = checkOraclePKconstraint(ex.Message, storedProcedure, , displayDupErrMsg) 'checking if error was as a rsult of an oracle primary or unique key constraint
            If uniqueKeyError = True Then Return False
            MessageBox.Show(errorMsg & Chr(13) & "An unexpected error occured while executing command" _
            & Chr(13) & ex.Message, "Notification", MessageBoxButtons.OK, MessageBoxIcon.Warning)       'message notification
            logError(errorMsg & ": insertORupdate: " & storedProcedure & ": " & logIdentifier & ex.Message)                                     'logging error
            oraTran.Rollback() : conn.Close()
            Return False
        End Try
    End Function

#End Region
end class

How to call function
  Dim dba As New classDbAccess

            Dim myParam() As String = {"param1","param2","param3","param4"}
            Dim myDtype() As OracleType = {22, 13, 13, 13}
            Dim myObj() As Object = {val1, val2, val3, val4}

            If dba.doDmlOperation(myParam, myDtype, myObj, "storedProcedureName", "logString") = True Then
msgbox ("error")
            End If


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer Bsystems Limited
Ghana Ghana
This member doesn't quite have enough reputation to be able to display their biography and homepage.

You may also be interested in...


Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.180111.1 | Last Updated 5 Apr 2010
Article Copyright 2010 by nan49
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid