In one of my recent project I had the situation where I wanted to update multiple Oracle database tables with one button click event. I do not want to update the second table if the update operation fails for the first table.
I have created an Oracle procedure with an output parameter so that user will be notified with number of records updated message. When creating a procedure make sure you grant execute privilege to the database user that you are using to manipulate.
CREATE OR REPLACE PROCEDURE RL_UPDATE_PROC (P_FAC_ID NUMBER,
P_TRAIL_ID NUMBER,
P_FAC_ASSET_DESC VARCHAR2,
P_FAC_ASSET_CONDITION VARCHAR2,
P_FAC_ASSET_STATUS VARCHAR2,
P_FAC_LOCATION VARCHAR2,
P_FAC_REPLACEMENT_COST NUMBER,
P_FAC_LONGITUDE_DEC NUMBER,
P_FAC_LATITUDE_DEC NUMBER,
P_FAC_UNIVERSAL_ACCESS VARCHAR2,
P_FAC_COMMENTS VARCHAR2,
P_FAC_LAST_UPDATED DATE,
P_FAC_UPDATE_BY VARCHAR2,
P_FAC_REVISED_COST NUMBER,
P_FAC_REASON_COST_REVISED NUMBER,
P_FACILITY_GROUP VARCHAR2,
P_TRAIL_LENGTH NUMBER,
P_TRAIL_WIDTH NUMBER,
P_TRAIL_TYPE VARCHAR2,
P_MARKERS VARCHAR2,
P_CLASSIFICATION VARCHAR2,
P_START_POINT VARCHAR2,
P_END_POINT VARCHAR2,
P_SURFACE VARCHAR2,
P_DESIGN VARCHAR2,
P_LATITUDE_START_POINT NUMBER,
P_LONGITUDE_START_POINT NUMBER,
P_LATITUDE_END_POINT NUMBER,
P_LONGITUDE_END_POINT NUMBER,
P_BICYCLE_CLASS VARCHAR2,
P_TRAILHEAD VARCHAR2,
P_INTERP_SIGNAGE VARCHAR2,
P_OVERNIGHT VARCHAR2,
P_LENGTH_T1 NUMBER,
P_LENGTH_T2 NUMBER,
P_LENGTH_T3 NUMBER,
P_TRK_NAME VARCHAR2,
P_INTERNAL_PATHS VARCHAR2,
rs OUT NUMBER)
IS
/******************************************************************************
This procedure is created to update rtt_facilities and rtt_trails table simultaneously.
Transaction will be roll backed if fail in one table.
Author: Mohammed Tajuddin, 15/08/2012
******************************************************************************/
i NUMBER;
BEGIN
-- UPDATE RTT_FACILITIES TABLE
IF p_fac_id IS NOT NULL
THEN
UPDATE RTT_FACILITIES
SET FAC_ASSET_DESC = P_FAC_ASSET_DESC,
FAC_ASSET_CONDITION = P_FAC_ASSET_CONDITION,
FAC_ASSET_STATUS = P_FAC_ASSET_STATUS,
FAC_LOCATION = P_FAC_LOCATION,
FAC_REPLACEMENT_COST = P_FAC_REPLACEMENT_COST,
FAC_LONGITUDE_DEC = P_FAC_LONGITUDE_DEC,
FAC_LATITUDE_DEC = P_FAC_LATITUDE_DEC,
FAC_UNIVERSAL_ACCESS = P_FAC_UNIVERSAL_ACCESS,
FAC_COMMENTS = P_FAC_COMMENTS,
FAC_LAST_UPDATED = P_FAC_LAST_UPDATED,
FAC_UPDATE_BY = P_FAC_UPDATE_BY,
FAC_REVISED_COST = P_FAC_REVISED_COST,
FAC_REASON_COST_REVISED = P_FAC_REASON_COST_REVISED,
FACILITY_GROUP = P_FACILITY_GROUP,
RL_SYNC = 'NO'
WHERE FAC_ID = P_FAC_ID AND RL_SYNC = 'YES';
-- count effected rows
i := SQL%ROWCOUNT;
END IF;
-- UPDATE RTT_TRAILS TABLE
IF i >= 1
THEN
UPDATE RTT_TRAILS
SET TRAIL_LENGTH = P_TRAIL_LENGTH,
TRAIL_WIDTH = P_TRAIL_WIDTH,
TRAIL_TYPE = P_TRAIL_TYPE,
MARKERS = P_MARKERS,
CLASSIFICATION = P_CLASSIFICATION,
START_POINT = P_START_POINT,
END_POINT = P_END_POINT,
SURFACE = P_SURFACE,
DESIGN = P_DESIGN,
LATITUDE_START_POINT = P_LATITUDE_START_POINT,
LONGITUDE_START_POINT = P_LONGITUDE_START_POINT,
LATITUDE_END_POINT = P_LATITUDE_END_POINT,
LONGITUDE_END_POINT = P_LONGITUDE_END_POINT,
BICYCLE_CLASS = P_BICYCLE_CLASS,
TRAILHEAD = P_TRAILHEAD,
INTERP_SIGNAGE = P_INTERP_SIGNAGE,
OVERNIGHT = P_OVERNIGHT,
LENGTH_T1 = P_LENGTH_T1,
LENGTH_T2 = P_LENGTH_T2,
LENGTH_T3 = P_LENGTH_T3,
TRK_NAME = P_TRK_NAME,
INTERNAL_PATHS = P_INTERNAL_PATHS
WHERE TRAIL_ID = P_TRAIL_ID;
rs := 1;
COMMIT;
ELSE
rs := 0;
ROLLBACK;
END IF;
END RL_UPDATE_PROC;
Now grant privilege like this –
grant execute on procedure_name to db_user;
Procedure is created, now let’s create the on click event for asp.net page. Before I start, I am implementing this on click event in viewing record details page like the image below.
I am using SQL datareader to read the data from SQL Express database then bind the variables with Oracle update command. This is what it looks like now –
Protected Sub btnPublish_Click(sender As Object, e As System.EventArgs)
'Connect to the database and bring back the image contents & MIME type for the specified picture
Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("ApplicationServices").ConnectionString)
Const ora As String = "SELECT * FROM rtt_trails WHERE trail_id = @trail_id"
Dim myCommand As New SqlCommand(ora, myConnection)
myCommand.Parameters.AddWithValue("@trail_id", Request.QueryString("tid"))
myConnection.Open()
Dim myReader As SqlDataReader = myCommand.ExecuteReader
If myReader.Read Then
'Connect to the database and insert a new record into Products
Using myOraConn As New OracleConnection(ConfigurationManager.ConnectionStrings("ratisDev").ConnectionString)
Try
Dim facCommand As New OracleCommand("ratis.RL_UPDATE_PROC")
facCommand.Connection = myOraConn
'command type
facCommand.CommandType = CommandType.StoredProcedure
facCommand.Parameters.Add("P_TRAIL_ID", OracleType.Number).Value = Convert.ToInt32(myReader("TRAIL_ID"))
facCommand.Parameters.Add("P_FAC_ID", OracleType.Number).Value = Convert.ToInt32(myReader("FAC_ID"))
facCommand.Parameters.Add("P_FAC_ASSET_DESC", OracleType.VarChar).Value = myReader("FAC_ASSET_DESC")
facCommand.Parameters.Add("P_FAC_ASSET_CONDITION", OracleType.VarChar).Value = myReader("FAC_ASSET_CONDITION")
facCommand.Parameters.Add("P_FAC_ASSET_STATUS", OracleType.VarChar).Value = myReader("FAC_ASSET_STATUS")
facCommand.Parameters.Add("P_FAC_LOCATION", OracleType.VarChar).Value = myReader("FAC_LOCATION").ToString()
facCommand.Parameters.Add("P_FAC_REPLACEMENT_COST", OracleType.Number).Value = myReader("FAC_REPLACEMENT_COST")
facCommand.Parameters.Add("P_FAC_LONGITUDE_DEC", OracleType.Number).Value = myReader("FAC_LONGITUDE_DEC")
facCommand.Parameters.Add("P_FAC_LATITUDE_DEC", OracleType.Number).Value = myReader("FAC_LATITUDE_DEC")
facCommand.Parameters.Add("P_FAC_UNIVERSAL_ACCESS", OracleType.VarChar).Value = myReader("FAC_UNIVERSAL_ACCESS")
facCommand.Parameters.Add("P_FAC_COMMENTS", OracleType.VarChar).Value = myReader("FAC_COMMENTS")
facCommand.Parameters.Add("P_FAC_LAST_UPDATED", OracleType.DateTime).Value = If(myReader("FAC_LAST_UPDATED"), DBNull.Value)
facCommand.Parameters.Add("P_FAC_UPDATE_BY", OracleType.VarChar).Value = myReader("FAC_UPDATE_BY")
facCommand.Parameters.Add("P_FAC_REVISED_COST", OracleType.Number).Value = myReader("FAC_REVISED_COST")
facCommand.Parameters.Add("P_FAC_REASON_COST_REVISED", OracleType.VarChar).Value = myReader("FAC_REASON_COST_REVISED")
facCommand.Parameters.Add("P_FACILITY_GROUP", OracleType.VarChar).Value = myReader("FACILITY_GROUP")
facCommand.Parameters.Add("P_TRAIL_LENGTH", OracleType.Number).Value = myReader("TRAIL_LENGTH")
facCommand.Parameters.Add("P_TRAIL_WIDTH", OracleType.Number).Value = myReader("TRAIL_WIDTH")
facCommand.Parameters.Add("P_TRAIL_TYPE", OracleType.VarChar).Value = myReader("TRAIL_TYPE")
facCommand.Parameters.Add("P_MARKERS", OracleType.VarChar).Value = myReader("MARKERS")
facCommand.Parameters.Add("P_CLASSIFICATION", OracleType.VarChar).Value = myReader("CLASSIFICATION")
facCommand.Parameters.Add("P_START_POINT", OracleType.VarChar).Value = myReader("START_POINT")
facCommand.Parameters.Add("P_END_POINT", OracleType.VarChar).Value = myReader("END_POINT")
facCommand.Parameters.Add("P_SURFACE", OracleType.VarChar).Value = myReader("SURFACE")
facCommand.Parameters.Add("P_DESIGN", OracleType.VarChar).Value = myReader("DESIGN")
facCommand.Parameters.Add("P_LATITUDE_START_POINT", OracleType.Number).Value = myReader("LATITUDE_START_POINT")
facCommand.Parameters.Add("P_LONGITUDE_START_POINT", OracleType.Number).Value = myReader("LONGITUDE_START_POINT")
facCommand.Parameters.Add("P_LATITUDE_END_POINT", OracleType.Number).Value = myReader("LATITUDE_END_POINT")
facCommand.Parameters.Add("P_LONGITUDE_END_POINT", OracleType.Number).Value = myReader("LONGITUDE_END_POINT")
facCommand.Parameters.Add("P_BICYCLE_CLASS", OracleType.VarChar).Value = myReader("BICYCLE_CLASS")
facCommand.Parameters.Add("P_TRAILHEAD", OracleType.VarChar).Value = myReader("TRAILHEAD")
facCommand.Parameters.Add("P_INTERP_SIGNAGE", OracleType.VarChar).Value = myReader("INTERP_SIGNAGE")
facCommand.Parameters.Add("P_OVERNIGHT", OracleType.VarChar).Value = myReader("OVERNIGHT")
facCommand.Parameters.Add("P_LENGTH_T1", OracleType.Number).Value = myReader("LENGTH_T1")
facCommand.Parameters.Add("P_LENGTH_T2", OracleType.Number).Value = myReader("LENGTH_T2")
facCommand.Parameters.Add("P_LENGTH_T3", OracleType.Number).Value = myReader("LENGTH_T3")
facCommand.Parameters.Add("P_TRK_NAME", OracleType.VarChar).Value = myReader("TRK_NAME")
facCommand.Parameters.Add("P_INTERNAL_PATHS", OracleType.VarChar).Value = myReader("INTERNAL_PATHS")
'get the output parameter
facCommand.Parameters.Add(New OracleParameter("rs", OracleType.Number)).Direction = ParameterDirection.Output
myOraConn.Open()
'execute insert command
facCommand.ExecuteNonQuery()
Dim count As Integer = 0
count = facCommand.Parameters("rs").Value
'log record if record is published successfully
If count > 0 Then
msg.InnerHtml = "This asset has been published to RecData"
msg.Attributes.Add("class", "sync")
ratislite.log_user("RTT_FACILITY_TRAILS", myReader("TRAIL_ID"), myReader("FAC_TYPE"), "Published: Updated trail and facility tables")
'change the sync flag to yes
dsUpdateSync.Update()
Else
msg.InnerHtml = "This asset cannot be published, either this record has been updated by another user or there must be some other issue/s."
msg.Attributes.Add("class", "error")
End If
'close connection
myOraConn.Close()
Catch ex As Exception
msg.InnerHtml = "Sorry the record cannot be published, please make sure you are connected to DEC network." 'ex.ToString()
msg.Attributes.Add("class", "error")
Finally
'finally bind the data again
FormView1.DataBind()
End Try
End Using
End If
myReader.Close()
myConnection.Close()
End Using
End Sub
In C# –
protected void btnPublish_Click(object sender, System.EventArgs e)
{
//Connect to the database and bring back the image contents & MIME type for the specified picture
using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings("ApplicationServices").ConnectionString)) {
const string ora = "SELECT * FROM rtt_trails WHERE trail_id = @trail_id";
SqlCommand myCommand = new SqlCommand(ora, myConnection);
myCommand.Parameters.AddWithValue("@trail_id", Request.QueryString("tid"));
myConnection.Open();
SqlDataReader myReader = myCommand.ExecuteReader;
if (myReader.Read) {
//Connect to the database and insert a new record into Products
using (OracleConnection myOraConn = new OracleConnection(ConfigurationManager.ConnectionStrings("ratisDev").ConnectionString)) {
try {
OracleCommand facCommand = new OracleCommand("ratis.RL_UPDATE_PROC");
facCommand.Connection = myOraConn;
//command type
facCommand.CommandType = CommandType.StoredProcedure;
facCommand.Parameters.Add("P_TRAIL_ID", OracleType.Number).Value = Convert.ToInt32(myReader("TRAIL_ID"));
facCommand.Parameters.Add("P_FAC_ID", OracleType.Number).Value = Convert.ToInt32(myReader("FAC_ID"));
facCommand.Parameters.Add("P_FAC_ASSET_DESC", OracleType.VarChar).Value = myReader("FAC_ASSET_DESC");
facCommand.Parameters.Add("P_FAC_ASSET_CONDITION", OracleType.VarChar).Value = myReader("FAC_ASSET_CONDITION");
facCommand.Parameters.Add("P_FAC_ASSET_STATUS", OracleType.VarChar).Value = myReader("FAC_ASSET_STATUS");
facCommand.Parameters.Add("P_FAC_LOCATION", OracleType.VarChar).Value = myReader("FAC_LOCATION").ToString();
facCommand.Parameters.Add("P_FAC_REPLACEMENT_COST", OracleType.Number).Value = myReader("FAC_REPLACEMENT_COST");
facCommand.Parameters.Add("P_FAC_LONGITUDE_DEC", OracleType.Number).Value = myReader("FAC_LONGITUDE_DEC");
facCommand.Parameters.Add("P_FAC_LATITUDE_DEC", OracleType.Number).Value = myReader("FAC_LATITUDE_DEC");
facCommand.Parameters.Add("P_FAC_UNIVERSAL_ACCESS", OracleType.VarChar).Value = myReader("FAC_UNIVERSAL_ACCESS");
facCommand.Parameters.Add("P_FAC_COMMENTS", OracleType.VarChar).Value = myReader("FAC_COMMENTS");
facCommand.Parameters.Add("P_FAC_LAST_UPDATED", OracleType.DateTime).Value = myReader("FAC_LAST_UPDATED") ?? DBNull.Value;
facCommand.Parameters.Add("P_FAC_UPDATE_BY", OracleType.VarChar).Value = myReader("FAC_UPDATE_BY");
facCommand.Parameters.Add("P_FAC_REVISED_COST", OracleType.Number).Value = myReader("FAC_REVISED_COST");
facCommand.Parameters.Add("P_FAC_REASON_COST_REVISED", OracleType.VarChar).Value = myReader("FAC_REASON_COST_REVISED");
facCommand.Parameters.Add("P_FACILITY_GROUP", OracleType.VarChar).Value = myReader("FACILITY_GROUP");
facCommand.Parameters.Add("P_TRAIL_LENGTH", OracleType.Number).Value = myReader("TRAIL_LENGTH");
facCommand.Parameters.Add("P_TRAIL_WIDTH", OracleType.Number).Value = myReader("TRAIL_WIDTH");
facCommand.Parameters.Add("P_TRAIL_TYPE", OracleType.VarChar).Value = myReader("TRAIL_TYPE");
facCommand.Parameters.Add("P_MARKERS", OracleType.VarChar).Value = myReader("MARKERS");
facCommand.Parameters.Add("P_CLASSIFICATION", OracleType.VarChar).Value = myReader("CLASSIFICATION");
facCommand.Parameters.Add("P_START_POINT", OracleType.VarChar).Value = myReader("START_POINT");
facCommand.Parameters.Add("P_END_POINT", OracleType.VarChar).Value = myReader("END_POINT");
facCommand.Parameters.Add("P_SURFACE", OracleType.VarChar).Value = myReader("SURFACE");
facCommand.Parameters.Add("P_DESIGN", OracleType.VarChar).Value = myReader("DESIGN");
facCommand.Parameters.Add("P_LATITUDE_START_POINT", OracleType.Number).Value = myReader("LATITUDE_START_POINT");
facCommand.Parameters.Add("P_LONGITUDE_START_POINT", OracleType.Number).Value = myReader("LONGITUDE_START_POINT");
facCommand.Parameters.Add("P_LATITUDE_END_POINT", OracleType.Number).Value = myReader("LATITUDE_END_POINT");
facCommand.Parameters.Add("P_LONGITUDE_END_POINT", OracleType.Number).Value = myReader("LONGITUDE_END_POINT");
facCommand.Parameters.Add("P_BICYCLE_CLASS", OracleType.VarChar).Value = myReader("BICYCLE_CLASS");
facCommand.Parameters.Add("P_TRAILHEAD", OracleType.VarChar).Value = myReader("TRAILHEAD");
facCommand.Parameters.Add("P_INTERP_SIGNAGE", OracleType.VarChar).Value = myReader("INTERP_SIGNAGE");
facCommand.Parameters.Add("P_OVERNIGHT", OracleType.VarChar).Value = myReader("OVERNIGHT");
facCommand.Parameters.Add("P_LENGTH_T1", OracleType.Number).Value = myReader("LENGTH_T1");
facCommand.Parameters.Add("P_LENGTH_T2", OracleType.Number).Value = myReader("LENGTH_T2");
facCommand.Parameters.Add("P_LENGTH_T3", OracleType.Number).Value = myReader("LENGTH_T3");
facCommand.Parameters.Add("P_TRK_NAME", OracleType.VarChar).Value = myReader("TRK_NAME");
facCommand.Parameters.Add("P_INTERNAL_PATHS", OracleType.VarChar).Value = myReader("INTERNAL_PATHS");
//get the output parameter
facCommand.Parameters.Add(new OracleParameter("rs", OracleType.Number)).Direction = ParameterDirection.Output;
myOraConn.Open();
//execute insert command
facCommand.ExecuteNonQuery();
int count = 0;
count = facCommand.Parameters("rs").Value;
//log record if record is published successfully
if (count > 0) {
msg.InnerHtml = "This asset has been published to RecData";
msg.Attributes.Add("class", "sync");
ratislite.log_user("RTT_FACILITY_TRAILS", myReader("TRAIL_ID"), myReader("FAC_TYPE"), "Published: Updated trail and facility tables");
//change the sync flag to yes
dsUpdateSync.Update();
} else {
msg.InnerHtml = "This asset cannot be published, either this record has been updated by another user or there must be some other issue/s.";
msg.Attributes.Add("class", "error");
}
//close connection
myOraConn.Close();
} catch (Exception ex) {
msg.InnerHtml = "Sorry the record cannot be published, please make sure you are connected to DEC network.";
//ex.ToString()
msg.Attributes.Add("class", "error");
} finally {
//finally bind the data again
FormView1.DataBind();
}
}
}
myReader.Close();
myConnection.Close();
}
}
You might notice, I have used custom class to include some other functionality e.g. logging user details etc.
Hope this will help someone!