Getting Id during sql query and using for another Insert Statment

Question

So I have been creating a library that uses dapper and allows user to manipulate a database.

I need some help with finding the best way to achieve the following.

Lets say I have an "order" table and I have a "transaction" table and an "order_line" table. I want to take the Increment Id of table "order" when inserting and use it to store it in a column in "transaction" and "order_line" table and I want all of this done in a SQL transaction so that I can roll back in case of any issue.

Now since my library is dynamic to any type and action, I am not sure on how to approach something like this.

Here is the code on how you would insert: I have 2 global variables

  private string connectionString { get; set; }

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

        private List> transactions = new List>();

Here is how you call to have a class to be saved to the database:

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 command = new KeyValuePair($"Insert Into[{ itemName}] ({ propertyNames}) Values({ propertyParamaters})", item);
    transactions.Add(command);
}

There are more methods and like edit, remove, edit list, remove list etc. but are not relevant in this case.

When you want to commit changes to the database you call:

public void SaveChanges()
        {
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
                using (SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())
                {
                    try
                    {
                        foreach (KeyValuePair 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();
        }

You can find my library at github.com
https://github.com/pietercdevries/Bamboo.Net

Chris Schaller · Accepted Answer

Can it be done... yes... should we be trying to do this ourselves... I wouldn't :) but lets try it any way.

Some ideas that can make this code simpler:

Define helper interfaces and force the data classes to implement them or use attribute declarations to specify id fields and foreign key references
Investigate Injection or code generation techniques so that you can get some of this 'dynamic' coding and lookup executed at compile time, not runtime.

I don't use Dapper and your SqlConnection.Execute() is an extension method I am not familiar with but I assume that it generates DbParameters from the passed in object and applies them to the SqlCommand when it gets executed. Hopefully dapper has some functions to extract the parameters, so that they can be used in this code example, or perhaps you can use some of these concepts and adapt them to your dapper code. I just want to acknowledge that upfront and that I have omitted any code example here that parameterises the objects when executing the commands.

This is the journey that the following snippets will go down

Prepare the generated SQL to capture the Id field
Output the Id value when we save changes
Iterate over all remaining objects in the array and set the foreign key values

Note: these code changes are not tested or exception handled for production, nor would I call this "best practice" its just to prove the concept and help out a fellow coder :)

You have already devised a convention for Id field tracking, lets extend that idea by preparing the sql statement to set the value of an output parameter:

NOTE: in MS SQL, please use SCOPE_IDENTITY() in preference to @@Identity.
What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current?

NOTE: because the generated statements are using parameters, and we are not yet reading the parameter values, we will not need to regenerate the saved SQL statements later after we have found an Id value to insert to other objects... phew...

public void Add(object item)
{
    List propertyNames = new List();
    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 command = new KeyValuePair
        ($"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()
}

In Save Changes, implement output parameter to capture the created Id, and if the Id was captured, save it back into the object that the command is associated to.

NOTE: this code snippet shows the references to the solution in item 3. And the foreach was replaced with a for so we could do forward iterations from the current index

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 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();
}


/// 
/// Update the Id field of the specified object with the provided value
/// 
/// Object that we want to set the Id for
/// Value of the Id that we want to push into the item
/// Name of the expected foreign key fields
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;
        }
    }
}

So now your objects have their Id's updated as they are inserted. Now for the fun part... After updating the Id, you should now iterate through the other objects and update their foreign key fields.

How you go about this in reality depends a lot on what kind of assumptions/conventions you are ready enforce over the data that you are updating. For simplicity sake, lets say that all of the foreign keys that we need to update are named with the convention {ParentClassName}_{Id}.

That means that if in our example we just inserted a new 'Widget', then we can try to forcibly update all other objects in this transaction scope that have a field 'Widget_Id' (or 'Widget_AutoId')

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

This is a very simplistic example of how you could go about updating foreign (or reference) keys in OPs data persistence library. You have probably observed in reality that relational key fields are rarely consistently named using any convention, but even when conventions are followed, my simple convention would not support a table that had multiple references to parents of the same type, for example a Manifest in one of my client's apps has 3 links back to a user table:

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

You would need to evolve some pretty advanced logic to tackle all possible linkage combinations.

Some ORMs do this by setting the values as negative numbers, and decrementing the numbers each type a new type is added to the command collection. Then after an insert you only need to update key fields that held the faked negative number with the updated number. You still need to know which fields are key fields, but atleast you don't need to track the precise fields that form the ends of each relationship, we can track with the values.

I like how Entity Framework goes about it though, try inject this linkage information about the fields using attributes on the properties. You may have to invent your own, but it's a clean declarative concept that forces you to describe these relationships up front in the data model classes in a way that all sorts of logic can later take advantage of, not just for generating SQL statements.

I don't want tobe too critical of Dapper, but once you start to go down this path or manually managing referential integrity like this there is a point where you should consider a more enterprise ready ORM like Entity Framework or nHibernate. Sure they come with some baggage but those ORMs have really evolved into mature products that have been optimised by the community. I now have very little manually written or scripted code to customise any interactions with the RDBMS at all, which means much less code to test or maintain. (= less bugs)

Getting Id during sql query and using for another Insert Statment

Answers (2)

Related Questions