Obteniendo ID durante la consulta SQL y utilizando para otro Insertar Estatus

.net c# dapper sql-server transactions

Pregunta

Así que he estado creando una biblioteca que usa dapper y permite al usuario manipular una base de datos.

Necesito ayuda para encontrar la mejor manera de lograr lo siguiente.

Digamos que tengo una tabla de "orden" y tengo una tabla de "transacción" y una tabla "order_line". Quiero tomar el identificador de incremento de la "orden" de la tabla al insertarlo y usarlo para almacenarlo en una columna en la tabla "transaction" y "order_line" y quiero que todo esto se haga en una transacción SQL para que pueda retrotraer caso de cualquier problema.

Ahora que mi biblioteca es dinámica para cualquier tipo y acción, no estoy seguro de cómo abordar algo como esto.

Aquí está el código sobre cómo insertar: Tengo 2 variables globales

  private string connectionString { get; set; }

        public void newConnection(string connection)
        {
            if (string.IsNullOrWhiteSpace(connectionString))
            {
                connectionString = connection;
            }
        }

        private List<KeyValuePair<string, object>> transactions = new List<KeyValuePair<string, object>>();

Así es como llama para que se guarde una clase en la base de datos:

public void Add(object item)
{
    string propertyNames = "";
    string propertyParamaters = "";
    Type itemType = item.GetType();

    System.Reflection.PropertyInfo[] properties = itemType.GetProperties();

    for (int I = 0; I < properties.Count(); I++)
    {
        if (properties[I].Name.Equals("Id", StringComparison.CurrentCultureIgnoreCase) || properties[I].Name.Equals("AutoId", StringComparison.CurrentCultureIgnoreCase))
        {
            continue;
        }

        if (I == properties.Count() - 1)
        {
            propertyNames += "[" + properties[I].Name + "]";
            propertyParamaters += "@" + properties[I].Name;
        }
        else
        {
            propertyNames += "[" + properties[I].Name + "],";
            propertyParamaters += "@" + properties[I].Name + ",";
        }
    }

    string itemName = itemType.Name;
    KeyValuePair<string, object> command = new KeyValuePair<string, object>($"Insert Into[{ itemName}] ({ propertyNames}) Values({ propertyParamaters})", item);
    transactions.Add(command);
}

Hay más métodos y como editar, eliminar, editar lista, eliminar lista, etc., pero no son relevantes en este caso.

Cuando desee realizar cambios en la base de datos llame:

public void SaveChanges()
        {
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
                using (SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())
                {
                    try
                    {
                        foreach (KeyValuePair<string, object> command in transactions)
                        {
                            sqlConnection.Execute(command.Key, command.Value, sqlTransaction);
                        }

                        sqlTransaction.Commit();
                    }
                    catch
                    {
                        sqlTransaction.Rollback();
                        throw;
                    }
                    finally
                    {
                        sqlConnection.Close();
                        transactions.Clear();
                    }
                }
                sqlConnection.Close();
            }

            transactions.Clear();
        }

Puedes encontrar mi biblioteca en github.com
https://github.com/pietercdevries/Bamboo.Net

Respuesta aceptada

¿Se puede hacer ... sí ... deberíamos tratar de hacer esto nosotros mismos ... No lo haría :) pero intentémoslo de cualquier manera.

Algunas ideas que pueden simplificar este código:

  • Defina interfaces de ayuda y fuerce las clases de datos para implementarlas o use declaraciones de atributos para especificar campos de identificación y referencias de claves externas
  • Investigue las técnicas de inyección o generación de código para que pueda obtener algo de esta codificación 'dinámica' y búsqueda ejecutada en tiempo de compilación, no en tiempo de ejecución.

No uso Dapper y su SqlConnection.Execute () es un método de extensión con el que no estoy familiarizado pero supongo que genera DbParameters a partir del objeto pasado y los aplica al SqlCommand cuando se ejecuta. Afortunadamente, dapper tiene algunas funciones para extraer los parámetros, de modo que se puedan usar en este ejemplo de código, o tal vez pueda usar algunos de estos conceptos y adaptarlos a su código apuesto. Solo quiero reconocer eso por adelantado y que he omitido aquí cualquier ejemplo de código que parametrice los objetos al ejecutar los comandos.

Este es el viaje por el que bajarán los siguientes fragmentos

  1. Prepare el SQL generado para capturar el campo Id
  2. Da salida al valor Id cuando guardamos los cambios
  3. Itera sobre todos los objetos restantes en la matriz y establece los valores de la clave externa

Nota: estos cambios de código no se prueban o la excepción se maneja para producción, ni llamaría a esta "mejor práctica" solo para probar el concepto y ayudar a un codificador compañero :)

  1. Ya ha ideado una convención para el seguimiento de campo de Id., Ampliemos esa idea preparando la instrucción sql para establecer el valor de un parámetro de salida:

    NOTA: en MS SQL, use SCOPE_IDENTITY () con preferencia a @@ Identity.
    ¿Cuál es la diferencia entre Scope_Identity (), Identity (), @@ Identity e Ident_Current?

    NOTA: debido a que las declaraciones generadas están usando parámetros, y aún no estamos leyendo los valores de los parámetros, no necesitaremos volver a generar las declaraciones SQL guardadas después de que hayamos encontrado un valor Id para insertar en otros objetos ... uf ...

    public void Add(object item)
    {
        List<string> propertyNames = new List<string>();
        Type itemType = item.GetType();
    
        System.Reflection.PropertyInfo[] properties = itemType.GetProperties();
    
        for (int I = 0; I < properties.Count(); I++)
        {
            if (properties[I].Name.Equals("Id", StringComparison.CurrentCultureIgnoreCase) || properties[I].Name.Equals("AutoId", StringComparison.CurrentCultureIgnoreCase))
            {
                continue;
            }
            propertyNames.Add(properties[I].Name);
        }
    
        string itemName = itemType.Name;
        KeyValuePair<string, object> command = new KeyValuePair<string, object>
            ($"Insert Into[{itemName}] ({String.Join(",", propertyNames.Select(p => $"[{p}]"))}) Values({String.Join(",", propertyNames.Select(p => $"@{p}"))}); SET @OutId = SCOPE_IDENTITY();", item);
        transactions.Add(command);
        // Simply append your statement with a set command on an @id parameter we will add in SaveChanges()
    }
    
  2. En Guardar cambios, implemente el parámetro de salida para capturar el Id creado, y si el ID fue capturado, guárdelo nuevamente en el objeto al que está asociado el comando.

    NOTA: este fragmento de código muestra las referencias a la solución en el ítem 3. Y el foreach fue reemplazado por a para así poder hacer iteraciones hacia adelante desde el índice actual

    public void SaveChanges()
    {
        using (SqlConnection sqlConnection = new SqlConnection(connectionString))
        {
            sqlConnection.Open();
            using (SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())
            {
                try
                {
                    for (int i = 0; i < transactions.Count; i++)
                    {
                        KeyValuePair<string, object> command = transactions[i];
                        // 1. Execute the command, but use an output parameter to capture the generated id
                        var cmd = sqlConnection.CreateCommand();
                        cmd.Transaction = sqlTransaction;
                        cmd.CommandText = command.Key;
                        SqlParameter p = new SqlParameter()
                        {
                            ParameterName = "@OutId",
                            Size = 4,
                            Direction = ParameterDirection.Output
                        };
                        cmd.Parameters.Add(p);
                        cmd.ExecuteNonQuery();
    
                        // Check if the value was set, non insert operations wil not set this parameter
                        // Could optimise by not preparing for the parameter at all if this is not an 
                        // insert operation.
                        if (p.Value != DBNull.Value)
                        {
                            int idOut = (int)p.Value;
    
                            // 2. Stuff the value of Id back into the Id field.
                            string foreignKeyName = null;
                            SetIdValue(command.Value, idOut, out foreignKeyName);
                            // 3. Update foreign keys, but only in commands that we haven't execcuted yet
                            UpdateForeignKeys(foreignKeyName, idOut, transactions.Skip(i + 1));
                        }
                    }
    
                    sqlTransaction.Commit();
                }
                catch
                {
                    sqlTransaction.Rollback();
                    throw;
                }
                finally
                {
                    sqlConnection.Close();
                    transactions.Clear();
                }
            }
            sqlConnection.Close();
        }
    
        transactions.Clear();
    }
    
    
    /// <summary>
    /// Update the Id field of the specified object with the provided value
    /// </summary>
    /// <param name="item">Object that we want to set the Id for</param>
    /// <param name="idValue">Value of the Id that we want to push into the item</param>
    /// <param name="foreignKeyName">Name of the expected foreign key fields</param>
    private void SetIdValue(object item, int idValue, out string foreignKeyName)
    {
        // NOTE: There are better ways of doing this, including using interfaces to define the key field expectations.
        // This logic is consistant with existing code so that you are familiar with the concepts
        Type itemType = item.GetType();
        foreignKeyName = null;
    
        System.Reflection.PropertyInfo[] properties = itemType.GetProperties();
    
        for (int I = 0; I < properties.Count(); I++)
        {
            if (properties[I].Name.Equals("Id", StringComparison.CurrentCultureIgnoreCase) || properties[I].Name.Equals("AutoId", StringComparison.CurrentCultureIgnoreCase))
            {
                properties[I].SetValue(item, idValue);
                foreignKeyName = $"{item.GetType().Name}_{properties[I].Name}";
                break;
            }
        }
    }
    
  3. Entonces ahora sus objetos tienen sus identificaciones actualizadas a medida que se insertan. Ahora, para la parte divertida ... Después de actualizar el Id, ahora debe iterar a través de los otros objetos y actualizar sus campos de clave externa.

La manera de hacerlo en realidad depende en gran medida del tipo de suposiciones / convenciones que esté dispuesto a aplicar sobre los datos que está actualizando. Para simplificar, digamos que todas las claves foráneas que necesitamos actualizar se nombran con la convención {ParentClassName} _ {Id}.

Eso significa que si en nuestro ejemplo simplemente insertamos un nuevo 'Widget', entonces podemos tratar de actualizar a la fuerza todos los demás objetos en este ámbito de transacción que tengan un campo 'Widget_Id' (o 'Widget_AutoId')

    private void UpdateForeignKeys(string foreignKeyName, int idValue, IEnumerable<KeyValuePair<string, object>> commands)
    {
        foreach(var command in commands)
        {
            Type itemType = command.Value.GetType();
            var keyProp = itemType.GetProperty(foreignKeyName);
            if(keyProp != null)
            {
                keyProp.SetValue(command.Value, idValue);
            }
        }
    }

Este es un ejemplo muy simplista de cómo podría actualizar las claves externas (o de referencia) en la biblioteca de persistencia de datos OPs. Probablemente haya observado en realidad que los campos de clave relacionales rara vez se nombran consistentemente utilizando cualquier convención, pero incluso cuando se siguen las convenciones, mi simple convención no admitiría una tabla que tuviera múltiples referencias a padres del mismo tipo, por ejemplo, un manifiesto en uno de las aplicaciones de mi cliente tiene 3 enlaces a una tabla de usuario:

public class Manifest
{
    ...
    Driver_UserId { get; set; }
    Sender_UserId { get; set; }
    Receiver_UserId { get; set; }
    ...
}

Debería desarrollar una lógica bastante avanzada para abordar todas las posibles combinaciones de enlaces.

Algunos ORM hacen esto estableciendo los valores como números negativos, y disminuyendo los números cada tipo se agrega un nuevo tipo a la colección de comandos. Luego, después de una inserción, solo necesita actualizar los campos clave que contenían el número negativo falso con el número actualizado. Aún necesita saber qué campos son clave, pero al menos no necesita rastrear los campos precisos que forman los extremos de cada relación, podemos rastrear con los valores.

Sin embargo, me gusta cómo lo hace Entity Framework, intente insertar esta información de vinculación sobre los campos usando atributos en las propiedades. Puede que tengas que inventar el tuyo propio, pero es un concepto declarativo limpio que te obliga a describir estas relaciones desde el principio en las clases de modelo de datos de una manera que todos los tipos de lógica pueden aprovechar más adelante, no solo para generar sentencias SQL.

No quiero ser demasiado crítico con Dapper, pero una vez que empiezas a recorrer este camino o administras manualmente la integridad referencial como este, hay un punto en el que debes considerar un ORM más empresarial como Entity Framework o nHibernate. Claro que vienen con algo de equipaje, pero esos ORM realmente se han convertido en productos maduros que han sido optimizados por la comunidad. Ahora tengo muy poco código escrito o escrito manualmente para personalizar cualquier interacción con el RDBMS, lo que significa mucho menos código para probar o mantener. (= menos errores)


Respuesta popular

No dice qué base de datos está utilizando. Si es MSSQL puedes hacer

var id =  connection.Query<int?>("SELECT @@IDENTITY").SingleOrDefault();

después de ejecutar el Insertar. Eso te da la identificación de la última inserción.



Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué
Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué