Home / Questions / 32854
Accepted
sioesi
Asked: 2020-11-10 14:33:50 +0800 CST 2020-11-10 14:33:50 +0800 CST

Create Stored Procedure that fills a table

  • 772

I have a mobile application that calls different services that I have in c#con Wcf. The issue is that I have one of those services in which the user from the mobile sends an object called Medicion. This object has a list of CeldaKeyValUpload.

public class Medicion
{
    [DataMember]
    public string medicion_user_email { get; set; }
    [DataMember]
    public string medicion_user_nombre { get; set; }
    [DataMember]
    public string medicion_app_os { get; set; }
    [DataMember]
    public string medicion_app_version { get; set; }
    [DataMember]
    public string medicion_latitud { get; set; }
    [DataMember]
    public string medicion_longitud { get; set; }
    [DataMember]
    public string medicion_sala_id { get; set; }
    [DataMember]
    public string medicion_codigo_empresa { get; set; }
    [DataMember]
    public string medicion_codigo_cadena { get; set; }
    [DataMember]
    public string medicion_codigo_modulo { get; set; }
    [DataMember]
    public string medicion_categoria_codigo { get; set; }
    [DataMember]
    public List<CeldaKeyValUpload> celdas { get; set; }
}

Y

public class CeldaKeyValUpload
{
    [DataMember]
    public int celda_id { get; set; }
    [DataMember]
    public string celda_key_codigo { get; set; }
    [DataMember]
    public string celda_key_valor { get; set; }
}

The list that can reach me from the mobile can be very extensive, let's say an example of 200 records. In my database I have two tables one called MedicionEncabezadoand MedicionDetalle. Everything in the class is saved in the first table Medicionexcept the list CeldaKeyValUpload, since it is saved in MedicionDetalle.

The problem I have is that I put myself in the case that for whatever reason the mobile loses internet and cannot upload these 200 records. For this I have created temporary tables called TmpMedicionEncabezadoand TmpMedicionDetalle. When the device sends the data, it loads these temporary tables, and upon successful completion, the load fills my official tables with the data . This is why I need to create one Stored Procedurethat is in charge of passing the data from the temporary tables to the official tables. Why am I doing this? To not fill my table with garbage data.

The problem I have is that I don't know how to get the data from the temporary tables and save it to the official tables. I still don't know much about sql-server-2005.

c#

2 Answers

  1. I don't know how you send the data, but if you send it all together and in a single call to the web service, it should not do anything until the upload is complete, that is, if the connection is cut, the web method should not be triggered service. Put a breakpoint, do the test and tell us. I hope it helps. Cheers!

    • 3
  2. Best Answer

    Instead of temporary tables, use a transaction.

    Using a transaction allows you to make multiple changes to the data in your tables through multiple SQL statements, but the changes are applied to the database atomically. In other words, either all changes are applied to your database, or none are applied. But it is not possible that only part of the changes will be applied, which may cause data corruption. Indeed, the use of a transaction allows several operations to be carried out as if they had been carried out in one.

    For your changes to be part of a transaction, you must execute SqlConnection.BeginTransaction()to indicate where the operations that are part of the transaction start, and SqlTransaction.Commit()or SqlTransaction.Rollback()to indicate where it ends. Commitconfirms that you want all changes to be applied to the database at once, and Rollbackindicates that you want to discard all changes you've made since the transaction began.

    Here I leave you a model of how you can use a transaction to avoid corruption of your data in case of error. Try adding an artificial error that occurs in the middle of the transaction and you will see that you will not find partial garbage data in your database.

    using (var conn = new SqlConnection("..."))
{
    conn.Open();

    // transacción empieza aquí.
    using (var tx = conn.BeginTransaction())
    {
        bool exito = false;

        try
        {
            // digamos que tienes que insertar varias entidades
            // uno por uno en un ciclo por medio de un stored procedure.
            foreach (var entidad in listaDeEntidades)
            {
                using (var cmd = new SqlCommand(
                    "insertarEntidad",
                    conn,
                    tx))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add("@param1", SqlDbType.VarChar).Value = entidad.Propiedad;
                    // ...
                    // otros parámetros
                    // ...

                    // Aquí, el cambio se manda a la base de datos,
                    // pero queda en "stand by", por decirlo así,
                    // hasta que hagas el "commit" final más abajo
                    // después de completar todos los cambios.
                    cmd.ExecuteNonQuery();
                }
            }

            // Si alcanzamos este punto, quiere decir que no hubo ningún error
            // y que podemos hacer el commit (confirmacón final).
            // De lo contrario, hay que hacer un Rollback.
            exito = true;
        }
        finally
        {
            if (exito)
            {
                // Confirma todos los cambios hechos dentro de la transacción.
                // Es como si hubiéramos hecho todos los cambios de un solo.
                tx.Commit();
            }
            else
            {
                // Si exito == false, significa que surgió un error
                // durante la transacción. Rollback descarta todos los cambios
                // hechos durante la transacción hasta este punto.
                tx.Rollback();
            }
        }
    }
}
    • 3