C# Oracle - Execute Stored Procedures in sequence

Question

Hi guys I'm having troubles trying execute Stored procedures on cascade, I need some help. Let's see the scenario:

I have a father table, let's call it "REQUEST" and a child table, "REQUEST_DETAILS"

Now from C# I know how to execute an Oracle SP, but I don´t know how to execute two in a chain, without commit until the end of all.

I need to insert the father table data and after get the generated REQUEST.ID to start to insert the children data with the REQUEST.ID

So the first stored procedure will insert the REQUEST data and the second one will insertrt the REQUEST_DETAIL data but if something goes wrong I want to rollback all the transactions.

There is a way to do this of a simple way ?

Here is my code any help will be usefull.

public Bool SaveRequest(Request newRequestData)
{
    var connection = new connection();
    bool isSuccess = true;

    OracleConnection Conn = connection._GetInstance();
    OracleCommand Cmd = new OracleCommand();
    Conn.Open();
    Cmd.Connection = Conn;
    Cmd.CommandType = CommandType.StoredProcedure;
    Cmd.CommandText = "PackageRequests.InsertNewRequest";
    Cmd.BindByName = true;

    //IN PARAM
    Cmd.Parameters.Add(new OracleParameter("P_LOCATION", OracleDbType.Varchar2, newRequestData.location, ParameterDirection.Input));
    Cmd.Parameters.Add(new OracleParameter("P_PCSTOTAL", OracleDbType.Int32, newRequestData.pcsTotal, ParameterDirection.Input));
    Cmd.Parameters.Add(new OracleParameter("P_STATUS", OracleDbType.Int32, newRequestData.status, ParameterDirection.Input));

    //Out Param
    Cmd.Parameters.Add(new OracleParameter("P_NEW_ID", OracleDbType.Int32)).Direction = ParameterDirection.Output;

    OracleTransaction transaction = Conn.BeginTransaction(IsolationLevel.ReadCommitted);
    try
    {
        Cmd.ExecuteNonQuery();
        //New request_id
        string newId = Convert.ToString(Cmd.Parameters["P_NEW_ID"].Value);
        //Here I think goes the logic for execute the another procedure that will insert the data into REQUEST_DETAIL
        /***
            foreach(var item in newRequestData.List)
            {
                //Insert request_detail_Data()
            }
        ***/

        //after all  -- transaction.Commit();
    }
    catch (OracleException ex)
    {   
        //If something goes wrong rollback.
        transaction.Rollback();
        isSuccess = false;
    }
    finally
    {
        Conn.Close();
    }
    return isSuccess;
}

Answer 1

I found the solution to do this the trick is on pass the Oracle Connection like parameter to the another functions and when all is done commit and if something fails rollback, from the initial function. I leave an example I hope will be helpful for someone.

public Bool SaveRequest(Request newRequestData)
{
    var connection = new connection();
    bool isSuccess = true;

    OracleConnection Conn = connection._GetInstance();
    OracleCommand Cmd = new OracleCommand();
    Conn.Open();
    Cmd.Connection = Conn;
    Cmd.CommandType = CommandType.StoredProcedure;
    Cmd.CommandText = "PackageRequests.InsertNewRequest";
    Cmd.BindByName = true;

    // IN PARAMETERS...
    Cmd.Parameters.Add(new OracleParameter("P_LOCATION", OracleDbType.Varchar2, newRequestData.location, ParameterDirection.Input));

    // OUT PARAMETER (Here I recover the master table ID)
    Cmd.Parameters.Add(new OracleParameter("P_NEW_ID", OracleDbType.Int32)).Direction = ParameterDirection.Output;

    // Initialize the Transaction 
    OracleTransaction transaction = Conn.BeginTransaction(IsolationLevel.ReadCommitted);
    try
    {
        //Execute the first SP
        Cmd.ExecuteNonQuery();
        string newId = Convert.ToString(Cmd.Parameters["P_NEW_ID"].Value);

        // Calls another function and pass Oracle Connection, and master table ID like parameters
        InsertRequestDetail(Conn, newId);

        transaction.Commit();
    }
    catch (OracleException ex)
    {   
        //If something goes wrong rollback.
        transaction.Rollback();
        isSuccess = false;
    }
    finally
    {
        Conn.Close();
    }
    return isSuccess;
}

private void InsertRequestDetail(OracleConnection Conn, string newId)
{

    OracleCommand Cmd = new OracleCommand();
    Cmd.Connection = Conn;
    Cmd.CommandType = CommandType.StoredProcedure;
    Cmd.CommandText = "MY_PACKAGE.AnotherSPName";
    Cmd.BindByName = true;

    //IN - OUT PARAMS
    Cmd.Parameters.Add(new OracleParameter("...

    Cmd.ExecuteNonQuery();
}