Stored Procs vs ... not Stored Procs (ORM)

Over the last few years I had believed that the general consensus was that stored procedures had become largely irrelevant for business logic (CRUD) DB interactions. I believe this would have a lot to do with the large increase in popularity of ORM's, Hibernate and Linq-to-Sql in particular. I understand they may still be necessary for reporting/data import/etc.

However it seems that there are a large number of people still believe stored procedures are the better option. Each to his own I guess but they seem to come up with some strange reasoning behind their decisions which I want to explore. Basically I wanted to outline my own experiences by looking at different aspects and see if I am missing the point somewhere. In general I much prefer to provide access to a database via a service layer.

Note: I'll specifically talk about ORMs here but inline sql is just as applicable (if not as desirable though :) .

Performance For all intents and purposes ORMs are just as performant as stored procs. Batching queries tend to make up for any shortfall in database round trips. Stored procedures that manipulate large datasets can be tuned more effectively. I wouldn't even attempt to do this with code. Basically I feel manipulating large datasets is one of the only reasons to actually use stored procs. This is where the DBA's expertise comes into play.

Security Not sure why anyone would think that stored procedures are more secure than direct table access. Is it that hard to set the correct permissions on DB objects? It always scares me when developers have access to live databases. I used to work in a government department and you needed written permissions from the "business owner" as well as the head of the deployment team if you wanted to get ANY information from a live database/application.

This way we only needed to set permissions on 3 logins (sa, application, reporting).

Scared about SQL injection attacks because those lazy dev's who don't sanitize their input. See Testing.

Loosely Coupled Not entirely sure why people think this is a good idea for a database ie the DBA can change the underlying query/t-sql etc without changing the interface. Great. How often does this need to happen? What processes are in place for testing, version control, etc? Applications and databases are tightly coupled by nature. At the moment, if my business requirements change, I need to make at most 3 changes - DB Script/ORM/Code. I don't want to have to write a bunch of extra update scripts to change a bunch of stored proc "interfaces" as well.

Reuse of Functionality People argue that multiple clients can access stored procs which is DRYer than each client rewriting the same functionality. This scares me. Not to mention the overhead of configuring security for each client, but any shared complicated business logic NEEDS to be pushed down into the the database. Personally I find it much easier to express business rules with OO concepts etc.

Now we have DBAs with the ability to change business rules without needing to go through all that guff such as version control, peer review, comprehensive testing, documentation etc ;) I'd much rather have my rules locked down in a dll where only the qualified development teams have change rights. And yes that does require a recompile. OMG the horror of a recompile!!! With the correct processes in place, this isn't a big deal.

Script Management Personally I find stored proc buckets to be a PITA to manage. There's a reason people prefer to write business rules in Visual Studio rather then Management Studio.

Testing Last time I checked there were far better options for testing applications as a whole. Show me the equivalent stack of Unit/Integration/Acceptance/Regression/Load/Security/etc tools for testing and profiling stored procedure changes?

Portability Wow. I can't believe some people think that stored procedures are good because both a) Reuse of functionality allows multiple clients to access the same database, DRY etc and b) stored procedures can be ported to a different database. Last time I checked there was no standard stored procedure language that could be used across all database. Hell, most SQL isn't even portable. ORMs on the other hand...

Yes I know Linq-to-Sql only supports SQL Server, just use EF instead :)

Seperation of Concerns SoC... huh? I thought we were talking about databases.

Easier Refactoring WTF! Now they're really clutching at straws.

"You don't have to write inline SQL anymore!" I give up...



Another big negative I see is the fact that a lot of devs I know shouldn't be writing stored procedures. Large scale use of stored procs would require far more time from a DBA when a dev could get a lot more done with a lot less effort. Let DBAs use there expert skills where they can be put to good use.

ASP.Net MVC Pipeline – Part 1

This is Part 1 of a series of posts I hope to write exploring the Asp.Net MVC pipeline. It won’t be your typical “this is a view, this is a controller, right click add new item…” type series although it will start off fairly basic and I will attempt to simplify things where necessary.

I’ll also be exploring some best practice and testing concerns along the way.

Pipeline 101

When I talk about the pipeline, I’ll mostly be talking about the stuff that happens OUTSIDE of your views, models and controllers. You might be wondering why you would want to do that? Well Asp.Net provides quite an array of conventions that can be overridden by the developer. Ever wondered how on earth you can specify an action to receive a populated instance of one of your domain classes? Well these “extensibility points” points allow you to change this type of behaviour.

Our First Request

Like I said, I was going to simplify things :) Anyone who’s had a cursory glance at Asp.Net MVC should be able to understand what’s going on here. I won’t go into great detail for each step but there are a few things I’d like to point out.

If we look at the controller action definition, it is simply:

public ActionResult Index() { return View(); }

Other than the method name, nowhere does it specify what view to actually use. We can verify this with the following test which will fail in this scenario

[TestMethod] public void Index_Returns_ViewName_Index() { AccountController ac = new AccountController(); ViewResult viewResult = ac.Index() as ViewResult; Assert.AreEqual(viewResult.ViewName, "Index"); }

So how does the pipeline know to render the view at “~/Views/Account/Index.aspx”? Well that’s a convention of the default MVC pipeline that we can extend and control as we see fit.

This also means that the controller action and view are not tightly bound too each other. The action cares not what happens with the action result it returns nor does the view care which action its data comes from. In this way we can even replace entire parts of the pipeline e.g alternate view engines.

This separation really sets MVC apart from webforms and allows for much easier testing. Despite their appearances controllers and views can still “Do Bad Things” like make calls to Response.Redirect(…) etc. Generally from upon though :)

In Part 2 we’ll be examining probably the easiest way to manipulate the pipeline: Routes!

Vista, WMDC and Microsoft Windows Mobile Device Emulator

In the past I had always assumed that for some reason or another Vista, Windows Mobile Device Center and Windows Mobile Device Emulator just didn't work together, i.e. i could never get "Active Sync" to work. So much so that I even reverted back to using XP as my primary development environment. Lately I haven't had that option due to driver incompatibilities with my laptop so I've effectively avoided doing any real Windows Mobile development.

However I recently purchased a Samsung Omnia* and wanted to write a quick app for it. Vista syncs fine with real devices but I just couldn't get it to work with the emulator. Seems my thoughts were justified as Microsoft Device Emulator v2 is required for Vista. To my surprise I already had V3 installed (not sure whether through VS 2008 or Windows Mobile 6 SDKs) but sync still no worky...

Turns out you have to enable DMA in WMDC. Did this and saw "Connect to - DMA Default" message flash up on the emulator. Done. I thought. It didn't actually sync though and after 20 more minutes of stuffing about i still couldn't get it to work. Finally stumbled upon a post by Jason R. Shaver that states

Make sure WMDC is set to listen to connections using DMA, then uncheck it and recheck it (fixes it sometimes)

. Lo and behold everything worked...shithouse!

* "Should have bought an iPhone" I hear you say, well a) the wife has one already and i'm not that fussed by it and b) I need a Windows Mobile Device for an upcoming (real) work project :)

Let them have their cake

I have been reading a lot of blogs and forums lately with heated discussions on why you should/shouldn't use Asp.Net MVC over Asp.Net Webforms. Starting to get a little tired of it all now as it's turned into the same sort of debates that happened when RoR hit the market. Although it's fun to note that (for once) this discussion can't devolve into the obligatory microsoft == CRAP shit fight.

I'm as used to rejection as the next IT nerd, so guys if people don't want to use your beloved MVC, get over it! MVC will grow and prosper regardless of whether EVERY Asp.Net Webforms developer starts using it. Let's just concentrate on making MVC a better product instead of trying to convince the naysayers to convert.

I must admit i was wary of things like Tag Soup, dealing with raw HTML/HTTP, etc as I've mostly done Asp.Net Webforms with a little Perl and RoR on the side. But the difficulty in utilizing these concepts is nothing compared to dealing with Page Lifecycle, View State, obfuscated element ids, etc, etc, etc.

Hate for Trackback/Pingbacks :(

Is there anything more annoying in the webosphere than having to filter out trackbacks/pingbacks that just add inane noise to otherwise interesting blog comment discussions? No? Must just be me then...

I understand that blog engines provide facilities for this to happen automatically, I'm probably even responsible for some myself. I just wish there were more that "filtered" these into another section of the page by default.

A quick google search didn't unearth an easy solution for blogspot, any ideas appreciated. Not that i think they're affecting the gripping discussions going on here :)


Edit: Had a look through blogspot settings and will have a play.

Asp.Net Web Development Server Tip

Well call me slow but I just realized that the built in Asp.Net Web Development Server allows you to recompile/deploy your assemblies without the need to restart the web server. This seems to have been available since at least VS 2005 so all this time I've been debugging through pages for apparently no reason... I seem to remember that closing IE would kill the web server while debugging, which definitely isn't the case (anymore?).

What's even more interesting is that Session data is preserved across deployments. Was hoping authentication would be as well but I haven't had time to test that properly.

I'm mostly using Asp.Net MVC these days which tends to mitigate the need to do the old login->browse->debug routine. Still handy to remember though

Downloading "Setup.exe"

Why do people insist on offering downloads of their software using the annoyingly uninformative filename "setup.exe"? Case in point...

This is especially annoying if your download manager is set not to ask for download location etc.. Something along the lines of {ProductName}_{Version}.exe would be perfectly acceptable i.e bare minimum. Feel free to chuck in {Publisher}, {ReleaseDate}, _setup, etc if you feel that way inclined ;)