Insert into 3 tables based on the sequence number from 1st insert in C#

I have 3 tables in Oracle database. From my asp.net C# page, I am inserting records into all开发者_运维百科 three tables as shown below:

INSERT INTO contactMaster
   (contactID, FName, MName, LName) 
VALUES
   (contactID.NextVal, 'John', 'G', 'Garnet')

INSERT INTO contactPhone
  (contactPhoneID, contactID, contactType, phonenum)     
VALUES
  (contactPhoneID.NextVal, 1, 2, 1234567890)

INSERT INTO contactAddress
  (contactAddressID, contactID, addressType, PHN, Street, City)
VALUES
  (contactAddressID.NextVal, 1, 1, 287, 'Blooper St', 'New Yor')

My question is, how do I make sure that either all the above are executed or none is executed in C#.

If the first 2nd or 3rd insert fails, everything should fail.

---

Use a SQL transaction to ensure atomicity:

public void RunOracleTransaction(string connectionString) {
  using (OracleConnection connection = new OracleConnection(connectionString))
  {
    connection.Open();

    OracleCommand command = connection.CreateCommand();
    OracleTransaction transaction;

    // Start a local transaction
    transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
    // Assign transaction object for a pending local transaction
    command.Transaction = transaction;

    try
    {
        command.CommandText = 
            "INSERT INTO contactMaster
               (contactID, FName, MName, LName) 
             VALUES
               (contactID.NextVal, 'John', 'G', 'Garnet')";
        command.ExecuteNonQuery();

        command.CommandText = 
            "INSERT INTO contactPhone
               (contactPhoneID, contactID, contactType, phonenum)     
             VALUES
               (contactPhoneID.NextVal, 1, 2, 1234567890)";
        command.ExecuteNonQuery();

        command.CommandText = 
            "INSERT INTO contactAddress
               (contactAddressID, contactID, addressType, PHN, Street, City)
             VALUES
               (contactAddressID.NextVal, 1, 1, 287, 'Blooper St', 'New Yor')";
        command.ExecuteNonQuery();

        transaction.Commit();
        Console.WriteLine("Both records are written to database.");
    }
    catch (Exception e)
    {
        transaction.Rollback();
        Console.WriteLine(e.ToString());
        Console.WriteLine("Neither record was written to database.");
    }
  }
}

C#: OracleTransaction

Alternative

The insert statements could be moved to a stored procedure (preferably within a package), so only a single query be made from C#.

---

Just another idea - you can do the inserts using one INSERT statement, e.g.:

INSERT ALL
INTO contactMaster
   (contactID, FName, MName, LName) 
VALUES
   (contactID, FName, MName, LName)
INTO contactPhone
  (contactPhoneID, contactID, contactType, phonenum)     
VALUES
  (contactPhoneID.NextVal, contactID, contactType, phonenum)
INTO contactAddress
  (contactAddressID, contactID, addressType, PHN, Street, City)
VALUES
  (contactAddressID.NextVal, contactID, addressType, PHN, Street, City)
(SELECT contactID.NextVal AS contactID,
        'John' AS FName,
        'G' AS MName,
        'Garnet' AS LName,
        2 AS contactType,
        1234567890 AS phonenum,
        1 AS addressType,
        287 AS PHN,
        'Blooper St' AS Street,
        'New Yor' AS City
 FROM   dual)

By the way, it looks like your original inserts suffer from the problem that contactID is always 1, regardless of what the sequence generated for contactMaster.contactID. If you want to stay with separate INSERTs for each table, you can get the most recently generated value by referring to contactID.CurrVal.

---

Before the first command, start a transaction:

start transaction

After the last command, commit the transaction:

commit transaction

You can send these to the database in one go, or using separate commands. Either way, either all or none of the inserts will be persisted.