Help with a transaction with dapper

1

Hi, I'm doing a transaction using dapper, but the detail is that when the error is marked. I reverse the changes with the rollback, but when I try a second execution. it tells me that the conectionString is empty.

  using (var cn = VariablesGlobales.GlobalConnection) {
                cn.Open();
                using (var exec = cn.BeginTransaction()) {
                    try
                    {
                        if (data.Count != 0)
                        {
                            if (txtCot.Text.Length != 0 && cboProv.Text != "")
                            {
                                concurso.CotizacionID = Convert.ToInt32(txtCot.Text);
                                concurso.ProveedorID = Convert.ToInt32(cboProv.SelectedValue.ToString());
                                concurso.Fecha = DateTime.Now;

                                int idx = VariablesGlobales.GlobalConnection.ExecuteScalar<int>(@"INSERT INTO ConcursoCompra (CotizacionID, ProveedorID, Fecha) VALUES (@CotizacionID, @ProveedorID, @Fecha)
                                                                    select SCOPE_IDENTITY() as ID;", concurso, exec);
                                for (int i = 0; i < countFila; i++)
                                {
                                    if (dgvCot.Rows[i].Cells[5].Value != null && dgvCot.Rows[i].Cells[6].Value != null)
                                    {
                                        concurso.DetallesCompra.Add(new ConcursoCompraDetalles()
                                        {
                                            ConcursoID = idx,
                                            ProductoID = Convert.ToInt32(dgvCot.Rows[i].Cells[0].Value.ToString()),
                                            UnidadID = Convert.ToInt32(dgvCot.Rows[i].Cells[1].Value.ToString()),
                                            CantidadCotizada = Convert.ToDecimal(dgvCot.Rows[i].Cells[4].Value.ToString()),
                                            CantidadAEntregar = Convert.ToDecimal(dgvCot.Rows[i].Cells[5].Value.ToString()),
                                            PrecioCotizado = Convert.ToDecimal(dgvCot.Rows[i].Cells[6].Value.ToString())
                                        }
                                        );
                                    }
                                }
                                if (concurso.DetallesCompra.Count != 0)
                                {
                                    VariablesGlobales.GlobalConnection.Execute("INSERT INTO ConcursoCompraDetalles (ConcursoID, ProductoID, UnidadID, CantidadCotizada, CantidadAEntregar, PrecioCotizado) VALUES (@ConcursoID, @ProductoID, @UnidadID, @CantidadCotizada, @CantidadAEntregar, @PrecioCotizado)", concurso.DetallesCompra, exec);
                                    MessageBox.Show("Concurso NO. " + idx + " Agregado", "Auditorias", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                    cboProv.Text = "";
                                    txtCot.Text = "";
                                    reloadGrid();
                                    concurso.DetallesCompra.Clear();
                                }
                                exec.Commit();
                            }
                            else
                            {
                                MessageBox.Show("Primero se debe haber seleccionado correctamente\n todos los campos.", "Auditorias", MessageBoxButtons.OK, MessageBoxIcon.Error);
                                return;
                            }
                        }
                        else
                        {
                            MessageBox.Show("La cotización que ingresaste no existe.", "Auditorias", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Asegurate de haber ingresado una cantidad con el formato correcto en la columna de cantidad", "Auditorías ", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        exec.Rollback();

                    }
                }
            }

In the class of global variables this is found ..

namespace Auditorias
{
    public static class VariablesGlobales
    {
        private static System.Data.SqlClient.SqlConnection _GlobalConnection;
        public static String UsuarioNombre { get; set; }
        public static String EmpresaNombre { get; set; }
        public static String SucursalNombre { get; set; }
        public static int UsuarioID { get; set; }
        public static int EmpresaID { get; set; }
        public static int SucursalID { get; set; }
        public static int RolID { get; set; }
        public static string Rol { get; set; }
        public static System.Data.SqlClient.SqlConnection GlobalConnection
        {
            get
            {
                _GlobalConnection = _GlobalConnection ?? new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString);
                return _GlobalConnection;
            }

            set
            {
                _GlobalConnection = value;
            }
        }
    }
}

Apparently I deleted all my connection settings when I indicated the rollback. I hope someone helps me, thanks

    
asked by JuanL 31.07.2018 в 21:29
source

1 answer

1

Hello, the problem is in this line:

using (var cn = VariablesGlobales.GlobalConnection)

When the using block ends, the GlobalConnection object that you have referenced in VariablesGlobal is called the "Dispose" method (Here is explained link )

This is what is "erasing" the settings. Ideally, whenever you request "VariablesGlobal.GlobalConnection" always return a new instance instead of keeping it as a member variable.

return new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString);

Or if you always need the same object, do not use the "Using" block on the connection.

I hope you get the answer. Greetings

    
answered by 02.08.2018 / 15:51
source