Simple Update in Oracle Table Using Transaction from ASP.NET

 

First Create Simple Update Procedure in your oracle databae

------------------------------------------------------------------

create or replace

PROCEDURE SP_UPD_MYPROC(myid Nvarchar2, mytime TimeStamp) AS BEGIN

/* UPDATE */

Update mytable s set my_datetimefield =mytime where s.id = id; commit; END SP_UPD_MYPROC;

This is how you will call from your asp.net application using transaction

-------------------------------------------------------------------------------------------------------

Make sure you imports these libraries

Imports Oracle.DataAccess.Client

Imports Oracle.DataAccess.Types

Public Function UpdateMyTable(ByVal myId as string)

Dim txn As OracleTransaction = Nothing

Try

Using conn As OracleConnection = New

OracleConnection(My.Settings.sConnectionString)

conn.Open()

txn = conn.BeginTransaction()

Dim cmdTyProcessTbl As OracleCommand = New OracleCommand("", conn)

cmdTyProcessTbl.CommandText = "SP_UPD_MYPROC"

cmdTyProcessTbl.CommandType = Data.CommandType.StoredProcedure

Dim prm As OracleParameter = New OracleParameter("a", OracleDbType.NVarchar2)

prm.Direction = Data.ParameterDirection.Input

prm.Value = myId

cmdTyProcessTbl.Parameters.Add(prm)

prm = New OracleParameter("b", OracleDbType.TimeStamp)

prm.Direction = Data.ParameterDirection.Input

prm.Value = Now()

cmdTyProcessTbl.Parameters.Add(prm)

cmdTyProcessTbl.ExecuteNonQuery()

cmdTyProcessTbl.Dispose()

txn.Commit()

conn.Close()

conn.Dispose()

'file.Dispose()

txn.Dispose()

GC.Collect()

End Using

Catch ex As Exception

Throw ex.Message.ToString

Finally

GC.Collect()

End Try

End Function

That’s All !