Insert data to sql server only if the 3 "insert" were correct

Question

Insert data to sql server only if the 3 "insert" were correct

Navigation

#1 by (2 votes)

2

Good afternoon, I am currently filling in 3 tables in my database, so when the data is obtained they are entered in the first table, I get the primary key of the first insert and then fill it in the following two, I tried my code and everything works perfect My question is how can I do so that the records are only inserted if the 3 inserts were successfully carried out, that is, if the first had an error that does not continue with the other 2 insert or vice versa if everything was inserted correctly in the first table but the insert of table 3 failed to insert the data of the first 2 tables since it would have flaws in my information given such relations between them (outsiders). Is there a way to do it? I share my code in the same way, greetings.

public static bool crearPlanificacion(Planificacion recibo)
{
    var query = "INSERT INTO ActividadPlanificada (idActividadGenerica, idSucursalEmpresa,anio, mes,descripcionActividadPlanificada) VALUES" +
        "(@idActividadGenerica,@idSucursalEmpresa,@anio, @mes,@descripcionActividadPlanificada);SELECT SCOPE_IDENTITY(); ";

    var query2 = "INSERT INTO UsuarioEstadoActividadPlanificada (idUsuario, idActividadPlanificada,idEstado) VALUES" +
        "(@idUsuario,@idActividadPlanificada,@idEstado)";

    var query3 = "INSERT INTO UsuarioActividadPlanificada (idUsuario, idActividadPlanificada,fechaAsignacionResponsable) VALUES" +
        "(@idUsuario,@idActividadPlanificada,@fechaAsignacionResponsable)";

    String fechaAsignacionResponsable = DateTime.Now.ToString("dd-M-yyyy HH:mm");

    SqlConnection connection = new SqlConnection(conexionString);

    try
    {
        connection.Open();
        //TANTAS VECES COMO idActividadGenerica VENGAN DESDE EL CLIENTE
        for (int i = 0; i < recibo.planificacion.Count ; i++)
        {
            //Tabla ActividadPlanificada
            SqlCommand command = new SqlCommand(query, connection);
            command.Parameters.Add(new SqlParameter("@idActividadGenerica", SqlDbType.Int)).Value = recibo.planificacion[i].idActividadGenerica; //desde array json
            command.Parameters.Add(new SqlParameter("@idSucursalEmpresa", SqlDbType.Int)).Value = recibo.idSucursalEmpresa;
            command.Parameters.Add(new SqlParameter("@anio", SqlDbType.Int)).Value = recibo.anio;
            command.Parameters.Add(new SqlParameter("@mes", SqlDbType.Int)).Value = recibo.mes;
            command.Parameters.Add(new SqlParameter("@descripcionActividadPlanificada", SqlDbType.Text)).Value = recibo.descripcionActividadPlanificada;

            //Ejecutamos la consulta y obtenemos la id de la actividadPlanificada para insertarla en las otras tablas
            Int32 pkActividadPlanificada = Convert.ToInt32(command.ExecuteScalar());

            //Tabla UsuarioEstadoActividadPlanificada
            SqlCommand command2 = new SqlCommand(query2, connection);
            command2.Parameters.Add(new SqlParameter("@idUsuario", SqlDbType.Int)).Value = recibo.idUsuarioResponsable;
            command2.Parameters.Add(new SqlParameter("@idActividadPlanificada", SqlDbType.Int)).Value = pkActividadPlanificada;
            command2.Parameters.Add(new SqlParameter("@idEstado", SqlDbType.Int)).Value = 1; //Por defecto empiezan con estado Pendiente
            command2.ExecuteNonQuery();

            //Tabla UsuarioActividadPlanificada
            SqlCommand command3 = new SqlCommand(query3, connection);
            command3.Parameters.Add(new SqlParameter("@idUsuario", SqlDbType.Int)).Value = recibo.idUsuarioResponsable;
            command3.Parameters.Add(new SqlParameter("@idActividadPlanificada", SqlDbType.Int)).Value = pkActividadPlanificada;
            command3.Parameters.Add(new SqlParameter("@fechaAsignacionResponsable", SqlDbType.DateTime)).Value = fechaAsignacionResponsable;
            command3.ExecuteNonQuery();

        }                

        connection.Close();                
        return true;                
    }
    catch (Exception)
    {
        return false;
    }
}

c# transacciones ado.net

asked by Kako 09.11.2017 в 20:55

source

1 answer

Normalize database up to the Third Normal Form Difference between classes of type Any and T in Kotlin?

score 2 · Accepted Answer

That is achieved with transactions. This allows several operations to be performed in an atomic manner.

The idea is that you make a connection.BeginTransaction() before starting the inserts. And just in case the 3 work, then you do commit() . If the commit() is not executed, all changes are discarded.

using(var connection = new SqlConnection(conexionString))
{
    connection.Open();

    using(var tx = connection.BeginTransaction())
    {
        // haces tus inserts aquí.

        // si llega hasta aquí sin error, entonces hacemos el commit
        tx.Commit();
    }
}

The other detail is that every time you create the SqlCommand , you must pass your transaction as a parameter in addition to the connection.

So instead of:

SqlCommand command = new SqlCommand(query, connection);

... you must create them this way:

SqlCommand command = new SqlCommand(query, connection, tx);

Regarding your question in the comments on handling errors, I am convinced that the pattern of ignoring the exception and then returning a true or false nothing more, is not good practice.

In my opinion, most of the code should not even have a try-catch . And only in a very central place, well up in the call stack, where you have the opportunity to communicate something to the user, there you can catch the error, and let the user know that something unusual happened, and include the details of the error for be able to diagnose the problem.

In this case, I would simply define the crearPlanificacion method as a void without any try-catch :

public static void crearPlanificacion(Planificacion recibo)
{
    // ...
}

And in the place where you need to tell the user if there was success or not, there you handle the error. An example:

try
{
    Planificacion recibo = //...algún código...
    crearPlanificacion(recibo);

    mostrarAlUsuario("Todo funcionó bien!!");
}
catch(Exception e)
{
    mostrarAlUsuario($"Algo falló. Detalles del error: {e}.");
}