Sorting Kendo foreign key columns by text in MVC

The Kendo UI MVC wrappers make it very easy to have a dropdown in a grid column as a foreign key. You may notice that the column sorts by the id though, and not the text value that would make sense to the user.

The Kendo documentation has a great example of custom binding in MVC for custom sorting, filtering, and paging.

I wanted something lightweight to use that didn’t require rewriting all of a grid’s sorting functionality. Here is how I translate the sort type in a grid’s Read method in the controller.

        public JsonResult Coco_Read([DataSourceRequest]DataSourceRequest request)
            // Sort foreign key columns by text not id
            foreach (SortDescriptor sortDescriptor in request.Sorts)
                switch (sortDescriptor.Member)
                    case "CocoTypeId":
                        request.Sorts[request.Sorts.IndexOf(sortDescriptor)].Member = "CocoTypeName";

            return Json(Model.GetCoco().ToDataSourceResult(request), JsonRequestBehavior.AllowGet);

My solution replaces any sort for the foreign key column CocoTypeId with the hidden text equivalent column CocoTypeName. This is the easiest technique I could come up with for sorting from what Telerik recommends.

Validating Bootstrap radio buttons with Kendo UI

Validating radio buttons isn’t supported by the Kendo form validator. Add Bootstrap radio buttons in the mix and you’ve got double the reason to roll your own validation! Fortunately we can use a little jQuery to require the user to select a radio button, and set the chosen value for form submission. Here’s the JavaScript I came up with, triggered on form submit:

$(function () {

    // On form submit, validate and send form by ajax
    $(document).on("click", ":submit", function (event) {

        // Custom validation for required bootstrap radio buttons
        if ($('#radiobuttons').children().filter("[class~='active']").length < 1) {

        } else {
             // Set hidden field equal to the chosen radio button's text
             // Submit form by ajax elsewhere

        // Prevent default navigation
        return false;

The related HTML uses Bootstrap radio buttons, and emulates the Kendo validator tooltip to alert the user that the field is required:

                                    <!--Hidden radio button field set on submit -->
                                    <input type="hidden id="radiovalue">
                                    <div class="btn-group" data-toggle="buttons" id="radiobuttons" onclick="$('#validator_msg').hide();">
                                        <label class="btn btn-primary">
                                            <input type="radio"> Pineapple
                                        <label class="btn btn-primary">
                                            <input type="radio"> Coconut
                                        <label class="btn btn-primary">
                                            <input type="radio"> Lime
                                    <!-- Custom kendo tooltip for required radio buttons -->
                                    <span class="k-widget k-tooltip k-tooltip-validation k-invalid-msg field-validation-error" id="validator_msg" role="alert" style="display: none">
                                        <span class="k-icon k-warning"> </span> Please select a value

Now when you attempt to submit the form without selecting a radio button, you should see the Kendo validation tooltip prompting you for required input!

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.]"

Single click checkbox editing for Kendo grids

This is possibly the most anti-climatic code ever. You click a checkbox in a column, and click the save button. It just works. Except that’s not a default behavior supported in Kendo UI. Depending on how you do it, you either have to click into the cell, then the click the checkbox, or maybe your checkbox value won’t save at all.

The goal here is to have a grid column displayed as checkboxes for boolean values. The problem is the checkbox displayed isn’t connected to the data structure behind the Kendo Grid.

Telerik has an example of checkbox column editing, but it’s hard coded with a grid id, column name, and checkbox class. We need something that can be applied to all grids in an application!


// On jQuery document ready
jQuery(function ($) {

    // Synchronize the kendo grid data to user clicked checkbox value
    // This is necessary for single-click grid editing on checkboxes for the checkbox state to be saved properly
    // This delegate event handler applies to all kendo grids
    $("body").on("click", "td", function(e) {

        // Get the parent grid from the cell clicked
        var grid = $(this).closest(".k-grid").data().kendoGrid;

        // Get the selected row and column from the cell clicked
        var row = $(this).closest("tr");
        var colId = $("td", row).index(this);

        // Get the column binding name from the grid properties
        var colBinding = grid.options.columns[colId].field;

        // Look for an enabled checkbox input inside the cell clicked
        var chk = $(this).children("input[type=checkbox]:enabled");
        if (chk.length) {

            // Get the Kendo data structure which keeps track of grid edits for the chosen row
            var dataItem = grid.dataItem(row);

            // FINALLY: Set the boolean data value to the checkbox value in the cell clicked
            dataItem.set(colBinding, chk[0].checked);


Now any grid in your application will allow the user to click into the checkbox directly, and save the grid.

Checkboxes in a Kendo grid

If you’re still stuck on how to get a checkbox showing in a Kendo grid column in the first place, here’s how to do it with ASP.NET MVC wrappers:

columns.Bound(p => p.IsActive).ClientTemplate("<input type='checkbox' #= IsActive ? checked='checked' : '' # />");