使用存储过程的一个小例子
使用存储过程的一个小例子,使用存储过程的一个小例子
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