TRANSACTION - How to correct a shipping error?

Question

I think you can understand what I’m trying to do there: This method will make a foreach for each item in my movement list, and send each as a parameter to the method that will save them in the BD.

I need to use a Transaction to ensure that either all such moves are sent, or none, to ensure the consistency of the information.

I forced the execution of that method without the internet, to enter the catch, and I found a problem on the line transaction.Rollback();:

Additional information: An error in the transport level occurred to send the request to the server. (Provider: TCP Provider, error: 0 - Forced cancellation of an existing connection by the remote host.)

Thank you very much for the charitable soul that has the time and willingness to help this beginner :)

What am I doing wrong?

public static void SalvaNotaFiscalXML(IList<MovimentacaoDTO> movimentacoesNotaFiscal)
        {
            using (SqlConnection cnx = new SqlConnection(Properties.Settings.Default.CS1))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cnx.Open();
                    SqlTransaction transaction;
                    transaction = cnx.BeginTransaction("MovimentacaoDeEstoque");
                    try
                    {
                        cmd.Connection = cnx;
                        cmd.Transaction = transaction;


                        foreach (var item in movimentacoesNotaFiscal)
                        {
                            MovimentacaoBLL.NewMovimentacao(item);
                        }

                        transaction.Commit();
                        cnx.Close();
                        return;
                    }
                    catch (SqlException ex2)
                    {
                            transaction.Rollback();
                            cnx.Close();
                            throw ex2;
                    }
                }


            }
        }

Answer 1

The Rollback will occur automatically if the connection to the database drops. That is, before running the rollback check if the connection is open, doing so, if the network crash, Rollback will occur automatically, but if it occurs another type of error, as invalid data, you will need to run the rollback.

          catch (SqlException ex2)
                {
                        if (cnx.State == System.Data.ConnectionState.Open)
                        {
                           transaction.Rollback();
                           cnx.Close();
                        }
                        throw ex2;
                }