We have an existing web application that uses a mysql database. Being a fairly mature application in some parts, it uses ADO.NET directly for the data access, so you’ve got hand-crafted SQL being fired against the database.

We wanted to bring this more upto-date, and have a lightweight DAL that doesn’t stomp all over the existing stuff (so ideally complemenets it rather than entirely replace it). From personal experience of the last few years using Linq to SQL and Entity Framework, I wanted something that didn’t rely on Linq for the query language. I love Linq and it’s a joy to use, AND is a simple way to execute simple quueries against the database quickly. But when you start getting serious with your queries - even when you just throw a few LEFT JOINS in there, then you start to suffer - how many Stack Overflow posts are there in the vein of “How to write this SQL in Linq..”.. So, because of this and the experience built up firing SQL using an IDbConnection object, it looked like an ideal bunch of requirements for a Micro-ORM like Dapper or Massive. I discounted Massive because it maps to dynamic objects, and we already had a bunch of mostly-POCO objects that we wanted to map to, so enter Dapper!

Dapper is one of those little projects that makes you smile whwen you use it (or is that just me?). You get 2 extension methods on top of an IDbConnection, the main one being .Query<> that allows you to fire sql against the db and get the results into an object of your choising. As a noddy example:

var products = conn.Query<Product>("Select * from products where DateCreated = @DateCreated",new {DateCreated = date});

It’s pretty obvious what this line is going to do, the nice thing is that when you run it you get the results automatically mapped into a List of products. Nice.

The source for the Dapper class is a single file you can throw into your project. The code uses a reasonable amount of reflection internally to do it’s magic, and more than a sprinkiling of direct IL emmitting, which frankly scares me and impresses me in equal measures :)

Column Mapping

One thing that immediately became an issue for us was how dapper maps columns in the database to properties on the result object. By default it looks for matches, so a field productid in the database maps to a property PropertyId in the class. But for us, this straight mapping did not exist. For our database, the column names sometimes needed entirely remapping (e.g db.name –> Class.Title), or for the most part we just needed to resolve underscores in the database (e.g. db.product_id -> Class.ProductId). Fortunately, Dapper is pretty flexible, and it gets round this using an ITypeMap interface. You can implemeent this class and then hook this into Dapper. For us, we used a simple combination of allowing a class property to have an attribute to define the column mapping, and failiing the presence of that, we handle the removal of the underscores manually. If anyone is looking to do this, I’d recommend taking a look at the CustomPropertyTypeMap class in Dapper. That class allows you to define your own rules and then you can plug this into Dapper using SqlMapper.SetTypeMap (it’s explaned well in this Stack Overflow post)

Wrapping Dapper

Because dapper is a simple class that provides minimal ORM functionality, theres a lot that you don’t get, that you might want. For example, here’s some simple EF/L2S code that shows a common use case:

var o = context.Produts.First(p => p.ProductId == 1); // Get a product from the db
o.Name = "Updated"; // Update something
context.SaveChanges(); // Tell the context to update the database with the change to the product.

Now, you can’t do that in Dapper. It’s not a fully fledged ORM like that. You’d have to do something like:

var o = connection.Query<Product>("Select * from Prodcuts where Id = 1").First();
o.Name = "Updated";
oconnection.Execute("Update Procuct set Name = @Name where Id = 1",new {Name=o.Name});

In fact, that code is pointless, because the first 2 lines serve no purpose. But you get the point. Fourtunately, Dapper is designed to be used as a building block to allow you to create the kind of functionality you need. I didn’t want or need full blown change tracking or anything like that that EF just does for you. But I did want to be able to send updates/inserts to the DB without hand-crafting SQL all over the place. I also wanted a class that was a little bit more like the Context class that EF gives you, so that I could access all my db tables from them. This is where Dapper Rainbows came in for me…

Dapper Rainbows was written by Sam Saffron to wrap Dapper how he wanted to wrap it. He’s written a great blog post about it and how it works with Dapper here. Again, it’s a one-file drop in that works with Dapper. It gives you a nice wrapper that looks like this:

public class MattsDatabase : Database<MattsDatabase>
{
    [Table(Name = "tblproducts")]
    public Table<Product> Products { get; set; }
}

So, using this, I can now rewrite that product update to something that looks like this:

var o = myDatabase.Products.Query("Select * from Products where Id = 1");
o.Name = "Updated";
myDatabase.Products.Update(o);

Much nicer IMHO. This simple wrapper is taking care of a lot of the mundanity of building up the update SQL. It wasn’t as simple as just using the Dapper.Rainbows file however - as Sam’s blog post says, it’s very opinionated - for example it assumes that all PKs are int and called “id”, and that you’re using SQL Server. None of these were true for us, we have mysql and some funky rules about where the ID values come from. So we created a heavily modified version of the rainbows file that works for our needs. We’ve got all our db rules nicely encapsulated in there and that’s quite nice.

DAL-tastic

The only missing piece of the picture was making sure that the DAL logic was nicely encapsulated in it’s own “layer” - After discussion we decided that we don’t want the dapper class to be referenced directly from the MVC controllers because then we’ve got a dependency on the database that is going to make testing difficult (We did toy with the idea of just going with that and having a test db, and might actaully come back to that, but for now getting a test db set up is tricky for us). We had 2 options here:

1) Make our Dapper Rainbows wrapper class implement an interface (IAppDatabase or similar), and work against that interface in the controller. If we did this, then the tests could get a bit unwieldly when we’re mocking out the databse.

2) Create a simple DAL class. For example a ProductsService DAL class, that implements IProductsService, that has methods like “GetProduct, Update, Insert” and the implementation of this uses the dapper wrapper. We went with #2. Yes, I know, it does look rather like a repository pattern doesn’t it :) And yes, I accept that it’s adding a lot of DAL noise to the projcet. It’s something we’re reviewing as we go along, and we might very well channge our mind as we use this.

Well, my blogging has been a bit rare of late. I’ve had lots of ideas about things to post about, but nothing has happened! So, now that I’m starting a new job soon, I’m going to try and get back on the blogging bandwagon and share my development journey here :)

New job

I’ll soon be starting a new job at mediaburst :) I’m very excited about this, it’s a partly remote role, and I’m really looking forwards to getting stuck in.

Agile

One thing mediaburst were keen to talk to me about during my interview (apart from my favourite beer), was agile, and how that could work when most of the developers are partly remote. It’s a very good question, and it will be interesting to see how it pans out. In a previous job, I’ve been lucky to work with the awesome IT consultancy ThoughtWorks. We handed over a piece of functionality to their Chinese dev team, and of course with it being ThoughtWorks that was handled with an agile process. It actually worked really well - we used a group IM app like Campfire for the daily standup (which was just as effective as a real standup, the only downside being it lasted longer), and we used Mingle (a web based agile management tool from ThoughtWorks) to manage the stories, estimate, and break the backlog down into sprints. I’d say the hardest thing was discussing technically how something was going to be implemented, and making sure that the BA/QA team in China had as good an understanding as we had of the story. But we managed that pretty well with emails and conference calls when needed so, overall, it was a success.

I think one contributory factor in its success was the experience of the entire team with agile (especially the TW guys) - so when faced with an issue of any sort we all pretty much knew how to resolve the issue.

If the team is pretty new to the whole agile process, then it’s obviously going to be a little harder to grok if the team is partly remote. But, if you can get the team together say one day a week, then that should be enough to tackle any problems - especially if that day is synced so that the whole team is in that day and the focus is on planning the next sprint.

Theres some related and interesting insights from this stack exchange question - in which most people report success. And of course with companies like 37Signals embracing remote working and an agile process, it shows what can be achieved.

Anyway, enough about that. It should be interesting :)

Se being a good .net developer, I use an ORM, like everyone else, right ;) Okay, so that’s not entirely true, I’ve seen some pretty old fashioned dataset-based data layers still doing the rounds, and of course some people seem to have decided that an ORM is the worst thing since global variables - hell it’s even an anti-pattern to some people.

Anyway, for a web application I started 2 years or so ago, I needed to create some sort of medium sized enterprise web application with an architecture that could take the current requirements and not be nightmare to expand in the future. For various reasons that made sense at the time, we decided to stick with Linq to sql, but with POCO entities, so kind of similar to EF code-first.

The simplest thing that could work

At the time, the repository pattern was a very popular approach - lots of sample apps were using it and most people were writing tutorials about the benefits of using it. So, to me it seemed a no brainer. To keep things simple, I decided that I would avoid a generic repository pattern, and just implement a simple repository class for each “major thing” that I could concieve of in the domain. I wanted my repository to offer me these things:

• A seperation of the data access code that I could use with my DI container to inject into my controllers. In short, unit testing was my main concern.
• To seperate the data access into something like the aggregate roots of Domain Driven Design (DDD).

And conversely, I wasn’t bothered at all about these things

• Some vague notion of swapping out the data layer for ORM x, or database Y. People don’t do this generally.
• Some sort of querying / specification pattern. I was happy to return IQueryables from my repo, and then filter them in the controller classes or wherever directly.

So my repository arhitecture was very simple. I’d have for example a UserRepository, which would contain various methods for dealing with users… Get, Find, Add, Delete, that kind of thing. I won’t bother to post code examples, but it was all fairly straight-forwards.

Experiences

So, I learnt a lot with this web application project…

Firstly, Linq to SQL was a bad choice, the technology was immature and never really done well when you want to come at it code-first with your own POCO obejct.. We are planning to swap in EF :)

Testing

Repositories do/can IMO make unit testing much easier - you can mock out repository interfaces and test your controllers to your hearts content, although often the overhead in populating the mocked / fake repositories can be time-consuming.

BUT… Mocking out your repositories makes your controllers much more testable, but likely leads to you not testing the repository layer. I found quite often that all my tests would pass but when running I’d get errors. If my controller action contained some linq code to filter data fetched from a repo, my test passed because the object-LINQ is different from linq to sql, which actually evaluates to SQL… You can find out more about that in this SO post.

Needlessly wrapping the ORM and making it useless?

Repositories risk you needlessly encapsuating your mature and powerful ORM framework to the point that you can’t benefit from it. This is a big deal. Ayende talks about this in this post (he makes some excellent observations about the pitfalls of the repository pattern and I agree with pretty much all of them). So my wrapping your ORM in a repository, you risk hiding away the power of the ORM, and then end up adding stuff to your repository just to be able to make use of it. After all, DataContext - that’s a Unit of work right there, why you wanna code your own?

If I had to it all again..

I wouldn’t use a repository pattern. That’s not to say I’m totally against them and think they’re a waste of time, but I think carefull consideration is needed reagrding WHY you’re using one, what you’ll gain, and the trade-offs you’ll encounter.

For the same project, I’d strip things right back and use the ORM right there in the controller. “OMG WHAT ABOUT TESTING” I hear you scream. Well, one thing I found for this particular project was that it took a lot of effort to setup my fakes/mocks to provide the right test data so I could meaningfully test things. What I’d do instead is create a test database that was populated for the tests - ideally an in-memory database with SQLServerCe, which wouldn’t be too slow. Think about it…

• No repositories to worry about
• Full power of ORM available where you need it
• The tests would test the ORM (db) logic too, and remove the need for seperate db tests
• Loads less code setting up mocks / stubs - just work off the test database.

In some situations I might find that I really don’t want the database getting in the way - I could perhaps solve this by creating an interface wrapper for the DbSet / whatever, depending on the ORM. I’m not 100% sure that not using a repository would be the right move, but I think I’d end up with much simpler (and less) code, and I’d be more productive.

If for whatever reason I decided to stick with a repository, I’d definately use a generic repository, and I’d pay closer attention to my domain, and make sure that my repositories mapped to my aggregate roots well, and wasn’t just a data access object, which seems to be an easy trap to fall into.

Obviously, as with everything, it depends. It depends on the project, how big it is, the complexity, the team working on it, the user-load, and all sorts of other factors. But if anyone is interested, I’d recommend checking out Ayende’s blog and looking at the back-log of repository stuff and general architecture simplification - it’s very interesting and hard to argue against.

Hope this was useful to someone ;)

Deployment. Don’t you just love it. Well, we don’t, which is why I needed to build something to make our asp.net mvc web application deployable, from a single click ideally. This post is about that, and what I did, and how it all works. A lot of what I did here was helped by a mammouth series of blog posts by Troy Hunt, which you can find here. He goes into a lot of detail about setting it up, so if you’re looking for a step-by-step guide, I’d recommend you head over that way. This post is more a summary explanation of what we did and how it works for us.

What we had

I’d already set up a continous build server using the fantastic TeamCity, so we had that as a starting point. I’d previously created a NANT script that did the build, ran all the tests, and this was all plumbed nicely into TeamCity and was monitoring our subversion repository and running the builds. If you don’t have a build server set up, then I seriously can’t recommend enough that you check out TeamCity. It’s free with some limitations (number of projects), but it’s awesome, very easy to install and setup, and you’ll be amazed you managed without it when you get it going.

So, as well as this, I’d also set-up Web Deploy which we were currently using for web-based deployments. More about this next.

MSDeploy / Web Deploy

MSDeploy, is Microsoft’s attempts to make web-deployment easier. It’s actually “Web Deploy”, MSDeploy is the name of the command line tool you get to work with it. You can find out more about it from here, but in a nutshell it has a number of nifty powers, such as the ability to perform transforms as it deploys, detect missing dependencies, and the ability to only send files that have changed. The downside of MSDeply IMO is that it just doesn’t seem to get enough coverage on the internet, and as such as quite badly documented.

Setting up Web Deploy

The first thing I did was to grab Web Deply (v2), from here. I installed that locally on my dev machine (which actualy doubles as the build server… for now), and also did the same on the production web server itself. When installed on the server, we only needed to install the remote agent service - more on that here.

Ok, so once all installed, I can then deploy the web app from Visual Studio. Before I do that, one thing I want to share is this useful nuget:

So here, you can tell the web application that when you deploy it, you’re also going to need to deploy additional assemblies for, for example “ASP.NET MVC”. Very useful indeed. So, now I can manually deploy using web deploy, by right-clicking on the web project, and choosing “Publish..”.

I won’t dwell too much on this, but I’m publishing it to MyDomain.com, to the IIS website called “MonitoringAppTest”, with the user account on the server of Administrator. I should be able to use a different account here, but didn’t get that to work… I posted on SO and someone posted a great reply to assist - if you’re worried about using the Admin account on the web server (and you have good reason to be) then check the SO post out here

Plumbing it into Nant

So, we have the ability to deploy using web deploy. That’s great, but what I was after was a single button deploy I could trigger from the TeamCity. This turned out to be pretty simple, once I had worked out how to get Web Deploy to work over the command line (and turn that into a nant script). My resulting nant script looks like this:

First, to “package up” the web app:

<exec program="${MSBuildPath}">
  <arg line='"MonitoringApp/MonitoringApp.csproj"' />
  <arg line="/property:Configuration=${SolutionConfiguration}" />
  <arg value="/T:Package" />
  <arg value="/verbosity:quiet" />
  <arg value="/nologo" />
</exec>

Troy hunt goes into detail about what the package contains if you’re interested. Once packaged, I need to fire it to the web server:

 <exec program="MonitoringApp/obj/${SolutionConfiguration}/Package/MonitoringApp.deploy.cmd">
  <arg value="/Y" />
  <arg value="/M:https://MyDomain.com:8172/msdeploy.axd" />
  <arg value="-allowUntrusted" />
  <arg value="/U:Administrator" />
  <arg value="/P:NotTellingYou" />
  <arg value="/A:Basic" />
  <arg value="-enableRule:DoNotDeleteRule" />
</exec>

So, this command runs the “MonitoringApp.deploy.cmd” script that actually exists when you build the package. The arguments tell it where to publish, and also I set an additional rule (DoNotDeleteRule) that tells it not to delete anything already on the destination that wasn’t deployed by web deploy (e.g. user uploads).

Almost done. You see, the script doesn’t know which IIS site to target (e.g. MonitoringAppTest in my screenshot above), and I have a lot of them. It turns out that the package that is generated contains a “.SetParameters.xml” file, that contains additional parameters for the deployment. I needed to set name to my IIS web site name…

<xmlpoke file="MonitoringApp/obj/${SolutionConfiguration}/Package/MonitoringApp.SetParameters.xml"
     xpath="parameters/setParameter[@name = 'IIS Web Application Name']/@value"
     value="${deploy.iisWebSite}" />

Nant to the rescue! So I “poke” the IIS web application name parameter, using the property $deploy.iisWebSite (kind of like a variable in a nant script). I’ll come back to where that gets set in the next section…

Plumbing it into TeamCity

This is really simple. First, I did all the simple stuff - created a new project, set the SVN repo, told it to ttrigger a NANT scipt target, and set the build trigger to manual - I only want to deploy when I say so!

Once all this is done, I needed some way to tell TeamCity which IIS site to deploy to. To do this, I added a bunch of command line parameters to my nant build step - like -D:deploy.iisWebSite=%env.iisWebSite% That tells TC to call nant and set the property “deploy.iisWebSite” to %env.iisWebSite%. Next, I go to the “Build Parameters” section in TeamCity, and set env.iisWebSite by adding an Environment variable with a value “MonitoringAppTest”. All done!

The beauty of this setup is that if I want to deploy to another site on the same box, I just need to copy the old deployment project in TC to a new one, and just change the build parameter to whatever… So simple!

Summary

There it is. Now I can see how many changes are waiting to be deployed from TC by looking at the “pending” dropdown, and when ready I hit “Run”, and off we go. I missed a few bits out from this, such as how we store the packages as artifacts in TeamCity, but I hope this shows how productive using Web Deploy, TeamCity, and a bit of NANT is to automate the otherwise dull process of deployment.

I’ll also talk about how we manage SQL updates automatically in another post.

I reckon I’ve been coding bits of JavaScript pushing on 14 years. In all that time I’ve gotten better at it, but was guilty of never really caring about it until about 4 years ago. At first it was a necessary evil (I even used vbscript circa 1999 for some sites because I thought it was easier!), then it became just something I did without thinking too much about the code I was writing. But as it grew and grew, and more impressive and elaborate libraries started appearing, I realised I should pay more attention.

Just use functions?!

So, for the last couple of years, I’ve been using things like jQuery and other JavaScript libs quite a lot, and whenever I look at the source code for these libs, I’m finding all sorts of crazy stuff that actually looks quite terse and readible. I’ve also been looking back at my code with a more critical eye. I mean, what’s wrong with

var x,y;

function doSomething() {
	// Blah blah blah
	x = 10;
}

function doSomethingElse() {
	// Blah blah...
	y = 10;
}

Oh. Turns out that plenty is wrong with that, actually.

Fast-forward to 2010 and I started at a new company, with the main responsibility of making a responsive real time web application. Obvously if it’s real-time it’s going to be big with the javascript and ajax, right? We need instant updates in the web app so we need to plumb in all sorts of good stuff to make that happen. All this good stuff that needs lots and lots of javascript code.

Time to learn me some JavaScript coding patterns

So, one book I’d seen referenced a lot on stackoverflow and mentioned in blogs was JavaScript - the good parts, by Douglas Crockford.

I can’t recommend this book enough. It’s pretty obvious that web applications are getting more complex with richer features now. Look at Gmail or Trello, these web applications rely on JavaScript to make all the good stuff happen. A good grounding in JavaScript is essential for a web developer, especially when you start to make use of JavaScript libraries like BackBone.js to manage your code. Sure, you can probably get away without this grounding, and you can use things like coffeescript to help you hide from the javascript, but you’re adding stuff ontop of a JavaScript foundation, and if you don’t know how that foundation works, you’re going to suffer. Let me talk a little more about what is wrong with the above code snippet..

this. And that.

The syntax function nameOfFunction() {...} is shorthand for var nameOfFunction = function() {...}. The standard seems to be to use the latter, it makes it more obvious that the function is an object.

First of all, those functions are scoped to the global object. Functions are objects just like everything else in JavaScript, and they havw a scope. If you define functions like that well then you’re defining then at the global scope level. That’s evil. You could in-advertadly create global methods or variables that clash with other sub-components or libraries you’re using, creating all sorts of bugs.

Every function you create in JavaScript also recieves 2 additional parameters: this and arguments. The value of this depends on the way you invoke the method. Now, in this global context, this is set to the global object, which isn’t much use.

Going back to the example code, perhaps the easiest way to fix that code is to place it inside an object literal, which is very simple:

var pageHandlers = {
	
	x: 1,
	y: 1,
	
	doSomething: function() {
		// Do something
		this.x = 2;
	},

	doSomethingElse: function() {
		// Do something
	},

}

So now all that gloabl stuff has been moved into pageHandlers. You can call PageHandlers.doSomething() for example. This moves the functions and the variables x & y out of the global scope, and into the pageHandlers object. Since this is an object, this is now scoped to the object, which is why you can safely call this.x inside that code.

One thing I wanted to know from this book was how to do the equivilent of classes in JavaScript, I had my C# head on and wanted to do the same thing in JavaScript. Oops. Got that wrong too. JavaScript is a prototypal language. There is no such thing as classes, it’s class-free. Objects inherit from other objects, not from classes.

What I was looking for in my code, was the module pattern, which I’ll quickly try to explain.

Module Pattern

The problem with the code now is that although it’s encapsulated inside an object, there is no security in place - all of my variables defined are accessible outside the object. For example you can pageHandlers.x = 10; and while that might be fine for some situations, for others it might be a serious flaw. You can solve that with the module pattern:

var pageHandler = (function() {
	
	var x = 1;
	var y = 2;

	return {
		get_x: function() {
			return x;
		},

		set_x: function(val) {
			x = val;
		},

		doSomething: function() {
			// Blah...
		}
	}
}());

So, plenty going on in this code example..

First, pageHandler is not set to a function, it’s set to the result of calling a function - see the brackets at the end that call this function immediately. So this returns an object literal that contains these 3 methods. Each of the 3 methods has access to x & y because functions defined inside functions have access to objects/variables defined in the outer scope. It is possible with this code to do pageHandler.set_x(10) but not pageHandler.x = 1, because x & y are just variables defined inside that function - they are not accessable outside of it.

Now, the really cool thing about this code is that the inner object (the object literal we are returning) has a longer lifetime than the function inside which it is defined. Because the inner function makes use of the outer function, everything in that outer function continues to live, this is called a closure. At least, this is my quite possibly somewhat simplified view of a closure. It’s a great way to create modular objects with private/public methods.

Did I mention the book’s really good?

There’s a lot more such as dealing with inheritence effectively in the book. I’m pretty excited by this now, and my view of javascript as a dull and extremely basic langauge that can’t even do classes has been changed thanks to this book!

I was debugging some long running queries the other day, using SQL Server profiler (BTW - here is a very decent free equivalent of this for those of you using SQL Express - which doesn’t come with the profiler: sqlprofiler ) .

Anyhoo, one thing that has always been a pain for me is that the output you get from a sql_exec command - which is how most ORMs execute SQL against the database. Take this example command:

exec sp_executesql N'SELECT [t1].[CableSweepChangeId], [t1].[FrontEndId], [t1].[FrontEndPortNum], [t1].[IncomingTime], [t1].[Description], [t1].[ChangePoint], [t1].[OldLength], [t1].[NewLength], [t1].[EquipmentId], [t1].[DataLinkStateChange], [t1].[IsLink], [t1].[IsConnection], [t1].[IsLaptopChange], [t1].[EquipmentPortNum], [t1].[EquipmentName], [t1].[CableId], [t1].[CableLabel], [t1].[InstallType], [t1].[ConnectionSide]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[IncomingTime] DESC) AS [ROW_NUMBER], [t0].[CableSweepChangeId], [t0].[FrontEndId], [t0].[FrontEndPortNum], [t0].[IncomingTime], [t0].[Description], [t0].[ChangePoint], [t0].[OldLength], [t0].[NewLength], [t0].[EquipmentId], [t0].[DataLinkStateChange], [t0].[IsLink], [t0].[IsConnection], [t0].[IsLaptopChange], [t0].[EquipmentPortNum], [t0].[EquipmentName], [t0].[CableId], [t0].[CableLabel], [t0].[InstallType], [t0].[ConnectionSide]
    FROM [CableChangeDetail] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]',N'@p0 int,@p1 int',@p0=0,@p1=15

Now, if I want to tweak this or play around with it, it’s not much fun having it in this format.. I would prefer it in a format with all those params as DECLARE statements at the top. Which is why I wrote, and present to you now….. SQLEXEC FORMATTER!!!

http://execsqlformat.com

Here it is in action:

At the moment the copy to clipboard button is a bit bad at formatting the text, but I’ll sort that one day, and you can always just copy the text manually.

Enjoy!