Com And Hosting

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!

Leave a Reply

Your email address will not be published. Required fields are marked *