Tracking Actions with SqlModelMapper

Last Updated: March 2020

Introduction

This document will describe how to track actions with SqlModelMapper. SqlModelMapper enables you to track database table operations, as well as actions which encapsulate customized methods. These actions will be called when the SaveChanges() method of the SqlModelMapper is called. This enables you to perform several different tasks and operations on the data before the transaction is committed.

Basic example (pseudo code)

In the following example, the SQL Delete operation is tracked first, then a needed action is tracked next, then the SQL Insert operation at the last. Finally the SaveChanges operations will be used to execute the SQL Delete operation, then the needed action, and the SQL Insert operation at the last. The tracking order determines the order of the actual operations to the database.

// Perform Tracking operations
_context.SqlModelMapper.TrackDelete<TheModel>(deleteModel);

// Track the Actions of the DataContext
_context.SqlModelMapper.Track((saveContext) =>
{
    // Use the ISaveContext APIs
    if (saveContext.Contains("name_of_the_value") == false)
    {
        // Make changes to the SaveContext
        saveContext.Add("name_of_the_value", anyValue);
    }
});

// Perform any other needed Track operations
_context.SqlModelMapper.TrackCreate<MyModel>(newModel);

// Call the SaveChanges API
_context.SqlModelMapper.SaveChanges();

Execution process of SqlModelMapper.SaveChanges()

When the SqlModelMapper.SaveChanges() is executed the SqlModelMapper object will go through a 4-step process as follows:

  1. Checks to determine if the transaction is explicit or implicit. If a transaction is already created then the instance of SaveChanges() is included in that (explicit) transaction; if no, then it creates a new (implicit) transaction.
  2. Goes through and executes each tracked item one by one. If the tracked item is a model create/update/delete, then it generates and executes the appropriate SQL. If the tracked item is a SQL create/update/delete, then it executes the specified SQL statement. If the tracked item is an action then it executes the specified action. All the tracked items are executed in a single batch and become part of the explicit or implicit transaction mentioned in step 1.
  3. Decides whether to commit or rollback the transaction (for implicit transactions only since explicit transactions are handled by the programmer). If all tracked items in step 2 are executed successfully then it commits the implicit transaction; otherwise, it rolls back the implicit transaction.
  4. Clears the list of all tracked items associated with the particular SaveChanges().

By tracking actions with SqlModelMapper, you will have the flexibility to add custom logics to the same transaction. You can track an action to get some values generated by the database, and set values to the other tracked models in the same transaction, or load data from the database, or cancel the Save operation and roll back the transaction.

Action <ISaveContent>

It is the type of parameter used by track action methods. It is actually a C# code block that contains the logic that you want to execute. Refer to Action<T> for more info.

// Tracks a C# code block (Action<ISaveContext> type). 
// (pseudo code)
_context.SqlModelMapper.Track(  
    // You can receive a parameter to access the Save Context.
    (saveContext) =>
    {
        // Uses the Save Context.
        if (saveContext.Contains("name_of_the_value") == false)
        {
            // Add a temporary value to the Save Context
            saveContext.Add("name_of_the_value", anyValue);
        }
    }
);

ISaveContext

When the SaveChanges() method is called, one ISaveContext object is instantiated by SqlModelMapper internally, and the ISaveContext object persists until all traced database operations or actions have been performed. This ISaveContext object is called Save Context.

Since the Save Context persists throughout the save process, you can use it to:

  • Record temporary values in a tracked action, and then get the temporary value from the save context in another action that is tracked later.
  • Get the result of last executed database operation.
  • Cancel the save operation and roll back the transaction.

ISaveContext Methods

Use the following methods to manipulate data and/or to cancel a save operation in the Save Context.

// Adds a value of any data type and its name to the Save Context.
// This name:value combination can then be used in other tracked actions.
void Add(string name, object value);

// Cancels the operation that is currently being executed to save the data to the database.
void CancelSave();

// Returns whether the Save Context contains the specified value.
// Uses this to determine if a given named value exists in the Save Context
bool Contains(string name);

// Uses this to Get a named value from the Save Context.
object Get(string name);

// Removes the specified value from the Save Context.
void Remove(string name);

// Removes all of the added values from the Save Context.
void RemoveAll();

ISaveContext Properties

The following properties are available from the Save Context to obtain specific information from the Save Context itself.

// Gets the number of added values in the Save Context.
int Count { get; }

// Gets the result of the most recent operation that uses the model to save data to the
// database. You can then use the properties of the IDbResult object to determine the
// Affected, Deleted, Inserted or Modified Count. As well as to determine if an operation
// was cancelled.
IDbResult LastDbResult{ get; }

// Gets the names of all of the added values.
// Use this to obtain a list of all the values from the Save Context.
IReadOnlyList<string> Names { get; }

Typical usage scenarios

There are 3 typical scenarios in which tracking action is very useful:

  • To utilize data generated by the database by the previous database operation.

  • To utilize data from other tables that are not part of the transaction.

  • To cancel the save operation when some conditions are met.

The following three examples will demonstrate these three usage scenarios.

Tracking actions to obtain Identity values

If you need to obtain Identity values from a database table after a track operation has been performed, you can accomplish this using the Save Context in the Track() method of the SqlModelMapper. (Note: Avoid calling any tracking method of the current ISqlModelMapper object in the script of the Actions)

public int AddMasterDetail()
{
    var mapper = _context.SqlModelMapper;

    // Set the properties of the new Customer
    CustomerMaster master = new CustomerMaster()
    {
        Id = 0, // This is an Identity column in the database
        Fname = "Govinda",
        Lname = "Lopez",
        Address = "369 Penny Lane",
        City = "Greeley",
        State = "CO",
        Zip = "80631",
        Phone = "8773277366",
        Company_Name = "Appeon Inc."
    };

    // Set the properties of the SalesOrder
    SalesOrderDetail detail = new SalesOrderDetail()
    {
        Id = 0, // This is an Identity column in the database
        Order_Date = System.DateTime.Now,
        Fin_Code_Id = "r1",
        Region = "Eastern",
        Sales_Rep = 856
    };

    // Track the Insert Operation of the new Customer
    mapper.TrackCreate<CustomerMaster>(master);

    // Track a callback action to set the newly inserted Customer Id
    // into the SalesOrder record.
    mapper.Track((saveContext) =>
    {
        // Check if anything was actually inserted
        if (saveContext.LastDbResult.InsertedCount > 0)
        {
            // Set the new Customer Id into the SalesOrder record
            detail.Cust_Id = master.Id;

            // Adds the new value to the Save Context
            saveContext.Add("newCustomerId", master.Id);
        }
    });

    // Track the Insert operation of the new SalesOrder
    mapper.TrackCreate<SalesOrderDetail>(detail);

    // When saving the changes, it executes the tracked SQL operations and 
    // actions, one by one, in the same transaction and commit them at last.
    var dbResult = mapper.SaveChanges();

    // Get the Inserted Rows Count
    var insertedRows = dbResult.InsertedCount;

    if (insertedRows > 0)
    {
        return master.Id;
    }
    else
    {
        return -1;
    }
}

Tracking actions to perform other SQL query operations

If you need to perform other database table operations, perhaps with other non-related tables, or any other operation before saving the changes of the transaction, you can accomplish this using the Save Context in the Track() method of the SqlModelMapper. (Note: Avoid calling any tracking method of the current ISqlModelMapper object in the script of the Action)

public decimal Add()
{
    // Create the object variables
    var queryBuilder = new SqlQueryBuilder();
    var mapper = _context.SqlModelMapper;

    // Create the numeric variables
    int itemSales = 0;
    decimal prodPrice = 0.0m;
    decimal totalSold = 0.0m;

    // Set the properties of the new Item detail
    SalesOrderItemsGrandDetail detail = new SalesOrderItemsGrandDetail()
    {
        Id = 3001,
        Line_Id = 2,
        Prod_Id = 300,
        Quantity = 1,
        Ship_Date = System.DateTime.Now
    };

    // Track the Insert operation of the new Detail
    mapper.TrackCreate<SalesOrderItemsGrandDetail>(detail);

    // Track a callback action to get the total sales by Sales Rep and 
    // Product Id.
    mapper.Track((saveContext) =>
    {
        // Create 2 new parameters
        queryBuilder.AddParameters(
            SqlBuilder.Parameter<int>("salesRep"),
            SqlBuilder.Parameter<int>("prodId")
         );

        // Create a SQL query to count the Products sold by Sales Rep
        // and Product Id
        queryBuilder.Select("COUNT(*)")
                    .From("sales_order_items")
                    .Join("sales_order")
                    .On("sales_order.id", "sales_order_items.id")
                    .Where("sales_order.sales_rep", "@salesRep")
                    .AndWhere("sales_order_items.prod_id", "@prodId");

        // Execute the Query
        itemSales = _context.SqlExecutor.Scalar<int>(queryBuilder, 856, detail.Prod_Id);

        // Get the Unit Price from the database table
        prodPrice = _context.SqlExecutor.Scalar<decimal>(
                                                    "SELECT UNIT_PRICE FROM PRODUCT WHERE ID = @prodId",
                                                    detail.Prod_Id);

        // Calculate the total sales by Sales Rep and Product Id
        totalSold = itemSales * prodPrice;
    });

    // When saving the changes, it executes the tracked SQL operations and 
    // actions, one by one, in the same transaction and commit them at last.
    mapper.SaveChanges();

    // Return the total sold amount by Product Id and Sales Rep
    return totalSold;
}

Tracking actions and canceling the Save Context

There may be certain times when you might need to cancel an operation based on other conditions, even if the operation was successful. In the following example the operation is canceled based on a value from a different database table that was not originally included in the transaction. (Note: Avoid calling any tracking method of the current ISqlModelMapper object in the script of the Action)

public int AddItem()
{
    var mapper = _context.SqlModelMapper;

    int productExistance;
    int insertedRows;

    // Set the properties of the new Item detail
    SalesOrderItemsGrandDetail detail = new SalesOrderItemsGrandDetail()
    {
        Id = 3001,
        Line_Id = 3,
        Prod_Id = 300,
        Quantity = 10000,
        Ship_Date = System.DateTime.Now
    };

    // Track a callback action to get the total sales by Sales Rep and 
    // Product Id.
    mapper.Track((saveContext) =>
    {
        // Get the current Existance for the selected Product Id
        productExistance = _context.SqlExecutor.Scalar<int>(
                                                                "SELECT QUANTITY FROM PRODUCT WHERE ID = @prodId",
                                                                detail.Prod_Id);

        // Check if trying to sell more products than there are available
        if (productExistance < detail.Quantity)
        {
            // Cancel the operation
            saveContext.CancelSave();
        }
    });

    // Track the Insert operation of the new Detail
    mapper.TrackCreate<SalesOrderItemsGrandDetail>(detail);

    // When saving the changes, it executes the tracked SQL operations and 
    // actions, one by one, in the same transaction and commit/rollback them at last.
    insertedRows = mapper.SaveChanges().InsertedCount;

    // Return the Inserted Count
    return insertedRows;
}

Overview of the tracking action methods

The following methods can track actions in the SqlModelMapper, refer to API reference for more info:

// This Method tracks an Action.
ISqlModelMapper.Track(Action<ISaveContext> saveAction)
    
// Tracks a database table operation (insert, update, or delete) and 
// an Action<ISaveContext> object. 
ISqlModelMapper.Track<TModel>(IModelEntry<TModel> modelEntry, Action<ISaveContext> afterSaveAction)
  
// Tracks a database table insert operation and an Action.
ISqlModelMapper.TrackCreate<TModel>(TModel model, Action<ISaveContext> afterSaveAction)
  
// Tracks a database table delete operation and an Action.
ISqlModelMapper.TrackDelete<TModel>(TModel model, Action<ISaveContext> afterSaveAction)

// Tracks an insert, update or delete operation to the master table 
// and an Action<ISaveContext> object, when working with multiple 
// models which are in the master-detail relationship. 
ISqlModelMapper.TrackMaster<TModel>(IModelEntry<TModel> modelEntry, Action<ISaveContext> afterSaveAction)
  
// Tracks a database table update operation and an Action.
// The data that will be updated to the database table is cached in the TModel object.
ISqlModelMapper.TrackUpdate<TModel>(TModel model, Action<ISaveContext> afterSaveAction)
  
// Tracks a database table update operation and an Action.
// The data that will be updated to the database table is cached in the modifiedModel and the original data is cached in the originalModel.
ISqlModelMapper.TrackUpdate<TModel>(TModel originalModel, TModel modifiedModel, Action<ISaveContext> afterSaveAction)