private SqlCommand BuildReleaseLeasesCommand()

in src/TriggerBinding/SqlTableChangeMonitor.cs [1008:1039]

• 28 lines of code
• 2 McCabe index (conditional complexity)

        private SqlCommand BuildReleaseLeasesCommand(SqlConnection connection, SqlTransaction transaction)
        {
            // The column definitions to use for the CTE
            IEnumerable<string> cteColumnDefinitions = this._primaryKeyColumns
                .Select(c => $"{c.name.AsBracketQuotedString()} {c.type}")
                // Also bring in the SYS_CHANGE_VERSION column to compare against
                .Append($"{SysChangeVersionColumnName} bigint");
            IEnumerable<string> bracketedPrimaryKeys = this._primaryKeyColumns.Select(p => p.name.AsBracketQuotedString());

            // Create the query that the update statement will match the rows on
            string primaryKeyMatchingQuery = string.Join(" AND ", bracketedPrimaryKeys.Select(key => $"l.{key} = cte.{key}"));
            const string releaseLeasesCte = "releaseLeasesCte";
            const string rowDataParameter = "@rowData";

            string releaseLeasesQuery =
$@"{AppLockStatements}

WITH {releaseLeasesCte} AS ( SELECT * FROM OPENJSON(@rowData) WITH ({string.Join(",", cteColumnDefinitions)}) )
UPDATE {this._bracketedLeasesTableName}
SET
    {LeasesTableChangeVersionColumnName} = cte.{SysChangeVersionColumnName},
    {LeasesTableAttemptCountColumnName} = 0,
    {LeasesTableLeaseExpirationTimeColumnName} = NULL
FROM {this._bracketedLeasesTableName} l INNER JOIN releaseLeasesCte cte ON {primaryKeyMatchingQuery}
WHERE l.{LeasesTableChangeVersionColumnName} <= cte.{SysChangeVersionColumnName};";

            var command = new SqlCommand(releaseLeasesQuery, connection, transaction);
            SqlParameter par = command.Parameters.Add(rowDataParameter, SqlDbType.NVarChar, -1);
            string rowData = Utils.JsonSerializeObject(this._rowsToRelease);
            par.Value = rowData;
            return command;
        }