How to execute a transaction within a Command

3

I am making an application that will register an order and for that I need to use the transaction sentences. I have developed an attempt that I will attach below and the following error:

  

Unhandled exception of type 'System.InvalidOperationException' in   System.Data.dll

     

Additional information: ExecuteNonQuery requires the command to have   a transaction when the connection assigned to it is in a   pending local transaction. The property has not been initialized   Transaction of the command.

I really have no idea how to solve it, because I'm new in this transaction. If anyone can help me I would be very grateful

CODE: Transaction Class

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data; using System.Data.SqlClient; using System.Data; using System.Data.SqlClient;

namespace CapaDatos { public class Transaccion { DataManager dataManager = new DataManager(); SqlDataReader leer; SqlCommand comando = new SqlCommand(); DataTable tabla = new DataTable();

    public void RegistrarPedido(string descripcion, int idCliente, string 
    idRepartidor, double montoTotal)
    {
        comando.Connection = dataManager.AbrirConexion();
        dataManager.BeginTransaction();
        comando.CommandText = "INSERT INTO PEDIDOSCLIENTES VALUES ('" +  
        descripcion + "','" + idCliente + "','" + idRepartidor + "','" + 
        montoTotal + "')";
        comando.ExecuteNonQuery();
        dataManager.Commit();
    } }

CODE: DataManager Class

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data; using System.Data.SqlClient;

namespace CapaDatos { public class DataManager { SqlConnection Conexion = new SqlConnection(@"Data Source=TOSHIBA\SQLEXPRESS;Initial Catalog=baseDeDatosPedidos;Integrated Security=True"); SqlTransaction Transaccion;

    public SqlConnection AbrirConexion()
    {
        if (Conexion.State == ConnectionState.Closed)
            Conexion.Open();
        return Conexion;
    }
    public SqlConnection CerrarConexion()
    {
        if (Conexion.State == ConnectionState.Open)
            Conexion.Close();
        return Conexion;
    }

    public void Commit()
    {
        if (Transaccion != null)
            Transaccion.Commit();
    }

    public void BeginTransaction()
    {
        if (Conexion.State == ConnectionState.Open)
            Transaccion = Conexion.BeginTransaction();
    }
    public void Rollback()
    {
        if (Transaccion != null)
            Transaccion.Rollback();
    }
} }
    
asked by Lucas 31.10.2018 в 14:04
source

1 answer

5

Transactions are a way to encapsulate actions based on data that must all be executed together. The most common case is that of bank accounts. When we take money from one account to put it in another, we want the two things to happen or none at all.

In that case, the querys are placed within a transaction, so that the database tries to do all of them and only records all the actions when it is sure to be able to do all of them.

For this, the DB blocks tables and records, so that it can not happen that the data changes in the middle.

The error you are receiving, says that your query is not associated with any transaction. Keep in mind that in a DB there can be lots of transactions happening at the same time.

You need, before executing your command, to pass the property SqlCommand.Transaction , which in your case would be:

comando.Transaction = DataManager.transaction

Keep in mind that the DataManager class needs to return that in some way (in your class, there is no way to get it).

Apart from that, as @ shop350 says in the comments, the whole idea of the transaction is that you can go back if there is an error, which you are not doing.

You should check if the execution of the query was not satisfactory, in some way, and if that happens, do a Rollback.

    
answered by 31.10.2018 в 14:55