One DB call per page load and stored proc code generator

A couple of yours ago I used to be an ORM fan. What a beautiful idea: abstract from the storage and have your objects saved/loaded for you. Your code clean and heaps of time saved.

I’m not an ORM fan any more. I’m not going to discuss why, there are tonnes of articles out there on the subject. But if I move to another DAL implementation I would like to keep the [clean code] and the [saved time] part.

My next (WEB) project I’d like to build on [one DB call per page] principle. Example: if you load order with details you select from 2 tables (2 DB requests). Plus authorisation. Plus session. Another example: you save order with details (parent object with collection of child objects), then load whatever you display on next page. Plus authorisation. Plus session. You make multiple DB requests for every page load.

What about calling one stored procedure, passing multiple values/collections to it, then getting multiple recordsets and having all the work done just in one DB call? Since introduction of table-valued parameters (a long time ago) it is a straight-forward task. To keep [clean code] and the [saved time] though, I need strong-typed representation of recordsets going in and out of the stored proc. This is the problem: I was not able to find a code-generation library that satisfies this requirement.

Let’s clarify the requirement: I want a tool than generates a function to call stored proc, all return recordsets being strong-types, and also all table-typed arguments being strong-typed.

I tried a couple of code generators, found the one closest to what I need and buit my version on top of it. Please note that it is just a first prototype. It have not been used in any projects, not even experimental and of course not in production. Hopefuly one day I will give it a try. Download it here.