Using tools is worth it (even with SPs)
Not exactly ORM tool, but you can use some other data access tool that will make it really easy for you to call those stored procedures and especially materializing their results. This is where these tools will help you greatly. Think of all the things you have to do to use SPs:
- creating connections and commands
- handling connection state
- manage strings related to stored procedure names and their parameters (especially when someone goes and changes something)
- execute calls and probably convert them to some object instances
- etc.
And all of this is very repetitive work, you can quite easily avoid.
I favour stored procedures over ORMs, because as soon as you start writing a bit more complex queries that access several tables and join them in any sort of ways, you're into a big pile of trouble and headache. Complex stored procedures usually take much less time to write compared to optimisations and workarounds that you have to do in an ORM to make it snappy (or at least comparable to some degree to an actual stored procedure call). To really optimise some call you have to know ORM tool through and through. Believe me I've been using Entity Framework quite a bit (including the great EF Extensions library that simplifies things enormously).
Anyway. I suggest you take a look at BLToolkit, that is more a DAL tool than an actual ORM tool. I'm using it to call my stored procedures and easily materialize their results.
This tool will actually make it really simple for you to use stored procedures transparently. And if you use T4 template I've also written, you can easily avoid magic strings in your code (stored procedures' names and parameter names). Template automatically imports stored procedures and creates classes with methods that take strong type parameters (C# equivalents to those of your SP parameters).
Your SPs are a bit strange since you have all CRUD operations in a single SP. You could change my T4 template to create all four methods for each SP @Mode call if you wanted to. This would get you the nice:
User newUser = db.User.Create(...);
User existingUser = db.User.Get(...);
db.User.Delete(...);
User updatedUser = db.User.Change(...);
If you don't know what I'm talking about, you can check out my linked blog post where I explain the problem and also solve it with this T4.
