I originally posted most of this in 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 recap what I did (for my test app):
Started with 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).
Set up the Subsonic Command line tool as per instructions in the Club Starter Kit (http://www.codeplex.com/ClubStarterKit) (which is a great starter kit not mentioned many places on Subsonic Community which uses Subsonic very nicely). Generated my code into the \Generated folder just like Club Starter Kit. Using this method, only a single app.config file necessary in the Assembly's root folder.
In my case, I wanted to limit the amount of code I generated to the objects (tables only) 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" />
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 SubsonicProvider
''' <summary>
''' Initialize Subsonic for the default provider.
''' </summary>
''' <remarks></remarks>
Public Shared Sub InitializeProvider()
InitializeProvider("<ConnectionStringNameOne>")
InitializeProvider("<ConnectionStringNameTwo>")
End Sub
''' <summary>
''' Initialize Subsonic for given Provider: Convention is to use same name
''' for Connection String and Provider name.
''' </summary>
''' <param name="ProviderName"></param>
''' <remarks></remarks>
Public Shared Sub InitializeProvider(ByVal ProviderName As String)
If DataService.ProviderCount = 0 Then
DataService.Provider = New SubSonic.SqlDataProvider()
DataService.Providers = New DataProviderCollection()
Dim provider As SubSonic.DataProvider = DataService.Provider
Dim config As System.Collections.Specialized.NameValueCollection = New System.Collections.Specialized.NameValueCollection()
config.Add("connectionStringName", ConfigurationManager.ConnectionStrings(ProviderName).ConnectionString)
provider.Initialize(ProviderName, config)
provider.DefaultConnectionString = ConfigurationManager.ConnectionStrings(ProviderName).ConnectionString
provider.GeneratedNamespace = "Data"
DataService.Providers.Add(provider)
End If
If DataService.Providers.Item(ProviderName) Is Nothing Then
Dim provider As New SubSonic.SqlDataProvider()
Dim config As System.Collections.Specialized.NameValueCollection = New System.Collections.Specialized.NameValueCollection()
config.Add("connectionStringName", ConfigurationManager.ConnectionStrings(ProviderName).ConnectionString)
provider.Initialize(ProviderName, config)
provider.DefaultConnectionString = ConfigurationManager.ConnectionStrings(ProviderName).ConnectionString
provider.GeneratedNamespace = "Data"
DataService.Providers.Add(provider)
End If
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.