Simple Bootbox message with callback

bootbox.js is a nice simple replacement for alerts. Here’s a quick extension that shows a message by hiding any existing boxes, then showing the new one. Since it’s using Bootstrap’s event-based modals, you have to wait for one to finish loading before closing it or expecting it to be open.

function showMessage(title, body, callback, noButton){
    bootbox.hideAll();
    var data = { title: title, message: body };
    if (callback) {
        data.onShown = callback;
    }
    if (noButton) {
        bootbox.dialog(data);
    } else {
        bootbox.alert(data);
    }
}

showMessage("Loading", "Working...", function () {
    // Do something
    showMessage("Second Message", "This is the real message");
}, true);

SQL Merge Insert/Update with output

if object_id('tempdb..#joe') is not null
    drop table #joe;

go

declare @results table (id int);

create table #joe (id int not null identity, name varchar(100));

insert #joe
    select 'John' [name]
    union select 'Jane'
    union select 'Mary'
    union select 'Pat';

select * from #joe order by id;

declare @id int, @name varchar(100);

-- Update
--set @id = 2; set @name = 'Phil';

-- Insert
--set @id = null; set @name = 'Phil';

merge #joe tgt
using (select @id [id]) src
on tgt.id = src.id
when matched then
    update set name = @name
when not matched then
    insert (name)
    values (@name)
output inserted.id into @results;

select * from #joe order by id;
select * from @results;

Claims Authorization Example

Startup.cs

using System.IO;
using System.Linq;
using System.Security.Claims;
using Microsoft.AspNetCore.Authentication.Cookies;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.DataProtection;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Newtonsoft.Json;

namespace WebApplication25
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        public void ConfigureServices(IServiceCollection services)
        {
            var cookieAuthSection = Configuration.GetSection("CookieAuth");
            string appName = cookieAuthSection.GetValue<string>("ApplicationName");
            string keyLocation = cookieAuthSection.GetValue<string>("KeyLocation");
            services.AddDataProtection()
                .SetApplicationName(appName)
                //.SetDefaultKeyLifetime(TimeSpan.FromDays(9999))
                .PersistKeysToFileSystem(new DirectoryInfo(keyLocation));

            services.AddAuthentication(CookieAuthenticationDefaults.AuthenticationScheme)
                .AddCookie(CookieAuthenticationDefaults.AuthenticationScheme, config =>
                {
                    config.Cookie.Name = "myapp";
                    config.LoginPath = "/Login";
                    config.AccessDeniedPath = "/AccessDenied";
                });

            services.AddAuthorization(config =>
            {
                config.AddPolicy("UserName", policyBuilder =>
                {
                    policyBuilder.RequireClaim(ClaimTypes.Name);
                });
                config.AddPolicy("Admin", policyBuilder =>
                {
                    policyBuilder.RequireClaim("IsAdmin");
                });
                config.AddPolicy("PhoenixAdmin", policyBuilder =>
                {
                    policyBuilder.RequireClaim("CityAdmin", "Phoenix");
                });
                config.AddPolicy("Zero", policyBuilder =>
                {
                    policyBuilder.RequireAssertion(context =>
                    {
                        var roleClaim = context.User.Claims.FirstOrDefault(x => x.Type == "Roles");
                        if (roleClaim == null) { return false; }

                        var roles = JsonConvert.DeserializeAnonymousType(roleClaim.Value, new[] { new { RoleCode = "", OrgID = 0 } });
                        return roles.Any(r => r.OrgID == 0);
                    });
                });
                config.AddPolicy("AdminOrCityAdmin", policyBuilder =>
                {
                    policyBuilder.RequireAssertion(context =>
                    {
                        return context.User.Claims.Where(x => x.Type == "IsAdmin" || x.Type == "CityAdmin").Any();
                    });
                });
            });

            services.AddRazorPages();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            else
            {
                app.UseExceptionHandler("/Error");
            }

            app.UseStaticFiles();

            app.UseRouting();

            app.UseAuthentication();
            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapRazorPages();
            });
        }
    }
}

appsettings.json

{
    "Logging": {
        "LogLevel": {
            "Default": "Information",
            "Microsoft": "Warning",
            "Microsoft.Hosting.Lifetime": "Information"
        }
    },
    "AllowedHosts": "*",
    "CookieAuth": {
        "ApplicationName": "MyApplication",
        "KeyLocation": "\\\\some-server\\some-share\\keys"
    }
}

_Layout.cshtml

@using System.Security.Claims

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>@ViewData["Title"] - WebApplication25</title>
    <link rel="stylesheet" href="~/lib/bootstrap/dist/css/bootstrap.min.css" />
    <link rel="stylesheet" href="~/css/site.css" />
</head>
<body>
    <header>
        <nav class="navbar navbar-expand-sm navbar-toggleable-sm navbar-light bg-white border-bottom box-shadow mb-3">
            <div class="container">
                <a class="navbar-brand" asp-area="" asp-page="/Index">WebApplication25</a>
                <button class="navbar-toggler" type="button" data-toggle="collapse" data-target=".navbar-collapse" aria-controls="navbarSupportedContent"
                        aria-expanded="false" aria-label="Toggle navigation">
                    <span class="navbar-toggler-icon"></span>
                </button>
                <div class="navbar-collapse collapse d-sm-inline-flex flex-sm-row-reverse">
                    <ul class="navbar-nav flex-grow-1">
                        <li class="nav-item">
                            <a class="nav-link text-dark" asp-area="" asp-page="/Index">Home</a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link text-dark" asp-area="" asp-page="/Privacy">Privacy</a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link text-dark" asp-area="" asp-page="/UserAccount">User Account</a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link text-dark" asp-area="" asp-page="/Admin">Admin</a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link text-dark" asp-area="" asp-page="/PhoenixAdmin">Phoenix Admin</a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link text-dark" asp-area="" asp-page="/Zero">Zero</a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link text-dark" asp-area="" asp-page="/AdminOrCityAdmin">Admin Or City Admin</a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link text-dark" asp-area="" asp-page="/Reset">Reset</a>
                        </li>
                    </ul>
                </div>
            </div>
        </nav>
    </header>
    <div class="container">
        <main role="main" class="pb-3">
            @RenderBody()
        </main>
    </div>

    <div>
        Machine Name: @Environment.MachineName
    </div>

    @{
        var claimsIdentity = User?.Identity as ClaimsIdentity;
        if (claimsIdentity != null)
        {
            <ul>
                @foreach (var claim in claimsIdentity.Claims)
                {
                    <li>@claim.Type | @claim.Value</li>
                }
            </ul>
        }
    }


    <footer class="border-top footer text-muted">
        <div class="container">
            &copy; 2020 - WebApplication25 - <a asp-area="" asp-page="/Privacy">Privacy</a>
            Hello @User?.Identity?.Name
        </div>
    </footer>

    <script src="~/lib/jquery/dist/jquery.min.js"></script>
    <script src="~/lib/bootstrap/dist/js/bootstrap.bundle.min.js"></script>
    <script src="~/js/site.js" asp-append-version="true"></script>

    @RenderSection("Scripts", required: false)
</body>
</html>

AccessDenied.cshtml

@page
@model WebApplication25.Pages.AccessDeniedModel
@{
    ViewData["Title"] = "AccessDenied";
}

<h1>AccessDenied</h1>

Login.cshtml

@page
@model WebApplication25.Pages.LoginModel
@{
    ViewData["Title"] = "Login";
}

<h1>Login</h1>

<form method="post">
    <input type="text" name="UserName" />
    <input type="password" name="Password" />
    <button type="submit">Submit</button>
</form>

Login.cshtml.cs

using Microsoft.AspNetCore.Authentication;
using Microsoft.AspNetCore.Authentication.Cookies;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Claims;
using System.Threading.Tasks;

namespace WebApplication25.Pages
{
    public class LoginModel : PageModel
    {
        [BindProperty]
        public string UserName { get; set; }
        [BindProperty]
        public string Password { get; set; }

        public void OnGet()
        {

        }

        public async Task/*<IActionResult>*/ OnPostAsync()
        {
            if (string.IsNullOrWhiteSpace(UserName))
            {
                throw new ApplicationException("UserName is required");
            }
            var claims = new List<Claim> { new Claim(ClaimTypes.Name, UserName) };

            if (UserName == "admin") { claims.Add(new Claim("IsAdmin", "1")); }

            if (UserName == "phoenix") { claims.Add(new Claim("CityAdmin", "Phoenix")); }

            var rand = new Random();
            var roles = Enumerable.Range(0, 100).Select(i => new
            {
                RoleCode = Guid.NewGuid().ToString().Substring(0, 3),
                OrgID = rand.Next(0, 1000)
            }).ToList();
            if (UserName == "zero") { roles.Add(new { RoleCode = "ZZ", OrgID = 0 }); }

            string rolesJSON = JsonConvert.SerializeObject(roles);
            if (UserName == "manyroles") { claims.Add(new Claim("Roles", rolesJSON)); }
            if (UserName == "zero") { claims.Add(new Claim("Roles", rolesJSON)); }

            var identity = new ClaimsIdentity(claims, CookieAuthenticationDefaults.AuthenticationScheme);
            var principal = new ClaimsPrincipal(identity);
            await HttpContext.SignInAsync(principal).ConfigureAwait(false);
        }
    }
}

Admin.cshtml

@page
@model WebApplication25.Pages.AdminModel
@{
    ViewData["Title"] = "Admin";
}

<h1>Admin</h1>

Admin.cshtml.cs

using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc.RazorPages;

namespace WebApplication25.Pages
{
    [Authorize(Policy = "Admin")]
    public class AdminModel : PageModel
    {
        public void OnGet()
        {

        }
    }
}

Others

Repeat for AdminOrCityAdmin.cshtml, PhoenixAdmin.cshtml, UserAccount.cshtml, Zero.cshtml, with the appropriate Authorize attributes representing the policies.

Notes

Adding the key location and app name to the Startup file gives you the ability to put the keys in a file share and share them among multiple servers in a web farm. On a single server, those lines are unnecessary, and it uses the default cookie encryption.

There are definitely better ways to do things like roles, but this example shows that you can dump everything into claims, retrieve them, and validate them with attributes, without a whole lot of work.

Is string an integer

SQL Server 2008 doesn’t yet have the TRY_PARSE function, so safely converting text to an integer is not as simple as you’d think.

There’s the ISNUMERIC function, but as people have pointed out, there are major issues with this function, and it doesn’t work for regular people like you and me. The real solution seems to be to add .e0 to the end of the string before checking, which handles these funky scenarios.

select cast(Val as int) [TheIntVal]
from dbo.MyTable
where isnumeric(Val + '.e0') = 1;

Insert with output

This can be an alternative to things like SCOPE_IDENTITY() or @@IDENTITY.

declare @result table (ID int);

-- dbo.Records has identity column ID
insert dbo.Records (PersonName)
output inserted.ID into @result
values (@PersonName);

select ID from @result; -- The newly created identity

GZip CLR Functions

Looks like SQL Server 2016 finally introduced a COMPRESS function. If you’re not quite there yet, then you can build a CLR function to compress/decompress data using GZip.

using System.Data.SqlTypes;
using System.IO;
using System.IO.Compression;
using System.Text;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction]
    public static SqlBytes GZipString(SqlString value)
    {
        using (var inputStream = new MemoryStream(Encoding.UTF8.GetBytes(value.Value)))
        {
            using (var outputStream = new MemoryStream())
            {
                Compress(inputStream, outputStream);
                return new SqlBytes(outputStream.ToArray());
            }
        }
    }

    [SqlFunction]
    public static SqlBytes GZipBinary(SqlBytes value)
    {
        using (var inputStream = new MemoryStream(value.Value))
        {
            using (var outputStream = new MemoryStream())
            {
                Compress(inputStream, outputStream);
                return new SqlBytes(outputStream.ToArray());
            }
        }
    }

    [SqlFunction]
    public static SqlString GUnZipToString(SqlBytes value)
    {
        using (var inputStream = new MemoryStream(value.Value))
        {
            using (var outputStream = new MemoryStream())
            {
                Decompress(inputStream, outputStream);
                return new SqlString(Encoding.UTF8.GetString(outputStream.ToArray()));
            }
        }
    }

    [SqlFunction]
    public static SqlBytes GUnZipToBinary(SqlBytes value)
    {
        using (var inputStream = new MemoryStream(value.Value))
        {
            using (var outputStream = new MemoryStream())
            {
                Decompress(inputStream, outputStream);
                return new SqlBytes(outputStream.ToArray());
            }
        }
    }

    private static void Compress(Stream inputStream, Stream outputStream)
    {
        using (var gzip = new GZipStream(outputStream, CompressionMode.Compress))
        {
            byte[] buffer = new byte[8192];
            int count;
            while ((count = inputStream.Read(buffer, 0, buffer.Length)) > 0)
            {
                gzip.Write(buffer, 0, count);
            }
        }
    }

    private static void Decompress(Stream inputStream, Stream outputStream)
    {
        using (var gzip = new GZipStream(inputStream, CompressionMode.Decompress))
        {
            byte[] buffer = new byte[8192];
            int count;
            while ((count = gzip.Read(buffer, 0, buffer.Length)) > 0)
            {
                outputStream.Write(buffer, 0, count);
            }
        }
    }
}

Drop Database

If you’re working locally, building a new app from scratch, you might be in a position where you want to wipe the database and start clean – but SQL Server may block you if you’ve got any open connections or any other issues blocking you.

This should set you as the only user of the database, which will clear the way for dropping it without ever getting an error:

use master;
go
if exists (select 1 from sys.databases where name = 'SomeDatabase')
begin
    alter database SomeDatabase set single_user with rollback immediate;
    drop database SomeDatabase;
end;
go

Disable all constraints

Sometimes you just need to disable all constraints on a database. Maybe you’re doing some massive data inserts or updates, and the constraints slow it down. Or maybe you just like living dangerously.

Don’t try this at home…

-- Disable all
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

-- Enable all
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'

Cursors

Here’s my preferred syntax for a simple one-way readonly cursor:

It uses the @variable syntax just because I find it weird in SQL Server to use a variable without the @ symbol. I don’t know if there are any meaningful differences in doing it this way, but this just feels more natural to me.

Many people will fetch upfront, then do a while @@fetch_status <> 0, and then fetch again at the end of the loop body. This example avoids that by fetching inside an infinite loop, and breaking when it stops fetching records.

declare @cur cursor;
set @cur = cursor local fast_forward for
    select * from
    (
        select 1 [a]
        union select 2
        union select 3
    ) x
    where x.a > 0;

declare @a int;
open @cur;
while 1 = 1
begin
    fetch next from @cur into @a;
    if @@fetch_status <> 0 break;

    print @a;
    -- do stuff
end;
close @cur;
deallocate @cur;

Cursor for update

When you’re working through a cursor, if you need to update each record one at a time, here’s a simple example:

if object_id('tempdb..#joe') is not null
    drop table #joe;

select a, b
into #joe
from
(
    select 1 a, null b
    union select 11 a, null b
    union select 111 a, null b
) x;

alter table #joe add primary key (a);

select * from #joe;

declare @cur cursor;

set @cur = cursor local forward_only for
    select a from #joe 
    for update of b;
open @cur;

declare @a int;
while 1=1
begin
    fetch next from @cur into @a;
    if @@fetch_status <> 0 break;

    update #joe set b = a * 2 where current of @cur;
end;

close @cur;
deallocate @cur;

select * from #joe;