Phil Haack just posted an update about the benefits of moving to Subsonic as a way to get strongly-typed stored procedures. He includes this bit:

I don’t know about you, but I find it a pain to call stored procedures from code. Either I end up writing way too much code to specify each SqlParameter explicitly, or I use a tool like Microsoft’s Data Access Application Block’s SqlHelper class to pass in the parameter values, which requires me to remember the correct parameter order (it actually supports both methods of calling a stored procedure). What a pain!

Now, I'm not upset that Subtext is moving away from the Microsoft Data Access Application Block. In fact, I couldn't be happier. Subsonic makes things even easier than ADO.Net because you can point it at a connection string and it auto-generates this stuff (and can regenerate appropriately in the event of changes). So Subsonic is the bees knees as far as I'm concerned.

Still, these kinds of statements make me crazy—mostly because I know that Phil is a sharp guy and representative of clued-in developers in general. Here's the thing: ADO.Net has strongly-typed stored procedure access already! Which is why I hate the MS DAAB. If it weren't for this piece of misbegotten code, more developers would have taken the time to actually learn ADO.Net and use it. Subtext could have had this functionality with the move to the .Net Framework 2.0.

And just to be absolutely clear, even if we had done so, we'd still want to implement Subsonic now. I mentioned the auto-generating and updating part, right?

Anyway, here is what it'd take to add a single strongly-typed stored procedure using ADO.Net's built-in adapters.

First, add a DataSet object to your project.

 Next, drag the stored procedure onto the design surface.

 Finally, add the code.

QueriesTableAdapter qa = new QueriesTableAdapter();

return (int) qa.InsertEntry(entry.Title
                          , authorId
                          , entry.Body
                          , (int)entry.PostType
                          , entry.Description
                          , BlogId
                          , entry.DateCreated 
                          , (int) entry.PostConfig
                          , entry.EntryName
                          , entry.DateSyndicated
                          , 0);

Note that this is truly strongly-typed.

Adding additional stored procedures is a matter of dragging and dropping them onto the designer surface.

Easy Schmeasy.

I have no idea why developers find it easier to use the MS DAAB than simply using ADO.Net itself. You don't have to add any new dependencies and you have some pretty sophisticated designers that are a) way more paranoid than anything a developer is going to write on their own and b) designed for extensibility with all the methods marked virtual and the classes marked partial.

Now, there's some quirks of this code that may need to be hashed out for something that's using the provider model to do data access. Altering the connection string, for example, takes some care if you aren't going to leave it fully referenced in the app.config (or web.config).

<add name="Subtext.Framework.Properties.Settings.SubtextData"
       connectionString="..." />

Instead of:

<add name="subtextData" connectionString="..." />

Even so, the partial class structure makes it a simple matter to add an initializer method that overrides the generated connection string if you truly need the complexity. And you only have to do that once to cover all your stored procedures.