Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeArchived Discus...Archived Discus...Developing Under Previous Versions of .NETDeveloping Under Previous Versions of .NETASP.Net 2.0ASP.Net 2.0Using SubSonic as the DNN Data Access LayerUsing SubSonic as the DNN Data Access Layer
New Post
9/18/2007 5:40 PM

I have found SubSonic to be a great productivity tool for building DNN modules. SubSonic is a code generator that provides the majority of the data access code that your application will need.

With it, you can concentrate on building the module functionality that you need and do not need to worry about the controllers, info classes, and data providers that are the standard way of doing module development.

You lose some of the portabilty that you get with using abstract and concrete providers, but I have never taken advantage of that anyway!

If you are interested in using Subonic and DNN, then check out the SubSonic Forums or take a look at my blog series on using DNN and SubSonic at http://walkspoiled.com/Home/tabid/70/EntryID/92/Default.aspx

Jim www.walkspoiled.com
New Post
9/20/2007 7:36 AM

Part 2 of the series on using Subsonic with DNN has been posted. It shows how to deploy the autoscaffold, a super admin editor module.

The autoscaffold editor can edit data in any table configured in your dotnetnuke system.

The demo module can be installed and subsonic can be used without making changes to the web config file.

The source code is released with the module.

It could use some CSS love, if any designers can help out!

Jim www.walkspoiled.com
New Post
10/4/2007 10:37 AM

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" ?>



    <section name="SubSonicService" type="SubSonic.SubSonicSection, SubSonic" allowDefinition="MachineToApplication" restartOnExternalChanges="true" requirePermission="false"/>




    <add name="SiteSqlServer" connectionString="Data Source=.; Initial Catalog=DotNetNuke4;Integrated Security=True" providerName="System.Data.SqlClient" />



  <SubSonicService defaultProvider="SiteSqlServer">



      <add name="SiteSqlServer" type="SubSonic.SqlDataProvider, SubSonic"

           connectionStringName="SiteSqlServer" fixPluralClassNames="false"

           spClassName="SPs" generatedNamespace="Database"


           includeTableList="ACIA_Committee,ACIA_Media_Files,ACIA_Meeting, ACIA_MeetingCommittee,ACIA_MeetingRSVP,ACIA_Organization,ACIA_UserOrganization"







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"


    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.



New Post
10/7/2007 4:12 PM

Just another vote of support for SubSonic.  As mentioned above, the speed of development is amazing.  The flexibility of the DAL options is equaly impressive.

However, I was more concerned about the performance, so I did some basic testing.

Test procedures: (running on localhost)

1) Starting with a SQL table with appproximately 6,000 rows in it.  Each record is about 70kb.

2) Record the time it takes to load the entire table into a List / Collection / etc.

3) Record the time it takes to bind a datagrid to the results from #2

4) Repeat the process 30 times and average the results.

Note: I didnt record the time it would take to actually output the datagrid HTML since this would be the same regardles of the DAL process used.



Using the "DNN DAL" way:

Average Load Time: 1426 ms

Average Bind Time: 1707 ms


Using SubSonic Controller.FetchAll() into generic List

Average Load Time: 777 ms

Average Bind Time: 1450 ms


Using SubSonic Controller.FetchAll() into SubSonic's generated Collection

Average Load Time: 867 ms

Average Bind Time: 1319 ms


Using SubSonic Object.FetchAll() - as a datareader

Avearge Load Time: 0 (there is no loading since its bound directly to a datareader)

Average Bind Time: 1317 ms


Note: Before SubSonic, the total load time was over 4 seconds.  After SubSonic, the load time is as low as 1.3 seconds!  Amazing.

Beside the obvious time differences, one of the things to note here is the sheer number of options the SubSonic DAL gives you to load data.  For example, why waste time, memory, and resources loading data into a collection if you dont need to?  Why not just bind directly to a data reader?  Clearly, there are plenty of cases when you will need to have the list so you can manipulate it, but frequently you are just plopping that data into a datagrid (or drop down, etc) and theres no reason to include the extra step.   The SubSonic DAL even has a built-in method GetListItems() that only returns a key value field and a description field.  No more loading a bloated object collection just to populate a drop down list.

The bext thing about it though is NO STORED PROCEDURES.  Of course it supports SPs if you must use them, but it gives you the option of reducing the overall Lines of Code, increasing speed of development, while still increasing the overall performance of the DAL over the "DNN method".

The entire thing is open source and it supports multiple DBs, etc.  I would love for the core team to explore integrating this as a default DAL for all of DNN.  The entire core could benifit from this and performance has always been somewhat of a sore spot for DNN.

New Post
10/7/2007 11:51 PM


Have a look at this blog: ayende.com/Blog/archive/2007/06/03/On-SubSonic-amp-NHibernate.aspx. if you're more a domain driven design (DDD) fan like me, NHibernate is the best choice in .NET.  IMHO, DDD and NHibernate have a perfect match.  The more complex your business logic is, the better they will work.  The  only thing I complain is that NHibernate learning curve is very steep.

I will blog about NHibernate, Inversion of Control (IOC), DDD and how to use them in DNN if I got time.



Frank Wang
HomeHomeArchived Discus...Archived Discus...Developing Under Previous Versions of .NETDeveloping Under Previous Versions of .NETASP.Net 2.0ASP.Net 2.0Using SubSonic as the DNN Data Access LayerUsing SubSonic as the DNN Data Access Layer

These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out