Transaction object

Problem
I had a situation where I had to add a record in the table, and at the same time I needed to add record in another table but I needed an Id of the first table to enter in the second second table. What must I do to achieve that.

Impact
Program will not do what it is suppose to do.

Solution
Solution to this problem is to use transaction object. Think of it this way, if we go to bank and transfer the money from one account to another, first bank has to take money out of the account and then transfer it in the other account, what happens if first transaction succeeds but the second one does't.  C# uses transaction object to solve this issue. When we use transaction object both of these transactions must succeed before we enter the record in the database, if one fails all other transactions will rollback. Here is the code that I wrote where I am entering record in the data base and retrieving the id of the record just been entered and then committing to the action.


public int Save(Customer customer)
        {
            int roweffected = 0;
            try
            {
                //first open the connection to the database
                _connection.Open();
                //begin the transaction.
                using(SqlTransaction transaction = _connection.BeginTransaction())
                {
                    //create a command object to enter a record in the database.
                    using (SqlCommand command = new SqlCommand())
                    {
                        command.CommandText = "Insert into Customer (FirstName,LastName,DateOfBirth,HouseNumber," +
                                              "Street,City,Country,PostCode,Phone,Email) Values(@firstname,@lastname," +
                                              "@dateofbirth,@housenumber,@street,@city,@country," +
                                              "@postcode,@phone,@email)";
                        command.Connection = _connection;
                        command.Transaction = transaction;
                        command.Parameters.AddWithValue("@firstname", customer.FirstName);
                        command.Parameters.AddWithValue("@lastname", customer.LastName);
                        command.Parameters.AddWithValue("@dateofbirth", customer.DateOfBirth);
                        command.Parameters.AddWithValue("@housenumber", customer.HouseNumber);
                        command.Parameters.AddWithValue("@street", customer.StreetName);
                        command.Parameters.AddWithValue("@city", customer.City);
                        command.Parameters.AddWithValue("@country", customer.Country);
                        command.Parameters.AddWithValue("@postcode", customer.PostCode);
                        command.Parameters.AddWithValue("@phone", customer.Phone);
                        command.Parameters.AddWithValue("@email", customer.Email);
                        roweffected = command.ExecuteNonQuery();
                    }
                    //Create another command object to retrieve the record id you just entered.
                    using (SqlCommand command = new SqlCommand())
                    {
                        //this query returns the id of the last record entered in the customer table.
                        command.CommandText = "Select @@Identity from Customer";
                        command.Connection = _connection;
                        command.Transaction = transaction;
                        var value = command.ExecuteScalar();
                    }
                    transaction.Commit();
                }
              
                return roweffected;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }