Creating a SQL Server Managment Studio Add-in

I've spent the last day or so playing with an idea for a SSMS add-in. It's something I've been thinking about for a while now but will save the details for another post. What I want to document are my experiences so far as well as how convoluted and poorly-resourced the process of producing an add-in to SSMS is. Here are some of the things you should know about before strating on the journey. Hopefully some of this will save you some pain.

General Points:

• As far as I can tell, Microsoft don't really want you to integrate into SSMS - at least they've no interest in supporting the endeavour.
• Luckily, due SSMS's similarity to Visual Studio, you can broadly follow the same instructions offered up for extending Visual Studio with add-ins, something MS seem to actively encourage.
• There appear to be two ways of skinning this cat: Add-ins and VSExtensions. Add-ins, the route I've taken, are the older of the techniques but are where the majority of the resources are.

SSMS Integration:

• In order to load add-ins SSMS looks in folder locations specified here: HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\11.0_Config\AutomationOptions\LookInFolders for XML files giving details of the add-ins.
• You can configure things to happen only the first time the add-in is loaded by SSMS or every time the add-in is loaded: ext_ConnectMode.ext_cm_UISetup & ext_ConnectMode.ext_cm_Startup. I've still haven't quite grasped fully how they work.

Menu Generation:

• A CommandBar can contain a CommandBarPopup which needs to contain a CommandBar which is where you place CommandBarButton objects produced by Command objects.
• A Command can exist entirely independently of anything else. They don't have to be attached to CommandBarButton objects.
• A Command hangs around. It doesn't disappear after you close SSMS. To have it do so you need to remove it in the OnDisconnection of the IDTExtensibility2 interface.

Resources:

• http://solutioncenter.apexsql.com/the-art-and-science-of-creating-ssms-2012-add-ins-part-1-laying-the-foundation
  
• http://www.mztools.com/articles/2005/mz2005003.aspx
  
• http://www.ssmsboost.com/create-own-ssms-2012-add-in-sample-code-with-download

Uri.TryCreate C# (Part 2)

It occurred to the other other day that the .NET core is now open-source so I can actually go see the code involved in the Uri.TryCreate method.

It transpires that Uri is a partial class and the meat of the functionality is split over the two following files:

https://github.com/dotnet/corefx/blob/41e203011152581a6c65bb81ac44ec037140c1bb/src/System.Private.Uri/src/System/UriExt.cs

https://github.com/dotnet/corefx/blob/41e203011152581a6c65bb81ac44ec037140c1bb/src/System.Private.Uri/src/System/Uri.cs

The first thing that stuck me was there's an awful lot of code involved with attempting to create a Uri.

The TryCreate static methods which lives in the UriExt.cs class are deceptively simple. The TryCreate overload I'm interested in is the most simple of those - it really just passes the work off to a CreateHelper method which in turn passes off the work to a ParseScheme method located in the Uri.cs class.

ParseScheme appears to do some basic length checking before deferring to ParseSchemeCheckImplicitFile, which is where the main body of work seems to take place. As far as I can glean the following rules are being observed:

1. Whitespaces at the start are ignored
2. A url is valid if it is at least 2 characters, as long as the first of which is not a number, followed by a colon - unless those letters are a scheme you'd recognise (ftp, http, https, etc) at which intuitive validation kicks in.
3. UNC paths are valid e.g. //foo

Given these rules, the following odd strings pass as valid absolute URIs:

"aa:"

"fo:o"

"        fo:o"

"javascript:void()"

Maybe there are just so many esoteric schemes out there that robust validation is not viable.

Licences & Additional Build Agents in TeamCity

We use a combination of TeamCity and Octopus Deploy to automate our deployment and release process at work. We’ve been using the Professional Server Licence (free version) of TeamCity which allows for 20 build configurations (each configuration delineates one logical group of actions. e.g. pull code; build it; run unit tests) and 3 build agents (in our case Windows services which execute build configurations).

We recently bumped up against the 20 configuration limit and the boss dusted off the credit card to purchase a Build Agent Licence (£236 for 10 additional build steps and one additional build agent, at the time of writing). I dutifully entered in the licence key and fairly quickly learned a few things:

1. What you purchase are build agent slots not build agents.
2. You need to install build agents into your available build agent slots
3. The person who set-up our TeamCity build server originally had only installed on build agent (probably due to #4)
4. Precautions need to be taken when switching from a single build agent to multiple agents

Prior to purchasing the upgrade licence we had only one build agent installed (I assumed that was our limit on the free license) and I expected, rather naively as it turns out, to see another build agent appear when I entered the licence key. What you need to do, in fact, is install additional build agents into your free build agents slots. This is made a little more complicated by the fact you can’t just straightforwardly use the build agent installer (on Windows at least) to install more build agents; each build agent needs some tweaks to be made to its installation configuration files halfway through the installation. It feel like TeamCity have missed a trick here and could make customers’ lives easier by bundling up these config changes as part of the installer.

Excellent article detailing the process here: http://www.diaryofaninja.com/blog/2011/07/26/teamcity--when-1-build-agent-isnrsquot-enough

Clean Code

I'm re-reading Clean Code by Robert C. Martin and a few quotes from the intro have really stuck out:

"Quality is the result of a million selfless acts of care - not just of any great method that descends from the heavens".

I like this quote a lot. I'd take the word "selfless" out of it though. When I'm writing code I'm often thinking about Future Me and trying to make his life as easy as possible. Every act of care, or short-cut not taken, makes Future Me's life that bit easier. I want Future Me to not curse Past Me.

The idiom "a bad penny always turns up" is cited too. This one rings true. The amount of times a short-cut taken in a fatigued moment has come back to bite me is unnerving!

Uri.TryCreate C#

Came across a strange problem at work yesterday:

was returning true.

On further investigation strings such as "foo:bar" will also return true. It seems a colon separating a few characters is enough to satisfy TryCreate's criteria of a valid URI being contained in the string. I assume colons are related to the username:password prefixes some URIs contain, but I would have assumed that in itself would not be enough.

Anyone know why this would be the case?

Using the Google Analytics API in .NET

An old application which talks to Google Analytics (GA) in order to produce some custom reports started throwing an error the other day. It turns out Google has discontinued the old way in which you connect to their GA API. They also seem to have been through a few iterations of how you connect to them since the application was originally written.

Unfortunately, details on language specific implementation examples (C# / .NET in my case) don't seem to be easily available. The official documentation appears particularly confused - it's been updated so many times you're never sure whether you're looking at the latest and greatest or some deprecated version.

After some searching about I found the following guide really useful: http://www.daimto.com/google-analytics-api-v3-with-c/

In essence it seems to boil down to the following:

1) You used to connect to the Google Analytics API with a straight-forward username and password combination. There is a token returned after successful credentials being supplied, which apparently didn't need to be used, at least in the code I was looking at.

2) Connecting now is done by registering your application in the Google Developer Console and then generating a public/private key pair, of which you'll use the private key in your application.

3) This application will have an email associated with it which looks like xx-yy@developer.gserviceaccount.com. You'll need to make sure this account is authorised to read the Google Analytics accounts you want to pull info from.

4) At the time of writing, in order to utilise the asymmetric key combo you need to nuget in the GoogleAnalytics .net library: https://www.nuget.org/packages/Google.Apis.Analytics.v3/. You use the private key to create an X509Certificate2 which you pass to the a new instance of the AnalyticsService.

It's a shame that by far the best documentation and examples are by third-parties! Of which I do not count myself one.

Working With DateTime

I recently listened to this fascinating and terrifying Hanselminutes podcast on the perils of working with datetime: http://hanselminutes.com/485/the-problem-with-datetime-nodatime-with-matt-johnson

It reminded me of another really good video on why working with datetimes in programming is hard and dangerous and generally error prone: https://www.youtube.com/watch?v=-5wpm-gesOY