Spikeh'd

GDPR: Application Password Security in 2018

2018-03-19T17:39:00.004+00:00

Security Primer

As we hurtle towards GDPR, more and more of my clients are becoming concerned with data security. This is great news - I've always recommended best practices but come up against resistance occasionally due to the extra amount of time some implementations take - after all, time is money, and to some clients money is more important than security. Thankfully we're all now being forced to consider it more seriously.

I'm no security or cryptographic expert but as part of my job I have to provide secure authentication and storage systems to protect my client's data. As I'm a Microsoft stack guy this is often dealt with by an Active Directory or Federated Identity Server - but on occasion I need to "roll my own" authentication system or heavily modify an existing one.

One client recently asked me about the password hashing I'd put in place for them - I'd implemented SHA-256 with salting, using the same piece of code for about a decade. Its fast, relatively light weight and portable - but unbeknownst to me (until now), is no longer recommended or appropriate for hashing passwords. The client wanted me to implement PBKDF2 as they'd heard it was more secure.

Hashing vs Encryption

Security novices often confuse hashing and encryption. They are both cryptographic concepts but that's where the similarities end; in simple terms, hashing is a one-way operation that takes some data, makes a right old mess of it ("a hash"), and stores it somewhere for future reference. The aim of "hashing" is to make that data unreadable by humans and computers even when they know how it was originally created. The output hash aims to be impossible to reverse engineer, but computationally exact given the same input.

Encryption on the other hand, is a two-way operation. Data can be encrypted, then decrypted back to its original format at a later date using a global key - anyone who knows what encryption algorithm was used and has access to the key can read the data. It's usually very fast and can be used for securing large amounts of data (such as data on your hard disk). Its also useful for sending sensitive data across boundaries - for example, sending a the contents of a web page to a client web browser via TLS/SSL, or sending a secure email message.

There are multiple types of hashing and encryption algorithms - you've probably heard of MD5 and SHA - two of the most widely used. AES and PGP are popular encryption algorithms, but there are dozens that have been developed by different bodies and individuals over the years. Its worth noting that the vast majority of these algorithms are RFCs or open-source, so anyone with a bit of maths knowledge could theoretically figure out how they work and use them in their software. Most languages and frameworks have their own implementations that are well tested - therefore rolling your own algorithm implementation is not advised; someone more experienced has already done that work for you, so you should use their libraries and focus on your business logic.

Security Never Changes

There are people out there that want your data for one reason or another. No, you're not special or unique - you're just part of a numbers game. Malicious users might gain access to a database where your password is stored in plain text, then use that same password to legitimately log on to your bank account - if you used the same password of course. They might simply be after a list of email addresses that they can sell to spammers - either way, that line of data in a database is worth something to someone, somewhere.

Computers are getting faster and attackers are always finding new ways to break cryptographic algorithms. MD5 used to be considered the most secure hashing function, but it was severely compromised in 2013, and it now takes less than one second to crack a MD5 hash on a standard home computer. The same is true for SHA-0 and SHA-1 - both compromised. Even SHA-2 (which comprises of SHA-256 and SHA-512) is considered to have some weaknesses. Tools are getting easier to use - even amateurs are able to get access to cryptographic toolkits that do all the complex maths for them.

Security experts and developers are therefore constantly trying to stay ahead of threats. Let's look at a few security breach scenarios and discuss which method is best to secure the data.

Scenario: Brute force attack against your api / application / login form

An attacker has set up an automated script or botnet that generates passwords and tries to log in to your online system. Depending on how the attack script is throttled, it may appear to to be a DDoS attack.

Mitigation: No amount of encryption or hashing will help here. You should implement hardware traffic management / monitoring and some kind of application level account lockout procedure - i.e. 3 incorrect passwords and the user can no longer log in.

Scenario: Attacker gains raw access to your database

An attacker has breached your server / network and has taken a copy of the database files stored there. This usually indicates that there are other major security flaws in your system that should be sorted out as soon as possible. However, it's happened - so how can you make sure that the hacker can't read the database?

Mitigation: Most modern databases can be encrypted using an integrated feature / tool; it's usually a relatively simple task, and should be a priority. It requires a little bit of overhead and a little bit more development work, but it's always worth it.

If the database isn't encrypted, or the attacker also has access to the encryption key, then they can read all plain text data in the database. This is where password hashing comes in - although the attacker now has access to all of the data in your database, you can still make it difficult for them to figure out what each user's password is by using a hashing algorithm such as MD5, SHA, bcrypt, scrypt or PBKDF2.

Sub-Scenario: The Dictionary / Rainbow Table Attack

So, you've hashed the heck out of your user's passwords - now what? Well, there's still a vulnerability you may not be aware of; attackers who gain raw access to your database can use a dictionary attack or rainbow table attack in order to crack a password or ascertain your global hash key or passphrase.

In simple terms, this works by using a pre-compiled list of words / phrases (a dictionary) or hashes (in a rainbow table) to search for common passwords in your database. The larger your database, the greater the likelihood of more than one user using the same password. If two users use the password "password", it will be hashed into exactly the same string - and attackers know how to reverse engineer these patterns into passwords or hashes.

Mitigation: You can control the passwords your users use to some extent using application level rules, though less security conscious people will try to use the simplest password possible, or simply increment a number at the end of their existing password when the system asks them for a new one. Whilst you can protect against people using "12345" and "password" with pattern matching, you still can't protect against people using "P@ssword1" (which would satisfy even the most annoying of password rules).

So what do we do? We salt the hash. A salt is a cryptographically safe string of a certain length, unique to each user. This string is concatenated with the plain text password before hashing takes place - its randomly generated, then stored alongside the hashed password. Its visible to anyone with access to the raw data - even attackers that manage to get their hands on your database.

It sounds mad - why does a salt help if the attacker can see it? Well, because the added salt generates a unique hash for each user's password - even when two users use the exact same password. Every password essentially now has its own key, making the pre-compiled dictionary or rainbow table attack infeasible as a new table will need to be generated for every user. Consider the following pseudocode:

var user1Password = "password";

var user2Password = "password";

var user1Hash = Hash(user1Password);

var user2Hash = Hash(user2Password);

user1Hash and user2Hash will output exactly the same value - which creates a predictable pattern that can be exploited.

Now consider this:


var user1Password = "password";

var user2Password = "password";

var user1Hash = Hash(user1Password + GenerateSalt());

var user2Hash = Hash(user2Password + GenerateSalt());

GenerateSalt() returns a random string. user1Hash and user2Hash are unique, even though the passwords are the same, reducing the overall risk. As long as the output of GenerateSalt() is stored alongside the hash, you can validate that any future input matches the original hash value.

There are some recommendations that a salt should be globally unique, but I don't subscribe to this thinking. Yes, a user might use the same password on your website as another website, but the likelihood that you're going to generate exactly the same salt as the other application is extremely thin. I believe this advice exists to discourage developers from using predictable data such as username, email addresses or other unique identifiers that the user has control over. My advice is to make sure that you use any framework provided salt generation methods, and ensure you use the recommended minimum salt length.

Sub-Scenario: Using PBKDF2 instead of SHA for password hashing

Why did my client ask me to strip out SHA-256 and replace it with PBKDF2? SHA-256 is secure enough, right? Well, yes - generally SHA-2 is still considered secure, and the salting I put in place makes it even more so. However, attackers are now using the extreme processing power of GPUs (graphics cards with thousands of cores, processing hundreds of millions - possibly billions - of operations per second) rather than CPUs (standard computer processors with dozens of cores, processing millions of operations per second), which makes password cracking a much quicker affair.

This post on StackOverflow gives some 2009 figures for older CPUs / GPUs - 48 million vs 160 million ops per second respectively, so - applying Moore's Law - you can imagine how much more powerful GPUs are in 2018.

The basic decision points come down to this:

SHA is designed to be as fast as possible, and will only get faster as processors increase in speed.
PBKDF2 stands for Password-Based Key Derivation Function 2, and as the name suggests, it was specifically designed for password hashing. It is slow by design and iterates X times over a pseudo-random function such as SHA-1 or HMAC to create a derived key.

The one drawback to using PBKDF2 is that you have to review the number of iterations used to generate passwords often. As hardware improves you'll have to increase these iterations to continue to keep up with the attackers - I expect that high-end password cracking rigs exist with similar specs to the popular high-end bitcoin mining rigs, as they perform very similar functions. The more GPU power available, the quicker an attacker can succeed in performing a brute force attack.

Summary

It seems that brute force is still the preferred vector for password cracking - attackers are leveraging better hardware to perform more operations per second, decreasing the time it takes for a password to be cracked.

There are now more suitable password algorithms available - Argon2 deserves a special mention, though it's still relatively new (2015) and not quite as trusted as PBKDF2.

Of course this blog post is not exhaustive. There are many more steps you can take towards application and network security. Drop me a line if you require any advice on securing your applications or network.

Disclaimer

This blog post aims to provide advice only - I am not a security expert, though I have much more experience in application and network security than I do with implementing and understanding the minutiae surrounding cryptographic functions. Please let me know if you notice any mistakes and I'll update this post ASAP.

How I Learned to Lose Weight and Love Exercise (again)

2017-08-16T16:12:00.004+01:00

Back in my youth I was an avid gym-goer, badminton and basketball player, utility cyclist and swimmer. I did all this because I enjoyed it / life necessitated it, and I never once thought about my weight or fitness levels - though I did care about how I looked. Everything stopped when I moved to a new town and made new friends; I took up music as a hobby and sport fell by the wayside. Lugging 250lb of bass gear up and down 3 flights of stairs every week felt like enough to me - alas, the lack of exercise over a period of about 10 years (combined with my love of all forms of food and drink) resulted in me gaining a considerable amount of weight without me really noticing it.

About a year and a half ago, I did notice it. I started hating pictures of myself, I was struggling to live everyday life without sweating constantly and getting out of breath doing menial tasks. Walking 2 miles into town was a huge amount of effort, and even sleeping was difficult (for both myself and my wife) due to my snoring. I decided to do something about it - inspired by a few friends who recently lost a ton of weight, and my wife who was already on her own weight loss journey, I set my mind on shedding the pounds and getting fit again.

I jumped on the scales for the first time in years... 298lb! That's just over 21 stone, to my UK brethren. I didn't think much of it at first as I've always been a "big lad"; in my twenties I was 16 stone with around 10% body fat - but I was at the gym 4 hours a night and took part in many different sporting activities. Looking back, I was very, very fit, without really knowing it.

Not pictured; modesty

That day I had a bacon butty for breakfast, followed by a mid-morning can of coke and a Mars bar. For lunch, I would have had a massive butty from a shop in whatever city I was working in at the time, a bag of crisps, a cake, another can of coke, followed by a massive afternoon tea of frozen beige food (regularly I'd have a chicken kiev, chicken nuggets, breaded fish AND oven chips in one meal), or even a takeaway. I'd have takeaways at least 2 times a week.

I was bought up on a truly awful diet (even worse than the above), and knew literally nothing about nutrition. Looking back, I'm not in the slightest surprised about my weight gain - but it just goes to show that it really can creep up on you if you don't treat your health and fitness as a priority.

This was back in March 2016. It took me a few weeks to really start taking my weight loss seriously - on 7th April I got on my old mountain bike and did a 2 mile loop of my local park with an average speed of 10.9mph.

I was absolutely exhausted, but I knew I needed to do exercise as part of my new regime. That's all I knew, though - my diet remained the same (i.e. I ate anything I wanted)... if anything, it got worse because I thought I needed more food to compensate for the pitiful amount of exercise I was doing.

A few weeks later I'd improved marginally, but was doing the same route every morning. I started varying things up, adding a mile here and there. I distinctly remember going from my 2 - 4 mile morning ride to attempting a 10 mile ride. Off I went, breaking a number of "personal bests" in the process. Halfway up a "hill" I just couldn't go any further, so I turned back and only managed 8.9 miles.

I bought a new bike in early June 2016 - a BMC hybrid, which I loved to bits. It was light(er), and was much nicer to ride than my old two-ton MTB.

N+1

I used this to do my first "big" ride on 16th June, about 20 miles around the local lanes.

Then Pokemon Go came out. My rides suddenly became less about speed, distance and effort, and more about Catching 'em All. Thankfully that didn't last too long, and shortly after I decided to try riding my bike in a group - I tagged along with Blackpool Clarion Cycling Club (BCCC) for a few rides before joining the club properly a few weeks later.

My ability improved and I eventually splashed out on a mid-range (though still rather expensive) Giant Defy Advanced Pro.

I love two things in this picture

However, the weekend I ordered it I managed to come off my BMC hybrid during a club ride and break my right elbow!

This set my weight loss and exercise regime back a little bit, but luckily it didn't destroy my motivation. In fact, I was so into biking at this point that I just couldn't wait to get back on the bike, and bought a second hand turbo trainer to try and keep my fitness up. Turns out turbos aren't for me, but at least it kept me going for a few weeks. I recovered pretty well, though I still have issues with it today.

Suffice to say, I am now regularly doing 40 - 100 mile bike rides on my own, with BCCC and various other cycling groups and friends I've made along the way. The "hills" that I was initially struggling on don't even register on my radar any longer, though there are of course still some very difficult undulations out there. I now seek out steep hills, as most cyclists of-a-certain-level-of-fitness do.

This posts isn't meant to be about cycling, it's just become a real passion of mine over the last year. Exercise is important for your cardiovascular system and as part of a weight loss routine, but you can lose a significant amount of weight without it. I've come to really enjoy exercise again - I now try to do something every day, whether it's 30 minutes of interval training in the pool, a high speed / intensity Time Trial, or a 5 hour leisurely bike ride.

In June 2016 I started writing my weight down in a notebook. Weigh-ins were sporadic and
infrequent, and I wasn't really making that much progress (especially considering what I know now).

Wed, 1 Jun 2016 - 290.1 Pounds
Sun, 12 Jun 2016 - 287.0 Pounds
Sat, 9 Jul 2016 - 282.8 Pounds
Sat, 30 Jul 2016 - 283.2 Pounds

My notebook entries continued until 2nd Jan 2017, when I started to use an app called "My Fitness Pal" (MFP) to log my weight instead. The choice of app was largely due to its integration with my new Garmin 820 bike computer, but logging weight was still a manual process. At the same time I also started logging my food and drink intake via MFP... and I logged everything; from a single gram of added salt, to the nuts and seeds I sprinkled on my breakfast on a morning. This lead me to buy some new kitchen scales accurate to 0.1 of a gram, and on March 8th I bought some Garmin scales which (after writing some rather dissatisfied emails to both Garmin and UnderArmor (the company behind MFP)) automatically synchronised my weight and other biometric data (bone mass, body water, body fat, BMI etc) to my Garmin Connect account, and subsequently MFP. This made the laborious task of logging my progress in all my apps a thing of the past.

I was already recording my exercise activity and seeing some mild gains, but this is when my weight loss journey got serious. The beauty of these kind of apps is that they have a huge database of user-managed consumables listing calories and micro-nutrients; you can use your smart phone to scan product barcodes to make logging food easier. It's not 100% accurate - and at first I entered everything manually as I didn't trust the database - but as I learned more about the foods I was eating, the less important it was to log my exact calorie intake; I used it as more of a general guide to make sure that I wasn't over-eating for my lifestyle. I work at home and am quite sedentary most of the day, so my calorie intake should be less than that of a plumber or roofer who's active all day - and it's worth noting that your calorie intake needs to be regularly reviewed based on your current weight, lifestyle and exercise regime in order to keep losing weight whilst remaining healthy. I started on 1,800 calories a day, but reduced it to 1,500 once I hit a plateau and stopped losing weight. MFP helps with this, but you can override all settings with custom values if you so wish.

I'm more familiar with this screen than I am with CSS3

This might all seem a bit too much for some of you. I am a detail oriented person - if I do something, I do it fully and completely to the very best of my abilities - a principal I apply to both my personal and professional lives. You might not need to log as much detail as I have, but it helped me greatly and my weight loss improved massively once I started doing it. I've been using MFP now constantly for 212 days straight... I'm not sure how to stop at this point!

Once I figured out that diet is much, much more important than exercise when it comes to weight loss (and maintaining weight), I started making some significant progress - I cut out refined carbs such as white rice, white bread and white pasta (replacing with brown equivalents), almost entirely excluded carbonated drinks and fruit juices, cut down on added sugar and salt significantly, and stopped snacking (I still occasionally have a cheeky chocolate bar, though). Some people need to make sure they don't have any temptations in the house; we still have a sweetie cupboard, but we also have the willpower to only treat ourselves when we deserve it (after a week of decent weight loss, on a birthday or other special occasion, or on cheat day). Learning about nutrition and how food is processed by my body is the number one offence and defense I have against obesity - and I really can't see me back-sliding now.

At some point I set an initial weight goal of 200lb - that would take me down to around 14 stone, which is right at the top end of the "healthy" part of my BMI scale. You shouldn't fuss too much about BMI though, as it doesn't take into consideration people who are muscular, have particularly dense bones, or have extremely low body fat such as elite athletes. It's still a good guide, and unfortunately the NHS and health insurance companies rely on this metric quite heavily for determining your health status.

So this brings us to the present day. The reason for this blog post is that as of 15th August 2017 I finally hit my goal! I woke up to my fancy Garmin scales showing this:

And whilst that's still a rather large number, be aware that I'm 6' 3" and I have particularly long limbs, big hands and feet, and of course a weighty ego to match. The last 2 months have been the most difficult - I was 210lb on Jun 22nd 2017 and have been struggling to lose that last 10lb since then. Here's my weight loss graph from Garmin Connect:

I've now upped my calorie intake to 1,900 calories a day so I can continue to lose weight, but at a slower rate - I'm aiming for 1 lb per week, but it already looks like I'm exceeding that.

Here are my weight loss stats. They're not 100% accurate as I didn't really log anything at the start, but they give you a good idea:

Weight started at 298lb between April - June 2016. I'm now 198lb (21.3 -> 14.3 stone). Total loss is 100lb or 7 stone in ~471 days.
Body fat is down to 19.9%. First recording was 31.6% when I was 252lb on March 8th 2017, the day I got my Garmin scales. I would imagine that I was closer to 40% when I started. That's about 20% body fat lost.
BMI started at 37.3 (critically obese), now 25 (healthy weight)
Waist started at 48", now 37" (11" loss)
Chest started at 55", now 42" (14" loss)
Bicep started at 16", now 13" (3" loss)

BMI Before

BMI After

Some of this loss will be muscle - I've not really done much exercise other than cycling, so my legs are like tree-trunks but my torso is pretty weak in comparison to what it used to be back in my weight training days. I'm trying to mitigate that at the moment by eating lots of protein, doing more intense swimming, and I'm planning to do some light weight / resistance training - though I will never become a gym bunny again - those days are well behind me.

Here are some before and after pics (apologies if this puts you off your food ;)):

Yeah, I'm not perfect, but I'm certainly a lot happier than I was. I also learned a hell of a lot about food, nutrition and the human body in the process. I'm no nutritionist or personal trainer but here's some of the important points that will stay with me for the rest of my life:

Losing weight is easy; battling with your cravings and "hunger" is not. If you have low willpower then join a weight loss group or find some family members or friends that want to do it with you. Support is very important - I am a very strong willed person, but my wife losing weight at the same time as me made things so much easier.
I am never hungry. Westerners generally don't know hunger, especially those that can afford to buy food. Those pangs you get are just cravings - ignore them or fail.
A lot of patience is required. Unless you're 600lb your weight loss is going to be a gradual, drawn out process. It's a permanent lifestyle change that you must embrace rather than a quick fix / fad diet - which do absolutely nothing except line the pockets of the people that come up with them. There is no pill, no magic wand or words you can utter to make the process quicker. Don't be tempted to starve yourself, skip meals or do excessive amounts of exercise to lose weight faster. 2lb a week is enough, though if you do things right from the start you will lose much more than that initially. Always keep in mind that you are aiming to lose 2lb a week - any more is a bonus but it is not sustainable, and you're only going to be disappointed when you get to a point where you can no longer drop 8lb a week without much effort. Once you get your head around this lifestyle change, you'll never go back - there are many more benefits to weight loss than just looking better.
Learning about what you're eating is the single most important piece of advice I can give you. There are many things I learned during the process; a glass of orange juice contains just as much sugar as a can of coke, and consuming the same amount of sugar that's in a pint of orange juice in whole oranges would be extremely difficult, though it would still be better for you as the fibre in the whole orange helps with the digestion and absorption of the fruit's sugars. Knowing some general facts helps your food decision making process massively. Learn as much as you can about the foods you eat, and work to guidelines you set yourself.
Logging your calorie intake is frustrating, but invaluable. Once I figured out that diet was more important than exercise I logged every single thing that passed my lips using MFP - even water (though water serves a different purpose and is calorie free). A cup of tea with milk and two sugars comes in at around 70 calories. Without sugar it's about 55 calories (I weaned myself off sugar in my tea gradually and now I don't miss it). If you have 8 cups a day (like I used to) that's 560 calories - which is more than a third of your entire daily calorie limit.
Snacking is evil, especially unconscious snacking. I no longer snack during the day and limit my intake in the evening to one or two snacks at the most, after my evening meal.
It's OK to occasionally go over your calorie limit. However, doing it too much will undo all your hard work. Remember that your calorie intake during weight loss will depend directly on your sex, body type, job activity levels and the amount of exercise you do.
Sugar and refined carbs are the enemy, not fat. Regardless of what food manufacturers try to tell us. In fact some fats are good for you - but added sugar is very dangerous - it's just a shame it tastes awesome. Sugar comes in two main forms - fructose and glucose. Fructose is the stuff that tastes sweet and is unfortunately all but useless to our body. Glucose apparently has no taste and is our body's preferred energy source. Because of this, they are both processed differently by our bodies - if you don't need the sugar you're eating (or carbs, for that matter, as they are converted into sugar by your body) it'll be converted straight to fat. Manufacturers add sugars to things like ready meals and pasta sauces in order to make them "taste better" and appeal to us more - avoid these like the plague. When you want to make a pasta dish and you use an off-the-shelf sauce, it contains much more sugar (and salt) than you need. Do you and your family a favour - get some tinned or fresh tomatoes, some fresh herbs and garlic and make your own sauce. It takes no time and in my opinion tastes better anyway. Read more about sugar here.
Hydration, hydration, hydration. Try and drink 2.5 litres of water a day as standard. This not only keeps you hydrated, but helps to fill you up and reduce your cravings. When you're doing exercise drink a lot more water to replace what you lose through sweating - I don't measure my water intake any more, but I drink a lot more than I used to. Generally, if I'm thirsty, I'll get a pint of filtered water. Funnily enough the more water your drink (within limits - drinking too much water can be dangerous) the less water your body retains. Not 100% on how this works, but it's good to know.
Monitoring micro-nutrients can be helpful. If you have bowel issues (my IBS has all but disappeared now), or want to lower or increase your intake of a certain nutrient. Apps like MFP will help you do that - so long as you do your research and don't just rely on what other users have entered into the database. I've only really used this to reduce my salt and sugar intake (to reduce my risk of cardiovascular issues), but it's also good to know which foods are low or high in particular nutrients. I've been inadvertently exceeding my recommended salt intake for years just because I didn't realise that the processed meats I was adding to my salads are extremely high in it.
Meal quality is critical. A full English breakfast is usually around 1,000 calories, but eating one means you can't eat much else for the rest of the day in order to continue to lose weight. Processed meats are also full of saturated fats and salt - too much of which are really bad for your cardiovascular system. A big bowl rammed full of fresh fruit, Greek yogurt, seeds and nuts usually comes in at around 400 calories, but fills you up just as much and is infinitely better for you. If you don't like fruits and vegetables then you need to STFU and deal with it ;) I also learned that apps like MFP don't distinguish between fruit sugar and added sugar - meaning these days MFP often warns me that I've exceed my sugar intake for the day just for breakfast. I generally ignore this because I know those sugars are being processed adequately by my body. I do still have a cooked breakfast once or twice a week, but tend to go for the smaller option at cafes, rather than the "landscaper's Breakfast" with 8 sausages and 46 black puddings.
Cheat. When you're on a 1,500 calorie diet for nearly a year and a half, you need a day off at least once a week. I generally also eat one or two "naughty snacks" a day but I've still cut down on bad foods considerably.
Exercise is not absolutely necessary to lose weight, 95% of the effort is your diet. Saying that, doing exercise is obviously good for you and increases the amount you can eat that day whilst still losing weight. Just be careful to not overeat on exercise days - I've been known to indulge a little more after an 80 mile bike ride that burned ~5,000 calories, only to have put weight on the day after.
Start to think of food as "fuel" for your body. Don't think that your body can deal with a 6 mile swim on just a Mars bar. No matter how much brand advertising and slogans make you think a product is good for you or full of good stuff, do your research before relying on a particular product regularly.
Healthy muscle retains a more water than body fat. I went from 50% body water when I was 250lb to 58% now.
Loose skin is weird. If you're going to be losing lots of weight be prepared to have some sag afterwards, especially if you've been fat for a long time and / or you're excessively overweight. If you're only losing a stone or two you'll be fine, or if you're young and your skin is still elastic. Unfortunately I missed the boat there, but here's to hoping my skin is just being a bit lazy and I can tone up a little bit in the next year or two. I'm still a lot happier than I was when I was fat and my lose skin is hardly noticeable at the moment.
Being skinny is unfathomably more comfortable than being fat. Life in general is easier, happier and less sweaty.
Gadgets are cool, but not necessary. The metrics you get from them can be a double-edged sword, but as long as you don't obsess over them too much they can be a great way to monitor your progress. I don't currently have a 24 hour activity tracker, but I'm planning on getting one to monitor my fitness improvement going forward.
BMI should only be used as a general guide. Definitely don't stress about it too much. Use a mirror and common sense instead.
Putting weight on during weight loss is not the end of the world. As long as its not a trend. If you plateau or start to put weight on and you're being 100% honest with yourself about what you're eating, then you need to do something about it. Also, unless you're doing some intense weight training don't fool yourself into the old "muscle weighs more than fat" bull - your aim is to lose weight, and light to moderate weight / resistance training is apparently one of the best ways to burn fat.
Weigh yourself consistently. Since I got my fancy Garmin scales I weigh myself every day. I do this first thing on a morning before any water / food, and after a poo. The scales automatically upload and sync with all my apps, which makes monitoring your process a lot less laborious. A lot of people prefer to weigh themselves weekly to see larger chunks come off at once, but I like to know that on a daily basis I've been doing things right the day before. This has allowed me to monitor trends in my consumption and make minor changes in my diet to lose weight quicker. I can pretty much predict how much I'll put on or lose each day now.
Weight loss will start quick, but slow down. Initially I was losing up to 8lb per week - that slowed down over time (doing the same stuff) until I plateaued.
You WILL plateau at some point. I thought I was safe from this - I lost well over 80lb before it happened to me. When it happens lower your calorie intake, do more regular / intense exercise, or take stock on the amount or type of foods that you're eating. I started doing interval training at the pool (as well as doing around 420 miles on the bike per month), and lowered my "sedentary" calorie intake from 1,800 to 1,500 calories - I started to lose 2lb+ per week again after I did that.

This is probably one of the biggest personal achievements of my life so far, and has been all-consuming for the best part of 2 years. The upshot is that I am now health-aware and have a much more active, healthy and happy lifestyle. Yes, eating all the food you want is awesome, but being fat is most definitely NOT awesome - and you can't have it both ways. I've considerably lowered my risk of cardiovascular disease, diabetes and numerous other potential issues, but the most noticeable thing has been that I no longer have constant aches and pains in my knees and other parts of my body. I sleep better, feel better and am generally more positive about life.

So, how did I celebrate this momentous occasion? I had the day off work, went out for a nice meal in the countryside with my wife, then chilled out all evening. Not particularly exciting to most, but it was ideal for me!

Deleting All PlayerPrefs for your Unity3D Project on Windows

2015-01-09T16:28:00.001+00:00

As a Unity3D developer you'll eventually find yourself wanting to delete the PlayerPrefs for a particular project on a regular basis. I've been recently using PlayerPrefs to store player settings like FOV, mouse sensitivity and acceleration, and I wanted to make sure that my code will run on a freshly installed machine as well as my development box.

If you click the link above, you'll see that all PlayerPrefs are stored in the registry - for a Windows machine at least. So, instead of writing some delete-later code to run PlayerPrefs.DeleteAll(), you can just create a simple registry file that you can click on whenever you want to clean up your machine. All you need to do is prefix the registry key of your project with a dash (-), and run the file.

Below is a sample file for Windows 8.1 - you'll need to alter the Registry Editor version at the top of the file if you're using any other version of Windows, but it should still work:

Windows Registry Editor Version 5.00

[-HKEY_CURRENT_USER\Software\Shadow Moses Developments Ltd\Subnet-POC]

Simple, yet effective. Happy coding!

Why GUIDs Are a Bad Choice for SQL Server Key and Identity Columns

2014-02-11T11:56:00.000+00:00

I've spent many years debating with my fellow developers across all manor of subjects (I do love to talk), but one subject that comes up time and time again is the usage of the UNIQUEIDENTIFIER data type in SQL Server; especially when they're used as identity and key columns. In fact, I see this more often than you would expect, and misconfigured UNIQUEIDENTIFIER columns can create "hidden" problems that can be difficult to discover and / or rectify, depending on the SQL experience throughout your team.
If you care to ask around your colleagues, it's almost guaranteed that you'll get multiple conflicting opinions on why you should or should not use GUIDs in SQL Server, but few developers actually realise the impact of such a design choice. In the interests of science, I'll try and keep this as factual as possible, and I'll focus on the topic at hand, rather than GUIDs / UUIDs in general.

Here's a list of common reasons for using a GUID in your database design:

You require an unpredictable seed for your records as it is exposed directly via some kind of public interface
You need a globally unique identifier for the record.
You have more than 2^31 (2,147,483,647) records in your table
You have more than 2^63 (9,223,372,036,854,775,807) records in your table
You use GUIDs all the time / you prefer GUIDs as they're a more secure solution

From the above, the only really acceptable scenario is no. 4. There aren't many tables in the world that hold 9.2 quintillion records, but if you happen to have one I can guarantee that choosing a data type for its primary key is the least of your problems (and you probably won't be reading this anyway)!

So here's a list of reasons - with code examples and supporting facts - as to why GUIDs are a bad design choice when it comes to SQL Server identity and key columns. I'm using primitive benchmarking techniques, but it still should give you a good idea of what you're up against - I will be inserting 1 million records into each table I test, and each table will have a single column with the data type and / or indices that are being tested.

First off, create a database - I've called mine "test", cos that's how I roll.

CREATE DATABASE test;

And before I begin my assault, let's write a bit of SQL to compare the three data types:

USE test;
GO
SET NOCOUNT ON
GO
IF OBJECT_ID('MyInt') IS NOT NULL
 DROP TABLE MyInt
GO
IF OBJECT_ID('MyBigInt') IS NOT NULL
 DROP TABLE MyBigInt
GO
IF OBJECT_ID('MyGuid') IS NOT NULL
 DROP TABLE MyGuid
GO
IF OBJECT_ID('MyGuidSeq') IS NOT NULL
 DROP TABLE MyGuidSeq
GO
CREATE TABLE MyInt (
    ID INT PRIMARY KEY IDENTITY
)
GO
CREATE TABLE MyBigInt (
    ID BIGINT PRIMARY KEY IDENTITY
)
GO
CREATE TABLE MyGuid (
    ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID()
)
GO
CREATE TABLE MyGuidSeq (
    ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID()
)
GO
DECLARE @StartTime DATETIME2, @Endtime DATETIME2, @i INT, @noRecords INT
SET @noRecords = 1000000
-- MyInt
SET @i = 1
SET @StartTime = GETDATE()
WHILE (@i <= @noRecords)
 BEGIN
 INSERT INTO MyInt DEFAULT VALUES
 SET @i = @i + 1
 END
SET @EndTime = GETDATE()
PRINT 'MyInt Time Taken: ' + CONVERT(VARCHAR, DATEDIFF(ms, @StartTime, @EndTime)) + 'ms'
-- MyBigInt
SET @i = 1
SET @StartTime = GETDATE()
WHILE (@i <= @noRecords)
 BEGIN
 INSERT INTO MyBigInt DEFAULT VALUES
 SET @i = @i + 1
 END
SET @EndTime = GETDATE()
PRINT 'MyBigInt Time Taken: ' + CONVERT(VARCHAR, DATEDIFF(ms, @StartTime, @EndTime)) + 'ms'
-- MyGuid
SET @i = 1
SET @StartTime = GETDATE()
WHILE (@i <= @noRecords)
 BEGIN
 INSERT INTO MyGuid DEFAULT VALUES
 SET @i = @i + 1
 END
SET @EndTime = GETDATE()
PRINT 'MyGuid Time Taken: ' + CONVERT(VARCHAR, DATEDIFF(ms, @StartTime, @EndTime)) + 'ms'
-- MyGuidSeq
SET @i = 1
SET @StartTime = GETDATE()
WHILE (@i <= @noRecords)
 BEGIN
 INSERT INTO MyGuidSeq DEFAULT VALUES
 SET @i = @i + 1
 END
SET @EndTime = GETDATE()
PRINT 'MyGuidSeq Time Taken: ' + CONVERT(VARCHAR, DATEDIFF(ms, @StartTime, @EndTime)) + 'ms'
exec sp_spaceused 'MyInt'
exec sp_spaceused 'MyBigInt'
exec sp_spaceused 'MyGuid'
exec sp_spaceused 'MyGuidSeq'

So, here we're inserting 1 million records with their default values into 4 separate tables. I've written two tests for the UNIQUEIDENTIFIER data type - one to test Identity creation using NEWID() and NEWSEQUENTIALID() respectively. The results are predictable, but interesting. I've added a little bit extra formatting for clarity:

MyInt Time Taken: 105,080ms
MyBigInt Time Taken: 104,380ms
MyGuid Time Taken: 159,670ms
MyGuidSeq Time Taken: 102,950ms

Table name  No. Rows   Reserved Space  Actual space  Index Size  Unused Space
MyInt       1,000,000  12,936 KB       12,864 KB     56 KB       16 KB
MyBigInt    1,000,000  16,904 KB       16,808 KB     88 KB       8 KB
MyGuid      1,000,000  34,760 KB       34,552 KB     160 KB      48 KB
MyGuidSeq   1,000,000  24,968 KB       24,768 KB     176 KB      24 KB

And now the critique...

1. Table Storage Size

"Disk Space is Cheap!" I hear you cry. Indeed it is, but why use more than you have to? This is probably the most contentious (but probably least important) issue surrounding the use of GUIDs in SQL Server, mainly because it's easy to have an opinion on it. I think it's safe to say that most developers understand how basic disk allocation works - but often enterprise storage systems are a lot more complex than your developer box.

So, on to the facts. INT columns have a numerical range of -2^31 to 2^31, and store only 4 bytes. BIGINT columns have a numerical range of -2^63 to 2^63, taking up 8 bytes. UNIQUEIDENTIFIER columns can have 2^122 possible combinations, but store a whopping 16 bytes; this results in noticeably larger table sizes when using a UNIQUEIDENTIFIER over the recommended key data types.

You'll have probably noticed that MyGuid is larger than MyGuidSeq; this is because SQL Server tables with a clustered index are stored in the indices physical order, something which I was unaware of until I posted this stack overflow question! I actually thought that clustered indices were a separate entity, stored along side the table.

So then, to make it űberfair, let's take the MyGuidSeq table and work out how much more storage is uses than the MyBigInt table: 24,986 - 16,904 = 8,064Kb extra storage per 1 million rows at its best, 21,824Kb at it's worst. Not much on its own, but consider that a table is likely to be much larger than that, and using the column as a foreign key in other tables means your storage requirements will rapidly expand.

2. Index Size

The above table also helps when comparing the size of UNIQUEIDENTIFIER, INT and BIGINT based indices. It's obvious for reasons already mentioned that a UNIQUEIDENTIFIER will use more space than an INT or BIGINT, and whilst the numbers in the Index Size column above are tiny for now, their sizes will increase considerably when more columns are included or referenced in the index. You can also see that both UNIQUEIDENTIFIER columns use roughly double or more space than even a BIGINT; the size of an index has repercussions, which I will address later in this article.

3. Index Fragmentation

The fragmentation of an SQL Server index often goes unchecked - in fact, most developers that work with databases don't even know that indices can get fragmented, yet it can often be the first place to look when you run into sudden performance issues in your software.

Index fragmentation occurs when a new page is inserted into an existing index, and the index does not have space for the page. Pages can be inserted or moved around for lots of different reasons, but the most common are inserting new records, and updating indexed columns in existing records.

The following SQL will give us stats on all four tables:

SELECT
    I.name,
    ROUND(S.avg_fragmentation_in_percent, 2) AS FragmentationPercent,
    i.fill_factor,
    s.page_count
FROM sys.dm_db_index_physical_stats (DB_ID('test'), OBJECT_ID('MyInt'), NULL, NULL, NULL) S
INNER JOIN sys.indices I (nolock) ON (
    S.object_id = I.object_id
 AND S.index_id = I.index_id
)
UNION
SELECT
    I.name,
    ROUND(S.avg_fragmentation_in_percent, 2) AS FragmentationPercent,
    i.fill_factor,
    s.page_count
FROM sys.dm_db_index_physical_stats (DB_ID('test'), OBJECT_ID('MyBigInt'), NULL, NULL, NULL) S
INNER JOIN sys.indices I (nolock) ON (
    S.object_id = I.object_id
 AND S.index_id = I.index_id
)
UNION
SELECT
    I.name,
    ROUND(S.avg_fragmentation_in_percent, 2) AS FragmentationPercent,
    i.fill_factor,
    s.page_count
FROM sys.dm_db_index_physical_stats (DB_ID('test'), OBJECT_ID('MyGuid'), NULL, NULL, NULL) S
INNER JOIN sys.indices I (nolock) ON (
    S.object_id = I.object_id
 AND S.index_id = I.index_id
)
UNION
SELECT
    I.name,
    ROUND(S.avg_fragmentation_in_percent, 2) AS FragmentationPercent,
    i.fill_factor,
    s.page_count
FROM sys.dm_db_index_physical_stats (DB_ID('test'), OBJECT_ID('MyGuidSeq'), NULL, NULL, NULL) S
INNER JOIN sys.indices I (nolock) ON (
    S.object_id = I.object_id
 AND S.index_id = I.index_id
)

And here are the results:

name                            FragmentationPercent  fill_factor   page_count
PK__MyGuidSe__3214EC276477ECF3  0.71                  0             3096
PK__MyGuid__3214EC275FB337D6    99.08                 0             4343
PK__MyInt__3214EC275812160E     0.44                  0             1608
PK__MyBigInt__3214EC275BE2A6F2  0.48                  0             2101

There's a tiny amount of fragmentation on the INT and BIGINT tables, and a little bit more for UNIQUEIDENTIFIER when NEWSEQUENTIALID() is used, but as you can see, the MyGuid table (which uses NEWID() as the column default value) is almost 100% fragmented. This is bad, in case you haven't been following. Fragmentation slows down the retrieval of data from the table, meaning the SQL query engine has more work to do when it needs to scan the index.

So why does NEWID() fragment the index so much? Well, NEWID() produces a pseudo-random GUID which is pretty much unpredictable, and 100% non-sequential. By their very nature, non-sequential rows are inserted into the middle of an index, whereas sequential rows are generally appended to the end of an index. When an insertion takes place, SQL Server will move pages around so that the new rows will fit in the space allocated to that particular page. If there's no space available in the page - or a new page won't fit in the space available - SQL Server will create the page at the end of the index. This causes index fragmentation.

Let's just take a very quick look at how quickly the MyGuid table gets to 99% fragmentation:

USE test;
GO
SET NOCOUNT ON
GO
IF OBJECT_ID('MyGuid') IS NOT NULL
 DROP TABLE MyGuid
GO
CREATE TABLE MyGuid (
    ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID()
)
DECLARE @StartTime DATETIME2, @Endtime DATETIME2, @i INT, @noRecords INT
DECLARE @Fragmentation DECIMAL(4,2)
SET @noRecords = 1000000
-- MyGuid
SET @i = 1
SET @StartTime = GETDATE()
WHILE (@i <= @noRecords)
 BEGIN
 INSERT INTO MyGuid DEFAULT VALUES
        
 SELECT
            @Fragmentation = ROUND(S.avg_fragmentation_in_percent, 2)
 FROM sys.dm_db_index_physical_stats (DB_ID('test'), OBJECT_ID('MyGuid'), NULL, NULL, NULL) S
        
 IF(@Fragmentation > 99)
 BEGIN
 PRINT '99% fragmentation after ' + CONVERT(VARCHAR, @i) + ' records'
 BREAK
 END
 SET @i = @i + 1
 END
SET @EndTime = GETDATE()
PRINT 'MyGuid Time Taken: ' + CONVERT(VARCHAR, DATEDIFF(ms, @StartTime, @EndTime)) + 'ms'
exec sp_spaceused 'MyGuid', true

Output:

99% fragmentation after 23495 records

So, without anything else going on with the table, we can insert 23,495 records before the table is fully fragmented. As it stands, this doesn't mean too much - as you'd want to avoid getting to 99% fragmentation anyway (by managing any indices that become more than 5% fragmented - but that's a whole other article). So, I also ran the above test with 5% and 30% fragmentation values, and both got to 324 records before becoming fragmented. Regardless of the reasons for this, I think you'll agree that having to perform index maintenance after 324 new records is pretty unreasonable.

We haven't even touched upon the differences between Internal and External fragmentation, CLUSTERED and NON CLUSTERED indices, and how to set up index specific properties such as fill factors. There is a lot more to database design than initially presents itself; I remember learning all about normal form at college, which - whilst important - doesn't prepare you for all the funky under-the-hood mechanics that goes on in the real world.

I won't go into any more detail here, as there are hundreds of articles out there explaining fragmentation and at what point you should reorganize or rebuild an index. A while back I also wrote a script to assist with automated index management which I still find quite handy, though it should be noted that the more complex your database, the more specific you should be about individual indices.

The only thing that's left for me to say on index fragmentation is this: if you absolutely must use a UNIQUEIDENTIFIER as your primary key (you don't, btw), then ALWAYS use NEWSEQUENTIALID() over NEWID(). However, if your reason for using a GUID is that you want a unique identifier that's difficult to guess, then you're shit out of luck. Unfortunately NEWSEQUENTIALID() generated identifiers are predictable.

NEWSEQUENTIALID() also inherently helps to keep indices contiguous - filling data and index pages fully before creating a new page. However, it stands to reason that - due to the fact NEWSEQUENTIALID() is reset when Windows is restarted (and can subsequently start at a lower GUID seed value than the last seed) - that the use of NEWSEQUENTIALID() can still insert pages into the middle of an index, fragmenting the index quicker than you otherwise would with an INT or BIGINT.

Also worth mentioning - if you ever update a UNIQUEIDENTIFIER column that is indexed, even if you use NEWSEQUENTIALID(), it will increase fragmentation quicker over time. Saying that, the same would apply to an INT or BIGINT column, as the new value is likely to create a new page in the index.

4. Join and Index Lookup Speed

Ignoring the NEWID() tables I created earlier (I think it's safe to say that I have already provided enough evidence that NEWID() is not suitable for use in a default constraint on a key column), we can now take a look at how quickly joins and lookups perform across the data types. If you're coding along with me, the below SQL will take a while (we're generating and inserting 6 million rows, and doing 3 million individual lookups - I would NOT recommend doing anything even remotely like this on a production server). Set it running and go and make a brew, hug your wife, feed your cat, or snuggle your guinea pigs (you have guinea pigs, right?). This takes around 11 minutes to run on my development PC:

USE test;
GO
SET NOCOUNT ON
GO
IF OBJECT_ID('MyInt') IS NOT NULL
 DROP TABLE MyInt
GO
IF OBJECT_ID('MyIntJoin') IS NOT NULL
 DROP TABLE MyIntJoin
GO
IF OBJECT_ID('MyBigInt') IS NOT NULL
 DROP TABLE MyBigInt
GO
IF OBJECT_ID('MyBigIntJoin') IS NOT NULL
 DROP TABLE MyBigIntJoin
GO
IF OBJECT_ID('MyGuidSeq') IS NOT NULL
 DROP TABLE MyGuidSeq
GO
IF OBJECT_ID('MyGuidSeqJoin') IS NOT NULL
 DROP TABLE MyGuidSeqJoin
GO
CREATE TABLE MyIntJoin (
    ID INT PRIMARY KEY IDENTITY
)
GO
CREATE TABLE MyBigIntJoin (
    ID BIGINT PRIMARY KEY IDENTITY
)
GO
CREATE TABLE MyGuidSeqJoin (
    ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID()
)
GO
CREATE TABLE MyInt (
    ID INT PRIMARY KEY IDENTITY,
    fk INT FOREIGN KEY REFERENCES MyIntJoin(ID)
)
GO
CREATE TABLE MyBigInt (
    ID BIGINT PRIMARY KEY IDENTITY,
    fk BIGINT FOREIGN KEY REFERENCES MyBigIntJoin(ID)
)
GO
CREATE TABLE MyGuidSeq (
    ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    fk UNIQUEIDENTIFIER FOREIGN KEY REFERENCES MyGuidSeqJoin(ID)
)
GO
DECLARE @StartTime DATETIME2, @Endtime DATETIME2, @i INT, @noRecords INT
DECLARE @fkInt INT, @fkBigInt BIGINT, @fkGuid UNIQUEIDENTIFIER
SET @noRecords = 1000000
-- Join tables
-- MyInt
SET @i = 1
WHILE (@i <= @noRecords)
 BEGIN
 INSERT INTO MyIntJoin DEFAULT VALUES
 SET @i = @i + 1
 END
-- MyBigInt
SET @i = 1
WHILE (@i <= @noRecords)
 BEGIN
 INSERT INTO MyBigIntJoin DEFAULT VALUES
 SET @i = @i + 1
 END
-- MyGuidSeq
SET @i = 1
WHILE (@i <= @noRecords)
 BEGIN
 INSERT INTO MyGuidSeqJoin DEFAULT VALUES
 SET @i = @i + 1
 END
-- Source Tables
-- MyInt
SET @i = 1
WHILE (@i <= @noRecords)
 BEGIN
 -- Just get a random ID between 1 and @noRecords
 SELECT
            @fkInt = ABS(CAST(CAST(NEWID() AS VARBINARY) AS int)) % @noRecords
        
 INSERT INTO MyInt(fk)
 VALUES(@fkInt)
 SET @i = @i + 1
 END
-- MyBigInt
SET @i = 1
WHILE (@i <= @noRecords)
 BEGIN
 -- Just get a random ID between 1 and @noRecords
 SELECT
            @fkBigInt = ABS(CAST(CAST(NEWID() AS VARBINARY) AS int)) % @noRecords
        
 INSERT INTO MyBigInt(fk)
 VALUES(@fkBigInt)
 SET @i = @i + 1
 END
-- MyGuidSeq
SET @i = 1
WHILE (@i <= @noRecords)
 BEGIN
 -- Getting a random row efficiently is a bit more complex
 SELECT TOP 1
            @fkGuid = ID
 FROM MyGuidSeqJoin
 WHERE (ABS(CAST((BINARY_CHECKSUM(id, NEWID())) as int)) % 100) < 1 -- Only process 1%
        
 INSERT INTO MyGuidSeq(fk)
 VALUES(@fkGuid)
 SET @i = @i + 1
    END

Back with me? Good... so, what we're doing here is generating some primary key values in three tables (the ones suffixed with Join) as we did before. After that, we're randomly inserting some of those foreign key values into each source table, so we can perform lookups and joins against them. The inefficient bit is getting the random foreign key value from the Join tables - so for the int and bigint tables, I've just generated a random value between 1 and 1,000,000 (as we can guess the PKs for those tables). However, because I'm unable to re-generate the GUID values out of thin air (like I did with the int and bigint keys), I have to retrieve a random row from the MySeqGuidJoin table directly. I've used the most efficient method I could muster (generating a checksum for each row and only processing the top 1% of records), which is nearly as efficient as the random number generation, but a lot more cumbersome. I initially used the ORDER BY NEWID() method to get random values, but this is way too slow and inefficient.

Ok, so let's just check the fragmentation on the newly created tables as before to make sure we're getting similar results. I won't paste the SQL again (you can re-use the sys.dm_db_index_physical_stats statements above and apply the new table names):

name                            FragmentationPercent  fill_factor  page_count
PK__MyInt__3214EC272F9A1060     0.38                  0            2101
PK__MyIntJoi__3214EC272334397B  0.37                  0            1608
PK__MyGuidSe__3214EC2739237A9A  0.67                  0            5077
PK__MyBigInt__3214EC272704CA5F  0.57                  0            2101
PK__MyGuidSe__3214EC272AD55B43  0.71                  0            3096
PK__MyBigInt__3214EC27345EC57D  0.45                  0            3096

So, things are looking good to start with. Low fragmentation on all the indices, and we have 1 million records in each of the 6 tables for us to test with.

First the join speed tests. We're going to return all 1,000,000 records in each source table (MyInt, MyBigInt and MyGuidSeq) and time the results.

USE test;
GO
SET NOCOUNT ON
GO
IF OBJECT_ID('MyInt') IS NOT NULL
 DROP TABLE MyInt
GO
IF OBJECT_ID('MyIntJoin') IS NOT NULL
 DROP TABLE MyIntJoin
GO
IF OBJECT_ID('MyBigInt') IS NOT NULL
 DROP TABLE MyBigInt
GO
IF OBJECT_ID('MyBigIntJoin') IS NOT NULL
 DROP TABLE MyBigIntJoin
GO
IF OBJECT_ID('MyGuidSeq') IS NOT NULL
 DROP TABLE MyGuidSeq
GO
IF OBJECT_ID('MyGuidSeqJoin') IS NOT NULL
 DROP TABLE MyGuidSeqJoin
GO
CREATE TABLE MyIntJoin (
    ID INT PRIMARY KEY IDENTITY
)
GO
CREATE TABLE MyBigIntJoin (
    ID BIGINT PRIMARY KEY IDENTITY
)
GO
CREATE TABLE MyGuidSeqJoin (
    ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID()
)
GO
CREATE TABLE MyInt (
    ID INT PRIMARY KEY IDENTITY,
    fk INT FOREIGN KEY REFERENCES MyIntJoin(ID)
)
GO
CREATE TABLE MyBigInt (
    ID BIGINT PRIMARY KEY IDENTITY,
    fk BIGINT FOREIGN KEY REFERENCES MyBigIntJoin(ID)
)
GO
CREATE TABLE MyGuidSeq (
    ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    fk UNIQUEIDENTIFIER FOREIGN KEY REFERENCES MyGuidSeqJoin(ID)
)
GO
DECLARE @i INT, @noRecords INT
DECLARE @fkInt INT, @fkBigInt BIGINT, @fkGuid UNIQUEIDENTIFIER
SET @noRecords = 10000000
-- Join tables
-- MyInt
SET @i = 1
WHILE (@i <= @noRecords)
 BEGIN
 INSERT INTO MyIntJoin DEFAULT VALUES
 SET @i = @i + 1
 END
-- MyBigInt
SET @i = 1
WHILE (@i <= @noRecords)
 BEGIN
 INSERT INTO MyBigIntJoin DEFAULT VALUES
 SET @i = @i + 1
 END
-- MyGuidSeq
SET @i = 1
WHILE (@i <= @noRecords)
 BEGIN
 INSERT INTO MyGuidSeqJoin DEFAULT VALUES
 SET @i = @i + 1
 END
-- Source Tables
-- MyInt
SET @i = 1
WHILE (@i <= @noRecords)
 BEGIN
 -- Just get a random ID between 1 and @noRecords
 SELECT
            @fkInt = (ABS(CAST(CAST(NEWID() AS VARBINARY) AS int)) % @noRecords) + 1
        
 INSERT INTO MyInt(fk)
 VALUES(@fkInt)
 SET @i = @i + 1
 END
-- MyBigInt
SET @i = 1
WHILE (@i <= @noRecords)
 BEGIN
 -- Just get a random ID between 1 and @noRecords
 SELECT
            @fkBigInt = (ABS(CAST(CAST(NEWID() AS VARBINARY) AS int)) % @noRecords) + 1
        
 INSERT INTO MyBigInt(fk)
 VALUES(@fkBigInt)
 SET @i = @i + 1
 END
-- MyGuidSeq
SET @i = 1
WHILE (@i <= @noRecords)
 BEGIN
 -- Getting a random row efficiently is a bit more complex
 SELECT TOP 1
            @fkGuid = ID
 FROM MyGuidSeqJoin
 WHERE (ABS(CAST((BINARY_CHECKSUM(id, NEWID())) as int)) % 100) < 1 -- Only process 1%
        
 INSERT INTO MyGuidSeq(fk)
 VALUES(@fkGuid)
 SET @i = @i + 1
    END

And the results:

MyInt Time Taken: 4,727ms
MyBigInt Time Taken: 4,812ms
MyGuidSeq Time Taken: 5,687ms

So, for a table that contains 1 million records, int and bigint perform similarly. UNIQUEIDENTIFIER increases the lookup by around 0.8 seconds (I ran this a few times and the results were consistent). This isn't the end of the world, but this still shows that GUID lookups are slower than both INT and BIGINT; and remember, these tables have no other columns, constraints, indices or keys - just a single primary key (and its clustered index), and a foreign key constraint.

I ran the same tests above with 10,000,000 rows to see what impact it would have on larger data sets. Here are the results - suffice to say that my dev PC was out of order for the afternoon whilst it generated over 1.5GB of data:

MyInt Time Taken: 47,357ms
MyBigInt Time Taken: 48,970ms
MyGuidSeq Time Taken: 58,940ms

So, about 11 seconds difference - which makes it a bit more obvious that GUID keys are slower than bigint, and int. These numbers include the time it takes to retrieve the data from the server - which probably makes this specific test a bit unfair.

These times may not seem like much, but yet again, it will be further affected by larger table sizes, larger indices, more complex queries.

4. Uniqueness

7895D7B4-6402-4552-A7CE-D3B48C5256CC

Apparrently, no one has ever seen the above string before now...

OK. This is a bit of a daft point to make, but it's still valid in my sick little mind. Let's start with the facts: there can be 2^122 GUID possibilities (that's 5,316,911,983,139,663,491,615,228,241,121,400,000 - a number I would struggle to say out loud, let alone ever expect to reach in terms of the number of rows in a table). This makes it extremely unlikely you'll ever collide with another GUID, but it is still possible, and as a defensive programmer, I try my best to cater for any and all possibilities. The more GUIDs you generate, the more likely it is you'll collide with an already generated GUID.

Using NEWSEQUENTIALID() gets around the uniqueness issue, although theoretically, it could make things even worse. The more often you restart the SQL box, the more likely it is that you'll get a conflict - and if you do, it won't be possible to insert another record until you restart the server. If you get a conflict in this situation, it's because the conflicted GUID, plus the range of sequential GUIDs generated after the conflicted GUID, have already been generated.

The possibility of a GUID conflict is insignificant. For this reason, most developers and database designers will largely ignore uniqueness as a concern when considering GUIDs as primary keys. It's a fair point, and not one I argue against very often - but I can tell you that I'm weirdly looking forward to the day I see a GUID conflict, so I can make notes, screenshot it and use it as physical proof that I was right all along! :P

6. Type Comparison

I decided to perform a little test on how SQL deals with comparisons between data types. I wanted to compare two INT variables, an INT and a VARCHAR, two UNIQUEIDENTIFIERs, then a UNIQUEIDENTIFIER and a VARCHAR. The only way I could think to reasonably test this was the following:

USE test;
GO
DECLARE @noTests INT, @i INT
DECLARE @StartTime DATETIME2, @EndTime DATETIME2
DECLARE @int1 INT, @int2 INT, @int3 VARCHAR(10)
DECLARE @guid1 UNIQUEIDENTIFIER, @guid2 UNIQUEIDENTIFIER, @guid3 VARCHAR(36)
SET @noTests = 100000000
SET @int1 = 76253421
SET @int2 = 93948585
SET @int3 = '348975764'
SET @guid1 = NEWID()
SET @guid2 = NEWID()
SET @guid3 = NEWID()
SET @i = 1
SET @StartTime = GETDATE()
WHILE (@i <= @noTests)
 BEGIN
 IF(@int1 = @int2)
 BEGIN
 PRINT '' -- Will never get called
 END
 SET @i = @i + 1
 END
SET @EndTime = GETDATE()
PRINT 'Int1 = Int2 Time Taken: ' + CONVERT(VARCHAR, DATEDIFF(ms, @StartTime, @EndTime)) + 'ms'
SET @i = 1
SET @StartTime = GETDATE()
WHILE (@i <= @noTests)
 BEGIN
 IF(@int1 = @int3)
 BEGIN
 PRINT '' -- Will never get called
 END
 SET @i = @i + 1
 END
SET @EndTime = GETDATE()
PRINT 'Int1 = Int3 Time Taken: ' + CONVERT(VARCHAR, DATEDIFF(ms, @StartTime, @EndTime)) + 'ms'
SET @i = 1
SET @StartTime = GETDATE()
WHILE (@i <= @noTests)
 BEGIN
 IF(@guid1 = @guid2)
 BEGIN
 PRINT '' -- Will never get called
 END
 SET @i = @i + 1
 END
SET @EndTime = GETDATE()
PRINT 'Guid1 = Guid2 Time Taken: ' + CONVERT(VARCHAR, DATEDIFF(ms, @StartTime, @EndTime)) + 'ms'
SET @i = 1
SET @StartTime = GETDATE()
WHILE (@i <= @noTests)
 BEGIN
 IF(@guid1 = @guid3)
 BEGIN
 PRINT '' -- Will never get called
 END
 SET @i = @i + 1
 END
SET @EndTime = GETDATE()
PRINT 'Guid1 = Guid3 Time Taken: ' + CONVERT(VARCHAR, DATEDIFF(ms, @StartTime, @EndTime)) + 'ms'

So, I'm looping 100,000,000 times for each test and timing the results:

Int1 = Int2 Time Taken: 50,043ms
Int1 = Int3 Time Taken: 62,137ms
Guid1 = Guid2 Time Taken: 52,333ms
Guid1 = Guid3 Time Taken: 72,560ms

The results clearly show that SQL server deals with strongly typed comparisons similarly - UNIQUEIDENTIFIER to UNIQUEIDENTIFIER comparisons are marginally slower than INT to INT (by 2 seconds), INT to VARCHAR takes considerably longer (12 seconds), and UNIQUEIDENTIFIER to VARCHAR is even slower (22 seconds).

100,000,000 records is a fair amount to test with, but it still shows that UNIQUEIDENTIFIER comparisons are slower than INT.

7. Retrieving recently inserted identity values

So, you've just inserted a record into a table, and you want to get the last generated IDENTITY value for use in your application. If you have any experience with T-SQL, I would imagine you're thinking about @@IDENTIFIER or SCOPE_IDENTITY() right now:

INSERT INTO MyInt(fk)
VALUES(2)
SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()

Both SELECT statements above return the same results in this instance, but they wouldn't 100% of the time. It is extremely handy having both @@IDENTITY and SCOPE_IDENTITY() immediately available for INT and BIGINT columns, even if you don't use them both that often (you should use SCOPE_IDENTITY() most of the time).

We already know that a UNIQUEIDENTIFIER column cannot have an IDENTITY specification, and that @@IDENTITY and SCOPE_IDENTITY() both return INT or BIGINT, but what happens when you want the most recently inserted DEFAULT value, such as the NEWSEQUENTIALID() default defined below?

CREATE TABLE IdentityTest (
    Id UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID()
)
 SELECT Id FROM IdentityTest

In the above instance, a simple SELECT will do the trick, but when you have multiple rows and multiple columns, it becomes impossible. When using NEWSEQUENTIALGUID() you can't rely on an ORDER BY Id DESC clause to get you the latest value, because if the server restarts, it's entirely possible for the new DEFAULT seed value to start below the last seed value.

There is a solution, but it is (in my opinion) cumbersome, more intensive (if only marginally), and only caters for the current scope:

CREATE TABLE IdentityTest (
    Id UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID()
)
DECLARE @IdentityTest TABLE (
    Id UNIQUEIDENTIFIER
)
INSERT INTO IdentityTest
 OUTPUT INSERTED.Id INTO @IdentityTest(ID)
DEFAULT VALUES
SELECT Id
 FROM @IdentityTest

So, we CAN get the newly inserted GUID value, but it's not pretty.

8. SSIS / BIDS / SSDT Handling

If you're writing an enterprise level database in SQL Server, it's very likely that at some point, you - or someone else - will run an SSIS package against it. If your primary keys are GUIDs, this creates more work for any BI tasks, as SSIS (both BIDS and SSDT) doesn't deal with GUID data very well.

Say, for example, you store a GUID as an SSIS package variable - first of all, you have to store it as a string. While this is all well and good, it's when you come to compare the variable in an SSIS expression (such as inside an Execute SQL Task or OLE DB Source) that it starts to become difficult. Take the following expression:

[CustomerId] == @[User::CustomerIdVariable]

CustomerId is a UNIQUEIDENTIFIER column (DT_GUID in SSIS), and CustomerIdVariable is (surprise, surprise) a string variable storing a GUID. One would expect an implicit cast to take place, but noooooo... instead, we get error after error until we stumble upon the correct cast, conversion and comparison. So, let's try casting CustomerIdVariable to a DT_GUID:

[CustomerId] == (DT_GUID)@[User::CustomerIdVariable]

Nope, doesn't like that... after a bit of Googling, you'll eventually discover that you should wrap any GUID data in curly brackets in order to work with it:

[CustomerId] == (DT_GUID)("{" + @[User::CustomerIdVariable] + "}")

But that doesn't work either:

Error at Process results into database [Create additional columns [158]]: The data type "DT_GUID" cannot be used with binary operator "==". The type of one or both of the operands is not supported for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Error at Process results into database [Create additional columns [158]]: Attempt to set the result type of binary operation "CustomerId == (DT_GUID)("{" + @[User::CustomerIdVariable] + "}")" failed with error code 0xC0047081.

Error at Process results into database [Create additional columns [158]]: Computing the expression "[CustomerId] == (DT_GUID)("{" + @[User::CustomerIdVariable] + "}")" failed with error code 0xC0047084. The expression may have errors, such as divide by zero, that cannot be detected at parse time, or there may be an out-of-memory error.

Error at Process results into database [Create additional columns [158]]: The expression "[CustomerId] == (DT_GUID)("{" + @[User::CustomerIdVariable] + "}")" on "output column "MyColumn" (222)" is not valid.

Error at Process results into database [Create additional columns [158]]: Failed to set property "Expression" on "output column "Mycolumn" (222)".

Which broadly translates as "Sorry, me no understand what GUID is - do not use a UNIQUEIDENTIFIER column in an expression, ever", without any good explaination as to why, which is nice. Naturally, the next step would be to try and convert the UNIQUEIDENTIFIER column to a string:

(DT_WSTR,36)[CustomerId] == @[User::CustomerIdVariable]

But guess what? This STILL doesn't work... the actual solution is to to convert the UNIQUEIDENTIFIER column to a string, and wrap the variable in curly brackets, as such:

(DT_WSTR,36)[CustomerId] == ("{" + @[User::CustomerIdVariable] + "}")

And I'm not going to even get into non-unicode conversions.

This is an issue with SSIS rather than the use of a UNIQUEIDENTIFIER in your table, but it's still worth bearing in mind when designing your DB. If all of your primary and foreign keys are GUIDs, your BI guys aren't gonna thank you for it.

Summary

I'm not saying GUIDs are useless, or should be totally avoided - I'm just yet to come across a situation where using a GUID as a key column is a better option than an INT or a BIGINT.

If you genuinely need a unique and unpredictable GUID for any reason, then simply create a separate UNIQUEIDENTIFIER column in your table, but don't index it or use it as a foreign key:

CREATE TABLE MyTest (
    Id           INT              NOT NULL PRIMARY KEY IDENTITY,
    UniqueKey    UNIQUEIDENTIFIER DEFAULT NEWID()
)

If you want to assure that UniqueKey is actually Unique, then add a Unique constraint to the column. However, be aware that by creating a UNIQUE constraint, you are also creating an index - which will need to be set up and maintained appropriately. The main thing we're trying to avoid here is having a fragmented clustered index.

INT and BIGINT outperform UNIQUEIDENTIFIER in every test I've performed here (and I challenge you to come up with a test where they don't) - some results are marginal, others are more obvious, but this speaks volumes. Your column keys should always be as small and efficient as possible, therefore using a 16 byte UNIQUEIDENTIFIER is counter-productive when a perfectly good 4 byte INT is sat there waiting for you.

Remember that the performance tests I've written here are only rough. Ideally each of these tests should be executed on a server a number of times and the results aggregated, as it's very likely that other applications on my dev machine are affecting SQL Server's I/O, CPU and memory usage. However, I think they serve to demonstrate my concerns well.

Finally, if you're considering using a GUID as a table key, ask yourself the following questions:

Do you really need a globally unique identifier?
Could the table end up holding millions of rows?
Could any of the table's foreign references hold millions of rows?
Would it be better to store UNIQUEIDENTIFIER data in another column, or even another table?
Does every row need a GUID, or can you generate a GUID ad-hoc?

Moq'ing Successive Calls to a Method - void method exception handling

2014-01-15T12:26:00.001+00:00

My last post expanded on a piece of code I found on Phil Haack's blog that extends Moq to allow you to return different values on each successive call to a method - I also improved it to allow you mock up any of the iterations to throw an exception in place of the return value.

This is great for methods that actually return values, but what happens when you want to throw optional exceptions when you invoke void methods in Moq? Well, void methods on Moq mocks return ISetup<TMock> instead of ISetup<TMock, TResult>, which means that you can't use the ReturnsInOrder method to handle exceptions. Normally, you would just append a .Throws() to the respective Setup method, but Throws() has no params[] parameter, and no Action based override (as Returns() does), so the only way to apply this kind of rule is with recursive callbacks, which are both cumbersome and hard to read. This is demonstrated below; I'm mocking up a call to an FTP class. The first and third calls to this method will return void (success), the second call will throw an exception:

mockFTP
    .Setup(x => x.Put(It.IsAny<string>(), It.IsAny<string>()))
    .Callback(() =>
        mockFTP
            .Setup(x1 => x1.Put(It.IsAny<string>(), It.IsAny<string>()))
            .Callback(() =>
                mockFTP
                    .Setup(x2 => x2.Put(It.IsAny<string>(), It.IsAny<string>())))
            .Throws(expectedException));

Wouldn't it be much nicer if you could do something like this?

mockFTP
    .Setup(x => x.Put(It.IsAny<string>(), It.IsAny<string>()))
    .ThrowsInOrder(null, expectedException, null);

The answer is "Yes", in case you've forgotten what you're agreeing to. Well... now you can! The answer is to create another extension method specifically for ISetup<TMock> that uses the same kind of queuing mechanism in Phil's original post, but wraps a load of recursive callbacks:

        public static IThrowsResult ThrowsInOrder<TMock>(this ISetup<TMock> setup,
            params Exception[] exceptions) where TMock: class {
            var queue = new Queue(exceptions);

            return setup.Callback(() => {
                var result = queue.Dequeue() as Exception;

                if (result == null) {
                    setup.Throws(null);
                    return;
                }

                setup.Throws(result);
            });
        }

Enjoy!

Moq'ing Successive Calls to a Method

2013-12-24T15:31:00.000+00:00

I just ran into a little problem when using Moq - I wanted to be able to mock up the result of a method call, but return a different result each time the mocked method was called (I'm pulling messages out of an MSMQ within a loop). Anyone who's used Moq will know that you usually mock results with the Setup() method - however, Moq has no built in way of achieving this (that I'm aware of).

To cut a long explanation short, I found a series of articles on Phil Haack's blog addressing (almost) the same issue. Phil's second post adds exception handling to his original example, but I wanted to return a "null", rather than throw an exception. Here's the code from Phil's blog, slightly rehashed to deal with nulls:

    public static class MoqExtensions {
        public static IReturnsResult<T> ReturnsInOrder<T, TResult>(this ISetup<T, TResult> setup,
            params object[] results) where T: class {
            var queue = new Queue(results);

            return setup.Returns(() => {
                var result = queue.Dequeue();
                if (result is Exception) {
                    throw result as Exception;
                }
                if (result == null) {
                    return default(TResult);
                }
                return (TResult)result;
            });
        }
    }

And here it is in use:

mockQueue
    .Setup(x => x.GetNextMessage())
    .ReturnsInOrder(new InputMessage(), new InputMessage(), new InputMessage(), null);

Thanks Phil.

Thil

UPDATE 28th Dec 2013

I've just been trying to append a callback to the above method using Moq's fluent notation - but I couldn't, as it returned void. I've modified the example above to return setup.Returns, allowing you to then chain notation as you would normally with Moq.

Getting Unity 3D working with Git on Windows

2013-12-17T13:22:00.001+00:00

Git is a brilliant, yet peculiar beast. It has the most flexibility of any of the VCS's I've used, and it's extremely powerful and quick once you get used to how it works. Suffice to say that I've fallen in love with it over the past year, for both personal and commercial projects, and online services like github and bitbucket just enhance the overall appeal of it. I therefore want to use it as my VCS of choice when developing games... but those who have tried to get Unity and git working together will probably either be dead by their own hand, or crouched in a corner, rocking back and forth in the foetal position.

Unity3D is an entirely different, and possibly more peculiar beast. It's a great tool, and the editor is feature rich, but it's source control compatibility is weak to say the least. I don't know if this is through necessity, or the marketing people at Unity have pressed the developers to make it as complicated as possible in order to sell their Asset Server control system. The complexity is mostly due to the fact that the Unity editor serializes most assets into binary by default, and git - by it's very nature - is built to compare textual files. In addition, Unity3D creates a shed load of files that should not be version controlled (such as its asset cache), and the Visual Studio / MonoDevelop solution and project files change constantly.

I spent the good part of a couple of weeks getting Git and Unity3D playing nice together, but, because I'm not currently collaborating with other developers I'm still not 100% convinced that Unity3D and git are suited to each other yet. Regardless, it's very handy for me at the moment, so here's how I've implemented my git repo for Subnet, my upcoming First Person stealth and parkour game.

This tutorial is for Unity Pro 4.1.5f, which - at the time of writing - is a little bit out of date, but I believe Unity have recently made a number of pro only features available in the indie version as well (such as asset serialization), so you should be able to follow this for any of the later versions. Unity have provided a tutorial on how to integrate with VCS's, but (as with pretty much all of the Unity documentation) it's not great.

Setting Up Unity

Open up your Unity project and go to Edit -> Project Settings -> Editor:

This will open the Editor Settings in the Inspector. Here you should set "Version Control -> Mode" to "Meta Files" and "Asset Serialization -> Mode" to "Force Text".

The "Meta Files" option will run through your entire Assets folder and generate a text based .meta file for each file and folder contained within. This file holds some internal Unity data, such as identifiers, version numbers and import settings. You will not need to manually interact with these files, unless you start moving assets around outside of the Unity Editor.

The "Force Text" setting will force relevant assets to serialize as YAML. This allows git to compare changes to editor settings, such as the assets location and scale. It's not brilliant (sometimes even changing a single setting in a prefab might regenerate the entire file), but it's about as good as you're going to get.

That's it for Unity Editor settings. The only other thing to mention is that you should always move files around within Unity Editor, rather than on your file system, unless you know exactly what you're doing, otherwise you might break physical references to your assets that can be difficult to resolve, especially if you have a lot of references set up to the moved asset within Unity.

Setting Up Git

My git repository has my Unity project inside it, as well as a number of standard Visual Studio solutions and projects that I use for creating some shared and unit tested code. I tend to set up the following directory structure:

/build/

For unity builds. This directory contains a .gitignore file with a single line in it - "!.gitignore", as I do not wish to push any of my unity builds to git.

/source/

For all source code. I create sub-directories inside this folder for each individual solution - so, one for my Unity project, and another directory for my separate VS solution.

I also set up a .gitignore file in the root of my repository containing the following patterns:

*.suo
*.sqlsuo
*.user
*_ReSharper.*
*.suo
*.log
obj/
bin/
Library/
Temp/
*.pidb
*.userprefs
*.orig
Assembly-CSharp*.csproj
Assembly-UnityScript*.unityproj
.DS_Store

*.subnet
source/POC/SubnetPOC/SubnetPOC-csharp.sln
source/POC/SubnetPOC/SubnetPOC.sln

The last three entries are project specific; "*.subnet" is the extension I have applied to my serialized Save Game files, and the other two lines are direct links to the generated Unity solution files. You don't have to be explicit here; if you don't have any .sln files in your repo that are external to the Unity directory (which I do), then you should just add "*.sln" to the list at the top.

A lot of the ignore patterns are pretty standard when using git with .Net repositories, but .Net developers paying attention might wonder why I'm also excluding .sln, .csproj and .unityproj files from the repo. This is because they are recreated every single time you modify the Project structure within the Unity Editor. Different versions of Unity also create slightly different versions of these files, meaning that if you're collaborating with someone who has a different version of Unity than you, these files will be constantly updated as you both commit to the repo, even if you don't change anything in the project structure. Because Unity Editor manages the creation of these files, you don't need to worry about them - anyone cloning the repo will generate their own version of the project and solution files as soon as they open up Unity.

This raises another "gotcha". You cannot add projects to the Unity Visual Studio / MonoDevelop solution, as they will be automatically removed next time you change a file inside Unity. This is why I have a separate solution containing any shared code - so Unity doesn't screw up my references and project links constantly!

Also notice that the Library, obj and Temp directories are excluded from the repo. Again, Unity generates these directories whenever you open the editor or build the solution - they are local cache directories that each collaborator will have a local copy of. They're full of binary files, and can get absolutely HUGE. The more assets you have, the larger these directories will become.

Lastly, but possibly most importantly, is the .gitattributes file. This controls the line endings that git applies to each file type when you check files in and out. If I remember correctly, Unity applies LF line endings to most files by default, which can confuse things (and present you with a lot of laborious popups in the various editors you might use) on a Windows machine. I found a template somewhere online (which I can't find again - please let me know if this if your file so I can credit you), but it still took me a while to get everything working to an acceptable level. I'm still not 100% convinced that this list is comprehensive, but it's a damn good start:

# Apply native OS line-endings on checkout of these files...
*.boo text
*.c text
*.cginc text
*.config text
*.contentproj text
*.cpp text
*.dae text
*.DAE text
*.dtd text
*.fx text
*.glsl text
*.h text
*.inc text
*.ini text
*.js text
*.JSFL text
*.jsfl text
*.json text
*.log text
*.md text
*.mel text
*.php text
*.shader text
*.txt text
*.TXT text
*.xaml text
*.xml text
*.xsd text
.gitattributes text
.gitignore text
COPYING text
INSTALL* text
KEYS* text
LICENSE* text
NEWS* text
NOTICE* text
README* text
TODO* text
WHATSNEW* text

# Apply Unix-style LF line-endings on checkout of these files...
*.meta text eol=lf
*.sh text eol=lf
*.vspscc text eol=lf
.htaccess text eol=lf

# Previous "binary"
*.unity text eol=lf
*.asset text eol=lf
*.prefab text eol=lf

# Apply Windows/DOS-style CR-LF line-endings on checkout of these files...
*.bat text eol=crlf
*.cmd text eol=crlf
*.csproj text eol=crlf
*.sln text eol=crlf
*.user text eol=crlf
*.vcproj text eol=crlf
*.cs text eol=crlf
*.css text eol=crlf
*.htm text eol=crlf
*.html text eol=crlf

# No end-of-line conversions are applied (i.e., "-text -diff") to these files...
*.7z binary
*.ai binary
*.anim binary
*.apk binary
*.bin binary
*.bmp binary
*.BMP binary
*.com binary
*.COM binary
*.controller binary
*.cubemap binary
*.dex binary
*.dll binary
*.DLL binary
*.dylib binary
*.eps binary
*.exe binary
*.EXE binary
*.exr binary
*.fbx binary
*.FBX binary
*.fla binary
*.flare binary
*.flv binary
*.gif binary
*.guiskin binary
*.gz binary
*.ht binary
*.ico binary
*.jpeg binary
*.jpg binary
*.keystore binary
*.mask binary
*.mat binary
*.mb binary
*.mp3 binary
*.mp4 binary
*.mpg binary
*.ogg binary
*.PCX binary
*.pcx binary
*.pdb binary
*.pdf binary
*.physicMaterial binary
*.physicmaterial binary
*.png binary
*.ps binary
*.psd binary
*.qt binary
*.so binary
*.swf binary
*.tga binary
*.tif binary
*.tiff binary
*.ttf binary
*.TTF binary
*.unitypackage binary
*.unityPackage binary
*.wav binary
*.wmv binary
*.zip binary
*.ZIP binary

You can read more about .gitattributes here.

Unity Script Default Line Endings

As I alluded to a bit earlier, Unity uses LF line endings by default. This is particularly annoying on Windows, especially considering that every time you create a script, you have to modify the line endings manually. Luckily, Unity uses text based template files to create the default scripts, and you can modify the line endings in a program like Notepad++ yourself. The templates are located in the following folder by default:

C:\Program Files (x86)\Unity\Editor\Data\Resources\ScriptTemplates\

In Notepad++ it's as simple as opening all the files at once, and replacing "\n" with "\r\n" In All Open Documents.

There's not much point of me providing these files for you, as it's possible they could be different in each version of Unity, and you might have already modified them yourself.

That's It

As I said, I'm not 100% convinced this approach will work for everyone, and I'm still very nervous about two people modifying a single Unity scene or prefab file at the same time (I had numerous issues last time I collaborated), but it's about as close as anyone's going to get, I think.

A Brave New World

2013-07-27T23:25:00.001+01:00

Anyone who reads my blog regularly will know that when it comes to development, I generally stick to Microsoft or web-based technology, and rarely step outside that comfortable little bubble. I made a promise to myself back in January that I'd get into mobile or games development this year, and finally I've found the time to start looking at one of them.

I'm no ideas man, but occasionally a spark ignites and the juices start flowing. Armed with an idea that's been rattling around in my head for a few years, I spoke to a friend who's been focusing on indie games development, and he suggested using Unity3D, a fully featured game engine that supports C# and JavaScript out of the box (making my learning curve a little bit easier). I told him my idea, and - as geeks do - we spent the next few hours hashing out concepts, and by the end of the night, we were fully bought into the idea. The next step was seeing how Unity would fit in.

So, for the past few months, my good friend Lew and I have been prototyping various aspects of our game, and playing around with source control and collaboration tools for Unity3D. There's a stark difference between developing client / server applications in .Net and developing for 3D games, but Unity's use of Mono for scripting (with built in support for C#, Boo and a weird derivative of JavaScript) has made the transition a lot easier than it would have been if I was to learn C++.

Of course, 3D game design is - and always will be - a completely different animal to enterprise application development; even if you consider that you'll need to learn at least the basics of 3D vector maths (which I totally suck at), you still have to factor in that game development - by its very nature - relies on multiple pieces of code executing asynchronously, which is completely the opposite of how most .Net developers go about their day-to-day jobs. Most of us have touched on asynchronous operations using .Net's built in libraries, but it really doesn't help when programming in Unity.

Developers Unite

Unity is a pretty advanced (yet generic) 3D games engine that comes with a plethora of built in functionality including (but not limited to) skeletal rig animation, a nifty physics engine and dynamic lighting. A lot of this you can get for free, though you really need the Pro version if you're going to do anything serious.

By far the most appealing aspect of using Unity is the thriving open source community that's built up around it. Whilst their Answers pages don't quite match the awesome prowess that is Stack Overflow, and their documentation is a little out of date and lacking in some areas, the community is alive and their Asset Store provides many free scripts, textures, models and systems to help you develop your game. I've generally found myself discarding or re-writing the scripts I've downloaded as I've started to understand things more, but they definitely help to point you in the right direction, and some are pretty awesome.

Obviously I'm no game dev pro, but I think it's safe to say that Unity helps you make a simple game, quickly. However, as with all games, anything more advanced will require a lot of your time. It's not long before you realize the potential - and limitations - of the engine, and of course figure out exactly how much work you've let yourself in for - in our case, it's a fuckton. Without revealing too much at this point, we're putting together a 3D FPS game with a heavy focus on stealth, parkour and hacking. The best way to describe it is Deus Ex meets Mirror's Edge. The ideas we've had are probably a bit ambitious, but I suppose it's better to aim too high than too low!

Unity's IDE is a bit buggy, but is also very extensible (in fairness the bugs I've experienced seem to be related to 3rd party scripts and plugins rather than Unity itself, though there are still definitely problems with it). It's not quite Winforms programming, and the Unity crew have written the Editor scripting objects in a very weird way, but you can achieve almost anything you want within the IDE; drag and drop systems, simple property enhancements, graphs, flow diagrams etc. This neat little addition increases your productivity when working with game objects that share custom scripts - which is basically how you write games in Unity.

Scripted Away

Unity's modular approach to object scripting is both refreshingly easy, and infinitely frustrating. It's a very different way to work than I'm used to, and certainly feels more like scripting / hacking than proper development. Saying all that, it's perfectly acceptable to abstract code out of the C# scripts within Unity and put them into your own project and develop them separately (maybe with TDD etc), but for the simple reason that Unity is a bit fiddly anyway, I can't see it being an easy task to accomplish.

As it stands, I've written quite a lot of C# scripts in Unity that deal with everything from player movement to object interaction and message relaying, but it doesn't feel quite right. It very much feels like I'm back to basics with C#; I know the syntax, and a number of patterns, but Unity's way of working feels loose and fumbly. I never feel like I'm doing things the right way, and my code is definitely starting to look a little bit like spaghetti code, and is at risk of bug creep. This has a lot to do with the code being "Proof of Concept", and me being unfamiliar with all this new stuff to play with, but I do feel that some things could have been organised just a little bit nicer when they were designing the engine.

Each "script" is basically a C# class that inherits from the Unity base class, MonoBehaviour. Within this class, there's a well documented event pipeline that helps you get off the ground quickly, and is fairly intuitive - for example, you override Start() to execute code when the script starts, and Update() when you want to control things on a per-frame basis. MonoBehaviour also provides a number of protected / public properties that can be used to access common components directly - although Unity suggests using the built in GetComponent() method to get them instead. I do like the way it works, but I'd like to add further redundancy by putting my code into a separate assembly, and writing unit tests for it.

Not So Safe Source

If you're doing any kind of collaboration with Unity, you'll soon discover that its source control support is not very good. Mainly, complaints have arisen to do with the free version previously not supporting text serialization of assets (which Unity have rectified recently in version 4.2), but I can confirm that it doesn't make that much difference, especially when it comes to large or complex assets like scenes. We're using git for our project, and I spent a considerable amount of time setting it up to work as well as it does at the moment. I'll write a blog post about it at a later date.

Through many months of trial and error, it seems that two people can't work on a single scene at once - and if you do, you basically have to ignore any merge that takes place and manually apply the changes. Obviously this can get very complex and time consuming, but it's the only way to do it. Instead, Lew and I have been working on separate scenes or projects; I really can't imagine what it's going to be like when we end up putting some proper levels together.

Model and the World Models with You

I've worked directly with some very good designers over the years, and I've been very appreciative of the experiences. My creativity comes in the form of architecture of systems and structuring code... most things graphical are quite literally mind-bending to me. I've used Photoshop and Illustrator for years, and I know them quite well, but only to manipulate images that already exist; I've never been able to create anything from scratch, but I've gawped at plenty of designers as they've magically created something awesome from literally nothing. Obviously there's going to be a certain amount of graphical work involved in game development, though I'm not sure how I'm gonna get through it!

Modelling, on the other hand, is something I quite enjoy. I've created a few designs over the years for custom built stuff around the house; my console collection cabinet, the BBQ in the back yard, and sound diffusers for my studio, and enjoyed making all of them. The thing is, I've only really modelled complex cubes... when it comes to doing anything more sophisticated, I'm pretty much starting from scratch. It's been challenging, especially having to learn new tools like SketchUp, 3DS Max, and Blender, and all the new terminology and 3D modelling techniques like hierarchies and UV maps. It's a crazy, new experience, and there's a hell of a lot to learn.

Then there's how to actually design models and levels for our game. So far, it's a lot of trial and error - different export settings, playing with hierarchies and origins, figuring out what we need to model individually and what we can combine inside other models, and how to arrange our meshes. Unity allows you to import entire 3D models into your project, automatically generating things like colliders, and UV lightmaps, seriously reducing the amount of time it takes to get things up and running. The hard part is exporting the model from whatever 3D package you have in a way that makes sense in Unity.

The Sound of Silence

These days, good sound design can make or break a game. Even Pong had sound effects, but anyone who's played any modern triple A title will know we've come a hell of a long way since then. Unity features a very easy to use audio system that lets you play various formats of audio file directly from disk, and adjust properties like pitch, volume, and distance rolloff. Luckily, my past experience in the recording studio pays off here, as I've got an array of tools available to me for both recording and editing. At the moment I'm just finding royalty free samples online, though I'm having to do some minor editing to most of them so they fit in with our game.

A game is a pretty boring place if the items your character is interacting with don't make a sound. One thing I didn't think about much before taking this on was that I'd have to think about how every single object will make a number of different noises depending on what you're doing with it. A gun, for example, requires numerous bullet firing sounds, a reload clip, a dry fire noise, noises for shells hitting the ground and bouncing, then there are bullet impact sounds for different materials, glass shattering, crates breaking, and ricochet noises. It all depends on the level of realism you want to achieve, or the type of game you're making, but each new sound you add and calibrate properly really makes a difference when you're play testing.

Hello World

If the coming months are anything like the last few, I'll be spending most of my nights following beginners tutorials on youtube and writing hello world scripts for every new thing I need to accomplish. It makes a refreshing change from writing software for insurance and travel companies, and challenges me on every level. I'm not sure if I'd have managed to get into it if I wasn't for Unity's C# support; I think the challenge of learning something like C++, plus all the extras I've mentioned above would have been overwhelming. It's a good move from the guys at Unity.

So, expect my normal blog posts to be interjected with the occasional Unity 3D related ramble... I imagine I'll be developing this game for a while!

Intelligent SQL Server REORGANIZE and REBUILD Script

2013-06-05T11:30:00.000+01:00

I've been doing a lot of database analysis recently, and fortunately I'm one of those people that enjoys it. I'm primarily a .Net developer, but I've spent many years looking after SQL Server databases, helping out (and even educating) DBAs, writing ETL packages and designing both large and complex databases.

The database in question was in pretty bad shape; missing, unnecessary, and extremely fragmented indexes, missing primary and foreign keys, bad normalisation, and (the main reason I decided to look into it) undesired table locks during ETL operations. It's a 24/7 system, and live lookups are performed around the clock - data is imported early in the morning (when usage is at a minimum), which is when most of errors occur.

Amongst many other pre-emptive actions I've recently performed on the database, I decided to set up regular table index maintenance job to keep queries as quick as possible. Unfortunately the database is SQL Server Standard edition, and with it being a 24/7 system, we had to schedule some downtime to do a REBUILD of the indexes (the vast majority of the indexes on the primary application tables are at 90% or greater fragmentation), but I wanted to try to eliminate, or at least reduce any future downtime.

Some of you will know that an index can be rebuilt (on-line or offline, depending on the version of SQL Server you have installed) or reorganized (which can only be performed on-line). You might also know that you shouldn't just run these commands willy-nilly, and shouldn't rebuild or reorganize at all under certain conditions. This article on MSDN provides a decent overview of when you should perform a rebuild or reorganize operation, but I also found quite a bit of evidence out there that says performing a REBUILD or REORGANIZE against small indexes yields no noticeable performance improvement.

Armed with this information, I set out to find an already written table maintenance script, but failed to find anything that was robust enough for my needs. I wanted to be able to specify a number of parameters that determine which indexes are rebuilt, determine if an index can be rebuilt online or not, and perform optional logging.

The script below has been tested on SQL Server 2008 R2 and SQL Server 2012, and although I'm sure it'll work on previous versions of SQL Server, I would do some testing before running it.

----------------------------------------------------------------------------------------------------
-- Developer:   Chris Sebok
-- Date:        10th May 2013
-- Description: A stored procedure that rebuilds or reorganizes indexes intelligently depending on
--              the SQL Server version.
----------------------------------------------------------------------------------------------------
/*
-- Pre-run server configuration

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
*/

/*
DECLARE @Database                VARCHAR(100)  SET @Database                = 'MyDatabase'
DECLARE @PageCountThreshold      INT           SET @PageCountThreshold      = 1000
DECLARE @ReorganiseThreshold     DECIMAL(5,2)  SET @ReorganiseThreshold     = 5.00
DECLARE @RebuildThreshold        DECIMAL(5,2)  SET @RebuildThreshold        = 30.00
DECLARE @Debug                   BIT           SET @Debug                   = 1
DECLARE @SafeMode                BIT           SET @SafeMode                = 1
DECLARE @DefaultFillFactorIfZero INT           SET @DefaultFillFactorIfZero = 90
DECLARE @LogPath                 VARCHAR(1000) SET @LogPath                 = 'D:\logs\mydatabase\'

EXEC spReindex @Database, @LogPath, @Debug, @SafeMode, @PageCountThreshold, @ReorganiseThreshold, @RebuildThreshold, @DefaultFillFactorIfZero

*/
IF OBJECT_ID('spReindex') IS NOT NULL
    DROP PROC spReindex
GO

CREATE PROC spReindex (
    @Database                VARCHAR(100),           -- Database to reindex
    @LogPath                 VARCHAR(1000),          -- The path to store log files in. Must be prefixed with a back slash.
    @Debug                   BIT            = 1,     -- 0 = Index work is carried out. 1 = Index statements are not executed, but everything is logged as normal (for analysis)
    @SafeMode                BIT            = 1,     -- 0 = Rebuilds indexes regardless of SQL Server version. 1 = Only rebuilds indexes if they can be rebuilt online.
    @PageCountThreshold      INT            = 1000,  -- Number of paged in the index before a rebuild / reorganize occurs
    @ReorganiseThreshold     DECIMAL(5,2)   = 5.00,  -- The lower percentage threshold for a REORGANIZE
    @RebuildThreshold        DECIMAL(5,2)   = 30.00, -- The lower percentage threshold for a REBUILD
    @DefaultFillFactorIfZero INT            = 90     -- The default fillfactor percentage for an index if it's currently set to 0 (100%)
)
AS

    -- Validation
    IF RTRIM(LTRIM(ISNULL(@Database, ''))) = ''
        BEGIN
            PRINT CONVERT(VARCHAR, GETDATE(), 113) + ': ' + '@Database can not be null or empty.'
            RETURN
        END

    IF ISNULL(@ReorganiseThreshold, 0) >= ISNULL(@RebuildThreshold, 0) OR ISNULL(@ReorganiseThreshold, 0) = 0 OR ISNULL(@RebuildThreshold, 0) = 0
        BEGIN
            PRINT CONVERT(VARCHAR, GETDATE(), 113) + ': ' + '@ReorganiseThreshold must be less than @RebuildThreshold, and neither can be null or zero.'
            RETURN
        END

    IF RIGHT(@LogPath, 1) != '\'
        BEGIN
            PRINT CONVERT(VARCHAR, GETDATE(), 113) + ': ' + '@LogPath must end with a backslash (\), and be a valid path.'
            RETURN
        END

    IF DB_ID(@Database) IS NULL
        BEGIN
            PRINT CONVERT(VARCHAR, GETDATE(), 113) + ': ' + '@Database (' + @Database + ') does not exist.'
            RETURN
        END


    -- Sproc variables
    DECLARE @OnlineRebuildEnabled BIT             SET @OnlineRebuildEnabled = CASE WHEN CONVERT(VARCHAR, SERVERPROPERTY('Edition')) LIKE '%Enterprise%' THEN 1 ELSE 0 END
    DECLARE @LogFile              VARCHAR(2000)   SET @LogFile              = @LogPath + CONVERT(VARCHAR, DATEPART(yyyy, GETDATE())) + '_' + RIGHT('00' + CONVERT(VARCHAR, DATEPART(MM, GETDATE())), 2) + '_spReindex.log'
    DECLARE @StartTime            DATETIME
    DECLARE @EndTime              DATETIME
    DECLARE @Difference           INT
    DECLARE @Prefix               VARCHAR(500)
    DECLARE @Instruction          VARCHAR(8000)
    DECLARE @IndexStatement       NVARCHAR(4000)

    -- Cursor variables
    DECLARE @Table VARCHAR(200), @Schema VARCHAR(200)
    DECLARE @IndexName VARCHAR(500), @Fragmentation DECIMAL(5, 2), @Fillfactor INT, @Pages INT, @Type VARCHAR(30)


    -- Manage log directory
    SET @Instruction = 'md ' + @LogPath
    EXEC master..xp_cmdshell @Instruction, 'NO_OUTPUT';


    -- Start cursor operations
    DECLARE cTables CURSOR READ_ONLY FAST_FORWARD FOR
        SELECT
            s.name AS SchemaName,
            t.name AS TableName
        FROM sys.tables            (nolock) t
        INNER JOIN sys.schemas    (nolock) s ON t.schema_id = s.schema_id
        WHERE t.[type] = 'u'

    OPEN cTables

    FETCH NEXT FROM cTables INTO @Schema, @Table

    WHILE @@FETCH_STATUS = 0
        BEGIN
            
            DECLARE cIndexes CURSOR READ_ONLY FAST_FORWARD FOR
                SELECT
                    I.name,
                    ROUND(S.avg_fragmentation_in_percent, 2),
                    i.fill_factor,
                    s.page_count,
                    i.type_desc
                FROM sys.dm_db_index_physical_stats (DB_ID(@Database), OBJECT_ID(@Table), NULL, NULL, NULL) S
                INNER JOIN sys.indexes I (nolock) ON (
                    S.object_id        = I.object_id
                    AND S.index_id    = I.index_id
                )
                WHERE name IS NOT NULL -- Ignore heap indexes

            OPEN cIndexes

            FETCH NEXT FROM cIndexes INTO @IndexName, @Fragmentation, @Fillfactor, @Pages, @Type

            WHILE @@FETCH_STATUS = 0
                BEGIN
                    -- Reset index statement
                    SET @IndexStatement = ''
                    
                    SET @Prefix = @Database + '.' + @Schema + '.' + @Table + '.' + @IndexName

                    SET @Instruction = 'echo ' + CONVERT(VARCHAR, GETDATE(), 113) + ': ' + @Prefix + ':: Index info: Type = ' + @Type + ', No. of Pages = ' + CONVERT(VARCHAR, @Pages) + ', Fragmentation = ' + CONVERT(VARCHAR, @Fragmentation) + '%, Fill Factor = ' + CONVERT(VARCHAR, @Fillfactor) + '%) >> ' + @LogFile
                    EXEC master..xp_cmdshell @Instruction, 'NO_OUTPUT';

                    -- Make sure the index should be rebuilt or reorganized
                    IF @Pages >= @PageCountThreshold AND @Fragmentation >= @ReorganiseThreshold
                        BEGIN
                            -- Rebuild or reorganize?
                            IF @Fragmentation >= @RebuildThreshold
                                BEGIN
                                    -- REBUILD
                                    IF @OnlineRebuildEnabled = 1 OR @SafeMode = 0 OR (@SafeMode = 1 AND @OnlineRebuildEnabled = 1)
                                        BEGIN
                                            SET @IndexStatement = 'ALTER INDEX ' + @IndexName + ' ON ' + @Schema + '.' + @Table + ' REBUILD WITH (' + CASE WHEN @OnlineRebuildEnabled = 1 THEN 'ONLINE = ON, ' ELSE '' END + 'FILLFACTOR = ' + CONVERT(VARCHAR, CASE WHEN @Fillfactor = 0 THEN @DefaultFillFactorIfZero ELSE @Fillfactor END) +');'
                                        END
                                    ELSE
                                        BEGIN
                                            SET @Instruction = 'echo ' + CONVERT(VARCHAR, GETDATE(), 113) + ': ' + @Prefix + '; unable to rebuild index ONLINE, SQL Enterprise required, or server must be taken offline. >> ' + @LogFile
                                            EXEC master..xp_cmdshell @Instruction, 'NO_OUTPUT';
                                            GOTO FetchNext -- Move to next index
                                        END
                                END
                            ELSE
                                BEGIN
                                    -- REORGANIZE
                                    SET @IndexStatement = 'ALTER INDEX ' + @IndexName + ' ON ' + @Schema + '.' + @Table + ' REORGANIZE;'
                                END

                            SET @Instruction = 'echo ' + CONVERT(VARCHAR, GETDATE(), 113) + ': ' + @Prefix + '; Running: ' + @IndexStatement + ' >> ' + @LogFile
                            EXEC master..xp_cmdshell @Instruction, 'NO_OUTPUT';

                            -- Perform reindex and time it
                            SET @StartTime = GETDATE()

                            IF @Debug = 0
                                BEGIN
                                    EXEC sp_executesql @IndexStatement
                                END

                            SET @EndTime = GETDATE()

                            SET @Difference = DATEDIFF(ss, @StartTime, @EndTime)

                            SET @Instruction = 'echo ' + CONVERT(VARCHAR, GETDATE(), 113) + ': ' + @Prefix + '; Finished. Time elapsed ' + CONVERT(VARCHAR, @Difference) + ' seconds (' + CONVERT(VARCHAR, @Difference / 60) + ' minutes) >> ' + @LogFile
                            EXEC master..xp_cmdshell @Instruction, 'NO_OUTPUT';
                        END
                    ELSE
                        BEGIN
                            -- No need to index
                            SET @Instruction = 'echo ' + CONVERT(VARCHAR, GETDATE(), 113) + ': ' + @Prefix + '; Index does not require reorganizing or rebuilding. >> ' + @LogFile
                            EXEC master..xp_cmdshell @Instruction, 'NO_OUTPUT';
                        END
FetchNext:
                    FETCH NEXT FROM cIndexes INTO @IndexName, @Fragmentation, @Fillfactor, @Pages, @Type
                END

            CLOSE cIndexes
            DEALLOCATE cIndexes

            FETCH NEXT FROM cTables INTO @Schema, @Table
        END

    CLOSE cTables
    DEALLOCATE cTables
GO

Handling uploads with MVC4, JQuery, Plupload and CKEditor

2013-06-05T10:25:00.001+01:00

I spent a considerable amount of time trying to get MVC to work nicely with various file uploaders while I was writing RustyShark, and I have to admit it's been pretty frustrating at times. On the plus side, it's taught me a lot about how Microsoft MVC works behind the scenes, and allowed me to play with quite a few different uploading tools that're out there.

My upload page on RustyShark has quite a complex model - each review is related to multiple Formats (PS3, 360, DVD, Blu Ray etc), each review / format combination can have multiple images associated with it, and each image has additional meta data attached, such as a caption and an upload date. The upload form is created using a combination of Editor Templates, JQuery and AJAX. I will be stripping down any examples to their bare bones to make things a little clearer.

This article assumes you are familiar with MVC fundamentals such as creating controllers, views and models within Visual Studio 2010 or later, you understand the Razor view engine syntax, you know how to use MVC Editor Templates, and you are fluent with the .Net Framework v4.0 or later. All code is provided as an example only, and is untested.

Input type="file"

To start, let's look at a standard <input type="file" /> upload. First, set up a controller:

public class MyController : Controller {
    public ActionResult UploadImage() {
        return View();
    }
}

Then create a view for the controller called "UploadImage.cshtml" that will contain the form mark-up.

When it comes to building your form, first and foremost (and something quite a few people forget) is the encoding type of the form submitting the file data:

@using(Html.BeginForm(null, null, FormMethod.Post, new { enctype = "multipart/form-data" })) {
    <input type="file" id="testingupload" name="testingupload"/>
    <input type="submit" value="Upload image"/>
}

The vast majority of the time in MVC you'll be using Html.BeginForm() without any parameters - remember, all a HTML helper does is output HTML, and most helpers have the "htmlAttributes" parameter to catch any attributes that aren't catered for by defined arguments. Here, we've added the "enctype" <form> attribute and set it so we can upload binary data when the form is submitted.

By default, when a form action isn't set, HTML forms POST to the current page. You don't need to set the "action" or "controller" parameters unless you're posting to a different location (which I only really use in shared partials or special AJAX forms anyway), hence why the first two parameters in my example are "null".

Note that the "id" attribute of the file input is not required for this example. However, it is recommended that you give all form elements an ID, to identify them as a unique element in the HTML DOM. The "name" attribute is the important thing here, though this does not need to be unique. You can give multiple form elements the same name so they're posted to the server as a collection, but you already knew that, right? :) More on that later.

You now have a page that should compile, but you'll get an error when you click the submit button as there's no code to handle the postback. Add the following to your controller:

[HttpPost]
public ActionResult UploadImage(HttpPostedFileBase testingupload) {
    return View();
}

As with all form posting in MVC, the HttpPost action filter restricts the action so that it only accepts the POST verb from your browser. The only other notable thing here is the single parameter - HttpPostedFileBase, and its name, "testingupload", which should always match the file input's "name" attribute. This (along with the parameter's type) allows MVC to automagically map form values to method parameters. Neat!

Now, when you select an image to upload, and click the submit button, the "testingupload" parameter should be populated with all the details you need for the file you've selected. From there you can do what you want - there's a "SaveAs" method on the HttpPostedFileBase object that allows you to easily save the file to disk, though you'll usually want to do a little bit more than just that, like resize an image or notify someone the upload has completed.

That's a simple file upload example, and as you can see there's nothing to it; but only really much use if you plan to upload a single file.

There's one last thing I need to mention before I move on - you can (and should, in this developer's opinion) implement your file input in a view model as you would any other form property, which makes the whole process a lot cleaner and more robust. Below is a sample model:

public class MyModel {
    public int EntityID { get; set; }

    [Display(Name = "Select the image you would like to upload.")]
    [Required(AllowEmptyStrings = false, ErrorMessage = "Please select the image to upload.")]
    public HttpPostedFileBase ImageToUpload { get; set; }
}

And the corresponding view:

@model MyModel
@using(Html.BeginForm(null, null, FormMethod.Post, new { enctype = "multipart/form-data" })) {
    @Html.HiddenFor(m => m.EntityID)
    <div>
        <fieldset>
            <legend>Upload my image</legend>
            <div class="formField">
                <div class="label">
                    @Html.LabelFor(m => m.ImageToUpload)
                </div>
                <div class="input">
                    @Html.FileUploadFor(m => m.ImageToUpload)
                </div>
                @Html.ValidationMessageFor(m => m.ImageToUpload)
            </div>
            <input type="submit" value="Upload!"/>
        </fieldset>
    </div>
}

MVC does not provide a helper for file upload inputs. The @Html.FileUploadFor() is a custom helper I've written that allows you to bind to your model, and it looks like this:

public static MvcHtmlString FileUploadFor<TModel, TValue>(this HtmlHelper<TModel> helper, Expression<Func<TModel, TValue>> expression, bool multiple = false) {
    object props;
    if(multiple) {
        props = new {
            type = "file",
            multiple = "multiple",
            @class = "file"
        };
    } else {
        props = new {
            type = "file",
            @class = "file"
        };
    }
    return helper.TextBoxFor(expression, props);
}

Discerning readers will have noticed that I've implemented the "multiple" attribute too... more on that later.

Unfortunately, without using some crazy reflection code, I don't think I can avoid using the if-else statement (please, please, please tell me if I'm wrong!). I usually prefer ternary operators for this kind of thing, but it's not possible with anonymous objects.

So, after you start using a strongly-typed view model, your POST controller method would look like this:

[HttpPost]
public ActionResult UploadImage(MyModel model) {
    var image = model.ImageToUpload;
    // TODO: Do something with image
    return RedirectToAction("UploadImage");
}

The above described method forms a solid basis for understanding how any of the other file upload tools work, so commit it to memory and get ready to do some serious configuration...

JQuery Uploadify

I used JQuery uploadify for the first release of RustyShark, but very quickly realised it couldn't do everything I wanted, and sometimes just stoped dead when uploading a lot of files or using multiple uploaders on one page. I also had a trouble manipulating the generated HTML elements and attaching to various events. During my development phase, a new version was released which - whilst much better - completely broke everything I'd already done; for that reason, I'm going to outline a number of gotchas, but not actually post any plugin code (I have since stripped it out, and I don't have the energy to rewrite it here).

My Uploadify implementation was sat in a control panel, behind .Net Forms Authentication. This created a security issue, which took me a good while to pin down; I was receiving a 403 when posting the image to the controller, which made little sense as I was logged in and all other form posts were working fine. It turned out that it was the flash object that Uploadify implements that didn't have the access, because the ASPNET Session cookie wasn't passed through on the form post. To rectify it, I needed to include the ASPNET session / cookie information in the form post manually, and write some code to create the ASPNET session cookies at the Application_BeginRequest() stage. In short, it was a right ball-ache.

PLUpload

After my catastrophe with Uploadify, I tried out a few more JQuery uploaders (and even experimented with writing my own), finally settling on PLUpload. Whilst it has it's own little issues, I actually found it to be a lot more robust and reliable than any of the other plugins I tried. Again, I spent minimal time on customising the look and feel, but I'm confident I could integrate it in to my site completely with a few hours of styling.

PLUpload was particularly appealing because it renders different implementations of itself based upon the user's browser capabilities, and supports multi-file uploads out of the box. Although I haven't tested it in a great amount of detail, you can implement browserplus, google gears, html5 or js - which means it's compatible with a wide range of browsers. It also offers an easy way to delete any accidentally uploaded items on the same screen, and lets you use AJAX to perform all operations.

The implementation I went with is way to complex to copy + paste here, so yet again I will break it down to the fundamentals to make it's use a bit clearer. My actual implementation uses both the input type="file" example above, plus it iterates though a custom object (IEnumerable<ImageModel>), rendering the PLUpload control for each item. Below, I'll just show you a single PLUpload control, which is all handled with AJAX calls to one of my MVC controllers.

Model

namespace Upload {
 public class ImageModel {
  public int UniqueId { get; set; }

  [Display(Name = "Select any images related to this format.")]
  public object PLUploadPlaceholder { get; set; }
 }
}

The "PLUploadPlaceholder" property above exists only to allow us to use MVC helpers to render the HTML, and use the DataAnnotation attributes to provide metadata to the view. My model is considerably more complex than this, so it made total sense to put this little bit of extra effort in, though you could easily get away with omitting the model entirely and rendering the PLUpload HTML manually. The UniqueId property is used to distinguish between each PLUpload control. Mine comes from a database.

 public class UploadingImageModel {
  public int UniqueId { get; set; }
  public HttpPostedFileBase file { get; set; }
  public string name { get; set; }
 }

The above class is used for the upload request. "file" and "name" are posted automatically from PLUpload, whereas "UniqueID" is set in the JavaScript during each upload.

View

@model ImageModel
<div class="plUploadContainer">
 @Html.Hidden("AUTHID", Request.Cookies[FormsAuthentication.FormsCookieName] != null ? Request.Cookies[FormsAuthentication.FormsCookieName].Value : string.Empty)
 @Html.Hidden("ASPSESSID", Session.SessionID)
 @Html.HiddenFor(m => m.UniqueId)
 <fieldset>
  <legend>Upload Your Files</legend> 
  <div class="formField">
   <div class="label">
    @Html.LabelFor(m => m.PLUploadPlaceholder)
   </div>
   <div class="input">
    <div class="file">
     <div id="@Html.ClientIDFor(m => m.PLUploadPlaceholder)" title="@Html.ClientNameFor(m => m.PLUploadPlaceholder)"></div>
    </div>
   </div>
  </div>
 </fieldset>
</div>

The above View is actually a modified version of the Editor Template I use within my view model structure (I use @Html.EditorFor() to render each item of the IEnumerable<ImageModel> in my parent view model), but it serves perfectly well as the view if you only want one PLUpload control.

Secondly, notice that the UniqueId property is being written out to a hidden form control. We will use JQuery traversal to retrieve that Id, and send it as a POST parameter to the server.

Thirdly, the other two hidden fields AUTHID and ASPSESSID are used to get around a flash authentication issue I was running into. In hindsight, I really should have created some HTML helpers for these fields, as they expose too much logic to the view. Flash based uploaders do not forward ASP.Net session information to the URLs they send their requests to (the requests are anonymous and probably create a new session), so when you're doing this behind Forms Authentication, you have to mimic this behaviour yourself. To get this working, you will also need to put the following code into your Global.asax file:

protected void Application_BeginRequest(object sender, EventArgs e) {
    var ctx = HttpContext.Current;

    try {
        const string sessionParamName = "ASPSESSID";
        const string sessionCookieName = "ASP.NET_SessionId";

        if(ctx.Request.Form[sessionParamName] != null) {
            UpdateCookie(sessionCookieName, ctx.Request.Form[sessionParamName]);
        } else if(ctx.Request.QueryString[sessionParamName] != null) {
            UpdateCookie(sessionCookieName, ctx.Request.QueryString[sessionParamName]);
        }
    } catch(Exception ex) {
        // Do something with the exception
    }

    try {
        const string authParamName = "AUTHID";
        var authCookieName = FormsAuthentication.FormsCookieName;

        if(ctx.Request.Form[authParamName] != null) {
            UpdateCookie(authCookieName, ctx.Request.Form[authParamName]);
        } else if(ctx.Request.QueryString[authParamName] != null) {
            UpdateCookie(authCookieName, ctx.Request.QueryString[authParamName]);
        }
    } catch(Exception ex) {
        // Do something with the excetion
    }
}

private static void UpdateCookie(string name, string value) {
    var cookie = HttpContext.Current.Request.Cookies.Get(name) ?? new HttpCookie(name);
    cookie.Value = value;
    HttpContext.Current.Request.Cookies.Set(cookie);
}

The last thing to mention in the view is the two helper methods, @Html.ClientIDFor() and @Html.ClientNameFor(). The code for these is below, and basically allow me to use property meta data for rendering custom controls, debugging or simply displaying the data to the user. Here, I'm using it to render a <div> with a unique id that we can replace with a PLUpload plugin.

  public static MvcHtmlString ClientIDFor<TModel, TProperty>(this HtmlHelper<TModel> htmlHelper, Expression<Func<TModel, TProperty>> expression) {
   return MvcHtmlString.Create(
    htmlHelper.ViewContext.ViewData.TemplateInfo.GetFullHtmlFieldId(
     ExpressionHelper.GetExpressionText(expression)));
  }

  public static MvcHtmlString ClientNameFor<TModel, TProperty>(this HtmlHelper<TModel> htmlHelper,Expression<Func<TModel, TProperty>> expression) {
   return MvcHtmlString.Create(
    htmlHelper.ViewContext.ViewData.TemplateInfo.GetFullHtmlFieldName(
     ExpressionHelper.GetExpressionText(expression)));
  }

Controller

public class UploadController: Controller {
  [HttpPost]
  public JsonResult UploadImage(UploadingImageModel model) {
   if (model.file != null) {
    var uploadedImageResult = MyService.UploadMyImage(model);
    if (!relatedImageResult.Success) {
     // Throw exception so javascript can handle it
     throw new Exception(relatedImageResult.ErrorMessage);
    }
    // Return object as Json string
    return Json(uploadedImageResult);
   }
   throw new ArgumentException("'file' is null");
  }

  [HttpPost]
  public JsonResult DeleteImage(int imageId) {
   // Try to delete the image
   var deletedImageResult = MyService.DeleteMyImage(imageId);
   if (!deletedImageResult.Success) {
    // Throw exception so javascript can handle it
    throw new Exception(deletedImageResult.ErrorMessage);
   }
   return Json(deletedImageResult);
  }
}

These two action methods are called via AJAX to perform an individual upload and delete of each image. MyService is a business component that deals with the complex logic for each method. However, the details of this are out of scope in this post.

JQuery

The JQuery required to get PLUpload working isn't very complicated (assuming you've installed it correctly and referenced the correct files), so I'll just annotate the code itself below:

$(document).ready(function () {
    // Used to retrieve the correct uploader for the current ajax request.
    function getUploader(file) {
        return $("#" + file.id).parents('div[id$="PLUploadPlaceholder"]');
    }

    // Get all uploaders on the page
    var uploaders = $('div[id$="PLUploadPlaceholder"]');

    // Apply PLUpload to each of the above DIVs
    uploaders.plupload({
        // Setting details available here: http://www.plupload.com/documentation.php
        runtimes: 'html5,flash,browserplus',
        flash_swf_url: '/res/img/l/u/plupload.flash.swf',
        url: '/Upload/UploadImage/', // Determines the URL to POST each upload to.
        max_file_size: '10mb',
        unique_names: true, // Generates client side unique names before the files are uploaded to the server
        multipart: true,
        // Specify what files to browse for in the client side browse dialogue
        filters: [{
            title: "Image files",
            extensions: "jpg,jpeg,gif,png"
        }],
        preinit: {
            // The preinit.UploadFile event is called just before a file is uploaded
            UploadFile: function (up, file) {
                // Use JQuery traversal to retrieve the unique ID (and any other information you might need)
                var uniqueId = getUploader(file)
                 .closest('.plUploadContainer')
                 .children('input[type="hidden"][name$="UniqueId"]')
                 .first()
                 .val();

                // Set up the additional parameters to be sent with the upload request.
                up.settings.multipart_params = {
                    ASPSESSID: $("#ASPSESSID").val(),    // Ignored by the action method, used internally by ASP.Net
                    AUTHID: $("#AUTHID").val(),            // Ignored by the action method, used internally by ASP.Net
                    UniqueId: uniqueId                    // Used by the action method, shown on the UploadingImageModel above.
                };
            }
        },
        init: {
            // The init.FilesRemoved event is called when the user requests the deletion of a file from the PLUpload UI.
            FilesRemoved: function (up, files) {
                // Loop through each file selected for removal and make an ajax call to the server
                $.each(files, function (i, file) {
                    $.ajax({
                        url: '/Upload/DeleteImage',
                        async: true,
                        type: "POST",
                        data: {
                            imageId: file.ImageId
                        }
                    });
                });
            },
            // The init.FileUploaded event is called after a file has been uploaded to the server.
            FileUploaded: function (up, file, info) {
                // Upload Success. Get the response as json
                var json = $.parseJSON(info.response);
                if (json) {
                    // Add the newly generated ImageId to the PLUpload file (so we can use it for deleting images)
                    $.extend(file, { ImageId: json.ImageId });
                    return;
                }

            }
        }
    });
});

And that's it. Obviously I haven't provided a 100% complete solution here, but you get the general idea of how it works. I'm sure there are things you can do to clean things up a little bit, but hopefully this will give you a good head start!

CKEditor Custom Uploader

CKEditor is a fairly bulky, but highly configurable and feature rich JavaScript based HTML editor. As is the case with an uncanny number of JS plugins out there, the documentation is not particularly intuitive, though there is a lot of it. Luckily, to simplify(!) matters, a JQuery plugin is available.

By default it uses an integrated single-image upload form, which can be customised if you wish. My requirements are simple and I don't care much for layout / looks at the moment, as long as it works; I wanted to upload an image, resize it and place it inside my review / news articles. Suffice to say, I had to bust out Fiddler to figure out what was actually going on in the background in order to get it working with MVC.

As with the input type="file" example above, CKEditor posts form values directly to the server, along with a few other details that may or may not be useful to you. You can safely use the following MVC action to pick up the CKEditor upload post:

public ActionResult UploadImage(HttpPostedFileBase upload, string CKEditorFuncNum, string CKEditor, string langCode) {
    var output = string.Concat("<html><body><script>window.parent.CKEDITOR.tools.callFunction(",
        CKEditorFuncNum, ", \"{0}\", \"{1}\");</script></body></html>");
    // Do something with the image

    // Return the content expected by CKEditor
    var webPath = "/images/path-to-my-new-image.jpg";
    var error = "Any custom errors associated with uploading";
    return Content(string.Format(output, webPath, error));
}

You could, in theory turn the action parameters into its own model, but I didn't bother myself - mainly due to the fact that no other form values are sent to the server, they're case sensitive, and I'm a stickler for consistency. If I implemented it, the model would look like this (note the varying case of members):

public class CKUploadModel {
    public HttpPostedFileBase upload { get; set; }
    public string CKEditorFuncNum { get; set; }
    public string CKEditor { get; set; }
    public string langCode { get; set; }
}

Once installed, you can implement the upload features of CKEditor via JQuery with as little code as this:

$("#MyEditor").ckeditor({
    filebrowserUploadUrl: '/MyController/UploadImage/'
});

Which is nice and neat. Installation isn't all that hard - as usual, it means copying some files to your web site and including them on the page. Read more about it here.

The above example isn't without its issues. One interesting thing to note is that I had a few problems when I wanted to send additional parameters with the upload POST. CKEditor doesn't have anything built in to it extend the POST parameters, but you can - with a lot of faffing - append a querystring to the filebrowserUploadUrl, and send additional information through to the action method. This, however, requires you to destroy and reload the editor each time you want to amend the querystring, which in my case, meant writing a few functions that I called whenever the relevant control on the parent form changed:

$("#SomeFormControl").change(function () {
    setFileBrowserUrl();
});
function setFileBrowserUrl() {
    // Remove editor instance
    $("#Content").ckeditorGet().destroy();
    // Recreate editor instance (needed to reset the file browser url)
    createEditor();
}
function createEditor() {
    $('#Content').ckeditor({
        filebrowserUploadUrl: '/MyController/UploadImage/?MyCustomID=' + $("#SomeFormControl").val()
    });
}

This then allows me to modify my UploadImage action method as such:

public ActionResult UploadImage(HttpPostedFileBase upload, string CKEditorFuncNum, string CKEditor, string langCode, int MyCustomID) {
    var output = string.Concat("<html><body><script>window.parent.CKEDITOR.tools.callFunction(",
        CKEditorFuncNum, ", \"{0}\", \"{1}\");</script></body></html>");
    // Do something with the image and MyCustomID

    // Return the content expected by CKEditor
    var webPath = "/images/path-to-my-new-image.jpg";
    var error = "Any custom errors associated with uploading";
    return Content(string.Format(output, webPath, error));
}

It's not pretty, and it's not the most efficient piece of code I've ever written, but as far as I can tell, it's the only way to change settings within CKEditor on the fly. When looking through the CKEditor code, I noticed that the properties seem to be initialized lazily, meaning that they're cached for the duration of the object's lifetime, and any values that are set against the properties are ignored unless it's reloaded.

At the moment, CKEditor is my rich text editor of choice. I'm yet to find another (free) editor that has all the features I want.

SSDT With Visual Studio 2012 and Using Custom Assemblies

2013-05-30T12:23:00.000+01:00

SQL Server Data Tools

SSIS 2012 / SQL Server Data Tools (SSDT) offers considerable benefits over SSIS 2008 / Business Intelligence Development Studio (BIDS). The UX is much quicker, smoother, feature rich (undo and redo, woohoo!), and one of the best new features is that we can finally attach the debugger to script components - which has been a bit of an issue in the past. It's not exactly perfect (opening the VSTA environment seems to take a lot longer than it used to, breakpoints are unpredictable, and there are a few little issues surrounding package encryption that I keep running in to), but the improvements are more than welcome, and certainly enhance development.

If you're familiar with SSIS, but you've avoided script tasks / components before, I have to say you're missing out; they're very powerful tools in the right hands, and basically allow you to do anything you can do in a .Net app within the context of a control flow or data flow in a package. Do remember though, that most of the time SSIS already offers a component that already does what you're looking for. It's very easy to go crazy with SSIS scripting, so pick and chose your components well.

Often you'll find the built in components lacking in some way, and you'll either have to implement your own component, buy one in, or write your own script task to do it. I usually prefer the script task method, mainly due to the fact it gives me more control over what I'm trying to do. Just to point out a few examples, SFTP isn't supported by the FTP component, and if you want to connect to a web service using anything more advanced than anonymous HTTP/S, then you'll have to go custom. I've ran in to both of these problems in the last few years, and solved them using both script tasks and script components.

SQL Server 2012 comes bundled with SSDT (using the VS2010 shell), but there are also options to install SSDT in both VS2010 and VS2012 separately. This page kept popping up when I was searching for the right installer, but none of the links actually installed anything into VS2012, even though they claimed to do so. It was last year, and there have been a few updates to the pages since, so I'm hoping the packages are now valid. In response to criticism received, Microsoft have since released a separate Visual Studio 2012 SSDT installer, though there are still some gotchas when running it.

If you already have an instance of SQL Server 2012 (I use SQL Server 2012 Business Intelligence on my development machines), the installer in the above link gives you the option to upgrade or install a new instance of SQL Server. Obviously I didn't want to install another SQL Server instance, so I tried to upgrade. Once I'd confirmed the components, I was told that my CPU Architecture was different in my current installation, so I resigned to the "New Instance" option and everything installed properly.

This installs a new Start Menu short cut inside your SQL Server 2012 folder:

Which opens either a VS2012 Shell (as per BIDS 2008 and the SSDT VS2010 shell), or (thankfully) your already installed version of VS2012, giving you new project templates to play with:

Custom .Net Assemblies

There are already a number of blog posts out there explaining how to get started with SSDT, so instead I thought I'd post a little on getting custom .Net assemblies working with SSDT 2012. The concept is similar to the BIDS 2008 method, but there a few specifics that might catch you out. Unfortunately there's still no assembly installer / package manager, so all assemblies that you want to reference in your script tasks and components still have to be installed in to the GAC.

On the plus side, you can now use .Net 4.0 (VS2010 shell) and .Net 4.5 (VS2012) assemblies, which is great news to those who want to use any of the latest .Net features. My examples are all going to use VS2012 and .Net 4.5, and I'll be connecting to a local copy of the Microsoft provided AdventureWorks SQL Server 2008 R2 sample database (all SQL Server versions can be downloaded from here). You can use any compatible version of SQL Server to do this, the data source is not important for the purposes of the demo.

Brief

Let's take a list of all Products in the database, and do something arbitrary with the data (for the purposes of the example, I'll be simply sending the SSDT data to a .Net assembly doing a very simple calculation, and returning it to the script component. Usually the assembly would do something more complicated with the row data, like send it to an MSMQ, a WCF service, validate it, or clean it up). We'll create a very simple SSDT package to do this.

The below example assumes you are familiar with SQL Server, Visual Studio, basic windows batch files / commands, and how to use basic SSDT components.

Project Setup

1. Start Visual Studio 2012 and create a new Integration Services Project (with a new solution).

2. Add a new "Class Library" project to the solution.

3. Check if the Class Library is set to target .Net 4.5 (Right Click Project in Solution Explorer -> Properties -> Application Tab -> Target Framework drop down).

4. Create a new Class with the following code and compile the ClassLibrary1 project:

namespace ClassLibrary1 {
public class ProductHelper {
public int GetStockCount(int id) {
// TODO: Perform inventory stock count via 3rd party service
return 4321;
        }
    }
}

5. Open Package.dtsx and create a Data Flow Task:

6. Double Click the Data Flow Task and create an "ADO NET Source", a "Script Component" (select "Transformation"), and an "ADO NET Destination", linking them together as follows:

7. Configure the ADO NET Source to pull all products out of the database:

8. Double click the Script Component and perform the following steps:

Select "ProductID", "Name" and "ProductNumber" as the input columns.
Click "Edit Script" on the "Script" tab to open up the VS2012 VSTA Editor
Right Click the VSTA project, and change the "Target Framework" to ".Net Framework 4.5"
Add a project reference to "ClassLibrary1" by browsing to the "bin\Debug\" directory of the solution.
Go to the Code Editor and find "public override void Input0_ProcessInputRow(Input0Buffer Row)"

Add the following code into the method:

        var x = new ClassLibrary1.ProductHelper();
  var count = x.GetStockCount(Row.ProductID);

Close the VSTA Editor and click "OK" on the Script Transform Editor dialogue.

Keen observers will notice that I haven't actually done anything with the "count" reference. This tutorial is to demonstrate that you can get Script Components and Script Tasks to work with your own custom assembly, not to demonstrate what to do with the results. Normally, you would update a column or append this value to a row or variable, but for now I just want to show you that it'll compile.

9. Double click the "ADO NET Destination" component, create a new table in the AdventureWorks database, then click "OK":

10. If we weren't trying to get an external assembly working with the Script Component, the SSIS package would now normally be in a compilable state. However, if you debug it (F5), you will receive the following error:

This is due to the fact that the assembly is not available to the DTSDebugHost.exe process. Dependent assemblies are not copied or loaded as they would be with a normal .Net project, so you need to tell the project to use a strongly signed GAC assembly instead.

Global Assembly Cache

1. The first thing we need to do is sign the dependent assemblies. You can do this by right clicking your Class Library project -> Properties -> Signing Tab -> Check "Sign the assembly". You can then create a new strong key file:

In the "Create Strong Name Key" dialogue, I usually name the key file the same as the libraries root namespace (in this instance, "ClassLibrary1"), then uncheck the "Protect my key file with a password" box, and leave the Signature Algorithm as the default. Of course, you can still protect the file with a password if you want, but you will need to adjust a number of the below steps.

Once created, a filed called "ClassLibrary1.snk" will show in the root of your ClassLibrary1 project.

2. Now we need to create a directory that the dependent assemblies can live in once they're installed to the GAC. I usually use a project or deployment path, but for the purposes of this demo, I'll use C:\MyAssemblies\.

3. The next step is to keep your local GAC up to date with the latest assemblies, and inform Visual Studio 2012 where these dependent assemblies are so they can be displayed in the "Add References" dialogue. The following commands expect you to be logged in as an administrator, and should be copied into the ClassLibrary1 project's Post-Build commands:

reg add HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319\AssemblyFoldersEx\MyAssemblies\ /f /t REG_SZ /d C:\MyAssemblies\
xcopy "$(TargetPath)" /Y/R/F "C:\MyAssemblies\"
"C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\gacutil.exe" /uf "$(TargetName)"
"C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\gacutil.exe" /i "C:\MyAssemblies\$(TargetFileName)" /f

Here's a summary of each line:

reg add HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319\AssemblyFoldersEx\MyAssemblies\ /f /t REG_SZ /d C:\MyAssemblies\

This creates / overwrites a registry key that tells Visual Studio where to find your custom GAC assemblies. It's not strictly necessary in a post-build command (you only need to run it once), but it does no harm, and allows other developers who might start working on the package to get up and running straight away. You can read more about it here.

xcopy "$(TargetPath)" /Y/R/F "C:\MyAssemblies\"

This command copies and overwrites any files in C:\MyAssemblies\ with the deployed project files.

"C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\gacutil.exe" /uf "$(TargetName)"

This command uses the latest version of gacutil to UNINSTALL the target assembly from the GAC. You'll see a warning in the output window the first time it executes on your machine, but it's needed to reinstall the assembly every time you compile your code.

"C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\gacutil.exe" /i "C:\MyAssemblies\$(TargetFileName)" /f

This command uses gacutil to install the assembly into the GAC.

4. You can now build your ClassLibrary1 project again. If it succeeds, you will see something similar to this in the Output window:

1>------ Build started: Project: ClassLibrary1, Configuration: Debug Any CPU ------
1> ClassLibrary1 -> D:\code_temp\Integration Services Project1\ClassLibrary1\bin\Debug\ClassLibrary1.dll
1> The operation completed successfully.
1>
1> D:\code_temp\Integration Services Project1\ClassLibrary1\bin\Debug\ClassLibrary1.dll -> C:\MyAssemblies\ClassLibrary1.dll
1> 1 File(s) copied
1> Microsoft (R) .NET Global Assembly Cache Utility. Version 4.0.30319.17929
1> Copyright (c) Microsoft Corporation. All rights reserved.
1>
1> No assemblies found matching: ClassLibrary1
1> Number of assemblies uninstalled = 0
1> Number of failures = 0
1> Microsoft (R) .NET Global Assembly Cache Utility. Version 4.0.30319.17929
1> Copyright (c) Microsoft Corporation. All rights reserved.
1>
1> Assembly successfully added to the cache
========== Build: 1 succeeded, 0 failed, 0 up-to-date, 0 skipped ==========

The assembly is now installed, and your project is set up to keep the GAC reference up to date as you develop it.

5. The next step is to update the reference in the Script Component in Package.dtsx to look to the new GAC assembly. In the Data Flow Task, double click the Script Component and click "Edit Script". In the VSTA editor, remove the ClassLibrary1 reference from the project, and add a new assembly reference.

In the Reference Manager dialogue, click "Assemblies" -> "Extensions". You should see two entries for ClassLibrary1. My screenshot only shows one, because I've restarted VS2012 since removing the original reference. Just hover over each of them and add the one that says "C:\MyAssemblies\ClassLlibrary1.dll" in the tooltip.

6. In the VSTE editor, stick a breakpoint on the following line, then save and exit the editor:

7. You can now debug (F5) the project, and after a little while, the breakpoint will be hit (give it time, it takes at least 15 seconds to open on my development machine). If you step through the code, you can see that the assembly is now being hit, and the values from the assembly are being returned:

Each row (all 504 of them) will hit the breakpoint, so delete it and press F5 to close the VSTE editor and finish the package:

Installing on a Server

Although I'm sure you have your own methods when deploying an SSIS package, figuring out how to install a GAC assembly isn't the easiest of tasks, and it's not usually something your casual DBA or even Database Developer would be familiar with. However, knowing how to install to the GAC on your development machine gives you an advantage, and the good news is that you don't need to install an SDK to get it working.

My install package is a simple zip file, which contains the following items:

The assemblies you want to install into the GAC.
The SSIS packages to deploy.
An installation batch file.
gacutil.exe and its dependencies.

gacutil.exe, like many other Visual Studio tools, changes with each version of .Net. To install a .Net 4.5 assembly, copy the following files into your install package folder

C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\1033\gacutlrc.dll
C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\gacutil.exe
C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\gacutil.exe.config

The installation batch file needs to contain a number of commands, similar to the post build command we set earlier, only using the server's physical path instead of your local development path. The first line should be changed to the directory you extract the zip file to:

set installPath=C:\MyProject
set assemblyName=ClassLibrary1
"%installPath%\gacutil.exe" /uf "%assemblyName%"
"%installPath%\gacutil.exe" /i "%installPath%\%assemblyName%.dll" /f

After you've successfully installed the assembly to the GAC, all you need to do is deploy your SSIS package and schedule it to run - if everything is set up correctly, your GAC enabled SSIS package is ready to go!

Benefits

I'm not a fan of using the GAC, if I'm entirely honest, but with any kind of complex SSIS package that requires you to write or reuse .Net code, it's a necessary evil. The main benefits of importing a custom assembly into a script task / component is separation of concerns - you can unit test, build and manage the assembly separately from SSDT, and of course, keep any complex logic out of the fiddly script tasks and script components.
I've used this method to carry out many different tasks, including complex validation, advanced web service integration, custom logging, complex transformations, and data retrieval and generation. It's easy to see how powerful SSIS can be, especially when using the GAC opens up your entire code base for reuse within an ETL process. The opportunities are endless.

TDD and Unit Testing with Moq

2013-01-07T19:16:00.003+00:00

In my last post, I mentioned that I've been using TDD and mocking quite a bit this year, and I've fallen in love. It's something I'm very keen on continuing with, as I've now seen that the benefits outweigh the extra time needed to develop your tests.

I've written my fair share of stand alone unit and integration tests throughout the years, but until recently I've not used object mocking or stubbing. I've never really appreciated what tests are, and what benefits they give me as a developer, other than they let me write a quick bit of throw-away code that enables me to test the functionality of a class or a method. Only now - 16 years in to my career - do I realise how critical unit tests are to an application, especially in enterprise level systems that have a lot of code reuse and crossover. Not only do well written tests test the functionality of your code base, they also allow you to modify anything with complete confidence that your changes aren't going to affect anything else in the software.

So, on to the wonderful world Test Driven Development (TDD). Shunned by many because of the extra time needed to implement it properly, and ultimately due to the perception that TDD is "doing development backwards". The fact of the matter is, the process of writing a test and making sure it fails before you even write a line of implementation code, proves that you haven't written the test incorrectly in the first place, or written a test that'll pass regardless of your implementation. It's also self documenting, and forces modular, well-written, and concise code - as long as you follow the TDD guidelines and principals. Probably most importantly, it forces developers to think about what they're writing; there's little point in writing a test that confirms that a + b = c if you haven't figured out what c is going to be used for.

Skipping merrily down the street hand in hand with TDD is mocking. I've tried to get my head around it in the past, but - as with TDD - I've never really understood its use, so I've never made time to learn it. Why would I want to mock an object up in my tests when I need to test real code? Why would I write a test against a mocked object? Both silly questions in hindsight, and both easily answerable if you consider that you don't actually test mocked objects - they merely give you the ability to ignore any logic contained in your external dependencies, letting you concentrate on one particular function block, or "unit" of code.

I'm gonna delve in to this a little deeper, in the vain hope that I can get a few people on board with TDD. Consider this simplified real world scenario; say you want to test if pressing the power button on your TV remote control will issue your TV with a "turn on" signal, and power it on. Now, consider that in order for this test to work, the TV must be plugged in and supplied with electricity, be in "stand by" mode, and the remote must have sufficiently powered batteries installed. All these variables make the original requirement hard to reliably test - how I can accurately and consistently test if the remote sends the "turn on" signal if the batteries might be dead, or if the TV will receive the signal when someone has turned the TV off at the wall, or a fuse has blown? My test will fail if one or more of the dependencies don't function as I expect them to, meaning I could spend a while trying to fix something that isn't even broken in the first place.

Suddenly you're presented with a plethora of things that can go wrong; if we were to run this test without mocking up the dependencies, we would be performing an integration test rather than a unit test. Integration tests are still important, but tests at the unit level are extremely useful to us. They confirm that - even after modification - one particular part of the software still functions as it was originally intended to, and our modifications haven't changed that. Further to this, mocking allows us to replace the batteries, electricity, and stand-by mode dependencies with rock-solid, predictable implementations that will always function in the same way.

Let's put this remote / TV scenario in to a code implementation. I've simplified things for clarity - I wouldn't normally pass around "magic strings", and I probably wouldn't design it this way if it was going in to production, but I think it makes things a bit clearer for the purposes of an example. I'm using MSTest and Moq in my examples, but the same can be achieved with any testing / mocking frameworks.

In TDD we write tests before code, so let's write one that checks to see if the TV is receiving signals from the remote (we're using text to represent the remote codes that a TV might receive):

        [TestMethod]
        public void DoesTVReceiveSignalFromRemote() {
            // Arrange
            // Create a mock TV - we don't really care what the TV does,
            // we just need to know that the remote successfully sends a signal
            var tv = new Mock<ITelevision>();

            // Create a concrete RemoteControl instance to test, passing in the 
            // TV via constructor injection.
            var remote = new RemoteControl(tv.Object);

            // Act
            // Have the remote send a signal
            remote.Send("foobar");

            // Assert
            // Assert that the TV received a signal with the text sent with the Send() method
            tv.Verify(x => x.ReceiveSignal(It.Is<string>(s => s == "foobar")), Times.Once());
        }

Obviously, because the ITelevision interface and the RemoteControl class don't exist, you won't even be able to compile the code, but this gives you a starting point. If I'm honest, I usually implement the very basics of my classes and interfaces before I write any tests (throwing a NotImplementedExceptions in any method implementations), but only because I find trying to build a test that's broken at compile-time a little bit pointless. The point of writing tests first is so you can confirm that your test is well written and concise. It also makes sure that your test is correct - if your test passes straight away, you've either already implemented the code you're testing (which means you've got a duplicate test somewhere), or your test has been written badly.

So, in the above test I'm passing a mocked TV object in to the RemoteControl as a dependency (which does nothing at all - it's just satisfying the constructor requirement for an instantiated class), invoking the RemoteControl's Send() method (passing the string "foobar"), then checking if the TV's ReceiveSignal() method is invoked with the same string. This test tells us that the RemoteControl's Send() method requires the invocation of the TV's ReceiveSignal() method as part of its functionality, and that the signal that's received must be the same as the one that's sent by the remote. The signal "foobar" is arbitrary - we're not testing the validity of the signal, we're just testing that a signal can be received by the TV.

One of the main principals of TDD is to write as little code as possible in order to make your test pass. This ensures you have clear, maintainable code, and have tests for as much functionality as possible. Here's the minimal implementation for the above test:

    public interface ITelevision {    
        void ReceiveSignal(string signal);
    }

    public class RemoteControl {
        private readonly ITelevision pairedTV;

        public RemoteControl(ITelevision tv) {
            pairedTV = tv;
        }
        public void Send(string signal) {
            pairedTV.ReceiveSignal(signal);
        }
    }

The test now passes. Notice that there's no implementation of ITelevision; as I mentioned earlier, I don't care what the television does at this stage, because I'm just testing that the RemoteControl.Send() method physically sends a message to the TV it's been paired with. Let's go a little deeper.

Imagine that I wanted to save the remote's battery consumption by detecting if the TV has electricity flowing to it before sending an "on" request. Ignoring the fact that the technicalities are extremely questionable, I could check this bit of logic by creating a new test:

        [TestMethod]
        public void RemoteDoesNotSendSignalIfTVIsTurnedOff() {
            // Arrange
            var tv = new Mock<ITelevision>();

            // Setup the TV mock so it is turned off
            tv.Setup(x => x.HasElectricalPower)
              .Returns(false);

            var remote = new RemoteControl(tv.Object);

            // Act
            // Have the remote invoke the Send() method - the data sent
            // does not matter (we are not testing the data)
            remote.Send(string.Empty);

            // Assert
            // Assert that the TV implementation does not invoke ReceiveSignal() at all.
            tv.Verify(x => x.ReceiveSignal(It.IsAny<string>()), Times.Never());
        }

I've now introduced the "HasElectricalPower" property to the ITelevision interface, and hard-wired the mock to return "false" in every circumstance. I'm then sending an empty string to the RemoteControl.Send() method, as the data I'm sending is even less important than in our first test; we're not testing the signal's value at all here, so we don't need to populate it; you could just as easily leave it as "foobar", or any other random string. Last but not least, I've changed the assertion to make sure that the ITelevision.ReceiveSignal() method is never invoked. Notice that I've also changed the matching condition (It.IsAny<T>()) so it will accept any string whatsoever.

This test tells us that the ITelevision.HasElectricalPower property must be "true" before a signal can be sent. To make this test pass with the minimal amount of code, add the following highlighted code to your implementation:

    public interface ITelevision {    
        void ReceiveSignal(string signal);
        bool HasElectricalPower { get; }
    }

    public class RemoteControl {
        private readonly ITelevision pairedTV;

        public RemoteControl(ITelevision tv) {
            pairedTV = tv;
        }
        public void Send(string signal) {
            if (pairedTV.HasElectricalPower) {
                pairedTV.ReceiveSignal(signal);
            }
        }
    }

The test now passes, but those who've been following along will notice that the first test we wrote now fails. This is because you've introduced an additional dependency that needs to be mocked; the ITelevision.HasElectricalPower() property. This now needs to return "true", because otherwise the first test we wrote will never pass. While we're at it, let's rename the test to reflect the changes we're making, and parameterise the signal text that we're sending (which is what I would do in my own tests to make them easier to maintain):

        [TestMethod]
        public void DoesTVReceiveSignalFromRemoteIfTurnedOn() {
            // Arrange
            // Create the signal text that we're going to send. We can use this variable 
            // to check the received text too.
            const string expectedSignal = "foobar";

            // Create a mock TV - we don't really care what the TV does,
            // we just need to know that the remote successfully sends a signal
            var tv = new Mock<ITelevision>();

            // Setup the TV mock to it's always turned on
            tv.Setup(x => x.HasElectricalPower)
              .Returns(true);

            // Create a concrete RemoteControl instance to test, passing in the 
            // TV via constructor injection.
            var remote = new RemoteControl(tv.Object);

            // Act
            // Have the remote send the signal
            remote.Send(expectedSignal);

            // Assert
            // Assert that the TV received the signal
            tv.Verify(x => x.ReceiveSignal(It.Is<string>(s => s == expectedSignal)), Times.Once());
        }

And there we have it - two working tests, and a better understanding of our code and why we should test it. Refactoring is part of the TDD design principals, and you can do it in confidence that you have tests that'll catch any slip ups you might make. I'm going to cover one more scenario before I finish.

Moving away from mocks; say we want to check that the TV actually turns on when we send the signal "on" to it. This time, I'm going to write three tests before I write the implementation:

        [TestMethod]
        public void TVIsOffByDefault() {
            // Arrange
            var tv = new Television();

            // Assert
            Assert.IsFalse(tv.IsOn);
        }

        [TestMethod]
        public void TVTurnsOnWhenTurnOnSignalIsReceived() {
            // Arrange
            var tv = new Television();

            // Act
            tv.ReceiveSignal("on");

            // Assert
            Assert.IsTrue(tv.IsOn);
        }

        [TestMethod]
        public void TVDoesNotTurnOnWhenOtherSignalIsReceived() {
            // Arrange
            var tv = new Television();

            // Act
            tv.ReceiveSignal("foobar");

            // Assert
            Assert.IsFalse(tv.IsOn);
        }

These tests now require me to implement a Television class. Notice that we're not using the RemoteControl class or mocking here - for the purposes of this example, the Television class doesn't have any external dependencies, so we're just checking that when it's first instantiated it's turned off. Also worth noting is that the Television implementation won't affect any of the previous tests, because they're testing with a mock implementation of the ITelevision interface, rather than the real implementation. In our production code, we would inject the Television class in to the RemoteControl implementation, similar to the way we injected the mock in our tests.

Once the Television class is created, the TVIsOffByDefault and TVDoesNotTurnOnWhenOtherSignalIsReceived tests will pass straight away (which is contrary to everything I've been saying about TDD), but what they do allow us to do is define that IsOn is always handled correctly when we finally get the TVTurnsOnWhenTurnOnSignalIsReceived implementation working. They allow us to check that you or any other developers don't do anything undesired, like implement a constructor that sets IsOn = true, or allow any signal at all to turn the TV on.

The TVTurnsOnWhenTurnOnSignalIsReceived checks that the receipt of the "on" signal turns the TV on, and the TVDoesNotTurnOnWhenOtherSignalIsReceived test makes sure that sending anything but "on" will not turn the TV on (well, not exactly, but it's as close as we're gonna get). The implementation for the Television class is below:

    public class Television : ITelevision {
        public bool HasElectricalPower { get; private set; }
        public bool IsOn { get; private set; }

        public void ReceiveSignal(string signal) {
            IsOn = signal.Equals("on");
        }
    }

So, there we have it - a beginners insight in to the world of TDD. As with anything to do with development, you can go absolutely crazy with mocking and testing, but I find it depends on the project. Unit tests should be simple and to the point, and a mocked object should only really ever implement the properties and methods that are required for the test you're writing to pass.

2012: The Year The World Would End

2013-01-04T19:55:00.001+00:00

Despite my growing cynicism towards anything even remotely joyous and celebratory, 2012 has been the best year of my life so far. I married my wonderful wife; celebrated 30 years on the Earth; visited a number of historic landmarks and cities in the UK; spent August travelling through nine American states, sampling a vast array of cuisine and witnessing some amazing sights; and dug my teeth in to a lot of patterns and technology that's really interested me. I've also started on the path to clearing out all of the junk I've accumulated over the past 15 years, which has turned out to be an extremely liberating experience, even if it means I had to bite the bullet and chuck out a vast number of things that "I might use before I die".

Fig 1. A shit tip

I could prattle on about how awesome married life is, or regale you with my adventures across land and sea, but this isn't one of those god-awful social networking sites where all but the most discerning have an ultrasound as their profile picture, and I'm pretty sure that the few subscribers I do have are more interested in development tech than how many inches my beard grew this year, or how I just spilled avocado soup on my crotch. So instead I'm choosing to highlight some of the interesting projects I've worked on, the tech I've managed to pick up, and some lessons I've learned.

Intermediary Code

2012 was a very productive year for me. I started out finishing up a contract with Paymentshield Ltd, an insurance broker that were undergoing a complete overhaul of their internal and external systems. I ended up doing quite a lot of different things while I was there, but my main task was designing and implementing a completely bespoke, role-based security system that could be used in other developers' systems, and by 3rd parties such as clients, brokers and insurance companies. It also needed to integrate in to their existing legacy core system seamlessly, which is where the main challenge presented itself. The experience fully exposed me to the wonderful world of WCF and SOA, and I really got my hands dirty with SSIS, writing some pretty complex packages that transfer heaps of data between their systems in a matter of minutes. I also put together a custom CMS for their new intermediary website in ASP.Net Web Forms, including the functional public facing pages, the CMS administration panel, and the vast majority of the control panel for their intermediaries.

The Paymentshield Intermediary website

Whilst at Paymentshield I met a couple of developers that I got along with pretty well. One in particular - a guy called Stephen - inspired me to really push myself technically; we had plenty of geeky discussions around bleeding-edge tech, built up a bit of a rapport (mainly surrounding his disgust in my placement of curly brackets in code :\), and I learned a hell of a lot from him. Anyway, man-crush aside, and not wishing to do myself out of a potential job, if you're looking for a particularly gifted and knowledgeable Microsoft / web developer, you can get hold of him through his website / blog.

As to what I learned; dare I admit that I'd never even heard of Dependency Injection and Inversion of Control before I started at Paymentshield, and although I'd touched upon it, WCF was still a bit of a mystery to me. I came away with a good, rounded knowledge of everything I worked on, and I've been consistently using and improving those skills ever since - in fact, I often advocate the use of DI / IoC, SOA, and a number of design patterns; I can't see how I managed without them!

Love Code

In May, I took on a project at webuyanycar.com, architecting and developing a new website they had in the pipeline. Even though I've put together a few smaller MVC apps over the years, this was my first commercial MVC focused role - and it's something I've been interested in for quite some time. Prior to this I'd been developing with MVC3 almost every day since it was released in January 2011 - spending hours per night on one of my personal projects. I'd been doing some pretty complex stuff too... so when I actually got down to the nitty gritty for the client's project, I realised that my knowledge was actually quite advanced and current. Suffice to say that I pumped out another bespoke CMS in just 3 months, that integrated in to their existing SOA architecture and CRM system.

www.weloveanycar.com

My contract ended a few weeks before the site was supposed to go live, as I took off to get married and spend a month on honeymoon in the States. The website has since launched, though it's not seeing much activity at the moment; a little birdy has told me they're in the process of redeveloping it as the focus has changed since I was there, so it looks like the fruit of my labour might not be there too long.

There were two of us developing the site; the second contractor was in charge of everything front-end, which helped to speed up my back-end development considerably. While I was there I managed to pick up a few things I'd not spent too much time on before; JS / CSS minification and bundling, a little bit of Test Driven Development, mocking, search engine optimisation, and software build management.

Rusty Code

Whilst at webuyanycar (in my free time, of course), I finally managed to launch my pet project rustyshark.com. I'd spent a couple of years developing it on and off, and at the beginning of 2012 I made a concerted effort to get it up to a point that I could launch a beta version. Since then, I've been feverishly adding features and fixing bugs, tacking spammers, writing as many reviews as I've been able to (considering my time invested elsewhere), and trying to optimise it for the search engines. I can't say it's been easy, but I finally feel as if I've put something together I can be proud of. When I've got a few more of the public facing pages completed (such as the search and the news filtering) I'll be happy to call it a "release".

Fig 2. Better looking than your girlfriend.

I did have high hopes for the website; it was never going to be able to compete with the likes of IGN or GameSpot, but I wanted it to be somewhere that me and my geeky friends could shoot the shit about games and films, whilst also serving to teach me a new technology that I've been meaning to learn for a while. Whilst the later was extremely successful (and then some), the former suffered due to a number of reasons. The friend I conceived the website with let me down more than a few times, and just couldn't commit to the project due to personal reasons, and the people who said they'd help out with the odd review here and there all have their own things going on. Whilst I can't complain (I totally appreciate everything I've received from people), unfortunately I can't do everything myself, meaning there isn't anywhere near as much content as I'd like there to be. I'm still posting reviews when I can (I've got two on the go at the moment), and hopefully it'll pick up again at some point in the near future.

I've managed to apply almost all of the skills and techniques I've learned over the last year to the website; only a few weeks ago I updated it to use .Net 4.5, EntityFramework 5.0 (turning off lazy-loading at the same time, which meant I had to test absolutely everything on the website thoroughly for missing data), AutoMapper 2.2.1-ci9000 (pre-release), MVC4, and added RSS feeds and a comment approval system. I'll continue to update the site, whether I'm posting reviews on there or not, as I enjoy the challenge, and it serves as a good test bed for new technology. One thing I'm very keen on doing is adding a lot of unit and integration tests to my service factories, as my most recent contract has exposed me to the vast benefits of TDD and mocking.

Independent Code

I just finished at Independent Group, a claims management company that are in the process of upgrading all of the hardware and software across the entire group; a vast undertaking that's being carried out in carefully planned steps. They've got a lot of legacy software, which requires staged replacement, and I was tasked with designing and implemented an SOA system that pulls information from various internal sources, and provides it to their suppliers. I got to flex my SSIS, WCF and MVC skills again, but also did a lot of infrastructure; setting up IIS and SQL servers, configuring DNS, installing certificates, and setting up builds and deployment. I've not really done much infrastructure for the past few years, as most of my clients have had departments that deal with it, so it was actually quite refreshing to get to grips with IIS7 and Windows Server for a change.

I'm quite proud of the work I've done at Independent - everything has been TDD from day one (last time I looked, I was at 89% code coverage), and all components are fully documented. I can't say I've learned all that much while I was there, but I did get a good grip on mocking, and had another opportunity to analyse and design a loosely-coupled, enterprise level piece of software.

My Code

Looking back over the year, I really feel like I've come a long way. I've been learning how to develop for around 16 years, in which time I've worked with clients of all shapes and sizes, and designed and developed software from simple web sites to massive, scalable enterprise solutions.

My aim this year it to look in to mobile app development; whether it's games or tablet apps. Even if I do despair every time I see a 14 year old with their face permanently glued to Angry Birds, it's the future, and if I don't start looking in to it soon, I'm gonna be left behind!

Windows 7: One Network Adapter, Two Connections; No Internet Connection

2012-07-02T07:04:00.000+01:00

For the past week, my main PC has been occasionally booting with a lovely little yellow exclamation mark over the network icon in the system tray. The only way to rectify it was to reboot once or twice, which confused me a little bit. Anyway, to cut a long story short, it's the Bonjour network service that was recently installed with Skype (I'm currently working at home and we're using Skype to communicate). It seems that the best way to solve this (without affecting Skype) is to "Delay Start" the service. The service (and it doesn't sound like this happens on everyone's machine) is pretty easy to find, only because it's got a GUID for a name in the Services snap-in (Start Menu -> Administrative Tools -> Services):

GUIDs: scaring the shit out of advanced computer users since 1998.

Suffice to say, it looks dodgy, so it was investigated. Luckily, there's plenty of info on Google about it. Just change the startup type to "Automatic (Delayed Start)" and everything goes away.

Apple are a pack of bastards

AutoMapper: UseValue vs ResolveUsing vs MapFrom

2012-06-28T11:15:00.001+01:00

I just hit a confusing mapping bug whilst using AutoMapper to map DateTime.Now to a field in my database. Every item that was being added to the database had the same value in the "DateAdded" - to the millisecond. This was because I was using the following mapping expression:

Mapper.CreateMap<SourceObject, DestObject>()
.ForMember(m => m.DateAdded, o => o.UseValue(DateTime.Now));

UseValue() retrieves a value on first-run and stores it in the mapping (hence the static DateTime value that's being stored in the database), whereas ResolveUsing() resolves at run-time - which is obviously why it's called ResolveUsing(). A minor oversight, but confusing nonetheless.

Use ResolveUsing(s => {}) when you want to resolve a destination field from a derived value. This should be used for any DateTime mappings, and any more complicated mapping functions.
Use MapFrom(s => s.MemberName) when you return an actual source object member.
Use UseValue() if you want to map a static value in the mapping, and you know the value itself won't change.

IMDB API Lookup JQuery Plugin

2012-06-24T09:52:00.000+01:00

I spent a few hours last night writing my first ever JQuery plugin, only to realise today that it's completely useless to me! I wanted an easy way to populate the "Add medium" form on RustyShark, as it tends to take me a few hours every time I add a new review (and therefore medium) to the website (y'know, looking up information, finding images, finding box art, editing the review etc), and I wanted to increase my production rate :)

IMDB offers its data for free, for non commercial use, but only in text files. I found an API at http://www.imdbapi.com/, which has gone a long way to simplifying and serializing the information in to something useful, so I decided to use this opportunity to teach myself how to write JQuery plugins. Below is my resulting plugin, queryable by Title + Year, or IMDB ID:

; (function ($) {
    $.fn.imdbLookup = function (options) {
        // Default options
        var defaults = {
            complete: function (imdbData) {
            },
            title: null,
            date: null,
            imdbId: null
        };
        // Overwrite defaults with passed options
        options = $.extend(defaults, options);

        return this.each(function () {
            var $this = $(this);
            $this.click(function () {
                // Get defaults
                var title = (options.title != null && options.title.val().length > 0) ? options.title.val().trim() : null;
                var date = (options.date != null && options.date.val().length > 0) ? $.parseDate(options.date.val().trim()) : null;
                var imdbId = (options.imdbId != null && options.imdbId.val().length > 0) ? options.imdbId.val().trim() : null;

                if ((title == null || date == null) && imdbId == null) return;
                var year = date.getFullYear();

                var qs;

                if (title != null && date != null) {
                    qs = {
                        t: title,
                        y: year
                    };
                } else {
                    qs = {
                        i: imdbId
                    };
                }
                
                $.ajax({
                    url: 'http://www.imdbapi.com/',
                    type: "GET",
                    dataType: "json",
                    data: $.extend({
                        r: 'JSON',
                        plot: 'short',
                        tomatoes: true
                    }, qs),
                    success: function (imdbData) {
                        options.complete(imdbData);
                    }
                });
            });
        });
    };

})(jQuery);

Usage:

    $("#MyElement").imdbLookup({
        complete: function (imdbData) {
            alert(imdbData); // Do something with the resulting object
        },
        title: $("#Title"),
        date: $("#ReleaseDate")
    });

Or:

    $("#MyElement").imdbLookup({
        complete: function (imdbData) {
            alert(imdbData); // Do something with the resulting object
        },
        imdbId: $("#imdbId")
    });

Please read http://www.imdbapi.com/ for notes on usage.

It seems to work for me at the moment, but treat it as completely untested - I've only tried it in Chrome, and I haven't tested the IMDB ID method at all. I just wanted to put the code somewhere before I removed it from the JS in RustyShark :)

RustyShark.com is finally online!

2012-06-11T00:39:00.001+01:00

Regular readers will know that I've been working on my new game and film review site for a good while now. I've been spending my spare time in the past few weeks editing the reviews I've written and gathered from friends, which has been a task in itself. Unfortunately, because the site is a bit of a movie and game database as well as a review site, it takes me a while to gather all of the information for each one, so I've not been able to launch with too many published! Suffice to say, I've got a lot more to get through, but when I've caught up I should be able to edit and publish reviews as they come in. All of the reviews I've received will be dealt with ASAP!

I've still got a long way to go until version 1 of the site is completely finished. I still have to write the news editing tools and there are lots of additional features I want to implement, like a Facebook login, more products from different sites and RSS feeds. Of course, I've still got quite a bit of layout tweaking to do, and quite a few additional niceties like a carousel and new panels.

For those interested, I've written the site in MVC3, using SQL Server 2008 and Entity Framework 4.3. I've written it from scratch, rather than use a CMS like Joomla or Umbraco, in order to thoroughly teach myself the MVC pattern and all of the tools Microsoft have added to the latest version. All sensitive information is encrypted with SHA256, and I've written a load of image manipulation classes and MVC helpers.

My JQuery isn't the best, so any front-end guys looking at the client-side code might have a chuckle or two. It's something I aim to improve in future years, as I quite enjoy it, but it's not something I've been involved with too heavily.

I've tested as much of the site as I can, with all of the major browsers. I haven't spent any time getting it working with older versions (especially not the dreaded IE6); it may be something I look in to in the future, but we'll see how it goes.

I still consider the site in "alpha", as I've not been able to really hammer the testing. If anyone sees any issues, or has any ideas about how to improve it, all feedback is appreciated!

www.rustyshark.com

AutoMapper 2.1 AfterMap() Fires Multiple Times

2012-05-15T10:16:00.000+01:00

I've been writing some complex mapping for the hierarchical data on my RustyShark site (the Media entities on the site consist of a lot of dynamic meta data, which can be added and removed when edited). I'm using Entity Framework and AutoMapper, and I've ran in to an issue where AfterMap() executes multiple times, when it should only really execute once. I just thought I'd post my workaround, which also helps to demonstrate how C# interfaces can be handy. Here's what my source entity looks like:

public class Medium : IAutoMapperAfterMapUsage {
    public int? MediumID { get; set; }
    public string Title { get; set; }
    public DateTime ReleaseDate { get; set; }
    public User CreatedBy { get; set; }
    public DateTime CreatedDate { get; set; }

    public List<Genre> Genres { get; set; }
    public List<MediaFormat> Formats { get; set; }
    public List<MetaData> MetaData { get; set; }

    #region IAutoMapperAfterMapUsage

    public bool Mapped { get; set; }

    #endregion
}

The destination entity isn't a concern here, so I won't post the code. As you can see, I've added a custom interface, IAutoMapperAfterMapUsage which has one property:

public interface IAutoMapperAfterMapUsage {
    bool Mapped { get; set; }
}

The boolean simply acts as a state manager, so whenever you need to use AfterMap(), implement the interface and add a single line of code at the top of your AfterMap method:

Mapper.CreateMap<Entities.Medium, Medium>()
    .ForMember(m => m.CreatedDate, o => o.Condition(s => (s.MediumID ?? 0) <= 0))
    .ForMember(m => m.CreatedDate, o => o.ResolveUsing(s => DateTime.Now))
    .ForMember(m => m.CreatedBy, o => o.Condition(s => (s.MediumID ?? 0) <= 0))
    .ForMember(m => m.CreatedBy, o => o.MapFrom(s => s.CreatedBy.UserID))
    .ForMember(m => m.Title, o => o.ResolveUsing(s => s.Title.Trim()))
    .ForMember(m => m.MediaType, o => o.Ignore())
    .ForMember(m => m.Genres, o => o.Ignore())
    .ForMember(m => m.MediaFormats, o => o.Ignore())
    .ForMember(m => m.MediaMetaData, o => o.Ignore())
    .AfterMap((source, dest) => {
        if (source.Mapped) return;
        // Do complex mapping for Genres, MediaFormats and MediaMetaData

        source.Mapped = true;
    });

Apparently this has been fixed, though the fix does say "Moved after map to execute less often, fixes #105", which isn't that reassuring. I also use this interface to expose my DbContext to the AfterMap() method, which is very handy for the complex scenarios I'm working with.

Long running processes: C# 5, Visual Studio 11, async and await; updating a Winforms UI asynchronously

2012-04-20T11:57:00.000+01:00

I recently downloaded Visual Studio 11 Beta in order to take a look at the new 4.5 .Net Framework and play around with its new features. I decided to take the opportunity to build a "quick" app that I've been meaning to build for a few years; a single form Windows Client that allows me to send out an email merge to the 1,400 agents I have in my outlook contacts list, to let them know I'm available for work. Before now, every time I've needed to send an "I'm available!" email, I've copied + pasted the same email a number of times, attached my CV and BCC'd 100 or so agents at a time (too many and Outlook just falls over). Not only is it a massive waste of my time, but it's very error prone and not a very friendly way of doing things. Unfortunately, the mail merge feature of Word has its limitations, so I've had to look in to my own solution.

Anyone who's worked with bulk email systems before knows fine well that any kind of mass emailing (solicited or unsolicited) is seen as "spam" by many email servers and ISPs. Some servers are clever enough to spot a mail merge, but most still work on limits. My current server host allows me to send 300 email per hour, which is way more than I intend to send, but I've still got to consider the receiving servers; I have 100+ agent email addresses per agency / domain, so I'm likely to be black listed if I sent them all at once. So, with this in mind, I decided to build an email system that I could configure to fire off emails asynchronously, over a configurable period of time. Here's a quick screenshot - it's not exactly pretty, but it does what I need it to :)

The Email Template drop down requires a little bit of explanation; I'd already written a emailer class for my RustyShark project that accepts XML templates and replaces place holders (wrapped in {curly brackets}) with any data I so wish. All I had to do was build the forms, add in some configuration options and write the threading code.

In my app, I wanted to be able to select a CSV of email addresses and names, then click a button to send the selected template to everyone in the list at various increments. All of this is pretty each to achieve, but as always, long running processes (the app will be running over a number of hours; 1400 contacts x 20 second intervals = 7.8 hours) lock up the UI thread, meaning it's very difficult to monitor their progress. Obviously there's the System.Threading namespace, and the various BeginInvoke() methods scattered around the Framework, but I downloaded the VS11 Beta to learn something, and learn something I did!

Enter the new "async" and "await" C# language features. I spent a while in the Visual Studio Developer Centre watching videos, reading whitepapers and trying examples, then I delved in to my specific requirement. Asynchrony is not an easy task in any language or framework, but the new keywords (as long as you understand what they mean) do help to simplify your code, and help with debugging. Let's jump straight in to an example.

When I click "Send" at the bottom right of the app, I wanted to fire off a foreach() loop, which sends an email, updates the DataGrid, the label next to the send button, and a progress bar, which is not visible in the screenshot above. Though my form has a lot more code (error handling, logging etc), normal, synchronous code would look like this:

private void btnSend_Click(object sender, EventArgs e) {
    // Set up progress bar
    progressBar.Maximum = recipients.Count;
    progressBar.Value = 0;
    progressBar.Step = 1;
    progressBar.Visible = true;
    // Disable form
    ToggleForm(false);

    foreach (var r in recipients) {
        try {
            // Send email
            var msg = new Email();
            if (!msg.Send()) {
                // Email Failure
                r.Error = "Unknown";
                continue;
            }
            // Update recipient
            r.Success = true;
        } catch (Exception ex) {
            r.Error = ex.Message;
            continue;
        } finally {
            // Update progress
            progressBar.PerformStep();
            progressBar.Refresh();
            // Refresh grid
            r.Processed = true;
            dgvRecipients.Refresh();
            // Update label
            UpdateSummaryMessage();
        }

        // Do not wait if there are no more recipients to process
        if (recipients.All(x => x.Success)) continue;

        // Wait this thread for the specified amount of time
        var waitTime = (int)(udSeconds.Value*1000);
        Thread.Sleep(waitTime);
    }
    // Enable form
    ToggleForm(true);

    progressBar.Visible = false;
}

Recipients is a List<Recipient>, which is data bound to the DataGridView. It contains the contact information loaded from the contacts CSV. udSeconds is the Up Down Control on the form that selects the number of seconds to wait between emails. The var msg = Email() line uses my custom Email templating class, and actually needs a lot more code to work, but you get the general idea.

As you can see, I'm using Thread.Sleep() to wait the thread for the number of seconds I've specified with the Up Down Control. I experimented with a timer at this point, but using one would have pretty much negated the need for the async / await keywords I wanted to play with (and required me to use callbacks and invokes to update the UI thread), so I avoided it.

UPDATE: 2nd May 2011 - Since I wrote this post, I found out that Thread.Sleep() should not be used for exact timing. The thread is blocked for a minimum of {n} seconds, after which, control is then passed back to the OS to be scheduled back in for execution. Each version of each OS has it's own idea of what a processor time slice is, so there is no guarantee that the thread will continue execution after the time you have specified. My examples below are still valid, but only as examples. Neither Thread.Sleep(), await Task.Delay() (also new to the Developer Preview) or Task.Wait() should be used for precise scheduling. Contrary to my comment above, there are various timers available in the .Net Framework that are much more reliable than trying to control the thread directly, and should be used for this type of scenario.

In my example, each call to Thread.Sleep() took exponentially longer than the last. As you can imagine, over a number of hours, this built up to quite a lot of additional time. This would be totally unacceptable in a mission critical application.

When the above code is implemented, the UI hangs for however long the task is running (in this case, nearly 8 hours). Even for a small, personal application, this is an unacceptable situation. Making this code asynchronous would normally require quite a bit of refactoring, but the new async / await keywords really speed it up:

private async void btnSend_ClickAsync(object sender, EventArgs e) {
    // Set up progress bar
    progressBar.Maximum = recipients.Count;
    progressBar.Value = 0;
    progressBar.Step = 1;
    progressBar.Visible = true;
    // Disable form
    ToggleForm(false);

    await Task.Run(() => {
        foreach(var r in recipients) {
            try {
                // Send email
                var msg = new Email();
                if(!msg.Send()) {
                    // Email Failure
                    r.Error = "Unknown";
                    continue;
                }
                // Update recipient
                r.Success = true;
            } catch(Exception ex) {
                r.Error = ex.Message;
                continue;
            } finally {
                // Update progress
                r.Processed = true;
                Report(r);
            }

            // Do not wait if there are no more recipients to process
            if(recipients.All(x => x.Success)) continue;

            // Wait this thread for the specified amount of time
            var waitTime = (int)(udSeconds.Value * 1000);
            Thread.Sleep(waitTime);
        }
    });
    // Enable form
    ToggleForm(true);

    progressBar.Visible = false;
}

"That's it?!" I hear you scream? Well, almost. Most examples you'll see online just show you the addition of the two new keywords, and usually have a second method (tagged with the async keyword), called in place of my await Task.Run() line. Task.Run() is new in the .Net 4.5 Developer Preview, and is basically a shortcut to the Task.Factory.StartNew() method from .Net 4.0, only much simpler to use.

So, what's this await keyword all about? In order for any method to use the await keyword within its body, it must be awaitable. In order to make a method awaitable, you have to tag the async keyword on to the method signature. If you tag async on to a method and do not include the await keyword in the body, the method is always executed synchronously. The await keyword simply tells the compiler that asynchronous code is to be executed, somewhere within the body of the method. Because the code is asynchronous, the method immediately returns to the UI thread and the code inside the Task.Run() block is started on another thread. The interesting thing to understand here, is that the rest of the btnSend_ClickAsync method waits for the asynchronous code to be executed before it is executed itself. This is immensely clever, considering everything looks perfectly synchronous, there are no callbacks specified, and we've hardly changed any code.

I've not only used Task.Run() because it has a cool lambda implementation and I dislike superfluous methods, I've used it to demonstrate that the await keyword can be applied to any method that returns a Task, as well as those that use the async keyword. The Task.Run() method does not use the async keyword, it simply returns a Task. The Task class implements IAsyncResult, which proves that the compiler still uses callbacks in the background, even though you don't have to deal with them yourself.

So, where's this callback? Well, the compiler puts everything that comes after the await Task.Run() line in to a callback (everything under the // Enable form comment), so it's only executed once the asynchronous code is finished. I don't fully understand this bit yet, but notice that the code I've written accesses the UI controls directly, as if they were being executed in the UI thread; the code looks synchronous, but I imagine the callback that's created by the compiler also rewrites these UI calls in a thread-safe manner (possibly using invokes).

The "Async" suffix on the method name does not seem to be required, but is used consistently in examples, blogs and whitepapers. Of course, this makes sense, as you may want to implement both synchronous and asynchronous methods for a single task.

It's also worth briefly mentioning that you can use the result of the Task.Run() method in the callback, to determine a return value from the asynchronous code. Apparently, you're supposed to be able to return a Task<T>, but I've not yet been able to figure out how to do that. The best I've been able to figure out is that I can return a value from the Task.Run() method. Take a look at the following simple example:

    var asyncResult = await Task.Run(() => {
        foreach(var r in recipients) {
            try {
                // Send email
                var msg = new Email();
                if(!msg.Send()) {
                    // Email Failure
                    r.Error = "Unknown";
                    continue;
                }
                // Update recipient
                r.Success = true;
            } catch(Exception ex) {
                r.Error = ex.Message;
                continue;
            } finally {
                // Update progress
                r.Processed = true;
                Report(r);
            }
            // Do not wait if there are no more recipients to process
            if(recipients.All(x => x.Success)) continue;

            // Wait this thread for the specified amount of time
            var waitTime = (int)(udSeconds.Value * 1000);
            Thread.Sleep(waitTime);
        }
        return true;
    });

    if(asyncResult) {
        // Enable form
        ToggleForm(true);

        progressBar.Visible = false;
    }

I've no use for it in my example, but I'm sure I'll need it in the future :)

So, on to the rest of the changes I've made to the above example. The UI update code that was in the finally() block has been moved to the Report() method. This method is provided by the IProgress<T> interface, which is another new interface available in .Net 4.5 Developer Preview. I'm using it here to post to a SynchronizationContext object I created on the main form. I might be missing something, but I think it's provided for convenience and separation only; I don't *think* you need to use the IProgress<T> interface to update the SynchronizationContext , it just makes it a bit neater. An example of the implementation is below:

public partial class AsyncTest : Form, IProgress<Recipient> {
    private readonly SynchronizationContext sc;

    public AsyncTest() {
        InitializeComponent();
        sc = SynchronizationContext.Current;
    }

    public void Report(Recipient value) {
        sc.Post(x => {
            value.Success = true;
            progressBar.PerformStep();
            progressBar.Refresh();
            // Refresh grid
            dgvRecipients.Refresh();
            // Update label
            UpdateSummaryMessage();
        }, value);
    }
}

SynchronizationContext has been in .Net since version 2.0, and allows you to post (asynchronous) or send (synchronous) information back to the UI thread. Report() is being called in place of the UI update code (which would have broken at runtime if we'd left it within the Task.Run() block), which in turn posts the data. I'm fairly confident that you could remove the IProgress<T> implementation and put the sc.Post() code in to the finally() block without it causing too much trouble, though I've not tried it yet.

Within .Net 4.5 there are a number of new Async methods built in to classes for potentially long-running operations, such as reading a Stream or sending an email with System.Net.Mail.SmtpClient(), which are all welcome additions. I imagine they're all just wrappers, though in theory you can create your own Async method for any method in the framework, which is quite exciting.

So, here's a full example of the code above. Please bear in mind that this is a cut-down version of my form code, so it won't work out of the box. It's intended to show you how to implement an asynchronous Winform button using the new async and await keywords, and carry out progress updates without having to specify callbacks:

public partial class AsyncTest : Form, IProgress<Recipient> {
    private readonly SynchronizationContext sc;
    private List<Recipient> recipients;

    public AsyncTest() {
        InitializeComponent();
        sc = SynchronizationContext.Current;
    }

    public void Report(Recipient value) {
        sc.Post(x => {
            value.Success = true;
            progressBar.PerformStep();
            progressBar.Refresh();
            // Refresh grid
            dgvRecipients.Refresh();
            // Update label
            UpdateSummaryMessage();
        }, value);
    }


    private void UpdateSummaryMessage() {
        // update summary message code
    }

    private void ToggleForm(bool enabled) {
        // enable / disable relevant form controls
    }

    private async void btnSend_ClickAsync(object sender, EventArgs e) {
        // Set up progress bar
        progressBar.Maximum = recipients.Count;
        progressBar.Value = 0;
        progressBar.Step = 1;
        progressBar.Visible = true;
        // Disable form
        ToggleForm(false);

        var asyncResult = await Task.Run(() => {
            foreach(var r in recipients) {
                try {
                    // Send email
                    var msg = new Email();
                    if(!msg.Send()) {
                        // Email Failure
                        r.Error = "Unknown";
                        continue;
                    }
                    // Update recipient
                    r.Success = true;
                } catch(Exception ex) {
                    r.Error = ex.Message;
                    continue;
                } finally {
                    // Update progress
                    r.Processed = true;
                    Report(r);
                }
                // Do not wait if there are no more recipients to process
                if(recipients.All(x => x.Success)) continue;

                // Wait this thread for the specified amount of time
                var waitTime = (int)(udSeconds.Value * 1000);
                Thread.Sleep(waitTime);
            }
            return true;
        });

        if(asyncResult) {
            // Enable form
            ToggleForm(true);

            progressBar.Visible = false;
        }
    }
}

I'm afraid I'm no expert with asynchrony, and my understanding is limited - still, I've done my research, so I'm hoping that all of the information I've posted is accurate. If you spot anything that's incorrect, please let me know and I'll update it.

My next task is to put an example together for MVC and / ASP.Net Webforms, to see how useful it'll be. I've been using AJAX for asynchrony for years, so I'm not sure how these methods will fit in the web world at the moment.

A learned journey

2012-02-29T11:51:00.000+00:00

As a mercenary developer, I come across programmers from many different walks of life; of all skill levels and abilities with varying levels of enthusiasm. Whilst I'm certainly not perfect (and am always learning), I encounter some who are yet to grasp some of the more basic programming concepts. It's very easy to take a blinkered view of the technologies you're familiar with and to become complacent in your current role or too comfortable with your existing skills. On the other hand, some developers I encounter are far keener than I, and therefore usually far exceed my skill; two things which are directly related.

I speak from first hand experience when I say that by taking a step outside of your comfort zone and making even one or two small changes to your daily routine as a developer could seriously improve your general programming knowledge and overall productivity. Although not quite ad-verbatim, I think it was Scott Hanselmann who recently blogged that simply stating that you are a developer with 20 years experience means very little; having 20 years experience writing the same code over and over is very different to going a little bit out of your way to learn something new with every project (or even every day - there's really no excuse!); (20 years x 1 years experience) > (1 years experience x 20 years). The fact is that if you do not look at what you're writing and constantly want to improve it, or at least to investigate new technologies and employ new techniques and patterns to your existing knowledge, you're digging yourself a career-swallowing hole.

I often see code written by senior developers that doesn't even follow some of the more simple programming principles. I appreciate that code can get "in a state" over time (especially in environments that aren't tightly managed), but it's rare that I ever need to "hack" code to get it to work - hacks are usually a result of bad planning or just because a developer doesn't know a right way to do things. Of course, new technologies are sometimes lacking in various areas, but most are extensible, and with a little bit of work can be neatly expanded to include functionality you require, whilst avoiding those nasty code smells.
I can forgive junior developers not understanding some of the more advanced techniques, but when I see senior developers (especially contractors) who still blindly copy & paste code segments from the web, constantly use WYSIWYG editors or hack their way around a language without making use of it's features and tools, it baffles and infuriates me. All it takes is to spend as little as 20 minutes a day researching the right things to keep your skills fresh and brain active. You can even fit things in and around work, which is a healthier way to approach it than taking time out of your personal life, if you're as busy as I can sometimes get.

I appreciate this post won't be very popular with the search engines - no developer I know (whether they are good or bad) is going to search for "how do I stop being a complete ball-bag at programming", mainly due to the fact that the people this blog is aimed at are, whilst being intelligent, usually ignorant to the fact they can do things better... or they simply don't care how terrible their code is. This isn't just me having a winge; developing better code (and understanding it) is the first step to a more rewarding and interesting daily work life, whether you've been somewhere for 30 years, or you move around from client to client like me. It certainly helps me land work - a breadth of knowledge is generally much more important to my clients than specialising in one particular area, though of course there are arguments for both.
So, I shall attempt to compile a list of "Dos and Don'ts" for those programmers who wish to become wiser in the world of development. Some of the items below are platform / language agnostic, whereas others are .Net / C# specific. I am, after all, a .Net developer :) I'm more than open to additional suggestions; as previously mentioned, I'm far from perfect, and my quest for knowledge is never ending.

Once and Only Once / Don't repeat yourself (DRY) - Two similar programming principals stating that "Every piece of knowledge must have a single, unambiguous, authoritative representation within a system". Hard to follow in their purest form, but in an ideal world, you would never write the same line of code more than once, nor would you ever copy + paste code from another part of your application; if you need to output the same error message on multiple web pages, store it in a class or a configuration file. If you need to log different levels of error, write a number of objects to do the complicated work, and a helper method to call and manipulate the objects etc.

I often see code that repeats itself, even within the same method; create directory A, create directory B, create sub-directory B-A etc... applying this principal (or at least having it in mind) will greatly improve maintenance of your code, and make your code more predictable.
Use the correct type - Please, for the love of Zeus, don't just use "int" and "string" for everything, and avoid using base types like "object" unless absolutely necessary. E.g. use boolean types for boolean data; if you're storing a CHAR(1) in a database that only has two potential values ("Y" and "N" or "0" and "1", for example), store as a boolean. Not only do you save memory / disk space (in the above example, CHAR(1) stores 1 byte, whereas you can have up to 8 BIT columns in a single table occupying 1 byte), but the column is naturally constrained to two possible values, reducing the possibility of a data error.
Code Formatting - Most IDEs will format for you these days, but there are still some that don't. Those that format should give you plenty of options on how to display your code, and a little bit of fiddling should do the trick. Well formatted code is easier to read and modify, for you and other developers that may need to access it.
Subscribe to blogs - One of the most productive things I ever did was starting using RSS to subscribe to tech blogs. Subscribing to the right blogs (it took me a while to filter down to the ones that are useful to me) gives you instant access to the latest tech releases and insider info. I'm always adding and changing my blog subscriptions - but there are a few .Net guys I will mention by name, due to the fact they provide consistent quality in their updates. Scott Hanselman, Scott Guthrie and Phil Haack. I enjoy reading their blogs more than most, though I am subscribed to quite a number of other developers too.
Expand (and understand) your toolset - I recently posted a Tools Roundup listing the apps and programs that I use on a daily basis. I have to admit, I don't know all of the tools I use inside-out, but I sure as hell know that what little I do know about them has improved my productivity unfathomably. I used to shun tools, until I grew out of it :)
Read whitepapers - As with blog subscriptions, another thing that I've been doing for the past few years is actually reading through online articles, instead of skipping through to find code examples. None of us have the time for that though, do we? By reading the full article, you have a better chance of understanding a concept, and taking away from it much more than just what you're looking for. They help you make informed decisions and give you all kinds of facts that you didn't know or think about. Online whitepapers can be written by individual developers, or sponsored by corporations - both can be full of relevant information, but beware - they can also be full of rubbish, just like blogs (ahem).
Embrace new technologies - It's hard to keep up, but it's definitely possible. I have a lot on my plate (see my "bio" section), but I still manage to fit in the odd example project now and then. At the time of writing, MVC4 is in Beta and not far off RC - I've already played around with the new MVC4 framework, but I've not spent too long on it. It doesn't hurt to download example projects or follow blog posts to at least see what a tech's new features are capable of.
Inform Yourself of Popular Design Patterns - Pretty much every developer uses at least one design pattern, whether they know it or not. Different patterns can be used to make your development quicker and easier, as long as you know where to apply each one. I'm no expert by any means, but since I started reading about and understanding different design patterns, I've come a long way. Take a look at MVC, MVVM, nTier, Factory and singleton to get you started.

The Internet is a wonderful place that can answer almost any question you have, as long as you know how to search, and you're willing to read through the information available. If you've read this far, there's hope for you yet - if you haven't, then I wish you luck travelling down that long and unrewarding road.

MVC3: Redirect Action to HTML Bookmark

2012-01-19T10:46:00.000+00:00

I'm currently in the process of writing my next personal project, RustyShark in .Net MVC3. It's a game and films review site, and will hopefully generate a bit of a community. Each review on the website has a comments form at the bottom that readers can use to post their own mini review, or just a simple comment. I'm using unobtrusive client side validation, but (as it should) the validation also occurs on the server side. This means that the user could potentially receive an error message on the comments form after a post-back but not notice it, as they will be redirected to the top of the page, rather than the comments form.

The "read review" page is accessed via the fairly standard route /Reviews/Read/{titleURL}, which means the reviews controller exposes the Read action method. The Read method has a single override which is tagged with the [HttpPost] attribute as follows:

    public class ReviewsController : Controller {

        public ActionResult Read(string titleURL) {
            // Get review by SEO friendly url
            var model = new ReadReviewModel();
            return View();
        }

        [HttpPost]
        public ActionResult Read(ReadReviewModel model, string titleURL) {
            if(ModelState.IsValid) {
                // Add user review comment
            }

            return View(model);
        }
    }

Fairly standard so far, yeah? Well, also pretty standard is applying a bookmark (e.g. /Reviews/Read/#comments) to a URL, which is especially useful when target content is located a good way down a page, such as in my example. MVC provides no built-in way to append a bookmark to your URL, and because bookmarks are not sent in the HTTP Header (browsers themselves implement the code to "scroll down" to a bookmark), its not possible to do this on the server side, unless I'm missing something fundamental.

Initially I went down the route of trying to modify the output route string on the fly, but I couldn't figure anything out. I'd be interested in if this is possible or not, though I don't imagine it would be easy or recommended.

So, after quite a bit of digging, I've managed to implement a fairly elegant solution (at least on the server side). I created an ActionFilter that modifies the HTTP buffer by adding a small amount of JavaScript just after the tag, after it's been rendered, but before the buffer is sent to the client. The ActionFilter is then applied it to the action method tagged with the HttpPost attribute (or any other action method, though I don't really see a good use for it anywhere else). The end result for the developer, is that you simply need to tag an additional attribute on to any action methods that you'd like to redirect to a bookmark, as follows:

    public class ReviewsController : Controller {

        public ActionResult Read(string titleURL) {
            // Get review by SEO friendly url
            var model = new ReadReviewModel();
            return View();
        }

        [HttpPost, AppendAnchor("commentForm")]
        public ActionResult Read(ReadReviewModel model, string titleURL) {
            if(ModelState.IsValid) {
                // Add user review comment
            }

            return View(model);
        }
    }

The AppendAnchorAttribute and its dependant classes are quite simple. First, the action filter:

using System;
using System.Web.Mvc;

namespace ShadowMoses.Core.Mvc.ActionFilters {
    /// <summary>
    /// Includes a small section of JavaScript in the response buffer that redirects the user to the desired page bookmark.
    /// </summary>
    [AttributeUsage(AttributeTargets.Method, AllowMultiple = false, Inherited = true)]
    public class AppendAnchorAttribute : ActionFilterAttribute {
        private readonly string _bookmark;

        /// <summary>
        /// Includes a small section of JavaScript in the response buffer that redirects the user to the desired page bookmark.
        /// </summary>
        /// <param name="bookmark">The bookmark to redirect to.</param>
        public AppendAnchorAttribute(string bookmark) {
            _bookmark = bookmark;
        }

        public override void OnResultExecuted(ResultExecutedContext filterContext) {
            var r = filterContext.HttpContext.Response;
            r.Filter = new IncludeAnchorScriptFilter(r.Filter) {
                Anchor = _bookmark
            };
            base.OnResultExecuted(filterContext);
        }
    }
}

The OnResultExecuted event stub takes the current HttpResponse (which has been rendered by this point) and applies a custom filter to it. The IncludeAnchorScriptFilter class inherits from Stream and does some simple string manipulation (made simple by a Regular Expression pattern):

using System.IO;
using System.Text;
using System.Text.RegularExpressions;

namespace ShadowMoses.Core.Mvc.ActionFilters {
    internal class IncludeAnchorScriptFilter : Stream {
        private static readonly Regex RegEx = new Regex("(<body([^>]*)>)", RegexOptions.Singleline);

        public string Anchor { get; set; }

        private readonly Stream _filter;

        public IncludeAnchorScriptFilter(Stream originalFilter) {
            _filter = originalFilter;
        }

        #region Overrides of Stream

        public override void Flush() {
            _filter.Flush();
        }

        public override long Seek(long offset, SeekOrigin origin) {
            return _filter.Seek(offset, origin);
        }

        public override void SetLength(long value) {
            _filter.SetLength(value);
        }

        public override void Write(byte[] buffer, int offset, int count) {
            var html = Encoding.UTF8.GetString(buffer, offset, count);
            html = RegEx.Replace(html, string.Concat("$1", string.Format(@"<script type=""text/javascript"">document.location = '#{0}';</script>", Anchor)), 1);

            buffer = Encoding.UTF8.GetBytes(html);

            _filter.Write(buffer, offset, buffer.Length);
        }

        public override bool CanRead {
            get {
                return _filter.CanRead;
            }
        }

        public override bool CanSeek {
            get {
                return _filter.CanSeek;
            }
        }

        public override bool CanWrite {
            get {
                return _filter.CanWrite;
            }
        }

        public override long Length {
            get {
                return _filter.Length;
            }
        }

        public override long Position {
            get {
                return _filter.Position;
            }
            set {
                _filter.Position = value;
            }
        }

        public override int Read(byte[] buffer, int offset, int count) {
            return _filter.Read(buffer, offset, count);
        }

        #endregion
    }
}

The Anchor property is populated when instantiating the filter in AppendAnchorAttribute. The RegEx pattern allows for multi-line tags with as many attributes as you'd like, unless an attribute value contains a right angle bracket. This minor issue can be rectified if you need it to be, but I have no use for that level of regular expression checking.

The Write() method reads the HTML buffer and inserts the specified JavaScript just after the HTML <body> tag in the response. The JavaScript is executed on page load, and simply instructs the browser to redirect itself to the bookmark. There is no HTTP POST or HTTP GET; the browser deals with the redirect on the client side, and because there is no post-back, the JavaScript only executes once.

The resulting URL will be /Reviews/Read/#commentForm, which will scroll down to my related anchor tag in my partial view:

<a id="commentForm"></a>

The "name" attribute of the anchor tag has been depreciated, and the id attribute seems to work just as well. I think you can also use the id attribute of an existing tag (such as <div id="commentForm"></div&gt>), though I haven't tested this yet.

Although I've tested this with Chrome, IE9, FireFox and Opera, I'm a little concerned that older browsers may not have implemented document.location in the same way over the years, and a post-back may occur.

It's not the cleanest solution to a problem I've come up with, but given the circumstances, I think it works quite well. Hope it helps people out :)

Debugging the SSIS Script Component

2011-11-17T14:25:00.001+00:00

I've been doing a lot of work with SSIS recently, and learning a lot on the way. One of my tasks over the last week was to convert each row of a result set in to XML and post it to a web service. The web service only accepted one row at a time, and generating XML in SSIS is not the most intuitive of tasks, so I wrote a script component to do the conversion work.

Anyone who's tried anything more advanced than a "Hello World" example in a script component will be able to tell you that you cannot use breakpoints in them - making it quite difficult to debug even the simplest of code. For a developer such as myself, it's like having an arm removed in a horrific train accident. I had a bit of a task ahead of me - I had to create an XML document with tags containing attributes and values, attributes and child elements, child elements and values, attributes, child elements and values, then self-closing tags with some of the above... whilst not exactly a daunting task, it certainly required a bit of debugging to get right.

MSDN suggests that you should use System.Windows.Forms.MessageBox.Show() to show any debugging information. Aside from the obvious ugliness of this method, it stops the package flow, and if the code is left in the package when deployed, you can lock up the package execution at runtime. Now... I'm not entirely sure why this hadn't came to me before now, but in a flash of super-hero-esq inspiration and inside-out underpants, I remembered a similar issue I had a few years ago...

System.Diagnostics.Debug.BloodyHorrible()

DebugView to the rescue!

I used DebugView extensively when I was developing a SharePoint 2007 site a few years ago. It's a tiny little app from the SysInternals suite (god bless Cogswell and Russinovich) which monitors the Win32 OutputDebugString, which is exactly what System.Diagnostics.Trace.Write writes to by default.

So. here's the main bulk of my Script Component code, along with the very simple tracing code:

    public override void Input0_ProcessInputRow(Input0Buffer Row)

        // For each row, compile XML and submit to the web service

        var sb = new StringBuilder();

        using (var writer = XmlWriter.Create(sb, new XmlWriterSettings()

            Indent = true

}))

            // Write to the XML Writer

            writer.WriteStartElement("request");

            writer.WriteAttributeString("action", "insert");

            writer.WriteAttributeString("database", Variables.MDatabase);

            writer.WriteStartElement("credentials", "");

            writer.WriteAttributeString("server", Variables.Server);

            writer.WriteAttributeString("layout", Variables.ClaimsLayout);

            writer.WriteAttributeString("username", Variables.Username);

            writer.WriteAttributeString("password", Variables.Password);

            writer.WriteEndElement(); // <credentials>

            writer.WriteStartElement("columns");

            // Data columns

            writer.WriteColumn("NCDAffected", Row.NCDAffected);

            writer.WriteColumn("PolicyReference", Row.PolicyReference);

            writer.WriteColumn("Status", Row.Status);

            writer.WriteColumn("TypeOfClaim", Row.TypeOfClaim);

            writer.WriteColumn("FileID", Row.FileID);

            writer.WriteColumn("TotalOutstandingReserves", Row.TotalOutstandingReserves);

            writer.WriteColumn("TotalPaymentsRecoveries", Row.TotalPaymentsRecoveries);

            writer.WriteColumn("ClaimDate", Row.ClaimDate.ToString("dd/MM/yyyy"));

            writer.WriteColumn("NotificationDate", Row.NotificationDate.ToString("dd/MM/yyyy"));

            writer.WriteColumn("TransactionDate", Row.TransactionDate.ToString("dd/MM/yyyy"));

            writer.WriteEndElement(); // <columns>

            writer.WriteEndElement(); // <request>

        //System.Windows.Forms.MessageBox.Show(sb.ToString());

        System.Diagnostics.Trace.Write(sb.ToString());

public static class Extensions

    public static void WriteColumn(this XmlWriter writer, string columnName, object value)

        writer.WriteStartElement("column");

        writer.WriteAttributeString("name", columnName);

        writer.WriteValue(value);

        writer.WriteEndElement();

The bold line is the one we're interested in - it's transparent to the user and won't affect performance if deployed with the application. In fact, it may even aid debugging on a production environment. Of course, it may expose a security hole if used incorrectly, but it does a much neater job of posting debug output that using MessageBox.Show(). It posts the following to DebugView:

System.Diagnostics.Debug.Trace.MuchCleaner()

So, until MS make SSIS Script Components easier to debug, I'll be sticking with this method :)

Tools Round-up

2011-11-09T14:30:00.000+00:00

The development world has been coming up with ways to improve productivity for years. However since Visual Studio 2010 was released, with its fully extensible model, things have seriously changed. Most tools have been modified to extend VS2010's already pretty awesome IDE. With just a few extensions, you can fill in almost all of the gaps the Visual Studio team have left in the VS experience.

I'm not a massive fan of NuGet; I use it, but not for production sites. It's good for quickly implementing a library in a test project, but I just don't like the "bloat" you end up with. I like to know what every single file does and why its there. The same goes for WebMatrix; although handy, I much prefer to manually create projects... I somehow feel like it makes it easier in the long run, even if it takes you a while to set them up in the first place.

I recently purchased ReSharper (after trying trials a number of times over the years and not getting along with it) and I've finally started to appreciate how awesome it actually is. I've already come to rely on a lot of the features - I find myself a bit lost when I use a version of VS without it installed these days!

So, after spending the duration of my career looking at new tools, plug-ins and libraries, I thought it was about time that I posted a round-up list of the ones I've found über useful. I've been using some for over a decade, though most have been superseded with better products. Note that a lot of these aren't Visual Studio specific - they're just good tools that I've found over the years to help me in my work.

RouteDebugger 2.0 - A nice little NuGet package that allows you to debug MVC routes - helps when you're starting out with MVC projects. Written by Phil Haack.

SortSQLFilesInProject - Very useful MSSQLMS plugin that allows you to reorder the .sql files contained in your MSSQLMS project. Not perfect (sometimes you have to reorder and reload the solution a number of times for it to work), but the best solution to get your sql scripts in any kind of order, aside from manually editing the project file.

LINQPad - I was having trouble with one particular LINQ statement I needed to write a few months ago, and I found this little beauty. I must admit, I've not used it too much, but it did the job when I needed it.

Nadger - I've been using this since the 90's. Developed by a guy I used to interact with regularly when I was heavily involved in the Quake II clan scene. Extremely handy, standalone colour picker written in Delphi. The only issue I've had with it is that it doesn't work over multi-monitors, much like many screen based tools.

ScreenHunter - Handy, free screenshot tool. I use it for documentation and user guides (I tend to write a lot as I'm always moving from company to company). It's the best FREE screenshot manager I could find, though I'm sure there are better ones if you're willing to cash up :) As with Nadger, this seems to only work on the main monitor.

Powermenu - Doesn't work so well with Windows 7, but saved me a huge amount of time in XP. Allows you to set any window "On Top". Also allows you to set the window's process priority and transparency. Aero has replaced many of the features, and the new Windows 7 shell disables the context menu, but you can still use the command line interface (I've set up some batch files to execute various commands for me) to set windows "On Top".

Spellchecker - A Visual Studio 2010 extension written by Microsoft that simply applies a spellchecker to any strings and comments within your code files. Even if you can spell well, I would recommend it... because none of us are perfect :) You can download it via Tools -> Extension Manager.

PowerCommands - Adds some handy shell commands to Visual Studio, such as "Open Command Prompt", which starts a VS Command Prompt in the directory you're pointing at in Solution Explorer, Clear Recent Files List, Copy Path, Copy Class and many more... very handy indeed.

NotePad++ - A free text editor, useful for regular expression matching and quick editing. I used to use UltraEdit, until it became slow-ass bloat-ware.

Web Essentials - Adds various editor features, such as CSS colour glyphs, JavaScript regions, outlining, JS minifying and CSS image embedding to the VS editor. In beta, but very handy.

T4 Tookbox - Essential if you're writing a lot of code-generator files. Provides ready built templates for your consumption, which greatly simplifies the process. There is lots of support on this, especially from Oleg Sych, who's blog saved me a considerable amount of time when generating templates.

Process Monitor - I keep forgetting about the Sysinternals suite - every now and then it comes up in conversation, and I remember how awesome it is. I'm a bit of a task manager whore, especially when developing, and the Sysinternals' Process Monitor gives me that little bit extra information when I need it.

T4 Editor - Another useful tool when working with T4 code generation. I had a few issues when installing it on WinXP, but the support is fairly good so I was sorted out quickly. This extension basically enabled highlighting and intellisense in .tt files. It's a bit of a disappointment that Visual Studio doesn't support this by default... though I'm sure it will in the future.

ReSharper - If you decide to spend money on a Visual Studio extension, this is the one to go for. Improves everything from productivity to coding practices. It has more features than I'll ever use, but the ones I do use have already improved my life as a developer tenfold. Highly recommended, even if it can be a bit buggy and laggy at times.

DotPeek - The new (and currently free!) .Net Reflector. I've not needed to use this much yet, but it's handy to have around. It only works with .Net 4.0 unfortunately, but whose still using <= 3.5 eh? Developed by JetBrains (the same developer as ReSharper).

So, there it is... the apps that make my life a lot easier than it could be. I'm sure there are more I use (there are definitely other small, free apps I use for other purposes such as the CCCP, Windows Media Player Classic and IrfanView), but I think that about does it for development-based apps and extensions.

Are there any development apps you use regularly that I've not listed? Or are there any better implementations of the above apps out there?

Getting Visual Studio 2008 to work with Team Foundation Server 2010

2011-05-26T09:52:00.001+01:00

Recently I've been collaborating via Team Foundation Server 2010 - it's my first time, and I'll be honest... it was very gentle. It eased me in with its promise of improved Source Control (improved over Source Safe, that is) and toyed with me, sporting its ideals surrounding automated builds and work item collaboration.

Whilst I've not been directly involved with the set up, using it on a daily basis has been a refreshing change. I'm one of those developers who obsessively refactors code, renames and moves files around until I'm happy everything is in the right place, so when using Source Safe, CVS or SVN, there's quite a bit of repeat work involved when it comes to making sure the source control server is also updated. Suffice to say that TFS 2010 works beautifully in this area, keeping the local, server and project file structure the same as each other.

I've been exclusively working in Visual Studio 2010 for a while now, as I've been creating new systems rather than maintaining old ones. I recently needed to revert back to Visual Studio 2008 in order to create some SSIS packages for some good old ETL. I've not used TFS with any other version of Visual Studio, but I wasn't surprised to find out that VS 2008 doesn't let you connect to TFS (any version) out of the box. There were a few gotchas, but after installing a few updates and patches, I managed to get Visual Studio 2008 working with TFS 2010 so I could store my BI projects in the same source control system as my VS2010 solutions. Although I'm sure there's a distribution / version available with TFS support out of the box, here are the steps I took to get my vanilla Visual Studio 2008 working with TFS 2010:

Install Visual Studio 2008
Install Visual Studio 2008 Team Explorer
Install Visual Studio 2008 Service Pack 1
Install Visual Studio Team System 2008 SP1 Forward Compatibility Update for TFS 2010
Open Visual Studio 2008 and connect to your Team Foundation Server 2010 as you would normally.

I already had VS 2008 installed, but because I haven't used it since my last reinstall, I didn't have SP1. If you install Team Explorer before SP1 (as per my instructions) and try to add a TFS 2010 server, you'll fist receive errors stating that you can't use forward slash characters etc. When I tried to add a server manually, via the below registry key:

HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\9.0\TeamFoundation\Servers

... I just received another error stating that the server was an older version than was compatible with VS2008, which is obviously a load of rubbish. The key here is the "Forward Compatibility Update" listed above. Once I installed that, the UI started to act more like I would have expected it to.

Visual Studio 2010 defaults to an undesired browser when opening MVC projects

2011-05-08T17:48:00.001+01:00

A while ago, I set my default browser in Visual Studio 2010 to Google Chrome by using the "Browse With..." trick, and I've been happy with it for some time. Recently I've found that I've needed to do quite a bit of testing in IE, so I wanted to change it back. I went down the "Browse With..." route again, which seemed to work... but every time I close and re-opened Visual Studio, it reset itself back to Chrome.

I've been living with it for a while, as I tend to do extended sessions anyway, so it's not been too much of a hassle... but today I've needed to close and restart Visual Studio a few times, so it's become a bit annoying. Anyway, to cut a long story short, it seems that when setting the "Browse With..." option in an MVC project, the settings aren't saved to the configuration file, probably because MVC projects don't actually have physical files to be "Browsed", unlike an ASP.Net project. I'm not making excuses for the bug, but its understandable how it's been missed. I've not worked on an ASP.Net project at home for a while, it's all been MVC.

Anyway, for anyone having the same issue as me - all you need to do is create a blank Web Application project, right click one of the generated .aspx files and click "Browse With...", then set the default browser as per normal; voila, your MVC projects will now open with your new default browser setting.

If that's not enough, Scott Hanselman, in his infinite geekiness put together a blog post a while ago outlining a workaround to give you a bit more control over the browser you run your pages in. I don't need this flexibility at the moment, but if you want to put a bit of effort in, its a pretty good solution considering the current options in Visual Studio.

The bug has been reported to Microsoft, and will hopefully be fixed in a future service pack, even though it was reported quite some time ago.