Oracle Transactions

When we have a web application with an open connection to a database, in this case Oracle 11G, we have to improve the calls to the db server, and keep the ACID rules (Atomicity, Consistence, Isolation, and Durability) of the database

We have the next scenario, a web form for sumbit a new customer of our book store and all his preferences marked

In our SQL Developer, this is the schema

and the code neccessary for accomplish this task is as follows

     
create or replace
PACKAGE PKG_BOOKSTORE AS
  TYPE CURSOR_ALL IS REF CURSOR;
  PROCEDURE P_SELECT_GENRES;
  PROCEDURE NEW_CUSTOMER;
  PROCEDURE NEW_PREFERENCE;
END;

-- 

create or replace
PROCEDURE P_SELECT_GENRES
  (RES OUT PKG_BOOKSTORE.CURSOR_ALL)
IS
BEGIN

  OPEN RES FOR SELECT IdGenre, Genre FROM T_GENRES;
  
  RETURN;

END P_SELECT_GENRES;

--

create or replace
PROCEDURE NEW_CUSTOMER
  (v_FirstName IN T_CUSTOMERS.FirstName%TYPE, 
   v_LastName IN T_CUSTOMERS.LastName%TYPE,
   v_IdCustomer OUT T_CUSTOMERS.IdCustomer%TYPE)
IS BEGIN

  SELECT sqCustomers.NextVal INTO v_IdCustomer FROM DUAL;
  
  INSERT INTO T_CUSTOMERS VALUES (v_IdCustomer, v_FirstName, v_LastName);
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
     RAISE_APPLICATION_ERROR(SQLCODE, 'Cannot insert duplicate value - ' + SQLERRM);
    WHEN OTHERS THEN
     RAISE_APPLICATION_ERROR(SQLCODE, SQLERRM);

END NEW_CUSTOMER;

-- 

create or replace
PROCEDURE NEW_PREFERENCE
  (v_IdCustomer IN T_CUSTOMERS.IdCustomer%TYPE, v_IdGenre IN T_GENRES.IdGenre%TYPE)
IS BEGIN

  INSERT INTO T_CUSTOMERS_GENRES VALUES (v_IdCustomer, v_IdGenre);

  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
     RAISE_APPLICATION_ERROR(SQLCODE, 'Cannot insert duplicate value - ' + SQLERRM);
    WHEN OTHERS THEN
     RAISE_APPLICATION_ERROR(SQLCODE, SQLERRM);
      
END NEW_PREFERENCE;

Now we include code to display all genres and get ready the form to submit a new customer

     
    private void displayGenres()
    {
        try
        {
            OracleDataReader drGenres = getGenres();

            while (drGenres.Read())
            {
                clbGenres.Items.Add(new ListItem() 
                  { Value = drGenres["IdGenre"].ToString(), 
                    Text = drGenres["Genre"].ToString() 
                  });
            }
        }
        catch (OracleException oex)
        {
            Response.Redirect("error.aspx?desc=" + oex.Message);
        }
        finally
        {
            if (connection.State == ConnectionState.Open)
                connection.Close();
        }
    }

    private OracleDataReader getGenres()
    {
        connection.Open();
        OracleCommand command = connection.CreateCommand();
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "P_SELECT_GENRES";
        OracleDataReader dr;
        OracleParameter pRes = new OracleParameter("RES", OracleType.Cursor);
        pRes.Direction = ParameterDirection.Output;

        command.Parameters.Add(pRes);

        dr = command.ExecuteReader(CommandBehavior.SingleResult);

        return dr;
    }

and we get this result set

The next step is adding the code to call the stored procs and stablish control over the transaction

     
    private void insertCustomer()
    {
        connection.Open();
        OracleCommand cmdCustomer = connection.CreateCommand();
        OracleTransaction trax_new_customer = 
           connection.BeginTransaction(IsolationLevel.Serializable);

        try
        {
            cmdCustomer.Transaction = trax_new_customer;
            cmdCustomer.CommandType = CommandType.StoredProcedure;
            cmdCustomer.CommandText = "NEW_CUSTOMER";

            cmdCustomer.Parameters.Add(new OracleParameter
               ("v_FirstName", txtFirstName.Text));
            cmdCustomer.Parameters.Add(new OracleParameter
               ("v_LastName", txtLastName.Text));

            OracleParameter v_IdCustomer = new OracleParameter
               ("v_IdCustomer", OracleType.Int32);
            v_IdCustomer.Direction = ParameterDirection.Output;

            cmdCustomer.Parameters.Add(v_IdCustomer);

            cmdCustomer.ExecuteNonQuery();

            int IdCustomer = (int)v_IdCustomer.Value;                

            foreach (ListItem genre in clbGenres.Items)
            {
                if (genre.Selected)
                {
                    OracleCommand cmdPreference = connection.CreateCommand();

                    cmdPreference.Transaction = trax_new_customer;
                    cmdPreference.CommandType = CommandType.StoredProcedure;
                    cmdPreference.CommandText = "NEW_PREFERENCE";

                    cmdPreference.Parameters.Add(new OracleParameter
                      ("v_IdCustomer", IdCustomer));
                    cmdPreference.Parameters.Add(new OracleParameter
                      ("v_IdGenre", genre.Value));

                    cmdPreference.ExecuteNonQuery();
                }
            }

            trax_new_customer.Commit();
        }
        catch (Exception ex)
        {
            trax_new_customer.Rollback();
            Response.Redirect("error.aspx?desc=" + ex.Message);
        }
    }

The .NET Framework Data Provider for Oracle only supports ReadCommitted and Serializable isolation levels

The result with a correct execution is the following

<METHOD SOFTWARE © 2012>