'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