I couldn't agree more with you about Subsonic!
Subsonic literally saved my most recent module development effort! Until now, I have followed all of the 'official' DNN Module development doctrine, most notably using the DNN DAL with my own codesmith templates I modified from DNN Jungle's templates provided many years ago. In any event, I started this module like any other, and development went smoothly again, like any other, until the need (like any other) to start renaming, adding, and removing fields came along. This brought my work to a virtual stand-still as every change required the usual modify in three to five separate stored procedures, modify in the SqlDataProvider, modify in the DataProvider, modify in the Controller class and finally in the Info class. All for what??? What a waste of time!
Once I switched out my 'classic' DNN DAL with Subsonic, I was able to rename all of the audit fields in all of my entity tables, regenerate the code, and fix any references to these audit fields in my code to the new names, in under 15 minutes. I could never have done this before, it would have taken the better part of several hours to regenerate all the stored procedures, etc. Managing so many moving parts just became impractical... with subsonic, there are essentially NO moving parts ... simply design your database schema ... regenerate, and wallah!
Following is how I did it:
Following instructions found on the Codeplex Club Starter Kit 3.0 Site similar to the Subsonic tutorial video "Using The Command Line Tool" I configured subsonic to generate it's classes into my module's business assembly's Generated folder. Following the CSK3 pattern, I added an app.config file to my business assembly. The app.config is used by the subsonic build provider to initialize the subsonic code generation, and it does not need to be distributed with the module. Following is how I configured mine, which I am providing to show how I limited subsonic to ONLY generate entities, query and stored procedure wrappers for objects managed by my module:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section name="SubSonicService" type="SubSonic.SubSonicSection, SubSonic" allowDefinition="MachineToApplication" restartOnExternalChanges="true" requirePermission="false"/>
</configSections>
<connectionStrings>
<add name="SiteSqlServer" connectionString="Data Source=.; Initial Catalog=DotNetNuke4;Integrated Security=True" providerName="System.Data.SqlClient" />
<!--AttachDBFileName-->
</connectionStrings>
<SubSonicService defaultProvider="SiteSqlServer">
<providers>
<clear/>
<add name="SiteSqlServer" type="SubSonic.SqlDataProvider, SubSonic"
connectionStringName="SiteSqlServer" fixPluralClassNames="false"
spClassName="SPs" generatedNamespace="Database"
stripTableText="ACIA_"
includeTableList="ACIA_Committee,ACIA_Media_Files,ACIA_Meeting, ACIA_MeetingCommittee,ACIA_MeetingRSVP,ACIA_Organization,ACIA_UserOrganization"
includeProcedureList=""
viewStartsWith=""
/>
</providers>
</SubSonicService>
</configuration>
Next, I DELETED all the Info classes I created with Codesmith, leaving all of my standard controller classes also generated by codesmith, and replaced all references to the codesmith generated info classes with the virtually identical subsonic generated activerecord classes.
A big hurtle in my own path to integrating Subsonic into my DotNetNuke modules was the fact that there was very little guidance on using subsonic without modifying configuration. I didn't want to have to modify DotNetNuke's web.config file in any way as that would limit my ability to distribute a module should I wish to, and I had to figure out how to do this. An answer was found in the subsonic forums that the provider could be programmatically initialized. I created the following simple class:
Imports SubSonic
Imports System.Configuration
Public Class Application
Public Shared Sub InitializeProvider()
DataService.Provider = New SqlDataProvider()
DataService.Providers = New DataProviderCollection()
Dim provider As DataProvider = DataService.Provider
Dim config As System.Collections.Specialized.NameValueCollection = New System.Collections.Specialized.NameValueCollection()
config.Add("connectionStringName", ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString)
provider.Initialize("SiteSqlServer", config)
DataService.Provider.DefaultConnectionString = ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString
DataService.Provider.GeneratedNamespace = "Database"
DataService.Providers.Add(provider)
End Sub
End Class
Then, in the constructor for each controller in my application, I simply call the InitializeProvider above, and that was it. Getting back to my module changes, in my controller classes, I ended up with an approach very similar to what you can find in the Club Starter Kit 3.0's data access layer, using the Subsonic Query tool to create generic Create, Read, Update, and Delete queries.
For my paged results query based upon the Core User Admin Paged results stored procedure, I ended up having to continue using stored procedure based paging for my module as my client was still using SQL Server 2000. Subsonic has a powerful stored procedure wrapper which made this a breeze as well, in addition to another benefit of how easy it was to work with output parameters, in this case needed to identify the total number of records for pagination of the results.