General function for all DML operations using vb.net and oracle stored procedures
'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 classHow 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