Tag Archives: C#

How Do I Unit Test Database Access?

If as a .Net developer you’re serious about making sure your code is properly tested, one of the biggest problem areas has always been around database code. Even with the more widespread adoption of Object Relational Mapping (ORM) frameworks that abstract some of the complexity of database access, unit testing code that accesses the database is still difficult.

Over the years there have been various strategies to unit test database code that developers have come up with, and at various times I’ve seen projects that use all of them. I’ve also seen examples of large projects where you could see several of these techniques used in different parts of the code base.

The simplest technique – which isn’t really a technique as such – is just to test with the real database. Often this will be a special instance of the database created by the test run into which test data is loaded. The biggest argument against this idea is this isn’t really a unit test and should more correctly be considered an integration test. The biggest problem with this technique is using the real database is pretty slow, and that often leads to compromises to allow the test suite to be run in a reasonable time frame, either with reducing the number of tests, or not starting each test with a clean database configuration. Reducing the tests increases the risk that important conditions may not be properly tested, whilst not cleaning the database can lead to unexpected interactions between different tests. However in situations where you have complex logic in stored procedures in the database, sometimes this is the only way you can test them.

If you are practising Test Driven Development, where you are running unit tests repeatedly, having a unit test suite that takes even just minutes to run is a real problem.

A step on from using the real database is to use an alternative that is faster than your real database, for example an in memory database. This idea has come to more prominence recently as Microsoft has added an in memory database provider to their latest version of their current ORM, Entity Framework Core. However there have been third-party in memory options around for a while such as Effort. In both the official offering and third-party options they are drop in providers that can use the same entity framework code, but just go to an in memory provider instead. Purists will argue that even using an in memory provider this is still really an integration test rather than a unit test, you are merely replacing the dependent database rather than removing it. However to a software developer it can be an attractive option compared to the effort required in stubbing, mocking or faking a full ADO.Net provider. The other criticism of this technique is that because this is a different type of database being used from the live system there is the risk of behavioural differences between that and the real database. Having said that since Microsoft are highlighting testing as a benefit of their new in memory provider hopefully those will be few and far between.

Moving on from using an in memory database, the next option, at least until Entity Framework version 6 came along was to build a fake context object that could be used for testing. I’m not going to go into a detailed explanation of how to do this, as there are a number of good tutorials around, including this one from a Microsoft employee. The basic idea is that you construct a complete fake context object that gets injected into the code being tested instead of the real database context. Although you generally only need to construct the fake database context once, it is comparatively a lot of code, so it is pretty obvious why developers are delighted at the in memory provider included in Entity Framework Core. If you’re not needing to use the full abilities of the context, you do have the option of only partially implementing the fake context. The main criticism of using fakes is that again you’re running the risk of behavioural differences. This time it is because you’re using a different type of context, in particular under the covers you’re using the Microsoft LINQ to Objects classes to talk to the fake object, whereas the real database code will be using LINQ to Entities classes. Put simply whilst the syntax will be the same, you’re not exercising the actual database access code you will be using in the live system. You’re relying on Microsoft LINQ to Objects and Microsoft LINQ to Entities behaving in a similar fashion.

With the arrival of Entity Framework 6, there were some changes made that made it a lot easier to use a mocking framework instead of fake objects. Microsoft have a good guide to testing using a Mocking Framework in their Entity Framework documentation, alongside a revised guide to using a fake object as a test double. The amount of code to fully mock a context is similar to a faked context, but again if you only need to use part of the functionality of the context in your tests you only need mock the parts of the context you need. As with any mocked object it’s important that your mock behaviour is the same as the real object you’re trying to simulate for the tests, and this can be pretty complex with an object like a database context. Particularly problematic areas are around the behaviour of the SaveChanges functionality, where some fairly subtle bugs can creep in with code that passes a test but doesn’t work in production if for example you test by just expecting the SaveChanges method to be called.

That takes us on to a collection of other techniques that are more about isolating the database access code to make it easier to test.

The long standing way to do this is based around the Repository and Unit of Work patterns. There are a variety of ways you can implement these, for example you don’t necessarily need the Unit of Work and could just use the Repository pattern alone. There is a good Microsoft tutorial on the pattern using Entity Framework 5. The basic idea with the repository is to wrap the database code in the repository, and then mock the repository for subsequent tests. The database code in the repository just consists of simple create, read, update and delete (CRUD) functions. Whilst this was a common pattern before Entity Framework, and persisted with early versions of Entity Framework that are difficult to mock or fake, it has largely gone out of fashion. This is not least because the Entity Framework DbSet is an implementation of the same repository pattern so it is totally unnecessary to create an additional implementation of the Repository pattern for mocking now you can just mock or fake DbSet itself.

The other method that has been used for a long while is a traditional data access layer. The actual database code is hidden abstracted behind a series of method calls that take the parameters and return the data which can be easily mocked. Rather than being generic, the code inside each of those methods is for particular queries, and whilst that will be fairly simple database code that can be easily tested, there will be a single function for each query. There are good ways and bad ways of doing this, for example I have seen projects with vast library classes containing all of the queries used by the business logic – a bit of a maintenance nightmare at times. Probably a better design and more in keeping with SOLID principles is to have smaller classes more closely related to how the queries are being used. Either way there is a big overhead with lots of query functions together in a big data access layer.

Data access layers again have started to go out of fashion, however some of the principles behind them can still be applied. The single responsibility principle part of SOLID can be interpreted as suggesting that even if you don’t have a formal data access layer, you shouldn’t be putting database access code in the same method as business logic. The business logic should be working taking and receiving generic collections, rather than retrieving data and working directly on DbSets all in one method. You really shouldn’t have one method that queries data, manipulates it and writes it back. That application of the single responsibility then gives the separation of concerns that can make your code easier to test. The business logic can be tested using simple unit tests rather than having to write complicated unit tests that prime an in memory database or mock, call a function and then examine database contents to see what has happened. The database access methods are again a lot simpler, often just retrieving data, and can easily be supported by a simple mock of the part of the database context being used – a full blown in memory database, or fake or mock context isn’t needed.

In conclusion unit testing code that is accessing a database has always been difficult, and whilst some of the problems have been addressed over the years, it is still not easy. However if you are following good design practices such as DRY and SOLID the occasions when the only way to test something is via a database context should be fairly minimal. If you are finding that you are needing to do that it is well worth looking again at whether you have inadvertently violated the single responsibility principle. Even though the advent of the in memory database makes database context based testing easier, that doesn’t mean you should be using it everywhere. A simple unit test of a loosely coupled method will always be faster than testing a more complex method even using an in memory database. It is well worth considering whether your design would be improved by not coupling your business logic directly to you database access code.

Replace the Replace

I grabbed this screenshot during an online conversation about some of the more bizarre coding people have seen that came about as a result of the ongoing nasty surprises Phil Winstanley (@plip) keeps turning up in his current work.

Replace the ReplaceThis picture however shows some code that used to be in one of our systems, but was swiftly coded out when we found it. It was written by a contractor who amongst other things claimed to have worked with Microsoft writing parts of the .Net Framework.

Looking at this, if you’re a .Net Framework user you’d be forgiven for thinking that he didn’t have anything to do with the string classes, however one of his other regular complaints was how inefficient parts of the .Net Framework were, hence why he needed to spend time rewriting stuff that was already there! We could find no evidence to indicate that this function was any faster than the built in string.Replace, and since having this was a confusion and maintenance overhead it was removed.

Aside from the function being unnecessary there are a couple of other things I wouldn’t do. First up is the use of “” rather than string.Empty – Brad Abrams explains why on this short blog post. Secondly the whole routine uses systems Hungarian notation which is explicitly discouraged by the Microsoft naming conventions – ironic as it was their programmers that created systems Hungarian notation in the first place. Joel Spolsky (@spolsky) has an excellent blog post here that amongst other things discusses Hungarian notation. The third thing, which is more a matter of my personal taste is that there is a single line if statement which I would usually surround with {}, partly to make it clear, and also to help with maintenance – I’ve come across one or two difficult to spot bugs caused by missing braces, so I tend to think it’s better to use them all the time.

DDD8 – Apples, Boots and @blowdart

Major embarass @blowdart session!  #DDD8Today was the eighth annual(ish) gathering of four hundred of the Microsoft Development community for a day of free technical training. Once again it was spectacularly over subscribed – sold out faster than Glastonbury (all the places went within fifteen minutes) and with no Microsoft speakers had the usual mix of sessions, some of which perhaps you wouldn’t expect to see at Microsoft.

The day started off cold – although there was no snow it was definitely a case of scraping ice off the car, and if you were in any doubt, one look at Rachel Hawley’s footwear could tell you! Having said that, as has become traditional at these events, bacon butties to warm you up awaited those who got there early.

First off a couple of observations. For a Microsoft Developer Day, it was a very good advert for Apple! Of the five talks I attended, two were obviously running off Mac’s. One was about iPhone development, so using a Mac was a given, although the presentation was also given using Keynote (and all the more slick for it) and rather than messing around with font sizes as all the PC based presenters have to do Chris Hardy used the built-in OS X zoom gestures to quickly focus in on what he was showing. The other Mac based presentation given by Ian Cooper wasn’t anything related to Mac development at all, but was presented in MacOS X, using the Mac version of Powerpoint, with a windows development environment running in VMWare. It’s not so long ago that developers would buy a Mac, largely ditch MacOS X and stick Windows on it – it does seem that even with the advent of Windows 7 that isn’t always the case now… The other massive advert for Apple was not surprisingly the vast numbers of iPhones in evidence. I certainly think it would have been worth somebody doing the same as Scoble did at Le Web to get a ball park figure of how many there were. There were a good few Google Android phones around, but few if any Microsoft based phones in evidence. This was also reflected in the sessions – no talks on Windows Mobile development, but there was a talk on using MonoTouch to develop iPhone applications!

As is normal for these days, what I thought I would attend, and what I actually attended were slightly different. I initially thought I’d just take up residence in Chicago 1 for the day, but in the end I fancied a change of pace.

First up I attended a talk by Ian Cooper on Real World MVC Architectures. This in part was because I’ve just done my first ASP.Net MVC project, and I was half expecting to find I’d done it all wrong, as to a large extent I’ve put it together as felt right rather than following any explicit paradigm. To my relief it seems all the talk of proper architecture seems to be sinking in, and the way I’ve constructed it is pretty much as was suggested, even to the point that I’ve used particular techniques without having read about them as yet in my MVC book in that I understood why they were being used but didn’t recognise the idea by name! I suspect the session might have been pitched a bit too much towards the beginner end of things for experienced MVC programmers, but for me it was certainly a good reinforcement of the techniques.

Next I slipped next door for a change of gear, and a non-technical talk by Liam Westley who was talking about how to be a small software development outfit and not go bust. To be honest, the principles Liam outlined can apply equally well to large software houses, a number of which I’ve come across who don’t get this stuff right, and even to people in a corporate environment like me as getting these sorts of things wrong will at the very least have your internal customers looking elsewhere for their software, or at the worst put you out of a job. Liam gave us a set of broad principles that any software developer should be doing as a matter of course – things like delivering properly tested software, applying proper logging (even in a corporate environment fixing a problem before the users have got round to reporting it scores serious brownie points), and understanding your users, all go to making people happy to give you their software work, and not go elsewhere.

For session number three it was a first for me, in that it was the first time that I have heard Jon Skeet speak. His name will be familiar to anyone who frequents Stack Overflow – and as his reputation is testament to he sometimes seems to answer C# questions within seconds of them being asked. What is slightly more surprising is that his day job is at Google as a Java developer. Even more surprising he fits all of that in with being a Methodist Local Preacher too – but I suspect that stands him in good stead for being able to deliver material well, as from the experience today his reputation is well deserved. The latest version of C# brings in some interesting, but quite complex new ideas, and he did manage to put them over in a way that even with the early start on a Saturday I pretty well followed them. Having said that whilst I liked the presentation, and many of the new features, I was less than impressed by the return of the ubiquitous VB variant data type, in the guise of the dynamic type. Whilst I am well aware that the way the variant and the dynamic work are rather different, it’s much more about how it will end up being used, or more likely abused. I’m with Jon Skeet on this in that I much prefer a situation where the types can be validated at compile time. Whilst there are legitimate reasons for adding dynamic, and as an exercise in language design the implementation is very impressive, as with the variant I am quite sure it will end up being thoroughly misused, and will lead to many a difficult to nail down bug.

Next up was lunch, and was the traditional scramble for a lunch bag. Unfortunately it seems that the entire occupants of the Chicago 1 side went the same way and got all the non veggie and non seafood sandwiches (I have to watch having too much of certain types of seafood with my gout) and as always it was a bit of a lucky dip as to what else you got, so I ended up with a sandwich, crisps and an apple that I wanted, and a can of diet coke and a snickers bar that I didn’t want. I know they’ve tried various things over the years, but I still think there has to be a better way than this, as it was pretty obvious looking around that not everybody wanted what was in their lunch and there was a lot going to waste.

The lunch time Grok Talks had relocated this year, and were in the atrium in building four. This certainly gave a bit more space, but did seem to make the security guards mighty jumpy – I got a stern “I’ve just seen you behaving strangely” from one for taking this picture – I just liked the look of the clear blue against the white of the building structure and was going to make some comment about the weather! The Grok Talks were marred rather by problems with the technology. For a start the speakers were badly positioned in relation to where the presenters were standing leading to endless feedback problems. The talks also took an absolute age to get started, and when they did people seemed to overrun, which as a result led to people who were further down the running order being disappointed. There were a couple of interesting talks though, and it was especially interesting watching Gary Short intensely watching somebody else demonstrate Code Rush! Looking at the response hopefully there will be a few more converts from Resharper, a jump I made many years ago!

After lunch was one of my personal interest talks. As an iPhone owner and software engineer I’ve always quite fancied giving an bit of iPhone development a go. The problem is that as well as learning a new platform and new environment, developing for the iPhone requires learning a new language, Objective-C. However Chris Hardy was demonstrating a way that I could leverage my existing C# skills using the Mono environment and an add on to it called MonoTouch. Whilst developers still need to be able to read Objective-C to understand what is going on, and still need to learn their way around the Apple API’s, it allows them to develop entirely in familiar C#, and even brings advantages in terms of some of the extra type safety that C# brings. I have to say I was pretty impressed at the environment and what it can do. I was less impressed by the price – $399 for a personal license, which only covers you for a year of updates, with even more for a corporate license – far too much for your average hobbyist programmer to even consider. I can’t help thinking that they are missing a trick here, and providing a low cost or free license for developers in return for a share of the revenues, maybe using some sort of phone home code to keep track would certainly broaden the base of programmers using it.

My last session of the day, to be honest I would have gone to even if Barry was just reading the phone book, as this was potentially his last appearance at a Developer Day before he loses the essential qualification for being allowed to speak of not working for Microsoft, as in a scant few days he will be starting a new job working for Microsoft at one of their offices in Redmond. As always there was the classic banter with people he knew in the audience, in particular Jon Skeet who was attempting to pose increasingly difficult questions it seemed. Barry also started off by hijacking the session next door as Ben Hall, the speaker had a birthday and was foolish enough to tell somebody! What I was also expecting, and got in spades were interruptions marking his departure from the UK development scene. His book Beginning ASP.NET Security featured in several. In the first Liam Westley gave a touching and heartfelt tribute, and said how much he had been looking forward to the arrival of the book – as it was just the right size to prop up his wobbly table. In another they spoofed the winter cold adverts, suggesting that the book was good fuel to keep the elderly warm. The session finished off with a clip from his appearance many years ago on The Crystal Maze, and several of the organising team appearing in T-shirts especially prepared for the occasion. All in all it was a memorable way to finish off the day, and hopefully a memorable occasion for Barry as he heads across the Atlantic. The one question that remains is whether all the spelling mistakes in the presentation were down to Barry, or whether somebody did get at his presentation before he went on…

All in all it was an excellent day, and although I know there were a couple of sessions that had problems, the ones I attended were all excellent, and well worth the spare time given up. It was great to catch up with friends from the community, previous developer days and previous jobs. Whilst it does appear that the day is very much a victim of it’s own success (even with local developer days around the country people still travel from far and wide to attend this one in addition to their local days) hopefully a way can be found to allow it to keep running in future years, and all credit to the organising team, and the staff at Microsoft for keeping the whole day running smoothly.

Ensuring WCF Correctly Reports Errors

This is another one of my note to my future self posts, that might be useful to somebody else, so skip past if you don’t know what WCF is…

Anyway, if you’re still here, I’ve spent the past day or so trying to track down a problem in some WCF code. Essentially the problem has been that whilst I have been out of the office over the last week, we’ve had a change propagate through to our development server which has caused problems with some of our existing services, specifically some code where one service needs to make a call to another service running on the same machine to finish it’s work. To do this it needs to pass through the Kerberos ticket that the initial service has received, and whilst up to now it has been quite happily doing this, now it has stopped and instead is getting the credentials for the underlying windows service passed.

The problem was made a lot harder to diagnose by a little WCF gotcha whereby the error that is generated is overwritten when the calling code tries to dispose of the service object. Damien McGivern has an excellent post describing the problem and giving a solution, however it didn’t quite meet our needs, as we sometimes need to specify an endpoint when creating the proxy object.

To get around the problem, I adapted Damien’s code slightly creating an extension method taking an object of type TService rather than creating the object within the method, so the method can be used as follows:

new RelationshipServiceClient().UsingService(service => ... );

Whilst it doesn’t actually solve the mystery of why our server started mishandling WCF calls, it did at least give us a bit more clue!

A Request

If anybody is writing .Net code that in the future I am going to have to find an error in, can they refrain from doing things like this:

catch (Exception e)
throw new ApplicationException("ConnectionException has occured: " + e.Message);

The particular application I was working on this morning used this particular gem pretty well everywhere. The problem was that we were getting an unhandled ApplicationException – the re-thrown error wasn’t being trapped anywhere else – and since the above bit of code throws away the stack trace from the original exception, we were left trying to guess where the error was actually occurring.

The irony is that the code was written by a contractor who apparently had a pretty stellar CV, and amongst other things quite regularly criticised other people on the team over their apparent bad programming. His contract wasn’t renewed, and since then we’ve been discovering quite how bad his programming really is – a definite lesson that however good a contractor appears on paper, you need to properly monitor what they are doing.

Other gems in this particular application include every SQL statement being built by concatenating strings together – and no, not a StringBuilder in sight. Using ToString() to do the conversions where needed, including quite a few points where he calls ToString() on a string – indeed a large amount of the data is just held as strings anyway, only being converted back to integers or dates in the stored procedures when SQLServer needs the right types. He had also written his own replace function, which he was using instead of String.Replace although it was functionally identical, and that wasn’t the only place he’d hand crafted code that replicated functionality in the framework. The code was littered with other common errors, for example it’s a good job we don’t have anybody with the surname O’Reilly…

Since we were fixing a specific problem, much as I would like to, there wasn’t the opportunity to fix all of these other problems, essentially we have to wait until something else breaks, and fix it then. And the source of the problem today? A stored procedure that was trying to convert one of the multitude of strings being passed through back to an integer in order to use it. That conversion was failing and throwing an error that was passed back to the client and helpfully chucked away by the error handling code I started with – the only clue we had as to where it was going wrong was that the error message seemed like it was coming from SQLServer.

Making the Change

So I’ve finally been and gone done it – I’ve resigned from Scottish and Southern Energy!

Rather than some long term big processes to move jobs, it’s actually come about initially quite by chance. One of the agents who put me forward for a couple of interviews before I got the job at SSE included me on a general e-mail circular last Tuesday asking for help filling a role. Having re-written my CV a few days before I sent an e-mail back saying that I couldn’t help with the particular role he was looking to fill, but that I’d just updated my CV, and added pretty much what I put in the posting – that I would be interested in any roles that cut my commute.

Anyway, the next morning, when I turned on my phone, I had a message from one of his colleagues, who was looking to fill a role just down the road in Wokingham. As with most roles, the skills match wasn’t exact in that I had no relevant C++, and I had C# for my .Net rather than VB.Net. However talking with the agent I said that I was happy to cross-train for a more local role, and also highlighted that I had cross-trained a number of times before, and also that using VB.Net as opposed to C# was not a particular jump as the underlying .Net framework is shared between the languages. As a result she said she was happy to put forward my CV, as long as I provided a covering letter explaining my willingness to cross-train, and why I believed that my skills were relevant. I have to say that I wasn’t massively confident, as I expected that there would be plenty of people with a better skills match.

However it was with some surprise when within an hour or so of the agent passing on my CV and covering letter, she called back to say that her clients were interested, and then a few hours later asking if I could come for interview on either Friday afternoon, or Monday, as one of the people who would want to talk to me was going to be out for the rest of the week. I opted for Monday morning, and booked the day off work.

So yesterday I went down into Wokingham for my interview. The first part was a general chat with one of the directors of the company, which apparently lasted a lot longer than with previous candidates. Then I moved on to a more technical interview with one of the senior technical staff, and finally a technical test.

The technical test is usually one of the most unpredictable parts of any software development interview process. What seems to happen a lot (and I admit I’ve been guilty of putting candidates through one or two of these) is what is essentially a quiz about key parts of whatever language the role requires. Mostly this is covering basic, fundamental concepts of the language, so for a VB 6 developer I’d often ask a candidate to explain about ‘Option Explicit‘, and the use of Variants. However it all to easily turns in to some sort of trivia test, indeed I still have a copy of a thirty question nightmare used by one company that I got sent at one point. The basic issue is that it doesn’t test whether somebody can actually program. Hi-Q used to supplement the interview with aptitude and then a psychometric test, which seemed to work quite well, in fact the DISC profile they did for me proved frighteningly accurate!

Anyway, the test yesterday was a straight practical test of my programming ability. Essentially I was given a choice of standard programming environments, including VB 6, Visual Studio 2003, and even Excel. I was then given the task, a limited time, and left to get on with it. The task was one of those classic programming exercises that you tend to get in any programming course – something that seems pretty simple on the surface, but has a lot of subtleties. I chose to do it in C#, and to do what was basically a quick and dirty solution, and then gradually refine the solution, commenting up the code in the places where I knew that it needed work, or where I was aware that it was inefficient. For example I made use of a Hashtable, but stored integers in the table, which would result in a large number of boxing and unboxing operations. It certainly seemed to be the right technique, as when the technical interviewer returned he seemed pleased, and actually commented that a significant number of people who had taken the test, hadn’t got something that worked – even incorrectly -by the end of the time.

After the technical test, I was then called back in to see the director I had met initially, who made me an offer!

All in all, I was quite surprised. From a point less than a week ago when I thought it was a bit of a long shot, I’d actually found a nice role at a small but growing company, that could make good use of my skills. On top of that, rather than being forty-four miles from home, it was barely four miles away, wiping almost eighty miles off my daily driving – that works out as about four hundred less miles per week, a massive difference. Certainly whilst I’m looking forward to the new challenges of the role, I’m also looking forward to not having to spend more than two hours every day driving the back roads or motorways of Hampshire. My last day at SSE is 24th February, and whilst I’ll be sad to leave the friends I’ve made there over the past few years, I’m really looking forward to getting started with my new job on the 27th.

It’s worth saying that, as before, since this is a personal blog, I specifically won’t be blogging about anything directly work related, hence why I have been careful not to mention the company by name, or give many details. Also, as I’ve now got a new job, I think it’s probably sensible to remove my CV from the site too.