Here is a link to my reply on the SubSonic Community Forums to a thread on how to incorporate SubSonic DAL into a DNN Module without having to modify the web.config file. I figured it would be useful here because I had a bit of a time figuring this out.

To start off, if you haven't seen or played with the Club Starter Kit, (http://www.codeplex.com/ClubStarterKit), check it out. I believe it offers one of the nicest non-DotNetNuke starter kits out there, and has a lot of great mini applications that would make nice additions to DotNetNuke, including a very nice photo album, team/statistics tracking application, blog, calendar, news, and more. All of which is wrapped around a very nice Subsonic DAL, which by the way uses the separate assembly approach and thus offers the best example for how to approach creating a Subsonic DAL for DotNetNuke.

To recap what I did (for my test app):

Following the practice I found in the Club Starter Kit, I created a new Class Library project, named it ACIA.Data, added this to my DotNetNuke 'classic' web application project solution (which includes the BuildSupport assembly which compiles all assemblies into the web site's \Bin folder... another topic but important for module developers). For each suite of modules I develop, I always start with an architecture whereby I have my Business code in one class and reference this from each module needing to access the database. My new architecture retains the business class, and possibly the BusinessClass.SqlDataProvider class associated with theBusiness class, but adds the new 'Data' Assembly for all Subsonic data access calls like so:

Solution DotNetNuke.ACIA
|_  ACIA.Business:  Controller & Entity Classes Generated by Codesmith... Custom Controllers are named EntityManager..
|_  ACIA.Business.SqlDataProvider  SqlDataProvider generated by Codesmith (mostly).
|_  ACIA.Data: Subsonic DAL Assembly, generated with Subsonic.exe.
|_  ACIA.Database: SQL Database project to store scripts, etc.
|_  ACIA.Org: Actual Module, Web Application project. 

Then, I set up the Subsonic Command line tool as per instructions in the Club Starter Kit (there is also a great video on this on the Subsonic web site) and generated my code into the \Generated folder just like Club Starter Kit. Another upside to this method is that only a single app.config file necessary in the DAL assembly's root folder.

In my case, I wanted to limit the amount of code I generated to the objects in my module, so my entire app.config looked like:

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

Once code generation was working OK, I created a new class in my ACIA.Data project ... named it Application ... Created new method in the class InitializeProvider:

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 my test method in my module:

        Private Sub TestSubSonic()

            NCIGF.Data.Application.InitializeProvider()

            Dim committee As Database.Committee = New Database.Committee(CommitteeID)

            Debug.WriteLine(committee.Name)

        End Sub

Having to call InitializeProvider before using Subsonic isn't so bad, and ultimately could just be put into a base class in my module project and never be messed with again: the import thing here is that I made no configuration changes to my web.config and was indeed able to exercise my Subsonic Dal.