使用存储过程的一个小例子

Public Overloads Sub Update(ByVal authorityArr As IList)
            log.Debug("Update a record in table TBL_M_AUTHORITY")

            Dim connection As OracleConnection = New OracleConnection(OraHelper.CONN_STRING_LOCAL)
            Dim command As OracleCommand = New OracleCommand(PLSQL_UPDATE, connection)
            command.CommandType = CommandType.StoredProcedure
            Dim txn As OracleTransaction

            Dim updateParms() As OracleParameter = {New OracleParameter(PARM_AUTH_CD, OracleDbType.Varchar2) _
                                                  , New OracleParameter(PARM_AUTH_NAME, OracleDbType.Varchar2) _
                                                  , New OracleParameter(PARM_SHORI_STAFF_CD, OracleDbType.Decimal) _
                                                  , New OracleParameter(PARM_VOID_FLG, OracleDbType.Varchar2) _
                                                  , New OracleParameter(PARM_DISP_ORDER, OracleDbType.Decimal)}
            Dim authInfo As AuthorityInfo

            Try
                connection.Open()

                txn = connection.BeginTransaction(IsolationLevel.ReadCommitted)

                For Each authInfo In authorityArr
                    updateParms(0).Value = authInfo.auth_cd
                    updateParms(1).Value = authInfo.auth_name
                    updateParms(2).Value = authInfo.shori_staff_cd
                    updateParms(3).Value = authInfo.void_flg
                    If authInfo.disp_order_str Is Nothing Or authInfo.disp_order_str.Trim = "" Then
                        updateParms(4).Value = DBNull.Value
                    Else
                        updateParms(4).Value = Decimal.Parse(authInfo.disp_order_str)
                    End If

                    Dim params As OracleParameter
                    command.Parameters.Clear()

                    For Each params In updateParms
                        command.Parameters.Add(params)
                    Next
                    command.ExecuteNonQuery()
                Next
                txn.Commit()
            Catch ex As Exception
                txn.Rollback()
                log.Debug(ex.Message)
                log.Debug(ex.StackTrace)
                Throw ex
            Finally
                If Not connection Is Nothing Then
                    connection.Close()
                    connection.Dispose()
                End If
                If Not command Is Nothing Then
                    command.Dispose()
                End If
            End Try
        End Sub

本文地址：http://com.8s8s.com/it/it41555.htm