In Case Your Compiler Decides to Take the Day Off…

02.19.2011 06:24 by kbeckman | Comments

The following is noteworthy because this is the type of unit test a conscious developer would write if their C# compiler occasionally takes the day off…


public interface IMyInterface
    void MethodThatDoesntMatter();

public class MyClass : IMyInterface 
    public void MethodThatDoesntMatter() { return; }


using System;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace Test.WastingYourTime
    public class MyClassTest 
        public void ImplementsIocInterface()
            Type interfaceType = typeof(IMyInterface);
            Type concreteType  = typeof(MyClass);



The problem is… Unlike the developer who wrote this unit test, the C# compiler doesn’t take days off. The .NET compiler also doesn’t waste valuable development time with senseless Internet browsing or BBQ recipe research. Just because you can test that a class implements a certain interface doesn’t mean you should waste your time doing so.

C4SC Update

01.27.2011 06:25 by kbeckman | Comments

I wanted to post a quick update to the C4SC audience and inform everyone about some recent site enhancements as well as some upcoming post changes, etc.


.NET 4.0, BlogEngine.NET 2.0

C4SC was redeployed this evening and is now running entirely on the .NET 4.0 platform! While nothing should change the user experience, it’s always nice to advertise major upgrades. Along with the .NET upgrade, C4SC has moved from BlogEngine.NET version 1.61 to the BlogEngine.NET 2.0 platform. I’m really excited about the new features and functions added by the BlogEngine.NET team and highly encourage anyone thinking about building a blog site to use BlogEngine.NET. The 2.0 upgrade has introduced some great features to enhance the administration experience.


Syntax Highlighting

I should have paid closer attention to the BlogEngine.NET roadmap... Then I might have noticed the development team was planning the implementation of Alex Gorbathev’s SyntaxHighlighter as a BlogEngine.NET extension. I had recently implemented this in C4SC, but have now scrapped my custom implementation in favor of the included extension. From now on all C4SC code posts will have well-formatted syntax rather than just plain text. I plan on updating older posts to accommodate the SyntaxHighlighter changes as time permits.


New Format for Refactoring-Related Posts

All posts related to any type of refactoring will resemble the format used by Martin Fowler in his book, Refactoring: Improving the Design of Existing Code. I consider that to the the single most beneficial book I’ve ever read on software development and will begin to follow the same style in this blog. Each post will include four sections detailing the problem and formulating a solution – Description, Motivation, Mechanics and Example(s).  It should be a good way to accumulate a targeted catalog of refactorings I’ve used and continue to use throughout my software development.

T-SQL Sproc Optional Parameters Using COALESCE

01.26.2011 22:42 by kbeckman | Comments


Your codebase has a glut of repeated stored procedure logic for “get” or SELECT-based operations. Table JOINS, stored procedure parameters (for row filtering) and return types are similar among a focused set of stored procedures. The focused stored procedure group resembles the following pattern: GetEntityById, GetEntitiesByParentId, GetEntityByExactName, GetEntitiesByPartialName, GetEntityBySomeOtherParameterCombo.


Use the T-SQL COALESCE function in the procedure’s WHERE clause logic to implement optional parameters and combine the many similar, individual procedures into a single procedure with functionality for multiple input parameter combinations that yield a single return type.



I’m currently working in a codebase with an overwhelming amount of T-SQL stored procedures. There’s an individual stored procedure for every CRUD operation you can think of… Unfortunately, with this particular application, it doesn’t make a lot of sense to start swapping out stored procedures and ADO.NET code for a more sexy ORM. The existing data access patterns already work well and all of the developers are very familiar with it. In addition, there are areas of the codebase where the domain model is coupled a bit too tightly to the data access code. The refactoring effort to introduce an ORM would be more than the deadlines allow for. An alternative is to reduce the number of similar “get” stored procedures into a single procedure that provides all of your required functionality. This approach can significantly impact the overall procedure count and make both database management and stored procedure enhancement easier .


Creating a single stored procedure for all of your “get” operations is possible with WHERE clause usage of the T-SQL COALESCE function. COALESCE takes an expression list and applies the first item that is NOT NULL. The expression list allows for the addition of stored procedure parameters as well as column values. Initializing all of your optional parameters = NULL in the parameter declaration will initialize the parameter as NULL if a value is not given by a call to the procedure. Apply a filter in the WHERE clause using the COALESCE function that uses your optional parameter and provide an alternate expression (if your parameter evaluates to IS NULL) that will always logically evaluate to TRUE. This will make sense when you evaluate the example…



1) Create a new stored procedure that will replace all of the existing “get” procedures for a given entity. Name it with a generic name that doesn’t indicate what parameter combination is required – GetEntity.

2) Add all of the possible input parameters to the new procedure. All required parameters should be declared as you normally would declare them. All optional parameters should be initialized as NULL in the declaration.


    @EntityId            INT = NULL
    , @PartialEntityName NVARCHAR(30)
    , @ParentEntityId    INT = NULL

3) For all optional filtering parameters, use the T-SQL COALESCE keyword to build an expression in the WHERE clause that first uses your optional parameter if it is NOT NULL and some other filter value(s) if your optional parameter IS NULL.


WHERE SomeTable.EntityId            = COALESCE(@EntityId, SomeTable.EntityId)
    AND SomeTable.ParentEntityId    = COALESCE(@ParentEntityId, SomeTable.ParentEntityId)
    AND((@PartialEntityName IS NULL AND SomeTable.EntityName = SomeTable.EntityName) 
        OR SomeTable.EntityName LIKE '%' + @PartialEntityName + '%')


4) Replace the usage of an existing “get” procedure with your new stored procedure and test its behavior.

5) Make sure any related unit tests are passing.

6) Repeat steps 4 and 5 for any usages of your old “get” stored procedures.

7) Be sure to tune/update any existing indexes or create additional indexes to accommodate your search criteria.



This example uses the AdventureWorks sample DB in SQL 2008.


-- ==========================================================================================
-- Author:      Keith Beckman
-- Create date: 1/7/2011
-- Description: Selects complete address data based on a variety of input criteria.
-- ==========================================================================================
CREATE PROCEDURE Person.GetAddresses
    @AddressId            INT = NULL
    , @PartialCity        NVARCHAR(30)
    , @StateProvinceId    INT = NULL


        SELECT TOP 20    
        , A.AddressLine1
        , A.City
        , A.PostalCode
        , SP.Name
        , CR.Name
    FROM    Person.Address A
            INNER JOIN Person.StateProvince SP ON SP.StateProvinceID = A.StateProvinceID
            INNER JOIN Person.CountryRegion CR ON CR.CountryRegionCode = SP.CountryRegionCode
    WHERE    A.AddressID             = COALESCE(@AddressId, A.AddressID)
            AND A.StateProvinceID    = COALESCE(@StateProvinceId, A.StateProvinceID)
            AND((@PartialCity IS NULL AND A.City = A.City) OR A.City LIKE '%' + @PartialCity + '%')


Here’s some usage examples to get an idea of what’s possible with the dynamic input parameter stored procedure approach…


--Get all addresses with a city name like "Birm"...
--Replaces: GetAddressesByCityName
EXECUTE AdventureWorks.Person.GetAddresses 
  @PartialCity = 'Birm'

--Get all addresses with a city name like "Birm" that are in Alabama...
--Replaces: GetAddressesByCityNameAndStateProvinceId
EXECUTE AdventureWorks.Person.GetAddresses 
    @PartialCity = 'Birm', 
    @StateProvinceId = 3

--Get all addresses in Alabama...
--Replaces: GetAddressesByStateProvinceId
EXECUTE AdventureWorks.Person.GetAddresses 
    @StateProvinceId = 3

--Get the address with AddressId = 49...
--Replaces: GetAddressById
EXECUTE AdventureWorks.Person.GetAddresses 
    @AddressId = 49


As you can see, there are numerous usages under this approach (certainly not limited to what I’ve shown above). The best part about this is that you’ve significantly reduced the amount of similar “Get” procedures you need to write! Any time you need to add additional JOINS or WHERE clauses, you can add them in a single procedure rather than replacing logic that’s scattered all over your codebase.


Now you and your DBA can call it a day… He/she owes you a beer after this one. Or if they helped you to arrive at this approach like mine did – you owe them one.

Losing Your Street Cred…

12.16.2010 21:03 by kbeckman | Comments

I just introduced a new category for C4SC posts – Losing Your Street Cred. The other day after seeing a particularly alarming exception cross my inbox, I felt the need to share. This particular item is the inverse of the widely-known cliché, “Saving the Best for Last”. We’re actually starting with the best here as I don’t imagine I’ll run into anything as disturbing anytime soon. Moving forward, I will be using Losing Your Street Cred to call out things that don’t do a lot of good for your street cred as a professional software developer.


And now for the charter member of the Losing Your Street Cred category…


The following exception is courtesy of a recent check-in by one of our 3rd party development partners. I thought I’d share because I had never seen this exception in 8+ years of .NET development. NEVER. I’m sure most of you haven’t either. If this was an isolated incident, I might have thought it was just a simple mistake, an exclusion in the developer’s SQL Compare script or a hiccup in one of the new Entity Framework features that creates your DB schema for you. Unfortunately none of those were the case and we’ve been plagued by chronic rookie-like mistakes exactly like this one for the last few months. This one in particular was the worst so far…


Server Error in '/' Application.

Can't perform Create, Update, or Delete operations on 'Table(SomeTable)' because it has no primary key.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:
System.InvalidOperationException: Can't perform Create, Update, or Delete operations on 'Table(SomeTable)' because it has no primary key. 


Yep, that was a table without a primary key… It’s really not necessary to say much else here except that you’ll have little to no street cred left after a mistake like this. It’s amazing the type of issues you can prevent by actually testing a fix in an integration environment (or at a minimum, on your local machine) before considering something fixed. And please, if you ever make a mistake like this, just fix it instead of trying to explain it away. Your peers just might have a shorter memory that way…

Boot2VHD Best Practices

09.14.2010 08:35 by kbeckman | Comments

This post comes as a result of an impromptu presentation I was asked to give during a weekly developer meeting at work a while back. I thought I’d share with all…


I’m sure by now most developers with access to the Windows 7 operating system have tried the Boot2VHD feature that allows you to boot to a native virtual hard disk without having to suffer through using Virtual PC. If not, there’s a glut of information out there on how to do it. There’s none better than Scott Hanselman’s post -- “Less Virtual, More Machine – Windows 7 and the Magic of Boot to VHD”.  This post however is a bit different and contains information that you won’t be able to find easily or find in one place (at least I haven’t anyway).


So you’ve decided to reap the benefits of Boot2VHD, now what? Below are my suggestions (through my own trial-and-error experience) for keeping your bootable VHDs operating at top performance.

1) Turn Off System Protection (Restore Points)


system_restoreThe few times I’ve ever actually had to use System Restore was when I had to repair someone else’s machine without having to totally rebuild it. Even then, it has never been that often.  While this utility can be nice for the novice PC user, it ends up being a disk space hog (if configured incorrectly) for the rest of us. Since your bootable VHD is just a file on the file system, you should be compressing it and backing it up somewhere else anyway… If you back-up your VHDs properly, there’s no real reason for System Restore (this only applies to VHDs). Turning this off will save a lot of churn and allocated space on your bootable VHD, especially when you’re installing a lot of developer machine applications. Another plus is a slight gain in speed during software installations and during Windows updates as no restore point is created prior to installation. If you must use System Restore on your VHD, enable it only after you’ve configured a pristine VHD; configure the utility to use only about 2GB of hard disk space; and make sure you’re consistently deleting all but the last restore point.



2) Maintain a Separate Physical Drive or Partitions for Bootable VHDs and Anything with High Churn (Code, Databases, etc.)

Development PCs endure a lot of hard disk churn. At work I’m constantly pulling down code from various source control branches; all of the which are mature, enterprise applications that contain thousands of code files. This isn’t something that I want to pull down to my bootable VHD on a regular basis -- I want to keep it as clean as possible… Use two separate physical internal drives when you can. Use a separate logical partition if another internal drive is not an option.


My work PC has two internal hard disks. This is the optimal solution since you can keep your VHDs on a physical drive separate from the drive in which you keep all of your high churn files. Before the addition of the additional drive though, I only had a single, unpartitioned drive – a single C: drive. Since I didn’t have the option of a separate physical disk or partition, I created another VHD that I attached at startup to keep all source code and databases on. I created a .bat file to automatically attach the code VHD and dropped it in my startup folder so I don’t have to worry about executing the script every time. My home development machine has a single internal physical drive but a separate partition for code so i don’t have to worry about attaching another VHD drive. Both of these options are nice because you can get to the same source code from various bootable VHDs (say one is a bootable Windows 7 VHD for development and the other is a bootable Server 2008 R2 also for development). Using the same physical drive with partitions is obviously the less optimal solution though because your “high churn” drive is on the same physical drive as your bootable VHD.


3) Once Your Bootable Development VHD is Pristine, Difference It!

Eventually, you’ll get to the point where your bootable development VHD is pristine and set up exactly the way you want. When that time comes, clean it, zero out the free space and compress it (see steps 4b and 4c) and back it up somewhere. The first step here is to use the file properties and mark your pristine VHD as read-only. This will prevent you from accidentally corrupting it somehow. Create a differencing disk off of your pristine parent and use BCDedit to change your boot entry to boot to the differencing VHD instead.


To create a differencing disk…

diskpart> create vdisk file=”[vhd drive:]\bootable vhds\differencing.vhd” parent=”[vhd drive:]\bootbale vhds\parent-readonly.vhd


A note here: I’ve tried to create bootable differencing disks in a directory other than where the parent VHD resides. While this works just fine in the context of Virtual PC (where VHDs are not bootable), I haven’t had any luck doing this with my bootable VHDs. I’ve had to keep any bootable differencing VHDs in the same directory as the parent.


4) Monthly Maintenance

Bootable VHDs require regular maintenance just as a normal disk would. Perform the following steps regularly to keep your VHD operating at peak performance.


a) Run Clean Disk and Defragment Your Bootable VHD

You should already know how to do this. I just included the hyperlinks for documentation’s sake… Run this from your bootable VHD.

b) Use SDelete to Zero-Out Your Bootable VHD Free Space

SDelete is a necessary utility for any power user. Not only is it great for performing a DoD wipe of sensitive files and folders (every developer has been there at least once), it can zero-out free space on your VHD allowing you to compress it later using Diskpart. I’ll let you read further about the full capabilities of SDelete on your own. Below is the command line call for zeroing out your VHD free space. Please note that that Sysinternals Suite is not provided through an installer. You can get SDelete separately or as part of the entire Sysinternals Suite application suite. The directory below is where I keep SDelete on my bootable VHD and this may vary from machine to machine. Run this from your bootable VHD.

>cd “c:\program files (x86)\sysinternals suite”

>sdelete.exe –p 1 –c c:


c) Use Diskpart to Compact Your Bootable VHD

If you’ve configured an expandable VHD as your bootable VHD, you’ll need to compress it now and then to reclaim some space on your drive. This works for both single and differenced VHDs. This post has some great specifics about this and other Diskpart VHD operations. Run diskpart as an administrator from your host OS.


diskpart> select vdisk file=”[vhd drive:]\vhds\bootableVhd.vhd”

diskpart> compact vdisk


d) Run Clean Disk and Defragment Your Host Drive(s)

Run this from your host OS.

5) Periodically Merge Differencing Disks with Their Parent

Differencing VHDs maintain changes to its parent VHD at the block level. After using a differencing VHD for an extended period of time, the disk can grow quite large. Depending on how many significant differences it maintains from the parent, it can become quite slow because the virtual disk interpreter has to do a significant amount of reading from both the parent and differencing disks. Merging the differencing VHD back with its parent resolves this.  There are a few things to keep in mind during this procedure – 1) you’ll have to remember to mark your parent VHD as editable if you’ve previously marked it as read-only; 2) your differencing VHD becomes useless and you’ll need to create a new differencing VHD off of the parent; and 3) your parent disk will contain all of the changes from the differencing disk – not the other way around. I compact all of my differencing VHDs before merging them with their parent.

diskpart> select vdisk file=”[vhd drive:]\vhds\differencingVhd.vhd”

diskpart> merge vdisk depth=1


I hope you get some good use out of these performance best practices… I’ll be following up soon with another post that contains some automation scripts I’ve created to save you some time and manual headache while maintaining your VHDs.