Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / VB

General function for all DML operations using vb.net and oracle stored procedures

0.00/5 (No votes)
5 Apr 2010CPOL 1  
'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
#Region "DML OPERATIONS USING ORACLE STORED PROCEDURE"
    '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......................
    ''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, optional...oracle 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))
        Try
            conn.Open()
        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

        Try
            '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

        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("successful")
else
msgbox ("error")
            End If

License

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