Posts
48
Comments
148
Trackbacks
0
Saturday, May 17, 2014
That Escalated Quickly

I have been working remotely out of my home for over 4 years now. All of my coworkers during that time have also worked remotely. Lots of folks have written about the challenges inherent in facilitating communication on remote teams and strategies for overcoming them. A popular theme around this topic is the notion of “escalating communication”. In this context “escalating” means taking a conversation from one mode of communication to a different, higher fidelity mode of communication. Here are the five modes of communication I use at work in order of increasing fidelity:

  1. Email – This is the “lowest fidelity” mode of communication that I use. I usually only check it a few times a day (and I’m trying to check it even less frequently than that) and I only keep items in my inbox if they represent an item I need to take action on that I haven’t tracked anywhere else.
  2. Forums / Message boards – Being a developer, I’ve gotten into the habit of having other people look over my code before it becomes part of the product I’m working on. These code reviews often happen in “real time” via screen sharing, but I also always have someone else give all of the changes another look using pull requests. A pull request takes my code and lets someone else see the changes I’ve made side-by-side with the existing code so they can see if I did anything dumb. Pull requests can facilitate a conversation about the code changes in an online-forum like style. Some teams I’ve worked on also liked using tools like Trello or Google Groups to have on-going conversations about a topic or task that was being worked on.
  3. Chat & Instant Messaging  - Chat and instant messaging are the real workhorses for communication on the remote teams I’ve been a part of. I know some teams that are co-located that also use it pretty extensively for quick messages that don’t warrant walking across the office to talk with someone but reqire more immediacy than an e-mail. For the purposes of this post I think it’s important to note that the terms “chat” and “instant messaging” might insinuate that the conversation is happening in real time, but that’s not always true. Modern chat and IM applications maintain a searchable history so people can easily see what might have been discussed while they were away from their computers.
  4. Voice, Video and Screen sharing – Everyone’s got a camera and microphone on their computers now, and there are an abundance of services that will let you use them to talk to other people who have cameras and microphones on their computers. I’m including screen sharing here as well because, in my experience, these discussions typically involve one or more people showing the other participants something that’s happening on their screen. Obviously, this mode of communication is much higher-fidelity than any of the ones listed above. Scheduled meetings are typically conducted using this mode of communication.
  5. In Person – No matter how great communication tools become, there’s no substitute for meeting with someone face-to-face. However, opportunities for this kind of communcation are few and far between when you work on a remote team.

When a conversation gets escalated that usually means it moves up one or more positions on this list. A lot of people advocate jumping to #4 sooner than later. Like them, I used to believe that, if it was possible, organizing a call with voice and video was automatically better than any kind of text-based communication could be. Lately, however, I’m becoming less convinced that escalating is always the right move.

Working Asynchronously

Last year I attended a talk at our local code camp given by Drew Miller. Drew works at GitHub and was talking about how they use GitHub internally. Many of the folks at GitHub work remotely, so communication was one of the main themes in Drew’s talk. During the talk Drew used the phrase, “asynchronous communication” to describe their use of chat and pull request comments. That phrase stuck in my head because I hadn’t heard it before but I think it perfectly describes the way in which remote teams often need to communicate. You don’t always know when your co-workers are at their computers or what hours (if any) they are working that day. In order to work this way you need to assume that the person you’re talking to might not respond right away. You can’t always afford to wait until everyone required is online and available to join a voice call, so you need to use text-based, persistent forms of communication so that people can receive and respond to messages when they are available. Going back to my list from the beginning of this post for a second, I characterize items #1-3 as being “asynchronous” modes of communication while we could call items #4 and #5 “synchronous”. When communication gets escalated it’s almost always moving from an asynchronous mode of communication to a synchronous one. Now, to the point of this post: I’ve become increasingly reluctant to escalate from asynchronous to synchronous communication for two primary reasons:

1 – You can often find a higher fidelity way to convey your message without holding a synchronous conversation

2 - Asynchronous modes of communication are (usually) persistent and searchable.

You Don’t Have to Broadcast Live

Let’s start with the first reason I’ve listed. A lot of times you feel like you need to escalate to synchronous communication because you’re having difficulty describing something that you’re seeing in words. You want to provide the people you’re conversing with some audio-visual aids to help them understand the point that you’re trying to make and you think that getting on Skype and sharing your screen with them is the best way to do that. Firing up a screen sharing session does work well, but you can usually accomplish the same thing in an asynchronous manner. For example, you could take a screenshot and annotate it with some text and drawings to illustrate what it is you’re seeing. If a screenshot won’t work, taking a short screen recording while your narrate over it and posting the video to your forum or chat system along with a text-based description of what’s in the recording that can be searched for later can be a great way to effectively communicate with your team asynchronously.

I Said What?!?

Now for the second reason I listed: most asynchronous modes of communication provide a transcript of what was said and what decisions might have been made during the conversation. There have been many occasions where I’ve used the search feature of my team’s chat application to find a conversation that happened several weeks or months ago to remember what was decided. Unfortunately, I think the benefits associated with the persistence of communicating asynchronously often get overlooked when people decide to escalate to a in-person meeting or voice/video call. I’m becoming much more reluctant to suggest a voice or video call if I suspect that it might lead to codifying some kind of design decision because everyone involved is going to hang up the call and immediately forget what was decided. I recognize that you can record and archive these types of interactions, but without being able to search them the recordings aren’t terribly useful.

When and How To Escalate

I don’t mean to imply that communicating via voice/video or in person is never a good idea. I probably jump on a Skype call with a co-worker at least once a day to quickly hash something out or show them a bit of code that I’m working on. Also, meeting in person periodically is really important for remote teams. There’s no way around the fact that sometimes it’s easier to jump on a call and show someone my screen so they can see what I’m seeing. So when is it right to escalate? I think the simplest way to answer that is when the communication starts to feel painful. Everyone’s tolerance for that pain is different, but I think you need to let it hurt a little bit before jumping to synchronous communication.

When you do escalate from asynchronous to synchronous communication, there are a couple of things you can do to maximize the effectiveness of the communication:

  1. Takes notes – This is huge and yet I’ve found that a lot of teams don’t do this. If you’re holding a meeting with  > 2 people you should have someone taking notes. Taking notes while participating in a meeting can be difficult but there are a few strategies to deal with this challenge that probably deserve a short post of their own. After the meeting, make sure the notes are posted to a place where all concerned parties (including those that might not have attended the meeting) can review and search them.
  2. Persist decisions made ASAP – If any decisions were made during the meeting, persist those decisions to a searchable medium as soon as possible following the conversation. All the teams I’ve worked on used a web-based system for tracking the on-going work and a backlog of work to be done in the future. I always try to make sure that all of the cards/stories/tasks/whatever in these systems always reflect the latest decisions that were made as the work was being planned and executed. If held a quick call with your team lead and decided that it wasn’t worth the effort to build real-time validation into that new UI you were working on, go and codify that decision in the story associated with that work immediately after you hang up. Even better, write it up in the story while you are both still on the phone. That way when the folks from your QA team pick up the story to test a few days later they’ll know why the real-time validation isn’t there without having to invoke yet another conversation about the work.

Communicating Well is Hard

At this point you might be thinking that communicating asynchronously is more difficult than having a live conversation. You’re right: it is more difficult. In order to communicate effectively this way you need to very carefully think about the message that you’re trying to convey and craft it in a way that’s easy for your audience to understand. This is almost always harder than just talking through a problem in real time with someone; this is why escalating communication is such a popular idea. Why wouldn’t we want to do the thing that’s easier? Easier isn’t always better. If you and your team can get in the habit of communicating effectively in an asynchronous manner you’ll find that, over time, all of your communications get less painful because you don’t need to re-iterate previously made points over and over again. If you communicate right the first time, you often don’t need to rehash old conversations because you can go back and find the decisions that were made laid out in plain language. You’ll also find that you get better at doing things like writing useful comments in your code, creating written documentation about how the feature that you just built works, or persuading your team to do things in a certain way.

Posted On Saturday, May 17, 2014 2:11 PM | Comments (0)
Wednesday, January 8, 2014
Limiting Variables to Ease Learning

To date I’ve primarily been a “.NET developer”; which is to say that most of the code I’ve written for work or for fun has been C# or VB .NET running in a Windows environment. I’ve often wanted to work with other languages or platforms, but have stuck to what I know because it’s paid the bills and I’ve always been able to find interesting things to work on in the .NET space. I have dabbled in some other stuff, but have typically found it difficult to overcome the inertia that’s kept me tied to .NET on Windows. I’ve recently come to realize that a lot of this inertia is rooted in the in the sheer number of ancillary things that you tend to need to learn when you want to experiment with a new language or platform.

A High Barrier To Entry

For example, if I wanted to learn about writing iOS apps in Objective-C I’d probably need to pick up OS X, XCode, Cocoa, and Objective-C. Basically, I’d need to pick up an entirely new technology stack to work this way and that is a very daunting proposition to me. If I wanted to pick up, say, Ruby, I’d be in slightly better shape because Ruby already runs on Windows, but I’d still need to pick an editor or IDE to work with and learn its quirks to find a good “code-test-run-repeat” cadence. I’m certain that I could get it all figured out if I really had to (e.g. if my job required it), but in the limited spare time I have for “software-related side projects” (which, in addition to learning new languages, includes blogging, contributing to open source, reading technical books, etc.) I simply can’t find enough uninterrupted time to wrap my head around more than one of those things at a time. To me it’s very suiting that the word “experiment” often gets used when referring to learning something new because, just like any experiment, there can be many variables at play: operating system, editor/IDE, framework, environment, etc. I’m not opposed to learning about new operating systems, frameworks, or IDEs, but I’ve found that I’m far more likely to be successful at learning something new if I can limit those variables and try to learn one new thing at a time.

Visual Studio is a Crutch

Visual Studio is a crutch. Visual Studio is a terribly large and sometimes awkward crutch. Most of the time it works and lets me write, compile, run, and debug code with great ease, but sometimes it slips and lets me fall on the pavement. Nonetheless, it’s “the devil I know”, and I do like using it for the most part. I’ve been very excited recently by the recent releases of things like Python Tools for Visual Studio and Node.js Tools for Visual Studio. Being able to use Visual Studio to play around with Python or Node.js makes them a lot more approachable for me and lets me focus on on learning the ins and outs of those languages and platforms themselves in an IDE and operating environment that I’m already familiar with. After downloading the Python tools a little while back I finally sat down and hacked out a short Python script that did more than just “hello world” and found it immensely satisfying. Being able to pick up a new language and actually build something that is even the slightest bit useful to you is a great catalyst to continuing to work with that language and keeping it mind for projects down the road. Are are probably better editors or IDEs to use with Python and if I were to embark on a Python project of any significant size I’d probably bite the bullet and learn how write Python the “right way”, but without being able to work with it in a familiar environment I would have never done anything useful with it at all.

Next?

My experiment with Python in Visual Studio went so well that I’m dabbling a bit with Node.js in Visual Studio as well. I’ve been interested in the Node.js project for awhile now, and have even put together the obligatory “hello world” app, but haven’t really been able to go much further with it yet. I’m hoping that the Visual Studio tools for Node.js work as advertised and let me get my feet wet with that platform.

Posted On Wednesday, January 8, 2014 8:06 PM | Comments (0)
Thursday, November 14, 2013
De-Duping Files With Powershell

I often have to do little one-off tasks that could be manually, but almost always are better done with a small utility application or script. I don’t have a very strong scripting background, so I’ve been trying to force myself to use (and therefore get more comfortable with) Powershell whenever possible. This week I had a perfect little one-off task that Powershell was a great fit for: de-duping a set of files.

I had a set of ~1000 files in a directory. They each had unique names, but some of them had duplicate contents. I needed to whittle that down to a set of complete unique files, removing any that were duplicates. I wasn’t able to reliably determine which files had duplicate contents based on file meta data like size or date modified. The approach I wanted to implement was to loop over all of the files and for each one:

  • Compute a hash of the file contents
  • If I hadn’t yet seen that hash value, add it to a hash table along with the full name of the file
  • If I had already seen that hash value, do nothing and go on to the next file.
  • After all files had been looked at, copy the files that were added to the hash table to another folder, which would represent the set of unique files.

Simple, right? This would be very easy for me to bang out with any .NET language, but I thought it was a good opportunity to use Powershell and maybe learn something new.

The PowerShell code needed to iterate through a list of files in a folder is pretty straightforward, and my hash table needs were easily met by using the .NET Dictionary<TKey, TValue> type. The only tricky part was calculating the hash of each file’s contents for comparison purposes. Luckily for me the PowerShell Community Extensions (PSCX) has a Get-Hash cmdlet that makes this easy. Here’s the full script:

Import-Module pscx

$inputFolder = "C:\input"
$outputFolder = "C:\output"

$lookupTable = New-Object 'System.Collections.Generic.Dictionary[String,String]'

Get-ChildItem $inputFolder | Sort-Object -Property "Name" | Foreach-Object {
    $currentFileHash = Get-Hash $_.FullName

    Write-Host $currentFileHash
    if($lookupTable.ContainsKey($currentFileHash)){
        return #I still don’t completely grok control structures in powershell
    }
    $lookupTable.Add($currentFileHash, $_.FullName)
}

$lookupTable.GetEnumerator() | Foreach-Object {
    Copy-Item $_.Value $outputFolder
}

The only part of this script that made me scratch my head a bit was needing to use the ‘return’ statement where I would normally use a ‘continue’ in C#. I still don’t completely grok flow control statements in PowerShell as I always seem to get this wrong on the first try. My understanding of what’s happening here is that the code is returning from the function that is being invoked by the Foreach-Object cmdlet for each item in the collection. Returning from that function simply moves control to the next item in the collection.

Posted On Thursday, November 14, 2013 9:16 PM | Comments (2)
Wednesday, October 30, 2013
Simple Excel Export with EPPlus

Anyone I’ve ever met who works with an application that sits in front of a lot of data loves it when they can get that data exported to an Excel file for them to mess around with offline. As both developer and end user of a little website project that I’ve been working on, I found myself wanting to be able to get a bunch of the data that the application was collecting into an Excel file. The great thing about being both an end user and a developer on a project is that you can build the features that you really want! While putting this feature together I came across the fantastic EPPlus library. This library is certainly very well known and popular, but I was so impressed with it that I thought it was worth a quick blog post.

This library is extremely powerful; it lets you create and manipulate Excel 2007/2010 spreadsheets in .NET code with a high degree of flexibility. My only gripe with the project is that they are not touting how insanely easy it is to build a basic Excel workbook from a simple data source. If I were running this project the approach I’m about to demonstrate in this post would be front and center on the landing page for the project because it shows how easy it really is to get started and serves as a good way to ease yourself in to some of the more advanced features.

The website in question uses RavenDB, which means that we’re dealing with POCOs to model the data throughout all layers of the application. I love working like this so when it came time to figure out how to export some of this data to an Excel spreadsheet I wanted to find a way to take an IEnumerable<T> and just have it dumped to Excel with each item in the collection being modeled as a single row in the Excel worksheet. Consider the following class:

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal HourlyRate { get; set; }
    public DateTime HireDate { get; set; }
}

Now let’s say we have a collection of these represented as an IEnumerable<Employee> and we want to be able to output it to an Excel file for offline querying/manipulation. As it turns out, this is dead simple to do with EPPlus. Have a look:

public void ExportToExcel(IEnumerable<Employee> employees, FileInfo targetFile)
{
    using (var excelFile = new ExcelPackage(targetFile))
    {
        var worksheet = excelFile.Workbook.Worksheets.Add("Sheet1");
        worksheet.Cells["A1"].LoadFromCollection(Collection: employees, PrintHeaders: true);
        excelFile.Save();
    }
}

That’s it. Let’s break down what’s going on here:

  • Create a ExcelPackage to model the workbook (Excel file). Note that the ‘targetFile’ value here is a FileInfo object representing the location on disk where I want the file to be saved.
  • Create a worksheet within the workbook.
  • Get a reference to the top-leftmost cell (addressed as A1) and invoke the ‘LoadFromCollection’ method, passing it our collection of Employee objects. Behind the scenes this is reflecting over the properties of the type provided and pulling out any public members to become columns in the resulting Excel output. The ‘PrintHeaders’ parameter tells EPPlus to grab the name of the property and put it in the first row.
  • Save the Excel file

All of the heavy lifting here is being done by the ‘LoadFromCollection’ method, and that’s a good thing. Now, this was really easy to do, but it has some limitations. Using this approach you get a very plain, un-styled Excel worksheet. The column widths are all set to the default. The number format for all cells is ‘General’ (which proves particularly interesting if you have a DateTime property in your data source). I’m a “no frills” guy, so I wasn’t bothered at all by trading off simplicity for style and formatting. That said, EPPlus has tons of samples that you can download that illustrate how to apply styles and formatting to cells and a ton of other advanced features that are way beyond the scope of this post.

Posted On Wednesday, October 30, 2013 9:50 PM | Comments (2)
Sunday, September 29, 2013
Ignoring Your Test Suite

If you practice any form of test-driven development or automated testing you’ve likely come across some kind of test framework. If you work with .NET you might have used nUnit, MSTest, or xUnit. These are all great tools that let you decorate classes and methods with attributes to define a test suite. These frameworks also integrate nicely with test runners that can be used to run your tests as part of your normal “check-in dance” and/or continuous integration efforts. While unit testing frameworks are indispensible for doing nearly any kind of automated testing, they all (or at least nearly all) have a terrible feature that encourages bad habits: the ‘ignore’ attribute. We’ve been having a little discussion surrounding a pull request on the SpecEasy project (an OSS test framework that I occasionally contribute to) about this subject, and I thought I’d jot down some thoughts. In short: I don’t think that a test framework should provide a built-in means to ignore tests because it encourages bad habits.

Needles In The Haystack

The ‘ignore’ attribute provided by a unit testing framework lets you take a single test (or spec or fact or whatever your framework likes to call the smallest unit of your test suite that can pass or fail) and tells the test runner to skip over it. The test runner will typically report the total number of tests that passed, failed, and were ignored/skipped when the test run is complete. The test runners that have GUIs showing red and green status indicators for passed and failed tests might also have a separate indicator for ones that were ignored. Here’s how a test suite with an ignored tests looks in the test runner GUI that comes with ReSharper:

image

You might notice that, for all intents and purposes, this looks like a 100% successful test run. The status bar at the top is all green, and we have a nice little green check mark next to the document tab representing this test run. In a manner of speaking this actually was a 100% successful test run in that all of the tests that were run passed. There were, however, a number of tests that were ignored. You can see one such test in the “zoomed-in” view of the test results shown above, but in a sea of dozens, hundreds, or thousands of tests you’re not likely to notice or pay much mind to the handful being ignored.

Justification

Why do tests get marked with an ‘ignore’ attribute? In my experience, tests get ignored for one very simple reason: to make the test run pass. Something was probably refactored, and a test started failing. Maybe the failure is pointing out a legitimate bug that’s been introduced by recent changes, or maybe the test in question simply isn’t valid in its current form anymore. The bottom line is this: there’s not enough time to figure out the root cause of failing test right now, so it gets marked as ignored with a silent promise made to return and fix it up later. I don’t really need to tell you how the rest of that story goes.

You could argue that marking tests as ignored is better than just commenting them out (you don’t do that, do you?), but I think that’s like saying that stealing from a rich person isn’t as bad as stealing from a poor person: it’s still theft. You might also argue that marking tests as ignored is better than letting them fail, but I disagree. If you don’t have time to fix the test right away, letting it fail is a far better option than marking with an ‘ignore’ attribute. Failure is an option.

Embrace Failure

A good development team notices failed tests because failed tests create failed continuous integration (CI) builds, and good development teams hate failed CI builds. I’m aware that most CI projects and test runners have options to fail test runs with a certain number of ignored/skipped tests, but if you’re going to use the presence of ignored tests to fail a build, why not just let the tests fail and fail the build that way?

Once a test is failing, it will need to get fixed. If the test is failing on a CI server that is visible to entire team, everyone should have a vested interest in getting it fixed as soon as possible. There are two possible outcomes at this point:

1 – Fix the test: After examining the failing test you might determine that there’s value in keeping it and making it work. You might also discover that the failing test is actually indicative of a problem in the application code. Either way: fixing the code so that the test passes is always the best option.

2 – Delete the test: Alternatively, you might determine that the test in question isn’t relevant or valuable anymore, and should just be deleted.

The test is either worth spending the time to fix now, or it’s not worth keeping around. Keeping a test around, but marking it as ‘ignored’ is like keeping that old video game around because you think that you’re going to have time to re-play it some day and do all of the side missions.

Enabling Poor Habits

When a test framework has a built-in attribute for ignoring tests, it’s enabling a poor habit. Just like the xUnit test framework did away with the ‘ExpectedException’ attribute in favor of Assert.Throws, I think newer test frameworks should get rid of the ‘ignore’ attribute. If I’m successful in convincing the other contributors to the project, SpecEasy won’t have a built-in way to ignore tests.

Posted On Sunday, September 29, 2013 12:18 PM | Comments (0)
Wednesday, August 28, 2013
Extending CLAP For Larger Applications

If you’ve been reading my recent posts you’ve probably noticed that I’ve been working a lot with the CLAP project lately. For anyone who is not familiar with that project, its name is an acronym that stands for Command Line Auto Parser. It lets you quickly and easily take an array of command-line arguments passed into a .NET application and map them to one or more target classes that expose methods. CLAP takes the strings representing the arguments and automatically parses them and passes them into the appropriate method. I like this library a lot because it has a very low-barrier to entry making it very easy to get up and running for simple applications. Unfortunately, things get more cumbersome if you want to work with a large or complex application. Let’s look at some examples:

Simplest Example – Static Methods Class

The simplest way to use CLAP is to create a class with static public methods that represent the commands you want to expose at the command line. Consider this example class:

public class Arithmetic
{
    [Verb]
    public static int Add(int value1, int value2)
    {
        return value1 + value2;
    }

    [Verb]
    public static int Substract(int value1, int value2)
    {
        return value1 - value2;
    }
}

Each public method we want CLAP to be able to use is decorated with a ‘Verb’ attribute. In order to invoke these methods from a command line application, all you need to do is create a a Console app and setup your Main method like this:

class Program
{
    static void Main(string[] args)
    {
        Parser.Run<Arithmetic>(args);
    }
}

In order to invoke the ‘Add’ command, you would run:

MyApp.exe add /value1:67 /value2:45

This approach is great for quick spikes or very simple command line utilities, but what if you want to split your commands into different classes or want to use dependency injection to inject external dependencies for use in your [Verb] decorated methods?

Using Multiple Classes

CLAP has support for splitting commands into multiple classes, and you can continue to use the same general approach. To build on the example from above, let’s now define another class to handle trigonometric functions:

public class Trigonometry
{
    [Verb]
    public static double Sine(double angle)
    {
        return Math.Sin(angle);
    }

    [Verb]
    public static double Cosine(double angle)
    {
        return Math.Cos(angle);
    }

    [Verb]
    public static double Tangent(double angle)
    {
        return Math.Tan(angle);
    }
}

In order to support both our basic arithmetic and more advanced trigonometry, all we need to do is tweak our Main method a bit:

class Program
{
    static void Main(string[] args)
    {
        Parser.Run<Arithmetic, Trigonometry>(args);
    }
}

The usage now gets a bit more complicated because there are multiple classes that might be able to handle any given command. We need to give CLAP a hint regarding what class to use:

MyApp.exe arithmetic.add /value1:67 /value2:45

The code here is still very simple, and the usage from the command line is still pretty intuitive. We can add more classes into the mix here as needed, but at some point this basic approach will begin to break down. Adding new generic type parameters for every new class we want to support will start to make for some pretty funny looking code eventually. Also, what happens if a static method won’t cut it anymore? What if we need to use instance methods instead?

Using Instance Methods

So far the example we’ve been using represents a simple utility application that we were building “from scratch”. When I first started using CLAP I wasn’t interested in using to build a new application or utility, but rather to create a new command-line interface for an existing application. The existing application is quite mature and already has a user interface, but is lacking in the area of the administrative tooling. There are a classification of commands that application can support that don’t require a full-blown user interface either due to the low frequency with which they need to be invoked or because they represent advanced features that only administrative users would perform. In my situation, using CLAP to build a command-line interface meant creating a harness from which an existing set of classes could be exercised from the command line. There are an ever-growing number of commands that I want to able to support from the command line interface, which means that cramming them all into a handful of classes as static methods isn’t really going to scale well. What I really want to be able to do is define many small, narrowly-focused classes that each contain a handful of closely-related [Verb] methods. Additionally, I will often want to inject one or more dependencies on existing application classes into these classes at run-time to avoid having to construct them manually or re-invent functionality that already exists elsewhere in the application.

In its current form, CLAP does support the requirements I’ve outlined above, but in a sub-optimal way. Let’s say I want to build a command line interface for an existing application that will allow administrative users to both reset passwords and delete customer data for multiple users or customers at once. Resetting a user’s password and deleting a customer are two very different things, so I’d likely want to create two different classes. Let’s say that these class signatures might look like this:

public class CustomerCleanup
{
    private readonly ICustomerDao _customerDao;

    public CustomerCleanup(ICustomerdao customerDao)
    {
        _customerDao = customerDao;
    }

    [Verb]
    public void DeleteCustomers(int[] customerIds)
    {
        /* method body */
    }
}

public class BulkUserManager
{
    private readonly IPasswordService _passwordService;

    public BulkUserManager(IPasswordService passwordService)
    {
        _passwordService = passwordService;
    }

    public void ResetPassword(int[] userIds)
    {
        /* method body */
    }
}

Note how each of these classes has an external dependency. Resetting passwords and deleting customers is already a solved problem in this application; we just want to give administrative users the ability to perform these operations on more than one user or customer at a time if needed. Because these classes have external dependencies I want to be able to inject them via the constructor and keep references to them as internal fields which means that I can’t simply create static methods and use CLAP like I did in the previous examples. In order to use CLAP with these instance methods, I’ll just need to pass instances of each class in like this:

class Program
{
    static void Main(string[] args)
    {
        Parser.Run(args, Resolve<BulkUserManager>(), Resolve<CustomerCleanup>());
    }
}

This works OK, but is inefficient because it requires that we pass in an instance of each potential target type. The ‘Resolve’ method in this example is responsible for building up an instance of  the specified type (like a call into an Inversion of Control container might do). So each time we run a command, we’re required to build up a full instance of each class that might need to be used to run that command. These classes have dependencies, and those dependencies might also have dependencies. In the end, each call to ‘Run’ is only going to end up using one of the classes that we created, and the rest of them were instantiated (along with all of their dependencies) for no reason. After awhile, you could easily be building up a dozen or more classes that you’re never going to use.

Deferring Construction

What I really want to be able to do is to tell CLAP about all of the classes that might handle the command but without actually having to instantiate each one. I want to defer the instantiation of the class until I know that it’s the one that I’m going to need to run the command. To support this, I’m proposing the use of something called the Target Resolver.

The Target Resolver will allow “registration” of classes that might need to be used to run a command. CLAP is already relying heavily on reflection to do its work, so it makes sense for the Target Resolver register Type instances representing the possible “target” classes for each commands. In addition to each Type, the Target Resolver also needs to be able to create the type on-the-fly if and when CLAP determines that it is the Type that will be needed to satisfy the command that was passed in. This is easily accomplished by providing a callback to construct each type. The example from above could be re-written like this using the Target Resolver:

class Program
{
    static void Main(string[] args)
    {
        var targetResolver = new TargetResolver();
        targetResolver.RegisterTargetType<BulkUserManager>(() => Resolve<BulkUserManager>());
        targetResolver.RegisterTargetType<CustomerCleanup>(() => Resolve<CustomerCleanup>());
        Parser.Run(args, targetResolver);
    }
}

Now we’re using generic methods on the TargetResolver class to ‘register’ each class that can handle commands, and providing a Func<T> that will construct and return each one if and when it’s needed. We then just pass that target resolver into the CLAP Parser and it takes care of inspecting each registered type and building it when it’s needed.

I think it will be fairly easy to create “adapter” packages that could easily port an Inversion of Control container into a TargetResolver instance (e.g. CLAP.Ninject, CLAP.Autofac, CLAP.TinyIoC, etc.)  for easily wiring up command-handling classes in an application. This would make it easy to add new classes as you expand the functionality of the command-line interface while being able to leverage any existing investment in an IoC container.

You can see the TargetResolver code on my fork of the CLAP project on github. I’ll be submitting this as a pull request to the main CLAP project where I’m hoping it will be accepted into a new version of the library.

Posted On Wednesday, August 28, 2013 9:37 PM | Comments (0)
Wednesday, July 31, 2013
A Simple Approach For Presenting With Code Samples

I’ve been getting ready for a presentation and have been struggling a bit with the best way to show and execute code samples. I don’t present often (hardly ever), but when I do I like the presentation to have a lot of succinct and executable code snippets to help illustrate the points that I’m making. Depending on what the presentation is about, I might just want to build an entire sample application that I would run during the presentation. In other cases, however, building a full-blown application might not really be the best way to present the code. The presentation I’m working on now is for an open source utility library for dealing with dates and times. I could have probably cooked up a sample app for accepting date and time input and then contrived ways in which it could put the library through its paces, but I had trouble coming up with one app that would illustrate all of the various features of the library that I wanted to highlight. I finally decided that what I really needed was an approach that met the following criteria:

  1. Simple: I didn’t want the user interface or overall architecture of a sample application to serve as a distraction from the demonstration of the syntax of the library that the presentation is about. I want to be able to present small bits of code that are focused on accomplishing a single task. Several of these examples will look similar, and that’s OK. I want each sample to “stand on its own” and not rely much on external classes or methods (other than the library that is being presented, of course).
  2. “Debuggable” (not really a word, I know): I want to be able to easily run the sample with the debugger attached in Visual Studio should I want to step through any bits of code and show what certain values might be at run time. As far as I know this rules out something like LinqPad, though using LinqPad to present code samples like this is actually a very interesting idea that I might explore another time.
  3. Flexible and Selectable: I’m going to have lots of code samples to show, and I want to be able to just package them all up into a single project or module and have an easy way to just run the sample that I want on-demand.

Since I’m presenting on a .NET framework library, one of the simplest ways in which I could execute some code samples would be to just create a Console application and use Console.WriteLine to output the pertinent info at run time. This gives me a “no frills” harness from which to run my code samples, and I just hit ‘F5’ to run it with the debugger. This satisfies numbers 1 and 2 from my list of criteria above, but item 3 is a little harder. By default, just running a console application is going to execute the ‘main’ method, and then terminate the program after all code is executed. If I want to have several different code samples and run them one at a time, it would be cumbersome to keep swapping the code I want in and out of the ‘main’ method of the console application. What I really want is an easy way to keep the console app running throughout the whole presentation and just have it run the samples I want when I want. I could setup a simple Windows Forms or WPF desktop application with buttons for the different samples, but then I’m getting away from my first criteria of keeping things as simple as possible.

Infinite Loops To The Rescue

I found a way to have a simple console application satisfy all three of my requirements above, and it involves using an infinite loop and some Console.ReadLine calls that will give the user an opportunity to break out and exit the program. (All programs that need to run until they are closed explicitly (or crash!) likely use similar constructs behind the scenes. Create a new Windows Forms project, look in the ‘Program.cs’ that gets generated, and then check out the docs for the Application.Run method that it calls.). Here’s how the main method might look:

   1:   static void Main(string[] args)
   2:  {
   3:    do
   4:    {
   5:        Console.Write("Enter command or 'exit' to quit: > ");
   6:        var command = Console.ReadLine();
   7:        if ((command ?? string.Empty).Equals("exit", StringComparison.OrdinalIgnoreCase))
   8:        {
   9:            Console.WriteLine("Quitting.");
  10:            break;
  11:        }
  12:   
  13:    } while (true);
  14:  }

The idea here is the app prompts me for the command I want to run, or I can type in ‘exit’ to break out of the loop and let the application close. The only trick now is to create a set of commands that map to each of the code samples that I’m going to want to run. Each sample is already encapsulated in a single public method in a separate class, so I could just write a big switch statement or create a hashtable/dictionary that maps command text to an Action that will invoke the proper method, but why re-invent the wheel?

CLAP For Your Own Presentation

I’ve blogged about the CLAP library before, and it turns out that it’s a great fit for satisfying criteria #3 from my list above. CLAP lets you decorate methods in a class with an attribute and then easily invoke those methods from within a console application. CLAP was designed to take the arguments passed into the console app from the command line and parse them to determine which method to run and what arguments to pass to that method, but there’s no reason you can’t re-purpose it to accept command input from within the infinite loop defined above and invoke the corresponding method. Here’s how you might define a couple of different methods to contain two different code samples that you want to run during your presentation:

   1:   public static class CodeSamples
   2:  {
   3:    [Verb(Aliases="one")]
   4:     public static void SampleOne()
   5:     {
   6:        Console.WriteLine("This is sample 1");
   7:      }
   8:   
   9:      [Verb(Aliases="two")]
  10:      public static void SampleTwo()
  11:      {
  12:        Console.WriteLine("This is sample 2");
  13:       }
  14:  }

A couple of things to note about the sample above:

  1. I’m using static methods. You don’t actually need to use static methods with CLAP, but the syntax ends up being a bit simpler and static methods happen to lend themselves well to the “one self-contained method per code sample” approach that I want to use.
  2. The methods are decorated with a ‘Verb’ attribute. This tells CLAP that they are eligible targets for commands. The “Aliases” argument lets me give them short and easy-to-remember aliases that can be used to invoke them. By default, CLAP just uses the full method name as the command name, but with aliases you can simply the usage a bit.
  3. I’m not using any parameters. CLAP’s main feature is its ability to parse out arguments from a command line invocation of a console application and automatically pass them in as parameters to the target methods. My code samples don’t need parameters ,and honestly having them would complicate giving the presentation, so this is a good thing. You could use this same approach to invoke methods with parameters, but you’d have a couple of things to figure out. When you invoke a .NET application from the command line, Windows will parse the arguments and pass them in as a string array (called ‘args’ in the boilerplate console project Program.cs). The parsing that gets done here is smart enough to deal with things like treating strings in double quotes as one argument, and you’d have to re-create that within your infinite loop if you wanted to use parameters. I plan on either submitting a pull request to CLAP to add this capability or maybe just making a small utility class/extension method to do it and posting that here in the future.

So I now have a simple class with static methods to contain my code samples, and an infinite loop in my ‘main’ method that can accept text commands. Wiring this all up together is pretty easy:

   1:  static void Main(string[] args)
   2:  {
   3:    do
   4:    {
   5:       try
   6:       {
   7:         Console.Write("Enter command or 'exit' to quit: > ");
   8:         var command = Console.ReadLine();
   9:          if ((command ?? string.Empty).Equals("exit", StringComparison.OrdinalIgnoreCase))
  10:          {
  11:             Console.WriteLine("Quitting.");
  12:             break;
  13:           }
  14:   
  15:           Parser.Run<CodeSamples>(new[] { command });
  16:            Console.WriteLine("---------------------------------------------------------");
  17:        }
  18:        catch (Exception ex)
  19:        {
  20:           Console.Error.WriteLine("Error: " + ex.Message);
  21:         }
  22:   
  23:      } while (true);
  24:  }

Note that I’m now passing the ‘CodeSamples’ class into the CLAP ‘Parser.Run’ as a type argument. This tells CLAP to inspect that class for methods that might be able to handle the commands passed in. I’m also throwing in a little “----“ style line separator and some basic error handling (because I happen to know that some of the samples are going to throw exceptions for demonstration purposes) and I’m good to go. Now during my presentation I can just have the console application running the whole time with the debugger attached and just type in the alias of the code sample method that I want to run when I want to run it.

Posted On Wednesday, July 31, 2013 10:04 PM | Comments (0)
Sunday, June 30, 2013
Making a Case For The Command Line

I have had an idea percolating in the back of my mind for over a year now that I’ve just recently started to implement. This idea relates to building out “internal tools” to ease the maintenance and on-going support of a software system. The system that I currently work on is (mostly) web-based, so we traditionally we have built these internal tools in the form of pages within the app that are only accessible by our developers and support personnel. These pages allow us to perform tasks within the system that, for one reason or another, we don’t want to let our end users perform (e.g. mass create/update/delete operations on data, flipping switches that turn paid modules of the system on or off, etc). When we try to build new tools like this we often struggle with the level of effort required to build them.

Effort Required

Creating a whole new page in an existing web application can be a fairly large undertaking. You need to create the page and ensure it will have a layout that is consistent with the other pages in the app. You need to decide what types of input controls need to go onto the page. You need to ensure that everything uses the same style as the rest of the site. You need to figure out what the text on the page should say. Then, when you figure out that you forgot about an input that should really be present you might have to go back and re-work the entire thing. Oh, and in addition to all of that, you still have to, you know, write the code that actually performs the task. Everything other than the code that performs the task at hand is just overhead. We don’t need a fancy date picker control in a nicely styled page for the vast majority of our internal tools. We don’t even really need a page, for that matter. We just need a way to issue a command to the application and have it, in turn, execute the code that we’ve written to accomplish a given task. All we really need is a simple console application!

Plumbing Problems

A former co-worker of mine, John Sonmez, always advocated the Unix philosophy for building internal tools: start with something that runs at the command line, and then build a UI on top of that if you need to. John’s idea has a lot of merit, and we tried building out some internal tools as simple Console applications. Unfortunately, this was often easier said that done. Doing a “File –> New Project” to build out a tool for a mature system can be pretty daunting because that new project is totally empty.  In our case, the web application code had a lot of of “plumbing” built in: it managed authentication and authorization, it handled database connection management for our multi-tenanted architecture, it managed all of the context that needs to follow a user around the application such as their timezone and regional/language settings. In addition, the configuration file for the web application  (a web.config in our case because this is an ASP .NET application) is large and would need to be reproduced into a similar configuration file for a Console application. While most of these problems are could be solved pretty easily with some refactoring of the codebase, building Console applications for internal tools still potentially suffers from one pretty big drawback: you’d have to execute them on a machine with network access to all of the needed resources. Obviously, our web servers can easily communicate the the database servers and can publish messages to our service bus, but the same is not true for all of our developer and support personnel workstations. We could have everyone run these tools remotely via RDP or SSH, but that’s a bit cumbersome and certainly a lot less convenient than having the tools built into the web application that is so easily accessible.

Mix and Match

So we need a way to build tools that are easily accessible via the web application but also don’t require the overhead of creating a user interface. This is where my idea comes into play: why not just build a command line interface into the web application? If it’s part of the web application we get all of the plumbing that comes along with that code, and we’re executing everything on the web servers which means we’ll have access to any external resources that we might need. Rather than having to incur the overhead of creating a brand new page for each tool that we want to build, we can create one new page that simply accepts a command in text form and executes it as a request on the web server. In this way, we can focus on writing the code to accomplish the task. If the tool ends up being heavily used, then (and only then) should we consider spending the time to build a better user experience around it. To be clear, I’m not trying to downplay the importance of building great user experiences into your system; we should all strive to provide the best UX possible to our end users. I’m only advocating this sort of bare-bones interface for internal consumption by the technical staff that builds and supports the software. This command line interface should be the “back end” to a highly polished and eye-pleasing public face.

Implementation

As I mentioned at the beginning of this post, this is an idea that I’ve had for awhile but have only recently started building out. I’ve outlined some general guidelines and design goals for this effort as follows:

  1. Text in, text out: In the interest of keeping things as simple as possible, I want this interface to be purely text-based. Users will submit commands as plain text, and the application will provide responses in plain text. Obviously this text will be “wrapped” within the context of HTTP requests and responses, but I don’t want to have to think about HTML or CSS when taking input from the user or displaying responses back to the user.
  2. Task-oriented code only: After building the initial “harness” for this interface, the only code that should need to be written to create a new internal tool should be code that is expressly needed to accomplish the task that the tool is intended to support. If we want to encourage and enable ourselves to build good tooling, we need to lower the barriers to entry as much as possible.
  3. Built-in documentation: One of the great things about most command line utilities is the ‘help’ switch that provides usage guidelines and details about the arguments that the utility accepts. Our web-based command line utility should allow us to build the documentation for these tools directly into the code of the tools themselves.

I finally started trying to implement this idea when I heard about a fantastic open-source library called CLAP (Command Line Auto Parser) that lets me meet the guidelines outlined above. CLAP lets you define classes with public methods that can be easily invoked from the command line. Here’s a quick example of the code that would be needed to create a new tool to do something within your system:

   1:  public class CustomerTools
   2:  {
   3:    [Verb]
   4:    public void UpdateName(int customerId, string firstName, string lastName)
   5:    {
   6:       //invoke internal services/domain objects/hwatever to perform update
   7:    }
   8:  }

This is just a regular class with a single public method (though you could have as many methods as you want). The method is decorated with the ‘Verb’ attribute that tells the CLAP library that it is a method that can be invoked from the command line. Here is how you would invoke that code:

Parser.Run(args, new CustomerTools());

Note that ‘args’ is just a string[] that would normally be passed passed in from the static Main method of a Console application. Also, CLAP allows you to pass in multiple classes that define [Verb] methods so you can opt to organize the code that CLAP will invoke in any way that you like. You can invoke this code from a command line application like this:

SomeExe UpdateName -customerId:123 -firstName:Jesse -lastName:Taber

‘SomeExe’ in this example just represents the name of .exe that is would be created from our Console application. CLAP then interprets the arguments passed in order to find the method that should be invoked and automatically parses out the parameters that need to be passed in.

After a quick spike, I’ve found that invoking the ‘Parser’ class can be done from within the context of a web application just as easily as it can from within the ‘Main’ method entry point of a Console application. There are, however, a few sticking points that I’m working around:

  1. Splitting arguments into the ‘args’ array like the command line: When you invoke a standard .NET console application you get the arguments that were passed in by the user split into a handy array (this is the ‘args’ parameter referenced above). Generally speaking they get split by whitespace, but it’s also clever enough to handle things like ignoring whitespace in a phrase that is surrounded by quotes. We’ll need to re-create this logic within our web application so that we can give the ‘args’ value to CLAP just like a console application would.
  2. Providing a response to the user: If you were writing a console application, you might just use Console.WriteLine to provide responses to the user as to the progress and eventual outcome of the command. We can’t use Console.WriteLine within a web application, so I’ll need to find another way to provide feedback to the user. Preferably this approach would allow me to use the same handler classes from both a Console application and a web application, so some kind of strategy pattern will likely emerge from this effort.
  3. Submitting files: Often an internal tool needs to support doing some kind of operation in bulk, and the easiest way to submit the data needed to support the bulk operation is in a file. Getting the file uploaded and available to the CLAP handler classes will take a little bit of effort.
  4. Mimicking the console experience: This isn’t really a requirement so much as a “nice to have”. To start out, the command-line interface in the web application will probably be a single ‘textarea’ control with a button to submit the contents to a handler that will pass it along to CLAP to be parsed and run. I think it would be interesting to use some javascript and CSS trickery to change that page into something with more of a “shell” interface look and feel.

I’ll be blogging more about this effort in the future and will include some code snippets (or maybe even a full blown example app) as I progress. I also think that I’ll probably end up either submitting some pull requests to the CLAP project or possibly forking/wrapping it into a more web-friendly package and open sourcing that.

Posted On Sunday, June 30, 2013 4:03 PM | Comments (11)
Wednesday, May 29, 2013
Tweaking sp_foreachdb’s database_list Parameter Handling

Aaron Bertrand wrote a great article over at MSSQLTips.com titled, “Making a more reliable and flexible sp_MSforeachdb”. In that article he eschews the use of relying on the undocumented (and unsupported) sp_MSforeachdb system stored procedure that can be found in SQL Server’s master system database.

As Aaron points out in his article, most people who do a lot of work with SQL Server have probably used the sp_MSforeachdb procedure once or twice in the past. The concept of this procedure is simple: you pass in a string containing a SQL statement and it loops over each and every database on the server and executes that SQL against it. Aaron points out a few reasons why relying on this stored procedure is bad including the fact that it sometimes doesn’t actually hit all of the databases. He goes on to provide the source code for an alternative stored procedure that can also be used to execute SQL against multiple databases on a server but in a much more reliable and consistent way. He named this alternative stored procedure 'sp_foreachdb’.

One of the most useful features of Aaron’s sp_foreachdb is the @database_list parameter which lets you pass in a comma-delimited list of database names that you want the command to be run against. I find this feature particularly useful for the multi-tenanted environment in which I work every day. In our setup each tenant has its own separate SQL Server database with an identical schema. In some cases these tenant databases might share a SQL Server instance with other non-tenant databases. The sp_foreachdb procedure makes it easy to write queries that can pull a particular set of data out of subsets of tenant databases and aggregate them into one combined dataset that we can use to identify patterns, track feature usage, or simply give us a 10,000 foot view of the data that spans across all of our tenants.

The Problem

I started out using the sp_foreachdb procedure for searching on a particular value within a particular column across all tenant databases. I was surprised when the procedure wasn’t returning any results even though I was nearly certain that at least some of the tenant databases contained the value I was searching on. Doing some direct querying against a few of the databases for the value proved that I was right; so why wasn’t the same query being run via sp_foreachdb finding results in these databases?

After reading the source code for a bit and planting a few ‘PRINT’ statements I discovered that a good portion of the databases I was passing in via the @database_list parameter were being truncated before the stored procedure began running the provided query against them. A bit more digging turned up some code that parses the @database_list parameter and massages the values a bit. If you pass in a value of “databaseA,databaseB,databaseC”, the stored procedure will parse that into “N’databaseA’,N’databaseB’,N’databaseC’”. This modified database list value is then used for an IN clause when selecting databases out of the sys.databases view. The resulting list of databases and then later used in a cursor for executing the provided SQL. The issue I was seeing was happening somewhere in the block of code that transforms the @database_list parameter which looks like this:

   1:  ;WITH n(n) AS 
   2:         (
   3:             SELECT ROW_NUMBER() OVER (ORDER BY s1.name) - 1
   4:              FROM sys.objects AS s1 
   5:              CROSS JOIN sys.objects AS s2
   6:         )
   7:         SELECT @dblist = REPLACE(REPLACE(REPLACE(x,'</x><x>',','),
   8:             '</x>',''),'<x>','')
   9:         FROM 
  10:         (
  11:             SELECT DISTINCT x = 'N''' + LTRIM(RTRIM(SUBSTRING(
  12:              @database_list, n,
  13:              CHARINDEX(',', @database_list + ',', n) - n))) + ''''
  14:              FROM n WHERE n <= LEN(@database_list)
  15:              AND SUBSTRING(',' + @database_list, n, 1) = ','
  16:              FOR XML PATH('')
  17:         ) AS y(x);

This code took me a bit to grok as there is a lot going on here. Let’s break this down into smaller pieces:

  1. Starting on line 1 a Command Table Expression (CTE) is declared and given the alias “n”.  This CTE just serves to create a temporary table full of sequential numbers from 0 to x where x is the total number of rows in the sys.objects table raised to the power of 2. On my local installation of SQL Server 2008 R2 I have 78 rows in sys.objects so this CTE results in a set of numbers from 0 to 6084.
  2. The SELECT statement that starts on line 11 is selecting rows out of the CTE for values up to and including the full length of the @database_list parameter. The select list and where clauses are using the SUBSTRING function to “chunk” the comma-separated @database_list parameter into pieces separated by commas. The result of this query is one row per distinct database name. Finally the FOR XML PATH(‘’) command takes that result set and flattens it into one big XML string. At this point the result for our example input would be: <x>N'databaseA'</x><x>N'databaseB'</x><x>N'databaseC'</x>
  3. Finally, the select list clause on lines 7-8 is taking that XML string and stripping out the <x></x> nodes to leave a comma separated string of database names.

This code is fairly complex and might seem like overkill for the task of simply converting ‘databaseA,databaseB,databaseC’ into ‘N’databaseA’,N’databaseB’,N’databaseC’, but it will gracefully handle things like duplicated database names, whitespace between entries, and leading/trailing commas. The biggest weakness of this code (aside from complexity) is the fact that it relies on the sys.objects view to create the crucial “numbers table” that it needs to parse out the database list. If the length of the @database_list parameter exceeds the number of rows returned from the CTE then you’ll end up losing some of the databases specified in the list. You’re probably thinking that 6084 rows in the numbers table should be more than enough for any @database_list parameter you might want to use, and you’d be right, but there are circumstances where you won’t get anywhere near 6084 rows from the CTE.

It turns out the number of rows you get from sys.objects can be affected pretty dramatically by the permissions of the user that is performing the query. I was seeing this issue when the query was being run by a user with restricted permissions which was causing the number of rows returned by sys.objects to be quite small. On my SQL Server 2008 R2 installation that I have my local workstation a “fresh” SQL login with no extra defined permissions only gets 6 rows from sys.objects in the master database. Having so few rows available in the CTE of the query above means that database names that fall into portions of the string that fall outside he bounds of the number range will never be included in the final query. In order to ensure that the @database_list parameter will behave properly we need an approach that will not be impacted by the permissions of the user running the query.

The Solution

I came up with a few possible ways to work around the potential limitations of relying on sys.objects to build a number range:

  1. Require that users pass in a properly formatted database_list: The easiest way to deal with this issue as the author or editor of the stored procedure code is to put the burden on the caller to provide a list that’s in a format that can be consumed in the WHERE clause of the query against sys.databases so that you don’t need to do any kind of adjustment. The downside to this approach is that it’s not very user-friendly. As a consumer of the stored procedure I’d much rather just be able to pass in a simple comma-separated list of database names without having to worry too much about white space or adding the Unicode ‘N’ identifier to the beginning of each database name.
  2. Pick another system table or view: I’ve seen some people use the undocumented ‘spt_values’ table in the SQL Server master system database for having a SELECT-able range of consecutive numbers. I personally don’t really like this approach because it relies on an undocumented table that may or may not be around in future versions of SQL Server. Also, it’s using that undocumented table for a purpose that it was not originally designed or intended for.
  3. Build your own numbers table: A lot of SQL Server gurus would say that you should have a pre-built “numbers table” available in every SQL Server instance that you manage for preforming tasks just like the one we’re dealing with here. I think this post from Adam Machanic sums up the virtues of a numbers table pretty well: You REQUIRE a Numbers Table! I like the idea of having a pre-built numbers table available for stuff just like this. You create this table expressly for this purpose and just keep the script for building it around as part of your SQL Server instance setup tasks.
  4. Build a proper list of numbers on-the-fly: Rather than rely on some pre-existing table (whether it be one that you build or one that’s built-in) we could instead opt to build up the list of numbers that we need in a table variable or temporary table just before we need them.

While thinking about this problem I whittled the possible solutions down to #3 or #4 above. I think both of these approaches would work very well and would avoid building a dependency on using undocumented system tables in a way in which they were not intended to be used. To keep the dependencies of the stored procedure to the bare minimum, I ultimately went with option #4 and used a simple WHILE loop to build up a table variable containing the list of numbers I needed to properly parse and adjust the database_list parameter. You might feel a bit “icky” using a WHILE loop in t-sql, but I feel like the result performs pretty well, will always perform correctly, and is very easy to understand and read. Here’s how the code ended up:

   1:  --create a numbers table big enough to let us parse out the @database_list parameter
   2:          DECLARE @n TABLE (n INT)
   3:          DECLARE @loopCounter INT, @loopLength INT
   4:          SET @loopCounter = 0
   5:          SET @loopLength = LEN(@database_list)
   6:          WHILE @loopCounter < @loopLength
   7:          BEGIN
   8:              INSERT @n VALUES (@loopCounter)
   9:              SET @loopCounter = @loopCounter +1
  10:          END
  11:     
  12:          SELECT @dblist = REPLACE(REPLACE(REPLACE(x,'</x><x>',','),'</x>',''),'<x>','')
  13:          FROM 
  14:          (
  15:             SELECT DISTINCT x = 'N''' + LTRIM(RTRIM(SUBSTRING(
  16:              @database_list, n,
  17:              CHARINDEX(',', @database_list + ',', n) - n))) + ''''
  18:              FROM @n WHERE n <= LEN(@database_list)
  19:              AND SUBSTRING(',' + @database_list, n, 1) = ','
  20:              FOR XML PATH('')
  21:          ) AS y(x);

This code could also very easily be changed to use a pre-built numbers table if desired. All you would have to do is get rid of the WHILE loop and swap out the name of the table variable for the name of the pre-built numbers table.

Posted On Wednesday, May 29, 2013 10:08 PM | Comments (0)
Sunday, April 28, 2013
Extending sp_WhoIsActive With context_info

If you work with SQL Server you should be familiar with Adam Machanic’s fantastic sp_WhoIsActive stored procedure. This procedure provides high levels of detail about all commands that are currently being executed on a SQL Server instance. I’ve found it immensely helpful for troubleshooting long-running queries and locking/contention issues in live production SQL Server instances. That said, I’ve always wanted to include one extra little bit of information in the dataset that sp_WhoIsActive returns: the id of the user responsible issuing for the database command. Obviously sp_WhoIsActive can report on the SQL or Windows account that was used to initiate the connection to the database and issue the command, but when you utilize connection pooling to let your application talk to SQL Server you end up having all of the connections use the same connection string which means that they all end up using the same SQL/Windows account to connect to the database. I want a way to see the user login from my application (what I’ll call the “application user” for the rest of this post) that is responsible for each of the queries being run. Let’s start by looking at the first problem: making SQL Server aware of the application user that initiated the connection.

Problem 1: Providing the Application User Information To The SQL Connection

In order for sp_WhoIsActive to have a prayer of figuring out what application user owns each database command being executed SQL Server needs to somehow tie that information to each spid that the application uses for running commands. The only reasonable way I’ve found to make this work is to leverage the CONTEXT_INFO SQL function. This function lets associate 128 bytes of data with the current database session. Fortunately this 128 bytes is more than adequate to store a 32 bit integer representing the ID of the application user that is responsible for initiating each database connection from your application. To do this, you’ll need to invoke the CONTEXT_INFO function on each connection that your application uses before you start using it to run commands. If you use a factory or provider pattern for doling out all of the database connections that your application uses (and if you’re not, you should definitely consider doing so) then implementing this CONTEXT_INFO call on each connection is pretty easy. Here’s a snippet of a function that can do this:

   1:   private void SetContextInfo(SqlConnection connection, int userId)
   2:          {
   3:              var command = connection.CreateCommand();
   4:              command.CommandType = CommandType.Text;
   5:              command.CommandText = "SET CONTEXT_INFO @currentUserId";
   6:   
   7:              var currentUserIdParameter = command.CreateParameter();
   8:              currentUserIdParameter.ParameterName = "@currentUserId";
   9:              currentUserIdParameter.DbType = DbType.Int32;
  10:              currentUserIdParameter.Size = 4;
  11:              currentUserIdParameter.Value = userId;
  12:              command.Parameters.Add(currentUserIdParameter);
  13:              command.ExecuteNonQuery();
  14:          }

This method takes a SqlConnection and user Id integer, then invokes a simple SQL command to call the SET CONTEXT_INFO command passing in the user id value that was provided.  Once this is working, the next step is to surface this user id to the sp_WhoIsActive stored procedure.

Problem 2: Exposing the CONTEXT_INFO data in sp_WhoIsActive Results

As of this writing, the latest version of sp_WhoIsActive (11.11) returns one row per session (SPID) that is actively executing a command. Since context_info is associated with each session, we should be able to see the context_info value for each row that sp_WhoIsActive returns. Unfortunately this info isn’t surfaced by sp_WhoIsActive out-of-the-box. That said there is a way that we can take the output of sp_WhoIsActive and add the context_info for each SPID that is returned.

The sp_WhoIsActive stored procedure exposes a number of optional parameters than affect its behavior. One of these parameters, ‘@destination_table’, lets you specify a name of a table into which you want to insert the dataset that the stored procedure gathers. We can use this to insert the results into a temp table and then join from that temp table to get the context_info for each session. We’ll use the sys.dm_exec_sessions dynamic management view to get the context_info for each session. Here’s how the SQL to do this shapes up:

   1:  --only including two columns here for brevity
   2:  CREATE TABLE #tempWhoIsActive(
   3:    [dd hh:mm:ss.mss] [varchar](15) NULL,
   4:    [session_id] [smallint] NOT NULL
   5:  )
   6:   
   7:  EXEC sp_WhoIsActive
   8:    @destination_table = '#tempWhoIsActive',
   9:    --define the outout columns to match the temp table definition. See the definition of
  10:    --the sp_WhoIsActive stored procedure for available column names
  11:    @output_column_list = '[dd%][session_id]'
  12:   
  13:  SELECT
  14:    ISNULL(CONVERT(INT, SUBSTRING(s.context_info,1,4)),-1) as [ta_user_id],
  15:    t.[dd hh:mm:ss.mss],
  16:    t.[session_id]
  17:  FROM #tempWhoIsActive t
  18:  JOIN sys.dm_exec_sessions s ON t.session_id = s.session_id
  19:   
  20:  DROP TABLE #tempWhoIsActive

The code above is creating a temp table, invoking the sp_WhoIsActive stored procedure with a pre-defined set of output columns to load that temp table, then joining the temp table to sys.dm_exec_sessions view on the session_id. By joining to the sys.dm_exec_sessions view we can access the context_info value for each session.  Note the use of the SUBSTRING and CONVERT functions to extract the first 4 bytes of the 128 byte context_info value and convert it to an integer. If you were putting some other type of data into context_info you’d need to extract and convert it accordingly. Also note the use of the ‘ISNULL’ function, as any SPIDs being run directly via SSMS, SQL Agents, or other types of applications talking to the database server might not have any context_info value set.

Now that I have the application user id for each SPID, I can join to the appropriate application tables to look up the user login and any other pertinent information I might need about the user. I can also now take this code and create a separate stored procedure to wrap the sp_WhoIsActive call and include the application user information from context_info to make this a bit easier to invoke in a pinch to see what’s going on at the database tier and correlate that activity to actual users of the application.

Posted On Sunday, April 28, 2013 2:16 PM | Comments (0)
Meta
Tag Cloud