Click here to Skip to main content
Click here to Skip to main content

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

By , 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 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)

About the Author

nan49
Software Developer Bsystems Limited
Ghana Ghana
Hi
The name is Nene. Age 24 @(2008)
I'm ur average programmer(ORACLE, VB.NET,ASP.NET, XHTML, css)
 
...Will actually call my self a beginner(...at least, enough to get employed by a software company as a developer).
 
I'm a Ghanaian. ...wld luv to share ideas with other developers outside my country.
 
I will be glad to receive criticisms to my codes. Would also be happy to know if i helped solve a problem somewhere. Wouldn't mind a personal contact(...nan492001@yahoo.com).

Comments and Discussions

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