Strongly Typed Stored Procedures Using Subsonic

0 comments suggest edit

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 classj 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!

What I need is a strongly typed stored procedure. Something that’ll tell me which parameters to pass and will break at compile time if the parameters change in some way.

Subsonic can help with that. In general, Subsonic is most productive when combining its code generation with its dynamic query engine and Active Record. But sometimes, your stuck with Stored Procedures and want to make the best of it. Subsonic, via the sonic.exe command line tool, can generate strongly typed stored procedure wrappers saving you from writing a lot of boilerplate code.

I recently just finished updating Subtext to call all its stored procedures using Subsonic generated code. This post will walk you through setting up a toolbar button in Visual Studio.NET 2005 to do this, using Subtext as the example. This pretty much follows the example that Rob set in this post.

First, I made sure to put the latest and greates sonic.exe and SubSonic.dll in a known location. In Subtext, this is the dependencies folder, which on my machine is located:

d:\projects\Subtext\trunk\SubtextSolution\Dependencies\

The next step is to create a new External Tool button by selecting External Tools…from the Tools Menu.

External
Tools...

This will bring up the following dialog.

External Tools
Dialog

I filled in the fields like so:

  • Title: Subtext Subsonic SPs
  • Command: D:\Projects\Subtext\trunk\SubtextSolution\Dependencies\sonic.exe
  • Arguments: generatesps /config “$(SolutionDir)Subtext.Web” /out “$(SolutionDir)Subtext.Framework\Data\Generated”
  • Initial Directory: $(SolutionDir)

This tells Sonic.exe to find the Subsonic configuration within the Subtext.Web folder, but generate the stored procedure wrappers in a subfolder of the Subtext.Framework project.

With that in place, I then created a new Toolbar by selecting Customize from the Tools menu which brings up the following dialog.

Customize
Dialog

Click on the New… button to create a new toolbar.

New
Toolbar

I called mine Subsonic. This adds a new empty toolbar to VS.NET. Now all I need to do is add my Subtext Stored Procedures button to it. Just click on the Commands tab.

Customize
Commands

Unfortunately, the External Tools command is not named in this dialog. However, since I know the first command is the one I want (it’s the same order as it is listed in the Tools Menu), I drag External Command 1 to my new Subsonic toolbar.

Subtext SPs
button

So now when I make a change to a stored procedure, or add/delete a stored procedure, I can just click on that button to regenerate the code that calls my stored procedures.

Found a typo or error? Suggest an edit! If accepted, your contribution is listed automatically here.

Comments

avatar

9 responses

  1. Avatar for DotNetKicks.com
    DotNetKicks.com May 29th, 2007

    You've been kicked (a good thing) - Trackback from DotNetKicks.com

  2. Avatar for Jan Bannister
    Jan Bannister May 29th, 2007

    Excellent work on the SubSonic project mate but I think that you're giving Stored Procedures a bad name. Sure they have been mis-used and will continue to be misused but they have there place and saying your 'stuck' with them make them sound like legacy stuff you should try and steer clear of.
    The rails movement has gained great traction because it took one more person out of the stack needed to build an application but it has rendered the DB almost defunct by reducing it to nothing more than a persistence medium.
    DB offer lots of power and moving or copying that power fully or partially to the 'middle' teir should be thought about carefully.
    A key place where SPs are great is in complex updates theat have to happen on the server atomicly. If the transaction is managed from the client end the overheads are higher, network partitioning can cause lockups and the intent of an application gets smeared across physical and execution boundries.
    I guess what I'm saying is: don't throw the baby out with the bathwater.

  3. Avatar for Dan Maharry
    Dan Maharry May 29th, 2007

    Wow, that's really cool but now I'm stumped. I can't decide which ActiveRecord implementation I should go with for my Apps and Websites. Subsonic for instance just mentions websites but there's nothing to say that I couldn't use the sprocs and table wrappers it generates for a windows service or app is there? Or maybe NHibernate \ ActiveRecord from the Castle Project or even the EDF coming post-Orcas with a bit of blinq to fill in the gap. The major problem is not having the time to try them all out. Any thoughts or suggestions?

  4. Avatar for kevin
    kevin May 29th, 2007

    Phil,
    Nice post. Like you said, very similiar to Rob's video - and although video's are sometimes good for quick tutorials, I don't think they always work well for displaying code tutorials. Having it here in text will be beneficial.
    I'm working on weekly SubSonic tutorials over at scribesonic.com.
    This week I'm actually covering strongly typed params using subsonic as I segway into using the Query Tool. (Not published yet though.)
    There should be a repository somewhere on one of the many subsonic sites (rob's blog, subsonic forums, subsonic codeplex, google code) for tutorial posts like yours and mine, and others.
    Keep it up!

  5. Avatar for Tod Birdsall
    Tod Birdsall May 29th, 2007

    Hi Phil,
    Thanks for sharing. I keep meaning to give Subsonic a try. My problem is that I am addicted to CodeSmith and you can still get ver 2.6 for free. I have a set of CodeSmith templates that I live by. Similar to your Sonic solution, simply re-generate my code for specific tables whenever the meta data changes.

  6. Avatar for Haacked
    Haacked May 29th, 2007
    but I think that you're giving Stored Procedures a bad name.


    Perish the thought! I love stored procedures. Don't get me wrong. For probably 99% of projects out there, they get the job done.
    I just happen to be on two projects (home and work) that require cross database support. An unusual requirement for most developers. Stored Procedures just aren't as portable as a dynamic query engine.
    However, if you're using Subsonic, you get so much more out of it if you put SPs aside and immerse yourself in its code generation and Active Record. That's all I'm saying. :)

  7. Avatar for Scruffy-Looking Cat Herder
    Scruffy-Looking Cat Herder May 29th, 2007

    MS DAAB Is Eeeeviiiiil

  8. Avatar for mike
    mike October 19th, 2007

    Hi Phil,
    Thank you for your blog. Im sure it has been very helpful to a lot of people.
    I do have a question about subsonic's ability to generate stored procs... our db is segregated into multiple schemas and we also had the stored procs generated. an example of a stored proc in our db is named people.Employee_Insert
    when i check on the code that was generated by subsonic, it only uses 'Employee_Insert' as the name of the stored proc, thus generating an error saying the stored proc cannot be found.
    I would like to know if there is any way to configure sonic.exe to include the schema name of the stored proc when it does its generation.
    I appreciate you help in this one..
    thanks,

  9. Avatar for Dola
    Dola January 20th, 2008

    Hi Sir, Good Morning!
    Sir, I have doubt that How can I reset(update) the Subsonic.dll in my webapplication. Becoz of I already configure the .dll, but now I added some SP's and New DataTables to the Database, Now I want to reconfigure the Subsonic.dll in my WebApplication. So Can you please help me to how I am reconfig the Subsonic.dll with Modified Database.