Concurrency control and transaction logging with NPoco

I’ve been happy with using a micro-ORM like NPoco for database persistence, but an ERP record keeping project may require logging and concurrency control. We can’t have a user update a record with old data that’s just been updated by another user now can we?

It’s super convenient that the NPoco library will update the appropriate database table automatically. We just need to add a log entry in the database, and roll back everything if logging fails. First lets check the timestamp on the record before updating it.

        private readonly Database db = new Database("SpaceDB");

        public int UpdateRecord(ClientRecord obj, int userId)
            var _ReturnResult = 0;
            var _ActionResult = 0;


                // Check record for concurrent updates
                var _Record = db.SingleById<ClientRecord>(obj.ClientRecordId);

                if (obj.UpdateDate.Equals(_Record.UpdateDate))
                    obj.UpdateDate = DateTime.Now;
                    obj.UpdateBy = userId;

                    _ActionResult = db.Update(obj);
                    LogEntry.Log("UPDATE", db, obj;

                // Commit transaction
                if (_ActionResult == 1)
                    _ReturnResult = 1;
            catch (Exception ex)

            return _ReturnResult;

First the code gets the most recent update timestamp from the database to check that it has not been touched since our client loaded it. If so, we’re clear to update the record. We use NPoco to update the table corresponding to the ClientRecord class, and log the update. Below is the the code for LogEntry.Log.

Since _ActionResult contains the number of updated rows returned from NPoco, we can expect that to be 1. Otherwise there was a problem, and any changes applied need to be removed from the database. All of this is done inside of a transaction, so anything fails, just use AbortTransaction() to roll back everything. In this case a 1 is returned from the UpdateRecord method only if the update transaction was successful.

    public class LogEntry
        /// <summary>
        /// Writes an entry to the log table. Throws an exception if not successful.
        /// </summary>
        /// <param name="db">Instance of the NPoco database used for the transaction.</param>
        /// <param name="obj">POCO class object being saved in the database.</param>
        public static void Log(String SQLAction, Database db, System.Object obj)
            var _LogEntry = new Log();

            // Use reflection to get values from object by name - update date and by are required
            _LogEntry.UpdateDate = (DateTime) obj.GetType().GetProperty("UpdateDate").GetValue(obj, null);
            _LogEntry.UpdateBy = (int) obj.GetType().GetProperty("UpdateBy").GetValue(ob, null);

            _LogEntry.SQLAction = SQLAction;

            // Get table name and key values from NPoco attributes
            _LogEntry.TableName = TableInfo.FromPoco(obj.GetType()).TableName;
            _LogEntry.TableKeyNames = TableInfo.FromPoco(obj.GetType()).PrimaryKey;
            _LogEntry.TableKey = obj.GetType().GetProperty(_LogEntry.TableKeyNames)?.GetValue(obj, null).ToString() ?? "";

            _LogEntry.ColumnNames = "";
            _LogEntry.ColumnValues = "";

            // Build log entry strings with object names and values
            foreach (var info in obj.GetType().GetProperties())
                var value = info.GetValue(obj, null) ?? "NULL";
                var ignoreColumn = false;

                // Check that the [NPoco.Ignore] or [NPoco.ResultColumn] attribute is not present
                foreach (var attr in info.CustomAttributes)
                    if (attr.AttributeType.Name == "IgnoreAttribute" || attr.AttributeType.Name == "ResultColumnAttribute")
                        ignoreColumn = true;

                if (!ignoreColumn)
                    _LogEntry.ColumnNames = _LogEntry.ColumnNames + "," + info.Name;
                    _LogEntry.ColumnValues = _LogEntry.ColumnValues + "," + value.ToString();

            // Trim leading char
            _LogEntry.ColumnNames = _LogEntry.ColumnNames.Substring(1);
            _LogEntry.ColumnValues = _LogEntry.ColumnValues.Substring(1);

            // Insert entry into the Log table
            db.Insert("Log", "LogId", true, _LogEntry);

The logging function logs the table name, column names, and values updated, along with the user ID and date.

Reflection is used to inspect the NPoco properties of the object passed in and get the table name and primary key attributes. It should be noted that this logging function can’t handle composite keys, that is, primary keys comprised of more than one database column. Some additional logic would be needed.

As an alternative to logging this way, you should consider setting up a database trigger, depending on your project and requirements. But that’s another post for another day.

Generating a year prefixed serial number in SQL

An existing SQL function I was using created year-prefixed serial numbers. This year it eventually ran into a problem where it couldn’t go higher than 201502015. Here’s my complete rewrite of that function:

	DECLARE @SerialNumber int

	-- Get current year as string
	DECLARE @Year varchar(4) = YEAR(GETDATE())

	-- Get current max inmate number
	DECLARE @CurrentMax int
	SELECT	@CurrentMax = MAX(SerialNumber)
	FROM	Serials
	WHERE	SerialNumber LIKE @Year +'%'

	-- Remove only the first instance of the year in the max serial number
	DECLARE @NextSeq int
	SET		@NextSeq = Stuff(@CurrentMax, CharIndex(@Year, @CurrentMax), Len(@Year), '')

	-- Increment sequence number
	SET		@NextSeq = ISNULL(@NextSeq, 0) + 1

	-- Convert sequence number to string for concatenation
	DECLARE @NextStr varchar(5) = @NextSeq

	-- Return zero padded serial number as integer with year prefix
	SET		@SerialNumber = @Year + RIGHT('00000' + @NextStr, 5)

	RETURN @SerialNumber

The function above generates an incrementing zero padded serial number with the current year prefixed, as such:


What you want to avoid is using the Replace() function to trim out the year from the preceding serial number, because, well, it will Replace() all over that value like nobody’s business. Here I’m using

Stuff(@CurrentMax, CharIndex(@Year, @CurrentMax), Len(@Year), '')

to only trim out the first instance of the year.

Greatest-N-Per-Group SQL: Getting the latest location for each user

This algorithm comes up a lot in the system I’m building. Get the most recent whatever for each user, along with all their information. Also known as the greatest-n-per-group problem. Here’s the most efficient SQL algorithm I’ve come across:

SELECT   C.ClientId, C.LastName, L.LocationName AS CurrentLocation
FROM     Client C
	FROM (SELECT * FROM Location WHERE UpdateDate < @Date) AS t1
	LEFT JOIN (SELECT * FROM Location WHERE UpdateDate < @Date) AS t2
	ON t1.ClientId = t2.ClientId AND t1.UpdateDate < t2.UpdateDate
	WHERE	t2.ClientId IS NULL) AS L ON C.ClientId = L.ClientId

This query finds the latest location for each client as of the supplied @Date. The main part of the algorithm reads as:

Return the row t1 for which no other row t2 exists with the same ClientId and a greater UpdateDate

The outer left join allows us to return users with no entries in the location table.

Automated stored procedures on Microsoft Azure

I needed to run a stored procedure in a Azure database every day at midnight.

To do this, set up an Azure automation account. Then create a new runbook with the following powershell script. Finally, you will need to create a credential asset to plug into this script as an input parameter.

Once it’s set up you can schedule it as a nightly job.

workflow DailyExpirationCheck 
        # Fully-qualified name of the Azure DB server 
        [string] $SqlServerName,
        # Database name on the Azure DB server 
        [string] $DatabaseName,
		# Credentials for $SqlServerName stored as an Azure Automation credential asset
		# When using in the Azure Automation UI, please enter the name of the credential asset for the "Credential" parameter
        [PSCredential] $Credential
        # Set up credentials   
        $ServerName = $Using:SqlServerName
        $DatabaseName = $Using:DatabaseName
        $UserId = $Using:Credential.UserName
        $Password = ($Using:Credential).GetNetworkCredential().Password
        # Create connection to DB
        $DatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
        $DatabaseConnection.ConnectionString = "Server = $ServerName; Database = $DatabaseName; User ID = $UserId; Password = $Password;"
        # Create command to execute stored procedures
        $DatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
        $DatabaseCommand.Connection = $DatabaseConnection
        $DatabaseCommand.CommandType = [System.Data.CommandType]::StoredProcedure
        # Update statuses by calling our stored procedure
        $DatabaseCommand.CommandText = 
        $DatabaseCommand.ExecuteNonQuery() | out-null
        if($? -eq 1)
            Write-Output "EndDate check successfully completed."   
        # Close connection to DB
        Write-Output "[Daily expiration checks complete.]"