Killing all connections to a SQL Server Database

One issue I’ve run across frequently during development is restoring a database to a newer state. Often, when I want perform the restore, there are active connections to my development database, so restoring will fail.

Of course, StackOverflow had the answer to this, but I’ve searched for the solution to this problem enough times where it made sense for me to finally write it down.

Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

User AlexK posted this excellent solution:

For MS SQL Server 2012 and above

USE [master];

DECLARE @kill varchar(8000) = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('MyDB')

EXEC(@kill);

For MS SQL Server 2000, 2005, 2008

USE master;

DECLARE @kill varchar(8000); SET @kill = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'  
FROM master..sysprocesses  
WHERE dbid = db_id('MyDB')

EXEC(@kill); 

 

Fix: Visual Studio doesn’t remember last open documents

After installing Visual Studio 2017 a few months back, I noticed that some projects were loading strangely, while others loaded just fine. The two main issues I experienced were:

  • Documents I had open on my previous run of VS wouldn’t load upon running the Visual Studio 2017 application
  • Windows I had arranged in my multi-monitor layout were not loading where I expected them

A quick Stack Overflow search led me to the answer regarding the first: the .suo file had become corrupt. Once I knew that, the trick was finding the .suo file:

  1. From the directory containing your solution file (.sln), open the folder named “.vs”.
  2. In the “.vs” folder, open the folder that has a name matching your solution name.
  3. Inside the solution folder, there may be multiple folders, one for each version of Visual Studio
    1. v14 is for Visual Studio 2015
    2. v15 is for Visual Studio 2017

These folders will contain your .suo file, which is hidden by default in Windows, so you need to enable “Show hidden files, folders, and drives” in your Folder options in order to see it. For instructions on that (Win 7,8, or 10), see the following article: https://www.howtogeek.com/howto/windows-vista/show-hidden-files-and-folders-in-windows-vista/

I still haven’t found a solution to my second issue (I will definitely write about it if I find one).

What the SUO (Solution User Options) file controls

After solving my problem, I decided to take a look at the responsibilities of the .suo file. Microsoft’s documentation (VS 2015 version – 2017 isn’t available at the time of this writing) isn’t very forthcoming in detailing what exactly the SUO is doing. Based on digging around on the web, it seems that the following are its responsibilities (among others):

  • Remembers last open files
  • Remembers breakpoints
  • Remembers expanded nodes in solution explorer
  • Remembers startup project
  • Remembers last open tool windows and their positions
  • Remembers watch window contents

The file is encoded and not human-readable, so it’s not something you can simply hack around with like you can a solution (.sln) or project (.xxproj) file. It should not be added to version control.

How to Fix credential validation issue on Azure WebJob renewal of Let’s Encrypt Certificate

A while back, I posted about setting up SSL encryption for free with Azure and Let’s Encrypt: Let’s Encrypt + Azure = Win!

This has been working smoothly for me since I set it up, but I noticed that errors started popping up in the log recently. Here is part of the stack trace:

Microsoft.Azure.WebJobs.Host.FunctionInvocationException: Microsoft.Azure.WebJobs.Host.FunctionInvocationException: Exception while executing function: Functions.RenewCertificate —> Microsoft.IdentityModel.Clients.ActiveDirectory.AdalServiceException: AADSTS70002: Error validating credentials. AADSTS50012: Invalid client secret is provided. Trace ID: 958b11ab-839d-4a8d-97e6-fad1c3df0300 Correlation ID: e3f7c035-8978-4aa2-b01a-5c8fc74661ac Timestamp: 2017-05-31 14:14:26Z —> System.Net.WebException: The remote server returned an error: (401) Unauthorized. at System.Net.HttpWebRequest.GetResponse() at Microsoft.IdentityModel.Clients.ActiveDirectory.HttpWebRequestWrapper.

It turns out that the API Key I had setup for my application registration had expired. I had to create a new key with no expiration and then update my Web Applications’ settings with the new Client secret. The exact steps I took are listed below:

  1. Login to Azure
  2. Navigate to “App Registrations”
  3. Choose the Registration you need to update
  4. Click the “settings” icon (or “All Settings” button)
  5. Choose “Keys” under API Access
  6. Type a description into the new row, choose “Never” under the duration drop down and then hit “Save” above.
  7. Once saved, copy the value (it won’t be visible again if you don’t copy it now)
  8. (Optional) delete your old key
  9. Navigate to the Azure App Service that has the web job that registers your SSL certificate
  10. Choose “Application Settings” from the menu
  11. Scrolling down to where you have a setting titled something like “letsencrypt:ClientSecret” (assuming you did the setup as in the article linked at the top) and paste the value you copied into the second text box
  12. Click “Save” above

Once you’re done, the web job should work the next time it runs. For another explanation with some pictures of the process, check out this blog post here: Let’s Encrypt on Azure Web Apps – Key Expiration Issue.

EntityFramework – Grouping by Date Ranges

If you’ve ever created an outstanding balance report or other report that deals with aggregating data into date ranges, you’ll know that it isn’t immediately obvious how to structure your query, whether using SQL or LINQ (at least, it wasn’t to me).

My initial thought was to run multiple queries (one for each time range) and munge the results together. However, an elegant solution is to use SQL’s CASE expression to group date ranges together.

Let’s say you wanted a report that summed the amount of unpaid invoices in 20 day groupings (0-19 days past due, 20-39 past due, 40+). You could write something like this:

SELECT DaysSinceDueRange, SUM(Amount)
FROM (SELECT CASE WHEN GETDATE() - DueDate < 20 THEN 0
		  WHEN GETDATE() - DueDate BETWEEN 20 AND 39 THEN 20
		  WHEN GETDATE() - DueDate > 39 THEN 40
             END AS DaysSinceDueRange,
             Amount
       FROM Invoices
       WHERE Unpaid=1) inv
GROUP BY DaysSinceDueRange

This is really elegant, but then the question becomes how to do this with an ORM like EntityFramework. There are a couple of tricks required here:

  1. To do the date comparisons, EntityFramework requires the usage of System.Data.Entity.DbFunctions.DiffDays method (in EF 6 – it used to be in System.Data.Objects.EntityFunctions). If you try to do something like (DateTime.Now – invoice.DueDate).TotalDays, you’ll get an exception “DbArithmeticExpression arguments must have a numeric common type” because the subtraction operator is not defined for Dates in SqlServer.
  2. To do CASE / WHEN / THEN / END in EntityFramework, you have to make use of a lot of ternary operators. It can be kind of ugly, but if you write your code well enough, it should be fairly readable (or at least as readable as the SQL expression).

Here is an example of the SQL above translated into LINQ:

Context.Set<Invoice>()
       .Where(inv => inv.Unpaid)
       .Select(inv => new
       {
           DaysSinceDueRange = DbFunctions.DiffDays(inv.DueDate, DateTime.Today) < 20 ? 0 :
                               DbFunctions.DiffDays(inv.DueDate, DateTime.Today) >= 20 && 
                                   DbFunctions.DiffDays(inv.DueDate, DateTime.Today) < 40 ? 20 : 
                               40,
           Amount = inv.Amount
       }).GroupBy(inv => inv.DaysSinceDueRange)
       .Select(g => new
       {
           DaysSinceDueRange = g.Key.DaysSinceDueRange,
           Amount = g.Sum(inv => inv.Amount)
       });

Of course, you can get more complicated in a hurry, but I think this is a pretty elegant way to handle grouping data by date ranges.

EntityFramework Performance and IEnumerable vs IQueryable

Working in the .Net world, you get pretty used to dealing with IEnumerable collections. However, you have to be aware of performance issues that can arise when using them with EntityFramework. Sometimes I forget about IQueryable because LINQ to Entities obfuscates much of the difference of retrieving objects from a database vs dealing with an in-memory collection and IQueryable is pretty specific to dealing with querying a database.

When using the Repository pattern, one of the things I love to do is add a flexible “Find” method to the repository. Below is an example:

public partial class Order
{
    public int ID { get; set; }
    public string CustomerItemNumber { get; set; }
    public DateTime? ShipDate { get; set; }
    public int OrderTypeID { get; set; }
}

public class FindOrdersReqeust
{
    public IEnumerable<int> OrderIDs { get;set; }
    public IEnumerable<string> CustomerOrderNumbers { get; set; }
    public IEnumerable<int> OrderTypeIDs { get; set; }
    public bool? HasShipDate { get; set; }
    public DateTime? ShipDateBefore { get; set; }
    public DateTime? ShipDateAfter { get; set; }
    
    public FindOrdersReqeust()
    {
        OrderIDs = new List<int>();
        CustomerOrderNumbers = new List<string>();
        OrderTypeIDs = new List<int>();
    }
}

//I would normally implement an interface here, but for the sake of brevity am excluding from this example
public class OrderRepository
{
    private IDbContext context;
    public OrderRepository(IDbContext context)
    {
        this.context = context;
    }

    public IEnumerable<Order> Find(FindOrdersRequest request)
    {
        IEnumerable<Order> orders = context.Set<Order>().AsEnumerable();
        if(request.OrderIDs.Any())
        {
            orders = orders.Where(o => request.OrderIDs.Contains(o.ID));
        }
        if(request.CustomerOrderNumbers.Any())
        {
            orders = orders.Where(o => request.CustomerOrderNumbers.Any(x => o.CustomerOrderNumber.Equals(x)));
        }
        if(request.OrderTypeIDs.Any())
        {
            orders = orders.Where(o => request.OrderTypeIDs.Contains(o.OrderTypeID));
        } 
        if(request.ShipDateAfter.HasValue)
        {
            orders = orders.Where(o => o.ShipDate.HasValue && o.ShipDate >= request.ShipDateAfter);
        }
        if (request.ShipDateBefore.HasValue)
        {
            orders = orders.Where(o => o.ShipDate.HasValue && o.ShipDate <= request.ShipDateBefore);
        }
        if(request.HasShipDate.HasValue)
        {
            orders = orders.Where(o => o.ShipDate.HasValue == reqeust.HasShipDate.Value);
        }

        return orders;
    }
}

public class OrderService
{
    private OrderRepository orderRepository;
    public OrderService(OrderRepository orderRepository)
    {
        this.orderRepository = orderRepository;
    }

    public void GetUnshippedOrders()
    {
        return orderRepository.Find(new FindOrdersRequest()
        {
            HasShipDate = false
        }).ToList();
    }
}

The major problem with this code is highlighted in the example above – namely that AsEnumerable() call on the context.Set<T> will enumerate every row from the database in full. The Sql generated will be equivalent to a SELECT *, and will probably look something like:

SELECT [Extent1].[ID] AS [ID], 
    [Extent1].[CustomerOrderNumber] AS [CustomerOrderNumber], 
    [Extent1].[ShipDate] AS [ShipDate]
    FROM [dbo].[Orders] AS [Extent1]

Now, you might not notice if you have 10 rows, but if you have 1,000,000, you’ll notice as your application burns to the ground and consumes all the memory on whatever server it’s running on.

So, an easy fix is to change that one line to IQueryable / AsQueryable() like so:

IQueryable<Order> orders = context.Set<Order>().AsQueryable();

Now we get the benefits of deferred execution until ToList is called on the results of the Find method from OrderRespository. The SQL generated will now be something like:

SELECT [Extent1].[ID] AS [ID], 
    [Extent1].[CustomerOrderNumber] AS [CustomerOrderNumber], 
    [Extent1].[ShipDate] AS [ShipDate],
    [Extent1].[OrderTypeID] AS [OrderTypeID]
    FROM [dbo].[Orders] AS [Extent1]
    WHERE [Extent1].[ShipDate] IS NULL

This is a huge improvement already, but it can be much better than this. In the OrderRepository class, if we also make the return type IQueryable, we can then further query the database before pulling the results into memory.

public IQueryable<Orders> Find(FindOrdersRequest request)
{
    //the rest of the method remains the same
}

This distinction is important, and I will provide an example.

After I add this “Find” functionality to my repositories, I tend to build reports using those methods, which frequently utilize .GroupBy() after filtering. If we were to leave the Find method as returning an IEnumerable<Order> collection, we would find that the SQL generated would not be what we wanted.

For example, let’s say I now wanted a report that showed the number of shipped and unshipped orders by OrderTypeID. I would add a method to the OrderService as such:

//assume that an object ReportItem exists with properties as defined below
public IEnumerable<ReportItem> GetUnshippedOrdersByTypeReport()
{
    var report = new List<ReportItem>();
    var results = orderRepository.Find(new FindOrdersRequest(){ HasShipDate = false })
                                 .GroupBy(order => new 
                                 {
                                     OrderTypeID: order.OrderTypeID
                                 })
                                 .Select(g => new
                                 {
                                     OrderTypeID: g.Key.ID,
                                     ShippedOrderCount: g.Sum(x => x.ShipDate.HasValue),
                                     UnshippedOrderCount: g.Sum(x => !x.ShipDate.HasValue)   
                                 });
    foreach(var result in results)
    {
        report.Add(new ReportItem()
        {
            OrderTypeID: result.OrderTypeID,
            ShippedOrderCount: result.ShippedOrderCount,
            UnshippedOrderCount: result.UnshippedOrderCount,
        });
    }
   
    return report;
}

With this code, the benefits of using IQueryable in the repository are clear over IEnumerable.

If we leave the repository with IEnumerable, the SQL generated will be done in two phases:

  1. The filtering of the “FindOrdersRequest” will be executed as the SQL statement above and the results will be stored into a temporary IEnumerable collection
  2. The Group By operation will operate on this temporary collection. More trips to the database will be taken if any navigation properties are referenced in the GroupBy (there are none in this example)

If we change the repository to use IQueryable, the SQL generated will be done in a single, neat statement. It will filter and perform the group by at once, resulting in much better performance. Another performance benefit is that we are projecting specific columns and not populating an entire Order object with every field. For this trivial example, it doesn’t make much of a difference, but if you’re dealing with tables/objects that have many columns/properties, you will notice. If you’re deploying to a cloud-based environment, you know that compute time and efficiency matter a lot, so following best practices for performance will help you out a lot in that respect.

 

Asp.Net Core appsettings tips

I’ve recently had the opportunity to work on a new project where I was able to use Asp.Net Core for the first time. Well, not completely – I’ve contributed to an open source project that has been using .Net Core for some time, when it was called DNX or ASPNET 5. Anyway, the work I did there really was focused on writing code for the application, not configuring the infrastructure.

A lot has changed, but the changes are largely for the better. There are a few things that tripped me up, so I figured I’d write about them here.

AppSettings have gone JSON

This in and of itself isn’t much of a revelation, but I, for one, am glad to have JSON configuration over XML. In the Startup.cs file, appsettings are configured by default as such:

public Startup(IHostingEnvironment env)
{
    var builder = new ConfigurationBuilder()
        .SetBasePath(env.ContentRootPath)
        .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
        .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true)
        .AddEnvironmentVariables();
    Configuration = builder.Build();
}

Just like before, when we had Web.config, Web.Release.Config, Web.{EnvironmentName}.config, any environment configuration will be applied on top of the rules defined appsettings.json file. So, if you have an appsettings.json file that looks like:

{
    "MyVariable1": "value1",
    "MyVariable2": "value2"
}

and then you define a file appsettings.production.json that looks like:

{
    "MyVariable1": "productionValue",
}

The production file’s value will be used for MyVariable1 when the application is running in a production environment, as expected.

Accessing appsettings

The easiest way to access a value from your appsettings file is to use Configuration.GetValue:

Configuration.GetValue("MyVariable1", "");

The above will retrieve the value for MyVariable1 or an empty string if there is no key found for MyVariable. The nice thing is you don’t get an exception if a key isn’t found, but this could be an issue if you were expecting a key and get the default value instead.

If your appsettings file has nested objects like this:

{
    "Logging": {
        "IncludeScopes": false,
        "LogLevel": {
            "Default": "Debug",
            "System": "Information",
            "Microsoft": "Information"
        }
    }
}

you can retrieve values by using Configuration.GetValue(“Logging:LogLevel:Default”);

Personally, I don’t like to use magic strings – I prefer to use a strongly typed configuration.

Strongly Typed appsettings

Rick Strahl has a very good article about Strongly typed appsettings, but I will cover the basics. In a nutshell, you need to do two steps to make this work:

  1. Create a class that has all of the corresponding properties of your appsettings (or just a subsection of your appsettings, as I will show below)
  2. Wire up your class by calling the services.Configure<T> method In the ConfigureServices method of your Startup.cs class

Let’s use the following appsettings.json file as an example:

{
    "MySettings" : {
        "AdminEmail" : "admin@email.com",
        "ErrorPath" : "/Home/Error"
    }
}

All we need to complete step 1 is to have a corresponding class for these settings. Here is the corresponding example:

public class MySettings
{
    public string AdminEmail { get; set; }
    public string ErrorPath { get; set; }
}

Now, in our Startup.cs class, we can add the following to our ConfigureServices method:

public void ConfigureServices(IServiceCollection services)
{
    services.AddMvc();

    services.Configure<MySettings>(Configuration.GetSection($"{nameof(MySettings)}"));
}

That’s it. Now, we can simply inject MySettings into our MVC/WebAPI controller constructors and Web API will be able to inject that dependency for us.

Note that in this example we called Configuration.GetSection and gave it the name of our section/class – if you only listed the keys AdminEmail and ErrorPath at the root of the appsettings file (without any nested objects), you could have done the same by calling just services.Configure<MySettings>(Configuration);

Using appsettings in your Startup.cs class

One gotcha that had me stumped for a little while was trying to use some of my appsettings configurations to provide configurations in my Startup.cs class. The trick here is using the Bind method on configuration. Here is a good example of what I mean: a lot of tutorials and examples will show configuring exception handling as:

public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
{
    app.UseExceptionHandler("/Home/Error");
}

I like to make that route configurable in my appsettings, so here is how to do that:

public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
{
    var mySettings = new MySettings();
    Configuration.GetSection($"{nameof(MySettings)}").Bind(mySettings);

    app.UseExceptionHandler(mySettings.ErrorPath);
}