Subsonic makes working with stored procedures and your database simple. This writeup was stolen from Kevin Fricovsky - thanks Kevin!
Getting Started
The SubSonic team obviously decided right out of the gate that allowing developers to use stored procedures was a main concern. SubSonic doesn't force you or limit you to make use of the generated ORM data access layer. That's right, believe it or not ActiveRecord doesn't solve all your problems. Sometimes a good 'ol stored procedure is the best answer to getting the data out of your system and back to the user.
It's all about Provider Configuration
Most of this information is already available on the configuration options SubSonic documentation but it's not very clear for the beginner. So I'm going to try and clear some of this up. But let me first just say — out of the box SubSonic is stored procedure friendly. You almost don't have to do anything for SubSonic to generate the DAL for your stored procedures for you. Yup, this basic provider configuration would generate generate a wrapper for all your stored procedures. Below is an example of the least information you need to provide for SubSonic to generate your DAL including a wrapper for your stored procedures. (Note: this example makes reference to my local ScribeEngine cms project).
Example #1
<SubSonicService defaultProvider="ScribeEngine">
<providers>
<add name="ScribeEngine"
type="SubSonic.SqlDataProvider, SubSonic"
connectionStringName="ScribeEngineDB"
/>
</providers>
</SubSonicService>
With the above SubSonicService provider configuration I would get all the SubSonic ORM goodness and a wrapper static class called SPs for all my stored procedures. This is where you'll find static methods for all your storec procs. If I wanted to make reference to a stored procedure in my database, the below example code is all you need to provide. For this example, the name of my stored procedure here is "usp_User_GetAllByInitial" - I'll be changing this later in the tutorial to show off some other configuration options.
Example #2
DataSet ds = SPs.usp_User_GetDetailedReportStats(userId).GetDataSet();
All the parameters you've specified for your stored procedure are now parameters on the static method. In example #2 the stored procedure expected an integer parameter so the generated static method signature emulates the design of your stored procedure - how cool is that? Now, you really don't need to do anything else beyond this if you don't want to. But some people want further customization and that's where we'll go next. Note: I wanted to make note that the SubSonic generated methods for your procs return much more than just DataSets. Take a look at the documentation to learn more but you can return DataReaders, ExecuteScalar, retrieve output params, etc, etc. It essentially does everything you'd expect from your stored proc wrapper.
I want more for my no money
The above works just rosy for most people but like many developers you want things a bit cleaner. I don't like the "usp" prefix on the method name. So let's get rid of that.
Example #3
<SubSonicService defaultProvider="ScribeEngine">
<providers>
<add name="ScribeEngine"
stripTableText="ScribeEngine_"
type="SubSonic.SqlDataProvider, SubSonic"
connectionStringName="ScribeEngineDB"
generatedNamespace="ScribeEngine.Model"
stripSPText="usp_"
/>
</providers>
</SubSonicService>
In the above example you'll see a refernce to stripSPText which tells SubSonic to remove the prefix usp_ from the generated stored proc method names. So now when you make reference to the SPs wrapper class you'll have this clean interface...
Example #4
DataSet ds = SPs.User_GetDetailedReportStats(userId).GetDataSet();
Ok, we're getting better now but I'm still not completely happy here. Maybe I don't like the generated class name SPs. No problemo bucko! We can change that with yet another configuration setting "spClassName"...
Example #5
<SubSonicService defaultProvider="ScribeEngine">
<providers>
<add name="ScribeEngine"
stripTableText="ScribeEngine_"
type="SubSonic.SqlDataProvider, SubSonic"
connectionStringName="ScribeEngineDB"
generatedNamespace="ScribeEngine.Model"
stripSPText="usp_"
spClassName="MyStoredProcedureWrapperClass"
/>
</providers>
</SubSonicService>
Nice, so now instead of the short and simple SPs class name you have the much more verbose MyStoredProcedureWrapperClass but hey, you got your way and that's what SubSonic is all about - making you happy. But damn it I still want more. Alright Mr. Picky. Let's give you everything...
Example #6
<SubSonicService defaultProvider="ScribeEngine">
<providers>
<add name="ScribeEngine"
stripTableText="ScribeEngine_"
type="SubSonic.SqlDataProvider, SubSonic"
connectionStringName="ScribeEngineDB"
generatedNamespace="ScribeEngine.Model"
stripSPText="usp_"
spClassName="SPs"
includeProcedureList="*"
excludeProcedureList=""
useSPs="true"
extractClassNameFromSPName="true"
/>
</providers>
</SubSonicService>
Wow, okay that's a lot more than what you saw in Example #5. Let's start with the simple stuff first. The includeProcedureList option does what you think it would do — allows you to specify what stored procs to include. It's sister config value excludeProcedureList does exactly the opposite - let's you specify which stored procs to exclude. Not too shabby, right?
Hmm...what's with that useSPs config setting? Well, maybe you don't want to generate a static wrapper class for your stored procs - then just say "false" here and your stored procs won't be abstracted into the SubSonic generated class. But we're not done yet, we still have one more configuration value you should be concerned with and this one is a personal favorite although I think there's a bug with the code generation and the configuration value...sorry. Don't let that scare you though...it works just dandy otherwise. Let's take a look at extractClassNameFromSPName and see why we may want to use it.
An organized mind is a simple mind
The configuration option extractClassNameFromSPName allows the developer to "attach" static references to stored procedures on the generated Model classes. So, instead of having to use the generated static wrapper class, I will now have the option to reference my User class to execute the above stored procedure. Why would you want to do this? Well, it makes for better organization. If the stored proc is used for User related forms, reports, etc - then why not have it easily accessible on the User class. If the config option value is set to false SubSonic will ignore this altogether. So, if you want this functionality we have one thing we need to do first and this is the "bug" I believe exists. What we need to do is rename our stored procedure from usp_User_GetDetailedReportStats to _User_GetDetailedReportStats, and yes I need that underscore prefix in the name - this is what I believe the bug is but maybe I'm wrong SubSonic guys? Either way, for now ... you need it in there. Now, with the renamed stored procedure and the above provider configuration from Example #6 we should be all set. So what happens?
Good question. SubSonic will attempt to match the '_CLASSNAME_" section of the stored procedure with a generated class name from your Domain Model and in this case that represents my ScribeEngine_User table in my database. An important note here is that I have set the configuration option for stripTableText="ScribeEngine_" which is required for my database here otherwise SubSonic would try to match on "ScribeEngine_User" versus just "User". So my stored proc looks like this "_User_" and SubSonic will find that match, and generate a partial class with a static method available named "GetDetailedReportStats". So here's the naming pattern you need to follow for SubSonic to generate this for you "_CLASSNAME_METHODNAME". If SubSonic doesn't find a match it then places the stored proc static method back into the generated SPs static class - which is what you'd expect because some procs you may want to match and others you may not want to match. This is an example of what you'll get...
Example #7
namespace ScribeEngine.Model{
public partial class SPs{ //non-matching procs methods here }
public partial class User
{
public static StoredProcedure GetDetailedReportStats(int userId){
SubSonic.StoredProcedure sp =
new SubSonic.StoredProcedure("_User_GetDetailedReportStats" , DataService.GetInstance("ScribeEngine"));
sp.Command.AddParameter("@userid", initial,DbType.Int);
return sp;
}
}
}
And here's how you can use it in your code...
Example #8
DataSet userReport = User.GetDetailedReportStats(100).GetDataSet();
Conclusion
It's clear the SubSonic core developers are database centric and were very focused on providing the greatest flexibility when it came to mixing code generation and flexibility. SubSonic provides the developer with enough options to make use of the great out of the box ORM capabilities but also makes extending your model to incorporate all the existing stored procedures you may have in your system very easy. There's often a debate when it comes to stored procedures and ORM. For me it comes down to time and flexibility. Sure, stored procedures can be overused, and are often misused, but when implemented correctly stored procedures offer an excellant tool set for complicated queries that just don't make sense written in your favorite ORM tool set. Just don't put any business logic in there...please.
